Home » RDBMS Server » Server Administration » can select on dba_segments view but throwing error when using on a package (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production)
can select on dba_segments view but throwing error when using on a package [message #684407] Wed, 26 May 2021 14:28 Go to next message
wtolentino
Messages: 348
Registered: March 2005
Senior Member
i have this package that is throwing an error:

SQL> create or replace package sqldba.sqldba_misc_pkg as
  2    function db_size (pOwner varchar2, pSegmentName varchar2, pSegmentType varchar2) return number;
  3    procedure help;
  4  end sqldba_misc_pkg;
  5  /

Package created.

Elapsed: 00:00:00.01
SQL> show errors;
No errors.
SQL> 
SQL> create or replace package body sqldba.sqldba_misc_pkg as
  2  
  3    function db_size (pOwner varchar2, pSegmentName varchar2, pSegmentType varchar2) return number is
  4  	vSize number := 0;
  5    begin
  6  	 select nvl(sum(bytes),0) db_size
  7  	   into vSize
  8  	   from dba_segments ds
  9  	  where ds.owner	= pOwner
 10  	    and ds.segment_name = decode(pSegmentName,null,ds.segment_name,pSegmentName)
 11  	    and ds.segment_type = decode(pSegmentType,null,ds.segment_type,pSegmentType);
 12  	 return (vSize);
 13    end;
 14  
 15    procedure help as
 16    begin
 17  	 dbms_output.put_line('Package allows authorized users to get the database size of schema and/or segment');
 18  	 dbms_output.put_line('It is comprised of 1 function and 1 procedures');
 19  	 dbms_output.put_line('...');
 20  	 dbms_output.put_line('Help    - this procedure ');
 21  	 dbms_output.put_line('db_size - this function returns the size of the schema and/or segment');
 22  	 dbms_output.put_line('function db_size (pOwner, pSegmentName, pSegmentType)');
 23  	 dbms_output.put_line('db_size function has 3 parameters:  schema name,  segment name, and segment type');
 24  	 dbms_output.put_line('schema name is required, segment name and segment type is optional');
 25  	 dbms_output.put_line('Examples: ');
 26  	 dbms_output.put_line('.');
 27  	 dbms_output.put_line('.');
 28  	 dbms_output.put_line('To get the schema size');
 29  	 dbms_output.put_line('.');
 30  	 dbms_output.put_line('select sqldba.sqldba_misc_pkg.db_size(''SQLDBA'',null,null) from dual;');
 31  	 dbms_output.put_line('.');
 32  	 dbms_output.put_line('To get the schema and segment size');
 33  	 dbms_output.put_line('.');
 34  	 dbms_output.put_line('select sqldba.sqldba_misc_pkg.db_size(''SQLDBA'',''TABLE_A'',''TABLE'') from dual;');
 35    end;
 36  end sqldba_misc_pkg;
 37  /

Warning: Package Body created with compilation errors.

Elapsed: 00:00:00.05
SQL> show errors;
Errors for PACKAGE BODY SQLDBA.SQLDBA_MISC_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/5      PL/SQL: SQL Statement ignored
8/12     PL/SQL: ORA-00942: table or view does not exist
SQL> 
i know the error is because of this view dba_segments. but when using the same sqldba account when i attempt to describe or select on that view it works.

SQL> describe dba_segments;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(128)
 SEGMENT_NAME                                       VARCHAR2(128)
 PARTITION_NAME                                     VARCHAR2(128)
 SEGMENT_TYPE                                       VARCHAR2(18)
 SEGMENT_SUBTYPE                                    VARCHAR2(10)
 TABLESPACE_NAME                                    VARCHAR2(30)
 HEADER_FILE                                        NUMBER
 HEADER_BLOCK                                       NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 EXTENTS                                            NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 MAX_SIZE                                           NUMBER
 RETENTION                                          VARCHAR2(7)
 MINRETENTION                                       NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 RELATIVE_FNO                                       NUMBER
 BUFFER_POOL                                        VARCHAR2(7)
 FLASH_CACHE                                        VARCHAR2(7)
 CELL_FLASH_CACHE                                   VARCHAR2(7)
 INMEMORY                                           VARCHAR2(8)
 INMEMORY_PRIORITY                                  VARCHAR2(8)
 INMEMORY_DISTRIBUTE                                VARCHAR2(15)
 INMEMORY_DUPLICATE                                 VARCHAR2(13)
 INMEMORY_COMPRESSION                               VARCHAR2(17)
 CELLMEMORY                                         VARCHAR2(24)


SQL> select substr(user,1,20) username, count(*) cnt from dba_segments;

USERNAME                    CNT
-------------------- ----------
SQLDBA                    13245

SQL>
and i checked these are the dba_ views that are granted to sqldba:

select substr(grantee,1,10) grantee,
       substr(owner,1,10) owner, substr(table_name,1,35) table_name,
       substr(grantor,1,10) grantor, substr(privilege,1,10) privilege
  from dba_tab_privs    
 where grantee   = 'SQLDBA' 
   and owner     = 'SYS' 
   and privilege = 'SELECT' 
order by table_name;

GRANTEE    OWNER      TABLE_NAME                          GRANTOR    PRIVILEGE
---------- ---------- ----------------------------------- ---------- ----------
SQLDBA     SYS        ALL_UNIFIED_AUDIT_ACTIONS           SYS        SELECT
SQLDBA     SYS        ALL_USERS                           SYS        SELECT
SQLDBA     SYS        AUDIT_NG$                           SYS        SELECT
SQLDBA     SYS        AUDIT_UNIFIED_ENABLED_POLICIES      SYS        SELECT
SQLDBA     SYS        DBA_2PC_PENDING                     SYS        SELECT
SQLDBA     SYS        DBA_AUDIT_POLICIES                  SYS        SELECT
SQLDBA     SYS        DBA_CONSTRAINTS                     SYS        SELECT
SQLDBA     SYS        DBA_CONS_COLUMNS                    SYS        SELECT
SQLDBA     SYS        DBA_DB_LINKS                        SYS        SELECT
SQLDBA     SYS        DBA_ERRORS                          SYS        SELECT
SQLDBA     SYS        DBA_HIST_SNAPSHOT                   SYS        SELECT
SQLDBA     SYS        DBA_HIST_WR_CONTROL                 SYS        SELECT
SQLDBA     SYS        DBA_INDEXES                         SYS        SELECT
SQLDBA     SYS        DBA_IND_STATISTICS                  SYS        SELECT
SQLDBA     SYS        DBA_OBJECTS                         SYS        SELECT
SQLDBA     SYS        DBA_PENDING_TRANSACTIONS            SYS        SELECT
SQLDBA     SYS        DBA_ROLE_PRIVS                      SYS        SELECT
SQLDBA     SYS        DBA_SEQUENCES                       SYS        SELECT
SQLDBA     SYS        DBA_SOURCE                          SYS        SELECT
SQLDBA     SYS        DBA_SYNONYMS                        SYS        SELECT
SQLDBA     SYS        DBA_SYS_PRIVS                       SYS        SELECT
SQLDBA     SYS        DBA_TABLES                          SYS        SELECT
SQLDBA     SYS        DBA_TAB_COLS                        SYS        SELECT
SQLDBA     SYS        DBA_TAB_COLUMNS                     SYS        SELECT
SQLDBA     SYS        DBA_TAB_COL_STATISTICS              SYS        SELECT
SQLDBA     SYS        DBA_TAB_HISTOGRAMS                  SYS        SELECT
SQLDBA     SYS        DBA_TAB_PRIVS                       SYS        SELECT
SQLDBA     SYS        DBA_TAB_STATISTICS                  SYS        SELECT
SQLDBA     SYS        DBA_USERS                           SYS        SELECT
SQLDBA     SYS        GV_$PROCESS                         SYS        SELECT
SQLDBA     SYS        GV_$SESSION                         SYS        SELECT
SQLDBA     SYS        GV_$UNIFIED_AUDIT_TRAIL             SYS        SELECT
SQLDBA     SYS        INT$AUDIT_UNIFIED_ENABLED_POL       SYS        SELECT
SQLDBA     SYS        OBJ$                                SYS        SELECT
SQLDBA     SYS        PENDING_TRANS$                      SYS        SELECT
SQLDBA     SYS        PRODUCT_COMPONENT_VERSION           SYS        SELECT
SQLDBA     SYS        STMT_AUDIT_OPTION_MAP               SYS        SELECT
SQLDBA     SYS        SYSTEM_PRIVILEGE_MAP                SYS        SELECT
SQLDBA     SYS        UNIFIED_FGA_AUDIT_TRAIL_V           SYS        SELECT
SQLDBA     SYS        USER_INDEXES                        SYS        SELECT
SQLDBA     SYS        V_$PARAMETER                        SYS        SELECT
SQLDBA     SYS        V_$SESSION                          SYS        SELECT

42 rows selected.

i can select on dba_segments view but cannot use it on a package function. i know there is something missing but could not find it. please advise.

thank you.

[Updated on: Wed, 26 May 2021 14:45]

Report message to a moderator

Re: can select on dba_segments view but throwing error when using on a package [message #684408 is a reply to message #684407] Wed, 26 May 2021 14:47 Go to previous messageGo to next message
Michel Cadot
Messages: 67996
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your SQLDBA has most likely the DBA role but it has not the SELECT privilege directly granted to it, this explains the error in the package.
Just add it to the privileges you already granted to it.

Re: can select on dba_segments view but throwing error when using on a package [message #684409 is a reply to message #684408] Wed, 26 May 2021 15:07 Go to previous messageGo to next message
EdStevens
Messages: 1344
Registered: September 2013
Senior Member
To expand a bit on Michel's comment .... privileges that are acquired through a role do not apply within a package/procedure.
Re: can select on dba_segments view but throwing error when using on a package [message #684410 is a reply to message #684409] Wed, 26 May 2021 15:24 Go to previous messageGo to next message
wtolentino
Messages: 348
Registered: March 2005
Senior Member
thanks Michel and Ed. right it needs a direct grant. i checked on other database like UAT. it does not have a direct grants and the package compiled successfully. but it does not compile on QA they both have no direct grants on DBA_SEGMENTS view.

UAT database
SQL> select substr(grantee,1,20) grantee,
  2         substr(owner,1,10) owner, substr(table_name,1,35) table_name,
  3         substr(grantor,1,10) grantor, substr(privilege,1,10) privilege
  4    from dba_tab_privs
  5   where table_name = 'DBA_SEGMENTS'
  6  order by table_name;

GRANTEE              OWNER      TABLE_NAME                          GRANTOR    PRIVILEGE
-------------------- ---------- ----------------------------------- ---------- ----------
SELECT_CATALOG_ROLE  SYS        DBA_SEGMENTS                        SYS        SELECT
CTXSYS               SYS        DBA_SEGMENTS                        SYS        SELECT
MDSYS                SYS        DBA_SEGMENTS                        SYS        SELECT
package compiled on UAT
SQL> create or replace package sqldba.sqldba_misc_pkg as
  2    function db_size (pOwner varchar2, pSegmentName varchar2, pSegmentType varchar2) return number;
  3    procedure help;
  4  end sqldba_misc_pkg;
  5  /

Package created.

Elapsed: 00:00:00.13
SQL> show errors;
No errors.
SQL> 
SQL> create or replace package body sqldba.sqldba_misc_pkg as
  2  
  3    function db_size (pOwner varchar2, pSegmentName varchar2, pSegmentType varchar2) return number is
  4  	vSize number := 0;
  5    begin
  6  	 select nvl(sum(bytes),0) db_size
  7  	   into vSize
  8  	   from dba_segments ds
  9  	  where ds.owner	= pOwner
 10  	    and ds.segment_name = decode(pSegmentName,null,ds.segment_name,pSegmentName)
 11  	    and ds.segment_type = decode(pSegmentType,null,ds.segment_type,pSegmentType);
 12  	 return (vSize);
 13    end;
 14  
 15    procedure help as
 16    begin
 17  	 dbms_output.put_line('Package allows authorized users to get the database size of schema and/or segment');
 18  	 dbms_output.put_line('It is comprised of 1 function and 1 procedures');
 19  	 dbms_output.put_line('...');
 20  	 dbms_output.put_line('Help    - this procedure ');
 21  	 dbms_output.put_line('db_size - this function returns the size of the schema and/or segment');
 22  	 dbms_output.put_line('function db_size (pOwner, pSegmentName, pSegmentType)');
 23  	 dbms_output.put_line('db_size function has 3 parameters:  schema name,  segment name, and segment type');
 24  	 dbms_output.put_line('schema name is required, segment name and segment type is optional');
 25  	 dbms_output.put_line('Examples: ');
 26  	 dbms_output.put_line('.');
 27  	 dbms_output.put_line('.');
 28  	 dbms_output.put_line('To get the schema size');
 29  	 dbms_output.put_line('.');
 30  	 dbms_output.put_line('select sqldba.sqldba_misc_pkg.db_size(''SQLDBA'',null,null) from dual;');
 31  	 dbms_output.put_line('.');
 32  	 dbms_output.put_line('To get the schema and segment size');
 33  	 dbms_output.put_line('.');
 34  	 dbms_output.put_line('select sqldba.sqldba_misc_pkg.db_size(''SQLDBA'',''TABLE_A'',''TABLE'') from dual;');
 35    end;
 36  end sqldba_misc_pkg;
 37  /

Package body created.

Elapsed: 00:00:00.04
SQL> show errors;
No errors.
SQL> 
package compiled on QA database
SQL> create or replace package body sqldba.sqldba_misc_pkg as
  2  
  3    function db_size (pOwner varchar2, pSegmentName varchar2, pSegmentType varchar2) return number is
  4  	vSize number := 0;
  5    begin
  6  	 select nvl(sum(bytes),0) db_size
  7  	   into vSize
  8  	   from dba_segments ds
  9  	  where ds.owner	= pOwner
 10  	    and ds.segment_name = decode(pSegmentName,null,ds.segment_name,pSegmentName)
 11  	    and ds.segment_type = decode(pSegmentType,null,ds.segment_type,pSegmentType);
 12  	 return (vSize);
 13    end;
 14  
 15    procedure help as
 16    begin
 17  	 dbms_output.put_line('Package allows authorized users to get the database size of schema and/or segment');
 18  	 dbms_output.put_line('It is comprised of 1 function and 1 procedures');
 19  	 dbms_output.put_line('...');
 20  	 dbms_output.put_line('Help    - this procedure ');
 21  	 dbms_output.put_line('db_size - this function returns the size of the schema and/or segment');
 22  	 dbms_output.put_line('function db_size (pOwner, pSegmentName, pSegmentType)');
 23  	 dbms_output.put_line('db_size function has 3 parameters:  schema name,  segment name, and segment type');
 24  	 dbms_output.put_line('schema name is required, segment name and segment type is optional');
 25  	 dbms_output.put_line('Examples: ');
 26  	 dbms_output.put_line('.');
 27  	 dbms_output.put_line('.');
 28  	 dbms_output.put_line('To get the schema size');
 29  	 dbms_output.put_line('.');
 30  	 dbms_output.put_line('select sqldba.sqldba_misc_pkg.db_size(''SQLDBA'',null,null) from dual;');
 31  	 dbms_output.put_line('.');
 32  	 dbms_output.put_line('To get the schema and segment size');
 33  	 dbms_output.put_line('.');
 34  	 dbms_output.put_line('select sqldba.sqldba_misc_pkg.db_size(''SQLDBA'',''TABLE_A'',''TABLE'') from dual;');
 35    end;
 36  end sqldba_misc_pkg;
 37  /

Warning: Package Body created with compilation errors.

Elapsed: 00:00:00.05
SQL> show errors;
Errors for PACKAGE BODY SQLDBA.SQLDBA_MISC_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/5      PL/SQL: SQL Statement ignored
8/12     PL/SQL: ORA-00942: table or view does not exist
SQL> 

[Updated on: Wed, 26 May 2021 15:26]

Report message to a moderator

Re: can select on dba_segments view but throwing error when using on a package [message #684411 is a reply to message #684410] Thu, 27 May 2021 00:55 Go to previous messageGo to next message
John Watson
Messages: 8622
Registered: January 2010
Location: Global Village
Senior Member
To identify how a session acquired the privileges used (eg, grant to PUBLIC, an ANY privilege, or an object privilege) the perfect tool is dbms_privilege_capture. It is fan-bloody-tastic. Create the capture, enable it, run the problem code, generate the result, and the query the dba%used%priv% views. And dba%unused%priv views to see the grants that you should revoke.
Re: can select on dba_segments view but throwing error when using on a package [message #684415 is a reply to message #684411] Fri, 28 May 2021 08:19 Go to previous messageGo to next message
wtolentino
Messages: 348
Registered: March 2005
Senior Member
thanks John. I'll try that dbms_privilege_capture.
Re: can select on dba_segments view but throwing error when using on a package [message #684416 is a reply to message #684415] Fri, 28 May 2021 09:49 Go to previous messageGo to next message
wtolentino
Messages: 348
Registered: March 2005
Senior Member
thank you all. the DBMS_PRIVILEGE_CAPTURE helps solved our issue.
Re: can select on dba_segments view but throwing error when using on a package [message #684417 is a reply to message #684416] Fri, 28 May 2021 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 67996
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How? Show us what you got and how it helped you to solve the issue.

Re: can select on dba_segments view but throwing error when using on a package [message #684422 is a reply to message #684417] Tue, 01 June 2021 08:53 Go to previous messageGo to next message
wtolentino
Messages: 348
Registered: March 2005
Senior Member
in development and UAT database the package compile successfully. however on QA database the package throws an error. this make me thinks that there is something missing privileges on QA. the DBMS_PRIVILEGE_CAPTURE helps me solved that missing privileges by comparing the development, UAT, and QA privileges.

to begin with i use this package procedure DBMS_PRIVILEGE_CAPTURE.create_capture. there are difference methods to use but since i need only to see what is going on a single schema i use this method:
execute dbms_privilege_capture.create_capture(name => 'SQLDBA_MISC_PKG_CAPTURE', 
                                              TYPE => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT, 
                                              condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SQLDBA''');

second step was to enable that capture and verify.
execute DBMS_PRIVILEGE_CAPTURE.enable_capture('SQLDBA_MISC_PKG_CAPTURE');
select * from dba_priv_captures;

next was to run a simple query.
describe dba_segment;
select count(*) from dba_segments where owner = 'SQLDBA';

then disable the capture to prevent further logging so as to make it easier to find what you are looking for.
execute DBMS_PRIVILEGE_CAPTURE.disable_capture('SQLDBA_MISC_PKG_CAPTURE');

then generate a report to analyze the capture.
execute DBMS_PRIVILEGE_CAPTURE.generate_result('SQLDBA_MISC_PKG_CAPTURE');

next was to check on the report by doing a query on the table DBA_USED_PRIVS. from this table you can find the privileges and compare it to the privilege on the other database to see what is missing.
select * from DBA_USED_PRIVS where capture = 'SQLDBA_MISC_PKG_CAPTURE';

and when all is set clear the capture to remove the collected information that you will no longer use.
execute DBMS_PRIVILEGE_CAPTURE.drop_capture('SQLDBA_MISC_PKG_CAPTURE');

Re: can select on dba_segments view but throwing error when using on a package [message #684424 is a reply to message #684422] Tue, 01 June 2021 11:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67996
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks.
And what was the missing privilege?

Re: can select on dba_segments view but throwing error when using on a package [message #684426 is a reply to message #684424] Tue, 01 June 2021 11:24 Go to previous messageGo to next message
wtolentino
Messages: 348
Registered: March 2005
Senior Member
select any dictionary
Re: can select on dba_segments view but throwing error when using on a package [message #684427 is a reply to message #684426] Tue, 01 June 2021 12:09 Go to previous message
Michel Cadot
Messages: 67996
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, then you can remove all SELECT on DBA views as SELECT ANY DICTIONARY means SELECT on (almost) all tables and views in SYS schema.

Previous Topic: DISK_ASYNCH_IO=TRUE, FILESYSTEMIO_OPTIONS=SETALL but v$iostat.file=ASYNC_OFF
Next Topic: User process details (merged)
Goto Forum:
  


Current Time: Tue Oct 19 01:22:03 CDT 2021