Blog

Often in our daily DBA tasks, we may get the requirement to grant all privileges to user in Oracle. Today we will learn about this.

Steps to Grant All Privileges to User in Oracle :

1. Get the Details :

• Find the Username to which the access needs to be provided (Below we have taken sample as JOHN)
• Find under which schema the access needs to be provided (Here we have taken sample as SAMIR)
• Get the access type too(select or all privileges). For all access we may need approval. Please check with your team internally as this is sensitive access.

2. Issue the below Dynamic Query to grant read write access to user in Oracle :

select 'grant select, update, insert, delete on '||owner||'.'||object_name||' to '||'JOHN'||';' from all_objects where owner='SAMIR' and object_type in ('TABLE','VIEW');

select 'grant execute on '||owner||'.'||object_name||' to '||'JOHN'||';' from all_objects where owner='SAMIR' and  object_type in ('PACKAGE','PROCEDURE','FUNCTION','TYPE');

 

3. After running the above dynamic query, you will get the output as below :

i) Output of 1st Query :

ii) Output of 2nd Query :

4. Now Copy both the outputs in a single sql file and execute it. Your work is done. Congratulations!!

So here are the steps, you can follow to grant all privileges to user in Oracle.

Reference Links :

How to Find DDL of user in Oracle :

Oracle Doc