How to create a trigger that will add newly created tables to a role

A commonly asked for functionality in my day-to-day work is READ-ONLY access for users to various tables in the database. This is easy to solve by creating a role, and granting SELECT on the tables to that role.

Every time a new table is created, the role must be updated. This will for most of the time be impossible to administer, and should be automated by creating a trigger on the schema owning the tables/views.

Here is how:

create a new role:

create role my_ro_role;

Grant execute permissions on dbms_jobs to scott:

grant create job to scott;

Note that if you do not have the password for the schema that should own the trigger, you cannot use the SYS user to create it for them. You need SYSTEM or another DBA user to avoid the error

ORA-30510: system triggers cannot be defined on the schema of SYS user

Create the trigger. In this example, I am using the system user to accomplish this:

conn system/password
create or replace trigger scott.add_to_ro_role
after CREATE on schema
declare
 l_str varchar2(255);
 l_job number;
begin
 if ( ora_dict_obj_type = 'TABLE' ) then
  l_str := 'execute immediate "grant select on ' || ora_dict_obj_name || ' to my_ro_role";';
  dbms_job.submit( l_job, replace(l_str,'"','''') );
 end if;
end;
/

Every time a new table is created in the SCOTT schema, SELECT on the same table will be granted to the role.
The role can then be granted to individual users.

Thanks to Tron Malmø-Lund for the idea and the code to implement it! ;-)