由于数据查询的需求，我们常常需要在数据库当中建立只读用户，并且都是赋予的select any table权限。但是有没有想过只读权限的用户也能锁表？并且是以排他锁的形式？
这样就意味着很多‘只读‘权限的用户并不是真正意义上的只读，这很有可能会由于人为失误或者故意的某些行为导致生产环境的严重灾难，先通过几个例子来重现这种现象。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| SQL> conn xb/xb@pdb12c
Connected.
SQL> grant create session to readonly;
Grant succeeded.
SQL> create table t(id integer);
Table created.
SQL> insert into t values(1);
1 row created.
SQL> grant select on t to readonly;
Grant succeeded.
|
通过只读用户连接上来，然后锁住t表
1
2
3
4
5
6
7
8
9
10
| SQL> conn readonly/readonly@pdb12c
Connected.
SQL> select * from xb.t for update;
no rows selected
SQL> lock table xb.t in exclusive mode;
Table(s) Locked.
|
可以发现只读用户readonly确实是可以以排他模式锁住表的，那检查下它到底锁住了什么
1
2
3
4
5
6
7
8
9
10
11
12
| SQL> @lock sid=626
SID TYPE LMODE MODE_HELD REQUEST MODE_REQUESTED LOCK_ID1 LOCK_ID2 CTIME BLOCK
------- ------ ---------- --------------- ---------- --------------- ---------- ---------- ---------- ----------
626 AE 4 Share 0 None 133 395521035 694 0
626 TM 6 Exclusive 0 None 137625 0 282 0
SQL> @oid 137625
owner object_name object_type SUBOBJECT_NAME CREATED LAST_DDL_TIME status DATA_OBJECT_ID
------------------------- ------------------------------ ------------------ ------------------------------ ------------------- ------------------- --------- --------------
XB T TABLE 2020-08-14 16:34:29 2020-08-14 16:34:43 VALID 137625
|
通过上述结果可以确认readonly用户以排他的方式锁住了xb.t表，在t表上持有表级锁。在实际生产环境中因为这样一个小失误可能会导致其他会话在对表进行dml操作时全部hang住，后果是很严重的。
好消息是从12c开始，oracle引入了一个全新的read权限，这个权限就可以赋予用户只有对表的读取权限，而没有锁表的权限。通过几个例子来展现在12c环境中，对于之前一样的场景来说有何区别。
先收回原有的select权限，并重新赋予read权限。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| SQL> revoke select on xb.t from readonly;
Revoke succeeded.
SQL> conn readonly/readonly@pdb12c
Connected.
SQL> select * from xb.t;
select * from xb.t
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> grant read on xb.t to readonly;
Grant succeeded.
|
测试for update和lock权限
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| SQL> conn readonly/readonly@pdb12c
Connected.
SQL> select * from xb.t;
ID
----------
1
SQL> select * from xb.t for update;
select * from xb.t for update
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> lock table xb.t in exclusive mode;
lock table xb.t in exclusive mode
*
ERROR at line 1:
ORA-01031: insufficient privileges
|
这对于数据库的权限控制来说是一个明显的优化，在要增加只读用户的时候提供了一个非常好的选项。