How to grant privileges on all tables to a user

Sometimes you need to grant privileges to one user’s objects to another user. While granting should be done very carefully, when you have a lot of tables it is nice to have an automated script to do it.

It is important to note that the best way to do that is creating a role, granting the privileges to this role and then grant the role to the user. This way, you can fine tune the privileges and the process will be a lot faster next time.

Another important thing to note is that you should avoid the temptation to use the  “SELECT ANY TABLE” privilege. This will indeed allow the user to select any table but (and this is a big but) since it is a system privilege, it will allow him to select any table from any schema. Depends on the setting, it might even allow to user to select from the dictionary tables themselves. There is no reason to grant this privilege to someone who is not the DBA.

The easiest way to grant select on all of  one account’s object to another user is to use a PL/SQL loop. This script will grant select on tables but it should be easy to adapt it to any other privilege.


 for i in (select * from user_tables)       

     execute immediate ‘grant select on ‘||i.table_name||’ to a_role_created_for_this’;
   end loop;

In case you need a pure SQL solution, the best way to go would be to spool the results of the following query and run the script created in grant-all.txt.

SQL> set lin 150

SQL> spool c:\grant-all.txt

SQL>select ‘grant select  to ‘||table_name||’ to “the new user”;’ from all_tables where owner=’the old user;

SQL> spool off

One thought on “How to grant privileges on all tables to a user

Leave a Reply

Your email address will not be published. Required fields are marked *