Grant Privilege on all objects in a Schema to a user

In this example I have given select on all tables in schema test1 to user test2. As well grant all DML privilege on tables and views, and grant execute on procedures,functions and packages in a schema test1 to user test2.

 

SQL>create user test1 identified by test1;

User created.

SQL> grant connect,resource to test1;

Grant succeeded.

SQL> create user test2 identified by test2;

User created.

SQL> grant connect,resource to test2;

Grant succeeded.


SQL> conn test1/test1

Connected.


SQL> create table test_table1(id number,name varchar2(30));

Table created.

SQL> create table test_table2(id number,name varchar2(30));

Table created.


SQL> show user;
USER is "TEST1"

If you want to grant select privilege:


SQL>
BEGIN
    FOR t IN (SELECT * FROM user_tables) 
    LOOP   
        EXECUTE IMMEDIATE
 'GRANT SELECT ON ' || t.table_name || ' TO test2';    
    END LOOP;
END;

/

If you want to grant select,insert,update,delete privilege on tables and views and grant execute on procedure,function and package:

SQL>

BEGIN
  FOR t IN (SELECT object_name, object_type FROM all_objects WHERE owner
='TEST1' AND object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE')) LOOP
    
IF t.object_type IN ('TABLE','VIEW') THEN
      
EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON SOURCEUSER.'||t.object_name||' TO TEST2';
   
 ELSIF t.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
      
EXECUTE IMMEDIATE 'GRANT EXECUTE ON TEST1.'||t.object_name||' TO TEST2';
   
 END IF;
  
END LOOP;
END;

/

 

Best approach is:

1. Create a role with appropriate grant

2. grant the role to the user

 

SQL> create role support;

 

If want to grant select privilege:

SQL>
BEGIN
    FOR t IN (SELECT * FROM user_tables) 
    LOOP   
        EXECUTE IMMEDIATE
 'GRANT SELECT ON ' || t.table_name || ' TO support';    
    END LOOP;
END;

/

SQL> grant support to test2;

 

If you want to grant select,insert,update,delete privilege on tables and views and grant execute on procedure,function and package:

SQL>

BEGIN
  FOR t IN (SELECT object_name, object_type FROM all_objects WHERE owner
='TEST1' AND object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE')) LOOP
    IF t.object_type IN ('TABLE','VIEW') THEN
      
EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON SOURCEUSER.'||t.object_name||' TO SUPPORT';
    
ELSIF t.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
      EXECUTE IMMEDIATE 'GRANT EXECUTE ON TEST1.'||t.object_name||' TO SUPPORT';
    END IF;
  END LOOP;
END;

/

SQL> grant support to test2; 

Comments