Privilege – OracleNotes

The READ privilege is new in Oracle Database 12c 12.1.0.2. Why do we need a READ privilege when there is SELECT privilege? Well, the SELECT privilege includes few more privilege than a pure read-only privilege! With the SELECT privilege, in addition to reading (or “selecting” from) the table, you are also able to do

LOCK TABLE<table_name> IN EXCLUSIVE MODE;
SELECT … FROM<table_name> FOR UPDATE;

So, for better security to enforce pure read-only privilege Oracle needed another privilege, which is READ privilege. You can use the READ object privilege or READ ANY TABLE system privilege. Both operate similar to their SELECT counterpart, except for the privileges listed above.

No, there is no READ statement to go with the READ privilege. You will still be using the SELECT statement to read from the table or view. You use the READ object privilege to enable users query database tables, views, materialized views, and synonyms. The READ ANY TABLE privilege enables to query any table or view in the database.

So, from now onwards, you may start granting the READ or READ ANY TABLE privilege instead of SELECT or SELECT ANY TABLE, to the users requiring only the query privilege on the table or view for better security.

Examples of grants and revoke:

SQL> GRANT READ ON XX.XXLE_CUSTOMERS_T TO smith;

SQL> GRANT READ ON XX.XXLE_SUPPLIERS_VW TO smith;

SQL> GRANT READ ANY TABLE TO peter;

SQL> REVOKE READ ON XX.XXLE_SUPPLIERS_VW FROM smith;

SQL> REVOKE READ ANY TABLE FROM peter;

Granting privileges on all or multiple objects to user/role

Question:
I need to grant SELECT, UPDATE privilege on all tables owned by schema XXVMX to users MSUBBU, SMARTIN. Is there a command in Oracle to grant a privilege on all objects in schema to user?

Answer:
No such privilege in Oracle. You will have to write a script to grant the privilege on individual objects to the users.

If you have to repeat the same to more users, it may be better to create a role and grant the privileges to the role. Then assign the role to the users that need the privilege. Thus when new tables are created under XXVMX, the privilege need to be added to the role once only, do not have to do grant to all the individual users.

Create Role:

CREATE ROLE XXVMX_UPDATE;

Grant Role to Users:

GRANT XXVMX_UPDATE to MSUBBU, SMARTIN;

SQL Script:

set pages 0
set lines 300 trims on feedback on echo off

spool grants.sql

SELECT ‘grant select, update on ‘ || owner ||’.’||table_name|| ‘ to XXVMX_UPDATE;’
FROM dba_tables
WHERE owner = ‘XXVMX’;

spool off

set pages 99 lines 80
set feedback on echo on

Execute the script file created to grant the privileges.

@grants.sql

You may replace the “dba_tables” in the query with “dba_objects” and change the WHERE clause appropriately to filter different sets of objects…

Category: Privilege

Oracle 12c – READ Privilege

Granting privileges on all or multiple objects to user/role