Grant Privilege to Other Schema at once

How to grant SELECT or any other privilege on all tables in a schema to another user

Oracle does not provide a single statement to grant privileges on all tables. Instead, we make use of PL/SQL block to acheive this.

Below, a user connected as user1 tries to SELECT a table owned by user2. The SELECT failed as user1 do not have SELECT privilege on user2's tables.

SQL> conn user1;
Enter password:
Connected.
SQL>
SQL>
SQL> select count (*) from user2.CUSTOMERS;
select count (*) from user2.CUSTOMERS
                          *
ERROR at line 1:
ORA-00942: table or view does not exist

Here, we want to give SELECT on all tables of schema user2 to user1.

Connection to user

connect to user SYS or as grantor. Here the grantor is user2.

connect sys as sysdba

or

connect user2/password

Execute the below code to grant SELECT on all tables of user2. Using EXECUTE IMMEDIATE, all tables owned by user2 are granted SELECT to user1.

BEGIN

  FOR i IN (SELECT owner, table_name FROM dba_tables WHERE owner = 'USER2')
  LOOP

      EXECUTE IMMEDIATE 'GRANT SELECT ON ' || i.owner || '.' || i.table_name || ' TO user1';

  END LOOP;
  
END;
/

The backslash ( / ) at the end executes the PL/SQL code. This code can be extened to provide other privileges as well by replacing SELECT by appropriate privilege.

SQL> sho user
USER is "USER1"
SQL>
SQL> select count (*) from user2.CUSTOMERS;

  COUNT(*)
----------
   4293792

As seen above, user1 is able to select CUSTOMER table owned by user2. The SELECTion always have to be qualified by the owner name. This can be overcome by creating a synonym as below

SQL> conn user1/password
SQL> create synonym CUSTOMER for user2.CUSTOMER;

user1 must have CREATE SYNONYM privilge

Comments