Skip to Main Content


Question and Answer

Connor McDonald

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 - Production

Viewed 100+ times

You Asked

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,

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

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

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library