Granting Oracle Schema Permissions (Objects not created yet)

Recently we had a customer who wanted to have read only access to all tables in a schema, even tables not created yet. Apparently in Microsoft SQLServer, there is a way to grant a user schema permissions and includes objects not created yet.

Since i am unaware of a way to do this automagically in Oracle, i decided to create a “after ddl” trigger on the schema to grant the permissions when objects are created. The challenge is that pl/sql doesn’t allow running ddl permissions, i had to create a work around for the pl/sql and ddl challenges. The resolution was to use dbms.job_submit (Thanks “Ask Tom”) and submit a job to run the permissions.

Here is the code I used and please modify to suit your needs: (For this example i’m using IOUG as the application name/user)

#1 – Create a read only role first:

Create role IOUG_READONLY;

#2 – Grant read only role to the user (IOUG) requiring the permissions

grant IOUG_READONLY to IOUG;

#2 – Connect to application schema user (Owns objects) and create “after ddl” trigger (Sorry for the doublespace !)

CREATE or REPLACE TRIGGER AFTER_DDL AFTER DDL on IOUG_OBJECTS.SCHEMA
v_sysevent varchar2(25);
v_message varchar(255);
l_job number;
begin
select ora_sysevent into v_sysevent from dual;
if ( v_sysevent in ('CREATE') )
then
v_message := 'execute immediate "grant select on IOUG_OBJECTS.'||ora_dict_obj_name||' to IOUG_READONLY";';
dbms_job.submit (l_job,replace(v_message,'"','''') ) ;
end if;
end;
/


Now whenever a new object gets created the role is granted via the pl/sql and dbms_job.  The following is a test output:

Connect IOUG_OJBECTS/pw

IOUG_OBJECTS@IOUGDEV > create table foo1 (col1 varchar2(255));

Table created.

IOUG_OBJECTS@IOUGDEV > connect IOUG/pw

Connected.
IOUG@IOUGDEV > select * from IOUG_OBJECTS.foo1;

no rows selected

IOUG@IOUGDEV > desc IOUG_OBJECTS.foo1;
Name Null? Type
—————————————– ——– —————————-
COL1 VARCHAR2(255)

 

select * from IOUG_OJBECTS. OBJECT_NAME  *** NOTE – You must put the schema name before the table name (IOUG_OBJECTS), because the grants didn’t include the a public synonym.

 

Please let me know if this works for you and big thanks to “Ask Tom” who helped me resolve the PL/SQL and DDL issue.  Also, please let me know if there is a automagic way to do this Oracle.