Skip to Main Content

Breadcrumb

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

Viewed 100+ 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

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