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;

1 comment:

bojo2112jon said...

Hi! Thanks for this information in this topic, as it's very interesting and useful for me, so I can recommend this blog to all my friends! But I know for sure that with so many scam writing agencies currently operational in the industry it may become too confusing for inexperienced customers to decide which educational company to place their trust in. There is, however, only one pro agency (see cheap research papers for sale wright here) which is trusted by thousands around the planet - it is the greatest company I've ever found!