I have been asked to create a read only user in a Oracle database 12c. The read only user must have read only grants only to the objects of select schemas, which I created and handed over.
But now the request from the team is that the newly created read only user must have read only grants to even to all those new objects created by those select schemas.
How is this possible? Inputs please!
Best Answer
No, no, no, you should definitely not do this. It is the responsibility of the object creator to grant the necessary privileges. (Although this is a common request, and it would be nice to finally have built-in support for this.)
Since you are on 12c, forget
SELECT
, and grantREAD
.SELECT
is not a read-only privilege. WithSELECT
granted, the grantee can runSELECT ... FOR UPDATE
, lock rows, and interfere.READ
does not allow that.What you asked is however still possible with some dirty tricks, using a DDL trigger, for example:
How do I create a Oracle trigger that grants permissions