8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
READ Object Privilege in Oracle Database 12c Release 1 (12.1.0.2)
Use the READ
object privilege to create read-only users that no longer have the ability to lock rows in the tables they query.
The Problem With the SELECT Object Privilege
There is a very real flaw related to the SELECT
object privilege. In addition to allowing a user to query a table, it allows them to perform a SELECT ... FOR UPDATE
, thus allowing the grantee the ability lock rows. This is a very dangerous privilege to grant a read-only user, since it gives them the ability to perform denial of service attacks on the system. An example of this issue is displayed below.
Create a new user to act as the read-only user for the test.
CONN sys@pdb1 AS SYSDBA CREATE USER read_only_user IDENTIFIED BY read_only_user; GRANT CREATE SESSION to read_only_user;
Create and populate a test table in a separate user.
CONN test/test@pdb1 DROP TABLE tab1 PURGE; CREATE TABLE tab1 ( id NUMBER, description VARCHAR2(20), CONSTRAINT tab1_pk PRIMARY KEY (id) ); INSERT INTO tab1 SELECT level, 'Description of ' || level FROM dual CONNECT BY level <= 5; COMMIT;
Grant access on the test table to the read-only user.
GRANT SELECT ON test.tab1 TO read_only_user;
Issue the following SELECT ... FOR UPDATE
query in a session connected to the read-only user. Do not issue commit or rollback after it and keep the session open while you work in a separate session.
CONN read_only_user/read_only_user@pdb1 SELECT * FROM test.tab1 FOR UPDATE; ID DESCRIPTION ---------- -------------------- 1 Description of 1 2 Description of 2 3 Description of 3 4 Description of 4 5 Description of 5 SQL>
While connected as the schema owner, attempt to update one of the rows. You will see it hang, waiting for the read-only session to commit or rollback and thereby release the locks.
CONN test/test@pdb1 UPDATE tab1 SET id = id WHERE id = 1;
Issue a commit in the read-only user session and you will see the update complete in the schema owner session. Remember to commit the update also once it completes.
This is exactly the problem with using the SELECT
object privilege for read-only users.
READ Object Privilege
The READ
object privilege was introduced in Oracle database 12c (12.1.0.2) to give read-only privilege on a table to a user, without giving them the ability to lock rows in the table. The following example uses the read-only user and test table built in the previous section.
Replace the SELECT
object privilege with the READ
object privilege.
CONN test/test@pdb1 REVOKE SELECT ON test.tab1 FROM read_only_user; GRANT READ ON test.tab1 TO read_only_user;
Connect to the read-only user and issue the following SELECT ... FOR UPDATE
query.
CONN read_only_user/read_only_user@pdb1 SELECT * FROM test.tab1 FOR UPDATE; * ERROR at line 2: ORA-01031: insufficient privileges SQL>
So the lock is not possible in the read-only user now, but we can still query the object in a read-only fashion.
CONN read_only_user/read_only_user@pdb1 SELECT * FROM test.tab1; * ID DESCRIPTION ---------- -------------------- 1 Description of 1 2 Description of 2 3 Description of 3 4 Description of 4 5 Description of 5 SQL>
So using the READ
objects privilege is a significant improvement when we want to set up a read-only user.
READ ANY TABLE System Privilege
Just as the SELECT
object privilege has an associated SELECT ANY TABLE
system privilege, the READ
object privilege has an associated READ ANY TABLE
system privilege. The READ ANY TABLE
system privilege allows a user to query tables, views, or materialized views in any schema in the database.
As a rule of thumb, every time you consider granting a system privilege containing the word "ANY" to a user, you are probably opening up a very bad security hole. Think very carefully before you continue!
For more information see:
Hope this helps. Regards Tim...