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  |
wtolentino
Messages: 421
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>
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>
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.
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 #684410 is a reply to message #684409] |
Wed, 26 May 2021 15:24  |
wtolentino
Messages: 421
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
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>
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 |
John Watson
Messages: 8960
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 #684422 is a reply to message #684417] |
Tue, 01 June 2021 08:53 |
wtolentino
Messages: 421
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');
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Nov 24 08:37:46 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
