Oracle Database 12c provides a set of security features that can be used to manage several and diverse topics such as authentication, privileges and roles, application security, encryption, network traffic, and auditing. This blog post will address the new READ and READ ANY TABLE object privileges introduced in Oracle database 12c. READ and READ ANY TABLE object privileges
The new READ object privilege enables users to query database objects like tables, views, materialized views, and synonyms.
What is the purpose of this new feature? Until release 12c the only object privilege to access data in "read only" mode was the SELECT object privilege. However, this privilege not only enables users to query objects, but also allows users to perform additional blocking operations like:
- SELECT ... FROM table_name FOR UPDATE;
- LOCK TABLE table_name IN EXCLUSIVE MODE;
In order to improve database access security, grant users the READ object privilege to restrict them to perform "read only" operations only.
Let's use the following example to show how this new privilege works.
Create two users named "user1" and "user2". Grant "CREATE TABLE" privilege to "user1" only:
SQL> CREATE USER user1
2 IDENTIFIED BY welcome1
3 DEFAULT TABLESPACE users
4 QUOTA UNLIMITED ON users;
User created.
SQL> GRANT CREATE SESSION, CREATE TABLE TO user1;
Grant succeeded.User "user2" will have only "CREATE SESSION" privilege assigned:
SQL> CREATE USER user2
2 IDENTIFIED BY welcome1
3 DEFAULT TABLESPACE users;
User created.
SQL> GRANT CREATE SESSION TO user2;
Grant succeeded.Create a sample table in "user1" schema and populate it with some data:
SQL> CONN user1/welcome1
Connected.
SQL> CREATE TABLE test
2 (description VARCHAR2(100));
Table created.
SQL> INSERT INTO test
2 VALUES ('record #1');
1 row created.
SQL> COMMIT;
Commit complete.Grant "SELECT" privilege on table "test" to "user2" in order to verify the old behavior:
SQL> GRANT SELECT ON test TO user2;
Grant succeeded.Now, verify that "user2" can select data from "user1.test" table:
SQL> CONN user2/welcome1
Connected.
SQL> SELECT *
2 FROM user1.test;
DESCRIPTION
-------------
record #1As stated before, SELECT privilege not only allows to select data in "read only" mode. In addition, this privilege enables users to lock tables in exclusive mode or select tables for update operations:
SQL> SELECT *
2 FROM user1.test
3 FOR UPDATE;
DESCRIPTION
--------------------------------------------------------------
record #1
SQL> LOCK TABLE user1.test IN EXCLUSIVE MODE;
Table(s) Locked.Now revoke SELECT privilege from "user1.test" and grant READ privilege on "user1.test" to user2 as follows:
SQL> CONN user1/welcome1
Connected.
SQL> REVOKE SELECT ON test FROM user2;
Revoke succeeded.
SQL> GRANT READ ON test TO user2;
Grant succeeded.Let's try again the same operations. Notice how this time, you can only retrieve records from "user1.test" table. Other operations are not allowed:
SQL> CONN user2/welcome1
Connected.
SQL>
SQL> SELECT *
2 FROM user1.test;
DESCRIPTION
--------------------------------------------------------------
record #1
SQL> SELECT *
2 FROM user1.test
3 FOR UPDATE;
FROM user1.test
*
ERROR at line 2:
ORA-01031: insufficient privileges
SQL> LOCK TABLE user1.test IN EXCLUSIVE MODE;
LOCK TABLE user1.test IN EXCLUSIVE MODE
*
ERROR at line 1:
ORA-01031: insufficient privilegesConclusion
In Oracle Database 12c, you can grant users either the READ or the SELECT object privilege, depending on the level of access that you want to allow the user. But if you want the user only to be able to query tables, then grant the READ object privilege or the READ ANY object privilege.