参数：sql92_security
此参数默认情况为FALSE，当sql92_security设置为TRUE时，那么其他schema的表执行UPDATE或DELETE操作时会检查当前用户是否拥有此表的SELECT权限，否则在执行UPDATE或DELETE时将提示“权限不足”的错误。
1：查看sql92_security参数默认值，准备测试环境
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SYS@PROD>show parameter sql92_security NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sql92_security boolean FALSE SYS@PROD> SYS@PROD>conn scott/tiger Connected. SCOTT@PROD> SCOTT@PROD>create table t as select rownum as id from dual connect by rownum<=5; Table created. SCOTT@PROD>select * from t; ID ---------- 1 2 3 4 5 SCOTT@PROD> |
2：验证，当hr用户无select、delete、update scott的t表权限时的效果
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SCOTT@PROD>conn hr/hr Connected. HR@PROD> HR@PROD>select * from scott.t; select * from scott.t * ERROR at line 1: ORA-00942: table or view does not exist HR@PROD>delete scott.t; delete scott.t * ERROR at line 1: ORA-00942: table or view does not exist HR@PROD>update scott.t set id=id+1; update t set id=id+1 * ERROR at line 1: ORA-00942: table or view does not exist HR@PROD> |
3：scott用户将update、delete t表的权限赋予hr
|
1 2 3 4 5 6 7 8 |
HR@PROD>conn scott/tiger Connected. SCOTT@PROD> SCOTT@PROD>grant update,delete on t to hr; Grant succeeded. SCOTT@PROD> |
4：再次验证赋予权限后的效果，此时我们会看到，select时依旧报错（不过此时报的是权限不足，而非表或视图不存在），update、delete均成功
|
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 |
SCOTT@PROD>conn hr/hr Connected. HR@PROD> HR@PROD>select * from scott.t; select * from scott.t * ERROR at line 1: ORA-01031: insufficient privileges HR@PROD> HR@PROD>delete scott.t where id=1; 1 row deleted. HR@PROD> HR@PROD>update scott.t set id=id+1; 4 rows updated. HR@PROD> HR@PROD>rollback; Rollback complete. HR@PROD> |
5：将参数sql92_security改为true，再次执行上述动作，测试如下：
|
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 |
SCOTT@PROD>conn / as sysdba Connected. SYS@PROD>show parameter sql92 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sql92_security boolean FALSE SYS@PROD> --sql92_security是静态参数，所以要重启生效 SYS@PROD>alter system set sql92_security=true scope=spfile; System altered. SYS@PROD>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@PROD>startup ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2255832 bytes Variable Size 423625768 bytes Database Buffers 197132288 bytes Redo Buffers 3313664 bytes Database mounted. Database opened. SYS@PROD> SYS@PROD>show parameter sql92 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sql92_security boolean TRUE SYS@PROD> |
6：验证修改sql92_security它后的效果
|
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 |
SYS@PROD>conn hr/hr Connected. HR@PROD> --hr无查询权限，这是一定的，scott用户并未给hr用户查询t表的权限 HR@PROD>select * from scott.t; select * from scott.t * ERROR at line 1: ORA-01031: insufficient privileges HR@PROD> --更新scott的t表，此时报权限不足了，之前未改参数时，这个动作是可以执行 HR@PROD>update scott.t set id=id+1; update scott.t set id=id+1 * ERROR at line 1: ORA-01031: insufficient privileges HR@PROD> --删除表中一条记录，此时的效果与更新一样，提示权限不足 HR@PROD>delete scott.t where id=1; delete scott.t where id=1 * ERROR at line 1: ORA-01031: insufficient privileges HR@PROD> --此时出现了个问题，当delete时不加任何条件，那么是可以删除的，本测试环境为11.2.0.4.0，不清楚为什么不加条件的delete，却并未收到参数sql92_security为true的限制 HR@PROD>delete scott.t; 5 rows deleted. HR@PROD> |
7：scott授予hr查询t表的权限，再次执行测试，我们可以看到，此时update、delete均执行成功
|
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 |
HR@PROD>conn scott/tiger Connected. SCOTT@PROD> SCOTT@PROD>grant select on t to hr; Grant succeeded. SCOTT@PROD>conn hr/hr Connected. HR@PROD> HR@PROD>select * from scott.t; ID ---------- 1 2 3 4 5 HR@PROD>update scott.t set id=id+1; 5 rows updated. HR@PROD>update scott.t set id=id+1 where id=2; 1 row updated. HR@PROD> |
小结：
通过这个测试，我们验证了参数sql92_security的作用。这个参数一般情况下没人太关注它，不过，当我们对数据库做表的细粒度安全考虑，那么可以尝试考虑此参数的设置。