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.
connect to user SYS or as grantor. Here the grantor is user2.
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