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;

14 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.

Lee Knight 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.

Qasim Khan said...

Someone do my assignment is a service that helps students and students who want to someone to write my assignment. They have a team of writers who are specialized in different subjects. This means that they have a writer who can write your essay on any topic that you provide them.

Anonymous said...

Cryptocurrency players have 카지노사이트 a incredible 150% up to as} $1500 welcome bonus plus an additional 150% up to as} $1500 poker bonus to take pleasure in. The international online playing market was estimated at USD 57.54 billion in 2021 and is expected to achieve USD 63.53 billion in 2022. Many of the world’s most giant brick-and-mortar casinos have invested in growing state-of-the-art digital casinos that mimic the expertise of playing in the real world. Logging into considered one of these platforms is like strolling into a real casino’s major ground. You additional agree that you will not access the Services by any means except via the interface offered by the Company for access to the Services. Creating or sustaining any hyperlink from one other web site or application to any page or performance on the Services with out the prior written authorization of the Company is prohibited.

mike buxter said...

Each student is obliged to study in order to become a good specialist who needs to understand a specific topic. All this is extremely important, therefore, the completion of the task plays a huge role. But what if you don't always have time for independent research? Contact the specialists from the company paper writer service who are leaders in the market for the provision of such services. They are actually the best!

keven john said...

You need to have the necessary privileges to execute the GRANT statement, and you should ensure that you only grant the minimum required privileges to users to maintain security and avoid granting excessive privileges.If you have to need online exam helper service.You can easily make a call and feel free to contact with our team.

James Brian said...

I really like your blog! Can you guide me about the Eyelid Surgery Dubai because i want to make my eyes like you.

Albert john said...

who has the necessary privileges to grant permissions to other users. This user should ideally have been granted the GRANT ANY PRIVILEGE privilege or have been explicitly granted the privileges you're trying to grant.https://ebookwritingservice.us.com/