In our production database SELECT_CATALOG_ROLE cannot be given to any normal user because of Security policy.
I am normal user and I would like to use DBMS_METADATA.GET_DDL to get the DDL's of all the objects for creation of physical data model using tool.
In absense of SELECT_CATALOG_ROLE, are there any alternate roles/grants present which can be given to the normal user so that he/she can use GET_DDL for any object in the database?
Thanks Ajay! user already has permission to DBMS_METADATA however cant see DDL of the object where he is not OWNER. If I give SELECT_CATALOG_ROLE it works but this role is not allowed to be given as per security policy.
Do you have any alternate solution.
Seems there is no other options by looking at the link you provided.
However I couldnt find any such in Oracle documentation. Are you aware of any official oracle documentation which confirms that there is no other alternative?
Another way could be by create script/procedure to generate the DDL (like we use to do in 9i) and call it in place of DBMS_METADATA. This would require granting of SELECT privs on few dictionary objects. Check below article.