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.
Thursday, December 1, 2011
dbms_metadata.get_ddl & ORA-31603
Posted by Apun Hiran at 6:24 AM
Labels: dbms_metadata, get_ddl, ORA-31603, oracle
Subscribe to:
Post Comments (Atom)
1 Comments:
I collected a lot of interesting things from your blog especially its discussion. From the tons of comments in your posts, I believe I’m not alone having all the enjoyment here! keep up the great work.
Post a Comment