Use DBMS_METADATA to extract the complete DDL (metadata) of an Oracle user, including system, object and role grants. This is handy when cloning a user or migrating it to another database.
1. User DDL
set long 999999
select dbms_metadata.get_ddl('USER','<username>') from dual;
2. System privilege grants
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','<username>') from dual;
3. Object grants
select dbms_metadata.get_granted_ddl('OBJECT_GRANT','<username>') from dual;
4. Role grants
select dbms_metadata.get_granted_ddl('ROLE_GRANT','<username>') from dual;
Tip: run set long 999999 and set pagesize 0 first so the output isn't truncated. If a grant type has no rows, you'll get ORA-31608; that just means the user has none of that type.
Comments
Post a Comment