Monday, October 3, 2011

Recreate GRANTS to user in oracle without dba privilege

If you want to get list of grants made to tables and you want to recreate the user permissions in a different environment, the easy way to generate a script is to use the dbms_metadata.get_granted_ddl function as described here. However, if you do not have DBA privilege on the database you will not be able to use that method. Here is a simple sql that you can use to generate a script that will work in most common situations. This just relies on the TABLE_PRIVILEGES table.
select replace('GRANT ' || decode(select_priv,'Y','SELECT','') || decode(insert_priv,'A',',INSERT','') || decode(delete_priv,'Y',',DELETE','') || decode(update_priv,'Y',',UPDATE','') || decode(references_priv,'Y',',REFERENCES','') || decode(alter_priv,'Y',',ALTER','') ||' ON '|| owner || '.' || table_name || ' TO ' || GRANTEE ||';','GRANT ,','GRANT ') from TABLE_PRIVILEGES where owner= 'OWNER' order by table_name, grantee;

No comments: