New READ Object Privilege in 12cR1

In writing a blog post about:
Creating a Read Only Database User Account in an Oracle Database

It came to my attention of the new “READ” object privilege, which is a New Feature in 12.1.0.2:
Changes in Oracle Database 12c Release 1 (12.1.0.2)
READ and SELECT Object Privileges

The “SELECT” object privilege in addition to querying the table, allows the user to:
LOCK TABLE table_name IN EXCLUSIVE MODE;
SELECTFROM table_name FOR UPDATE;

The New Feature of “READ” object privilege, does not allow the user to lock tables in exclusive mode nor select table for update.

Prior to 12.1.0.2, the “SELECT” object privilege is only available which allows the locking:

GRANT SELECT ON ... TO ...;

12.1.0.2 onwards, the new “READ” object privilege is available which doesn’t allow the locking:

GRANT READ ON ... TO ...;

This also applies to the “SELECT ON ANY TABLE“, prior to 12.1.0.2 which allows the locking:

GRANT SELECT ANY TABLE TO ...;

12.1.0.2 onwards, the new “READ ON ANY TABLE” object privilege is available which doesn’t allow the locking:

GRANT READ ANY TABLE TO ...;

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Advertisements

Creating a Read Only Database User Account in an Oracle Database

It can be quite common to create a “Read Only” database user account in an Oracle database.  To do this is pretty simple using the principle of least privilege:

CREATE USER READ_ONLY IDENTIFIED BY "password";
GRANT CREATE SESSION TO READ_ONLY;

Expected output:

SQL> CREATE USER READ_ONLY IDENTIFIED BY "password";

User created.

SQL> GRANT CREATE SESSION TO READ_ONLY;

Grant succeeded.

SQL>

Depending on the Oracle Version, it’s recommend to use “READ” object privilege new in 12.1.0.2 instead of “SELECT” to prevent locking.  See the following blog post for more info:
New READ Object Privilege in 12cR1

PLEASE NOTE: For anyone using Oracle Version prior to 12.1.0.2, replace the object privilege “READ” with “SELECT“.

To read specific tables for a schema:

SET HEADING OFF
SET PAGES 9999 LINES 400
SPOOL TABLE_GRANTS.sql
SELECT 'GRANT READ ON ' || OWNER || '.' || TABLE_NAME || ' TO READ_ONLY;'
FROM DBA_TABLES
WHERE OWNER IN ('ZEDDBA')
ORDER BY 1;
SPOOL OFF
@TABLE_GRANTS.sql
!rm TABLE_GRANTS.sql

Expected output, where you can see the user “READ_ONLY” able to select from the table owned by user “ZEDDBA“:

SQL> SET HEADING OFF
SQL> SET PAGES 9999 LINES 400
SQL> SPOOL TABLE_GRANTS.sql
SQL> SELECT 'GRANT READ ON ' || OWNER || '.' || TABLE_NAME || ' TO READ_ONLY;'
 2 FROM DBA_TABLES
 3 WHERE OWNER IN ('ZEDDBA')
 4 ORDER BY 1;

GRANT READ ON ZEDDBA.DEMO TO READ_ONLY;

SQL> SPOOL OFF
SQL> @TABLE_GRANTS.sql
SP2-0734: unknown command beginning "SQL> SELEC..." - rest of line ignored.

Grant succeeded.

SP2-0734: unknown command beginning "SQL> SPOOL..." - rest of line ignored.
SQL> !rm TABLE_GRANTS.sql

SQL> conn read_only/password
Connected.
SQL> SELECT * FROM ZEDDBA.DEMO;

Read Only example.

SQL> exit

To read all tables in the databases:

GRANT READ ANY TABLE TO READ_ONLY;

Expected output, where you can see the user “READ_ONLY” able to select from the table owned by user “ZEDDBA“, even after the revoke and the user “ZEDDBA2“, which hasn’t been explicitly referenced:

SQL> conn zeddba/zeddba
Connected.
SQL> REVOKE READ ON ZEDDBA.DEMO FROM READ_ONLY;

Revoke succeeded.

SQL> GRANT READ ANY TABLE TO READ_ONLY;

Grant succeeded.

SQL> conn read_only/password
Connected.
SQL> SELECT * FROM ZEDDBA.DEMO;

COMMENTS
--------------------------------------------------------------------------------
Read Only example.

SQL> SELECT * FROM ZEDDBA2.DEMO;

COMMENTS
--------------------------------------------------------------------------------
Read Only example.

SQL>

To grant access to dictionary tables:

GRANT SELECT ANY DICTIONARY TO READ_ONLY;

Expected output, where you can see the user “READ_ONLY” able to select from dictionary i.e. table starting with DBA and V$:

SQL> conn zeddba/zeddba
Connected.
SQL> GRANT SELECT ANY DICTIONARY TO READ_ONLY;

Grant succeeded.

SQL> conn read_only/password
Connected.
SQL> SELECT TABLE_NAME
 2 FROM DBA_TABLES
 3 WHERE OWNER = 'ZEDDBA';

TABLE_NAME
--------------------------------------------------------------------------------
DEMO

SQL> SELECT NAME FROM V$DATABASE;

NAME
---------
ZEDDBA

SQL>

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)