Presentations are still being accepted for the Michigan Oracle Users Summit 2015 (www.mous.us) on November 18th, 2015. As in the past, this year’s all day event will be held at Schoolcraft College VisTaTech Center in Livonia, Michigan. Its a Premier event with many Oracle Ace’s presenting and Rich Niemiec, President of Rolta and Past-President of the IOUG as our Keynote speaker and the deadline for presentations is July 31, 2015. If you have any questions, please contact me – Mike Gangler at firstname.lastname@example.org.
Presentations are being accepted for the following focus areas:
If you’re interested in submitting a topic for consideration for the upcoming Michigan Oracle User Summit on November 18th, 2015, we want to hear from you. To submit your topic(s) today by clicking here.
All presenters receive FREE registration to the Summit – a $50 value!!!!
Registration will start soon.
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
select ora_sysevent into v_sysevent from dual;
if ( v_sysevent in ('CREATE') )
v_message := 'execute immediate "grant select on IOUG_OBJECTS.'||ora_dict_obj_name||' to IOUG_READONLY";';
dbms_job.submit (l_job,replace(v_message,'"','''') ) ;
Now whenever a new object gets created the role is granted via the pl/sql and dbms_job. The following is a test output:
IOUG_OBJECTS@IOUGDEV > create table foo1 (col1 varchar2(255));
IOUG_OBJECTS@IOUGDEV > connect IOUG/pw
IOUG@IOUGDEV > select * from IOUG_OBJECTS.foo1;
no rows selected
IOUG@IOUGDEV > desc IOUG_OBJECTS.foo1;
Name Null? Type
—————————————– ——– —————————-
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.