Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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 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:
https://livesql.oracle.com/apex/livesql/s/oeteplc5t27wyi7kxakzlxnjm

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

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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