ANBOB™

Oracle 12c后的安全增强可能会导致运维中出现些差异， 如有时需要非sys用户查询sys的user$、link$等基表，这些表是因为存有password hash值，在之前一些安全部门查询是否有弱密码时喜欢采集user$，之前授权select any dictionary系统权限或dba role可以，但在是12c后增强不再允许，还有像Toad这种第三方工具如11.6的老版本在连接数据库时还以检测select any dictionary 判断user$权限也提示ORA-1031错误。

演示以后除了select any table外还有2个小的安全变化。环境oracle 19.3

dba role

# session 1

SQL> @cc pdb1
ALTER SESSION SET container = pdb1;
Session altered.


USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR                                          PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------                              - ----------------
SYS                  PDB1-anbob19c        oel7db1                     1 59    12343    19.0.0.0.0 20230110 27785      46    27033           0000000078111AB                              8 0000000078D09528

SQL> grant dba to anbob;
Grant succeeded.

# session 2
[oracle@oel7db1 admin]$ sqlplus anbob/oracle1234@cdb1pdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 10 22:21:18 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Last Successful login time: Wed Nov 30 2022 12:09:29 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR                                          PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------                              - ----------------
ANBOB                PDB1-anbob19c        oel7db1                     1 33    30890    19.0.0.0.0 20230110 27745      33    27742           00000000780D0D2                              8 0000000078CF7F48


SQL> select * from sys.user$;
select * from sys.user$
                  *
ERROR at line 1:
ORA-01031: insufficient privileges

Note:
有DBA角色也没有权限查询sys.user$.

select any dictionary

# session 1
SQL> grant select any dictionary to anbob;
Grant succeeded.

# session 2
[oracle@oel7db1 admin]$ sqlplus anbob/oracle1234@cdb1pdb1

SQL> select * from sys.user$;
select * from sys.user$
                  *
ERROR at line 1:
ORA-01031: insufficient privileges

grant select on sys.user$

# session 1
SQL> grant select on sys.user$ to anbob;
Grant succeeded.

# session 2
SQL> select count(*) from sys.user$;

  COUNT(*)
----------
       146

Note:
有select any dictionary系统权限也没权限查查询sys.user$.

风险

# session 2

SQL> select * from sys.user$ for update;
-- never commit or rollback

# session 3
 [oracle@oel7db1 ~]$ sqlplus anbob/oracle1234@cdb1pdb1
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 10 22:56:49 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
-- logon hang

# session 1
SQL> @ase

USERNAME          SID EVENT                MACHINE    MODULE               STATUS   LAST_CALL_ET SQL_ID          WAI_SECINW ROW_WAIT_OBJ# SQLTEXT                        BS          CH# OSUSER     HEX
---------- ---------- -------------------- ---------- -------------------- -------- ------------ --------------- ---------- ------------- ------------------------------ ---------- ---- ---------- ---------
ANBOB              77 enq: TX - row lock c oel7db1    sqlplus              ACTIVE              5 9zg9qd9bm4spu   0:5                   22 update user$ set spare6=DECODE 1:33          1 oracle       1000002

Note:
如果普通用户select any dictionary权限，那用户有可能使用for update给sys.user$加系统锁，导致其它用户无法登录。

grant read on sys.user$ (12c new feature)

# session 1
SQL> revoke select on sys.user$  from anbob;
Revoke succeeded.

SQL>  grant read on sys.user$ to anbob;
Grant succeeded.

# session 2
SQL> select * from sys.user$ for update;
select * from sys.user$ for update
                 *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> select count(*) from sys.user$ --without for update;
  COUNT(*)
----------
       146

Note:
orcle 12c引入grant read 替代grant select ，这样只有select权限没有for update的权限。

SQL92_SECURITY变化

# session 1
SQL> grant update on u10.tab1 to anbob;
Grant succeeded.

# session 2, as 'anbob' user
SQL> show user;
USER is "ANBOB"

SQL> update u10.tab1 set id=2 where id=1;
update u10.tab1 set id=2 where id=1
           *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL92_SECURITY变化，在12cR2中userA如果只有update其它用户表的权限，而没有select权限将会报错ora-1031, 需要授予select 权限on update的表。

ORA-01031: insufficient privileges inspite of having Update Privilege in 12cR2 (Doc ID 2345625.1)

In 12.2.0.1 database, Security has been tighten up and thus users who doesn’t have a SELECT privilege cannot update objects inspite of having UPDATE privileges. SQL92_SECURITY parameter default value is set to TRUE, starting from 12.2 DB.

Reference:
————–
SQL92_SECURITY: specifies whether users must have been granted the SELECT privilege on a table to execute an UPDATE or DELETE statement that references table column values in a WHERE or SET clause.

Parameter type : Boolean
Default value : true

— over —

打赏

微信扫一扫，打赏作者吧～