Locking Privileges in Oracle
14/07/2015 7 Comments
What permissions do you need to lock rows on an Oracle table?
What about to lock the whole table?
It’s not quite as much as you may think!
Lets have a couple of users; schema_owner and user1
SQL> show user USER is "SYS"
SQL> create user schema_owner identified by schema_owner; User created.
SQL> grant connect,resource to schema_owner; Grant succeeded.
SQL> grant unlimited tablespace to schema_owner; Grant succeeded.
SQL> create user user1 identified by user1; User created.
SQL> grant create session to user1; Grant succeeded.
Now for a table and grants
SQL> conn schema_owner/schema_owner Connected.
SQL> create table tab1 (col1 date, col2 number); Table created.
SQL> insert into tab1 values (sysdate,1); 1 row created.
SQL> commit; Commit complete.
SQL> select * from tab1; COL1 COL2 --------- ---------- 14-JUL-15 1
SQL> grant select on tab1 to user1; Grant succeeded.
So, what can USER1 do with that table?
SQL> conn user1/user1 Connected. SQL> select * from schema_owner.tab1; COL1 COL2 --------- ---------- 14-JUL-15 1
good
SQL> update schema_owner.tab1 set col2=2 where col2=1; update schema_owner.tab1 set col2=2 where col2=1 * ERROR at line 1: ORA-01031: insufficient privileges
nice
SQL> insert into schema_owner.tab1 values (sysdate,2); insert into schema_owner.tab1 values (sysdate,2) * ERROR at line 1: ORA-01031: insufficient privileges
yeah
SQL> delete from schema_owner.tab1; delete from schema_owner.tab1 * ERROR at line 1: ORA-01031: insufficient privileges
great
SQL> select * from schema_owner.tab1 for update;
COL1 COL2
--------- ----------
14-JUL-15 1
oh
SQL> lock table schema_owner.tab1 in exclusive mode;
Table(s) Locked.
What?!? Is this real? Has that REALLY lock the entire table with only SELECT permissions? Can I delete from that table from a different session + user which has permissions?
SQL> show user USER is "SCHEMA_OWNER" SQL> select * from schema_owner.tab1; COL1 COL2 --------- ---------- 14-JUL-15 1
SQL> delete from schema_owner.tab1; (no return....)
A quick look in gv$session will show you that USER1 is indeed blocking SCHEMA_OWNER despite only having SELECT privileges on the table:
select .... from gv$session;
CON_ID SID USERNAME SQL_ID STATUS BS_STAT BL_SID EVENT
------ --- --------------- ------------- -------- ---------- ------ ---------------------------
3 47 USER1 INACTIVE NO HOLDER BLOCK SQL*Net message from client
3 55 SCHEMA_OWNER 5n1hw77std3h5 ACTIVE VALID 47 enq: TM - contention
SQL> select * from dba_blockers
2 ;
HOLDING_SESSION CON_ID
--------------- ------
47 3
SQL> select * from dba_waiters;
WAITING_SESSION WAITING_CON_ID HOLDING_SESSION HOLDING_CON_ID LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
--------------- -------------- --------------- -------------- -------------------------- ---------------------------------------- ---------------------------------------- ---------- ----------
55 3 47 3 DML Exclusive Row-X (SX) 96178 0
This is because of a side effect of an Oracle philosophy; “don’t do now what you may never need to do”. If Oracle can defer any actions from now, such as writing a dirty buffer to disk, or seeing if a session has permissions to perform an update when all you have done is request a lock, then it will, if possible, do it later.
You may request the lock so Oracle checks that you can access the object (SELECT), but you may never try to actually change the row, or table so it’s not necessary to see if you can modify the object…
This is a pretty problematic security hole; In Oracle 12c, a new table privilege has appeared: READ. If we re-run the above with GRANT READ instead of GRANT SELECT…
SQL> show user USER is "USER1" SQL> select grantee,privilege from user_tab_privs where table_name = 'TAB1'; GRANTEE PRIVILEGE -------------------- ---------- USER1 READ SQ> select * from schema_owner.tab1; COL1 COL2 --------- ---------- 14-JUL-15 1
ok
SQ> select * from schema_owner.tab1 for update; select * from schema_owner.tab1 * ERROR at line 1: ORA-01031: insufficient privileges SQL> lock table schema_owner.tab1 in exclusive mode; lock table schema_owner.tab1 in exclusive mode * ERROR at line 1: ORA-01031: insufficient privileges
Thats better!
So the next time someone says “it’s only SELECT permissions”, it’s not. You might want to check out using READ.
Neil Chandler's DB Blog 