8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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...

Back to the Top.