It is quite common for sites to have a set of read only (or read mostly) privileges they grant DBAs and support technicians for day to day investigation. Such a set of privileges may be similar to the role below:
create role readonly_dba; grant create session to readonly_dba; grant select any dictionary to readonly_dba; grant select any table to readonly_dba; -- and maybe a few other bits and pieces such as below (site permitting) grant execute on dbms_monitor to readonly_dba;
With such a role a user can figure out most things including the DDL that defines an object. However with such a user DBMS_METADATA.GET_DDL on an object in a different schema returns an error (output below from 10.2 but 11.2 is similar):
SQL> create user mydba identified by mydba;
SQL> grant readonly_dba to mydba;
SQL> conn mydba/mydba
SQL> -- picked a table at random - DBSNMP.MGMT_SNAPSHOT
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','MGMT_SNAPSHOT','DBSNMP') FROM dual;
ERROR:
ORA-31603: object "MGMT_SNAPSHOT" of type TABLE not found in schema "DBSNMP"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
The solution to this is documented as below but I must admit I only discovered this once I’d figured things out the hard way. A classic case of something being obvious if you already know the answer.
The DBMS_METADATA package considers a privileged user to be one who is connected as user SYS or who has the SELECT_CATALOG_ROLE role.
So now anyone Googling “ORA-31603” has a potential answer I’ll show you how this is implemented.
When faced with the error above I enabled SQL_TRACE and tried again.
SQL> exec dbms_monitor.session_trace_enable;
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','MGMT_SNAPSHOT','DBSNMP') FROM dual;
ERROR:
ORA-31603: object "MGMT_SNAPSHOT" of type TABLE not found in schema "DBSNMP"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
no rows selected
The penultimate fetch in the resulting trace file was for the statement below (reformatted for readability).
PARSING IN CURSOR #7 len=283 dep=1 uid=39 oct=3 lid=39 tim=2007863993 hv=2568162639 ad='af4fed2c'
SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$)
, XMLFORMAT.createFormat2('TABLE_T', '7'))
, KU$.OBJ_NUM
FROM SYS.KU$_HTABLE_VIEW KU$
WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192)
AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0
AND KU$.SCHEMA_OBJ.NAME=:NAME1
AND KU$.SCHEMA_OBJ.OWNER_NAME=:SCHEMA2
END OF STMT
PARSE #7:c=0,e=721,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=2007863991
EXEC #7:c=0,e=674,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=2007864789
FETCH #7:c=0,e=120,p=0,cr=14,cu=0,mis=0,r=0,dep=1,og=3,tim=2007866558
The FETCH line shows no rows were returned (“r=0”). Next I checked the definition of the view – using DBA_VIEWS as DBMS_METADATA isn’t working for other schemas 🙂
SQL> set pages 100
SQL> select text from dba_views where view_name = 'KU$_HTABLE_VIEW';
TEXT
--------------------------------------------------------------------------------
... SNIP ...
AND (SYS_CONTEXT('USERENV','CURRENT_USERID') IN (o.owner_num, 0) OR
EXISTS ( SELECT * FROM session_roles
WHERE role='SELECT_CATALOG_ROLE' ))
The role is hardcoded in the view.
After granting SELECT_CATALOG_ROLE to the READONLY_DBA role DBMS_METADATA.GET_DDL returns the desired DDL.
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','MGMT_SNAPSHOT','DBSNMP') FROM dual;
DBMS_METADATA.GET_DDL('TABLE','MGMT_SNAPSHOT','DBSNMP')
--------------------------------------------------------------------------------
CREATE TABLE "DBSNMP"."MGMT_SNAPSHOT"
( "INSTANCE_NUMBER" NUMBER NOT NULL
Success.
Lessons learned:
1) Once again – read the documentation.
2) Shying away from the 1600+ privileges (2100+ in 11.2) in SELECT_CATALOG_ROLE is not always ideal.
3) Even Oracle aren’t averse to a bit of hardcoding
ORAganism 