Read-only user query in Oracle

oracleoracle-12cschema

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 grant READ. SELECT is not a read-only privilege. With SELECT granted, the grantee can run SELECT ... 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