Automation on Grant to Any user/ schema in Oracle database

Some times you may have request from application owners that some uses should have access as soon as any table created on specified schema. To meet this requirement, you can create once database level trigger. It is depend on what kind of access to different user. based on the requirement you need to create some roles and grant those roles to specified to users before creating database level trigger.

Here, I am creating one role with all DML access and one role with read-only ( select) access.
Example: test_rw_role and test_ro_role

SQL> create role test_ro_role;
SQL> create role test_rw_role;

-- Create Trigger

create or replace trigger T_GIVE_GRANTS
        after CREATE on database 
declare 
        l_str1 varchar2(255); 
l_str2 varchar2(255);
        l_job1 number;
l_job2 number;  
obj_own varchar2(30);
obj_name varchar2(30);
str varchar2(255);
begin 
obj_own:=ora_dict_obj_owner;
obj_name:=ora_dict_obj_name;
str:=obj_own||'.'||obj_name;
        if ( ora_dict_obj_type = 'TABLE' )
and ora_dict_obj_owner not in('SYS','SYSTEM','DBSNMP') then 
                l_str1 := 'execute immediate "grant select,insert,update,delete on ' ||str || ' to test_rw_role";'; 
l_str2 := 'execute immediate "grant select on ' ||str || ' to test_ro_role";'; 
                dbms_job.submit( l_job1, replace(l_str1,'"','''') ); 
dbms_job.submit( l_job2, replace(l_str2,'"','''') ); 
        end if; 
end; 
/ 

Trigger Created.

========================
--- Testing ---
========================

--- connecting to SYS

SQL> connect / as sysdba
SQL> grant test_ro_role to test;
SQL> grant test_rw_role to scott;

Note: There are two users scott and test are exiting users

---- Connecting to SCOTT schema

SQL> conn scott
Enter password: 
Connected.

SQL> create table scott_tab(id NUMBER GENERATED ALWAYS AS IDENTITY, remark varchar2(10));
SQL> insert into scott_tab(remark) values ('test1');
SQL> commit;
SQL> select * from scott.scott_tab;

        ID REMARK
---------- ----------
         1 test1

-- Connecting to TEST schema

SQL> disc
SQL> conn test/test
Connected.

SQL> select * from scott.scott_tab;

        ID REMARK
---------- ----------
         1 test1

SQL> insert into scott.scott_tab(remark) values ('test2');
insert into scott.scott_tab(remark) values ('test2')
                  *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> create table test_tab(n number);
Table created.

SQL> insert into test_tab values (9999);
1 row created.

SQL> commit;
Commit complete.

SQL> 


---- Connecting to SCOTT schema

SQL> disc
SQL> connect scott
Enter password: 
Connected.
SQL> 
SQL> select * from test.test_tab;

         N
----------
      9999

SQL> insert into test.test_tab values(8888);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from test.test_tab;

         N
----------
      9999
      8888

SQL> 


-- Some Debugging:

That, when run in a given user account will schedule a job to grant SELECT on any table created to SCOTT. We use dbms_job to run the grant in the background because this is executing as part of a larger data dictionary transaction and we cannot commit (nor can we use an autonomous transaction - the table is not yet "fully" created). 


-- Before I change the value

SQL> show parameter job_queue_processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
job_queue_processes                  integer     1000
SQL> 

-- Modifying the value 

SQL> alter system set job_queue_processes=10;
System altered.

SQL> show parameter job_queue_processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
job_queue_processes                  integer     60
SQL> 

-- If any pending jobs are there, you can see using below query

SQL> col WHAT for a120;
SQL> select job, what from user_jobs;

       JOB WHAT
---------- ---------------------------------------------------------
         7 execute immediate 'grant select,insert,update on T1 to test_rw_role';
         6 execute immediate 'grant select,insert,update on SAMPLE1 to test_rw_role';
         4 execute immediate 'grant select,insert,update on T1 to test_rw_role';
         2 execute immediate 'grant select,insert,update on T3 to test_rw_role';
         1 execute immediate 'grant select,insert,update on T2 to test_rw_role';

6 rows selected.

SQL> 

-- remove job from queue

SQL> exec DBMS_JOB.REMOVE(1);

PL/SQL procedure successfully completed.

Ref:
https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg14evt.htm
https://docs.oracle.com/database/121/ADMIN/appendix_a.htm#ADMIN11002

Configuring DBMS_JOB

The JOB_QUEUE_PROCESSES initialization parameter specifies the maximum number of processes that can be created for the execution of jobs.

Beginning with Oracle Database 11g, JOB_QUEUE_PROCESSES defaults to 1000. The job coordinator process starts only as many job queue processes as are required, 

based on the number of jobs to run and available resources. You can set JOB_QUEUE_PROCESSES to a lower number to limit the number of job queue processes.


Setting JOB_QUEUE_PROCESSES to 0 disables DBMS_JOB jobs and DBMS_SCHEDULER jobs.