Thursday, December 1, 2011

dbms_metadata.get_ddl & ORA-31603

I wanted to allow a database user/schema called TEST (other than user with SYSDBA privileges) to have privileges to run dbms_metadata.get_ddl for any object in the database. Basically for other schemas in the database. Even though I provided execute privileges on the package and even DBA privilege to the user it still gave:

16:37:39 SQL> select dbms_metadata.get_ddl('PACKAGE','PACK1','SCOTT') from dual;
ERROR:
ORA-31603: object "PACK1" of type PACKAGE not found in schema "SCOTT"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3912
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1

The privileges required to allow a user/schema (TEST) to be able to run the package are:

grant SELECT_CATALOG_ROLE to TEST;
The most important steps is:
ALTER USER TEST DEFAULT ROLE SELECT_CATALOG_ROLE;


Just by granting SELECT_CATALOG_ROLE to the user/schema you would still get ORA-31603.
So we must alter the user and make SELECT_CATALOG_ROLE as its default role.