Thanks for the question, Dackard.
Asked: November 29, 2022 - 10:33 am UTC
Last updated: December 01, 2022 - 4:07 am UTC
Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Viewed 1000+ times
You Asked
Hi,
is there a method to set default system privileges for all new created objects in Oracle, such as tables, sequences, procedures and functions and triggers? For example, select privilege assigned to an Oracle user.
How to make it possible without having to write additional code except for e.g. create table, sequence, function etc.?
Best regards,
Dackard.
with LiveSQL Test Case:
and Connor said...
Unfortunately not currently.
23c may have a schema level privilege, eg "grant select any table on schema HR to JOHN" which would mean JOHN can query any current (and future) object in the HR schema.
Take a look at my blog post here
https://connor-mcdonald.com/2019/04/24/grab-all-the-ddl/ which talks about capturing DDL executed, but you could tailor this to do something like
- trigger fires to identify the object name, stores in a table, submit a job via DBMS_JOB
- that jobs runs shortly after to issue the grants you want