DBA Hub

📋Steps in this guide1/3

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.

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

The Problem With the SELECT Object Privilege

There is a very real flaw related to the object privilege. In addition to allowing a user to query a table, it allows them to perform a , 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. Create and populate a test table in a separate user. Grant access on the test table to the read-only user. Issue the following 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. 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. 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 object privilege for read-only users.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
CONN sys@pdb1 AS SYSDBA

CREATE USER read_only_user IDENTIFIED BY read_only_user;
GRANT CREATE SESSION to read_only_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 SELECT ON test.tab1 TO read_only_user;

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>

CONN test/test@pdb1

UPDATE tab1
SET    id = id
WHERE  id = 1;
2

READ Object Privilege

The 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 object privilege with the object privilege. Connect to the read-only user and issue the following query. So the lock is not possible in the read-only user now, but we can still query the object in a read-only fashion. So using the objects privilege is a significant improvement when we want to set up a read-only user.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CONN test/test@pdb1

REVOKE SELECT ON test.tab1 FROM read_only_user;
GRANT READ ON test.tab1 TO read_only_user;

CONN read_only_user/read_only_user@pdb1

SELECT *
FROM   test.tab1
FOR UPDATE;
            *
ERROR at line 2:
ORA-01031: insufficient privileges

SQL>

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

READ ANY TABLE System Privilege

Just as the object privilege has an associated system privilege, the object privilege has an associated system privilege. The 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: - GRANT Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!