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;

8 comments:

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!

shopify said...

business challenges In this huge sample, we recognized 171 accounting and financial services (A&FS) firms. These companies accounted for more than $100 billion in income and engaged over 160,000 professionals.

shopify said...

Here at SecurityX, our Cyber Security methodologies are based on the combination of comprehensive threat intelligence, big data analytics and advanced security automation tools

shopify said...

At Shopifyninja, we have developers and vetted professionals who put effort that allows your business to grow. shopify app development

shopify said...

time tracking software Your team can start tracking time with one click. Employees can track time toward specific tasks, projects, Work orders, clients, or locations.

Naty Grace said...

Now writing an essay doesn’t make me panic and I don’t waste my already frayed nerves. Since I turned to the guys for help with a written assignment, I have not looked for other services anymore write me a speech Excellent work, the text is well written, everything is always clear and on time. I am satisfied, professionals work.

Gina Morgan said...

Thanks for the information provided. I want to share my find, maybe it will also be useful. The best online assistant https://essaykeeper.com/ for writing student papers. I have been using the service for over a year. Very satisfied. I recommend to everyone.

Doug Warner said...

Sup dude! Our paper writers understand the correct term paper format and the required academic standards. From composing to editing and proofreading, you will get a meticulous essay relevant to your research topic.