[20230119]12c grant read 特性.txt

[20230119]12c grant read 特性.txt --//昨天看了链接:https://www.anbob.com/archives/7053.html --//想起以前我家里的机器使用toad以scott用户登陆遇到的问题,我虽然已经授权dba权限,但是还是报ora-1031错误. --//仔细检查才发现登陆时需要访问sys.user$,当时直接scott可以访问sys.user$权限. --//toad 登陆要执行如下: SELECT u.NAME FROM sys.USER$ u WHERE u.TYPE# = 1 ORDER BY 1; --//我个人对数据库安全并不是很重视,我现在维护多数情况下都是sys用户登陆. --//简单测试链接遇到的情况. 1.环境: SCOTT@test01p> @ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 --//我的scott用户建立如下: CREATE USER SCOTT   IDENTIFIED BY <password>   DEFAULT TABLESPACE USERS   TEMPORARY TABLESPACE TEMP   PROFILE DEFAULT   ACCOUNT UNLOCK;   -- 3 Roles for SCOTT   GRANT CONNECT TO SCOTT;   GRANT DBA TO SCOTT;   GRANT RESOURCE TO SCOTT;   ALTER USER SCOTT DEFAULT ROLE ALL;   -- 2 System Privileges for SCOTT   GRANT ALTER SYSTEM TO SCOTT;   GRANT UNLIMITED TABLESPACE TO SCOTT;   -- 6 Object Privileges for SCOTT     GRANT EXECUTE ON SYS.DBMS_LOCK TO SCOTT;     GRANT EXECUTE ON SYS.SYS_PLSQL_D9B1149D_9_1 TO SCOTT WITH GRANT OPTION;     GRANT EXECUTE ON SYS.SYS_PLSQL_FAA5F685_2385_1 TO SCOTT WITH GRANT OPTION;     GRANT EXECUTE, READ, WRITE ON DIRECTORY SYS.TMP_EXPDP TO SCOTT WITH GRANT OPTION;     GRANT SELECT ON SYS.USER$ TO SCOTT;     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~     GRANT SELECT ON SYS.V_$SESSION TO SCOTT; 2.测试: --//session 1,以scott用户登陆: select * from sys.user$ for update; --//输出略. --//session 2: sqlplus scott/xxxx@test01p --//挂起!! --//session 1; SCOTT@test01p> @ wcx &1min -- Display ASH Wait Chain Signatures script v0.7 by Tanel Poder ( http://blog.tanelpoder.com ) %This     SECONDS        AAS WAIT_CHAIN                                                                                              FIRST_SEEN          LAST_SEEN ------ ---------- ---------- ------------------------------------------------------------------------------------------------------- ------------------- -------------------  100%          60          1 -> 251,37182,@1=>99,49718,@1=>enq: TX - row lock contention -> [idle blocker 1,251,37182 (sqlplus.exe)] 2023-01-23 21:09:49 2023-01-23 21:10:48 SCOTT@test01p> rollback; Rollback complete. --//session 2可以正常登陆.也就是以前用户select权限,可以执行select .. for update,导致其它用户无法执行dml操作. --//取消select权限. SYS@test01p> revoke select on sys.user$  from scott; Revoke succeeded. SYS@test01p> grant read on sys.user$ to scott; Grant succeeded. --//session 1: SCOTT@test01p> select * from sys.user$ for update; select * from sys.user$ for update                   * ERROR at line 1: ORA-01031: insufficient privileges 3.一点疑问: --//12c有1个新特性,每次登陆要修改记录登陆时间,在sys.user$的spare6字段. SCOTT@test01p> select SPARE6 from sys.user$ where name='SCOTT'; SPARE6 -------------------- 2023-01-23 13:21:04 --//要执行dml语句. SCOTT@test01p> select sql_text ,PARSING_SCHEMA_NAME from v$sqlarea where sql_id='9zg9qd9bm4spu'   2  @ prxx_win; ============================== SQL_TEXT                      : update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1 PARSING_SCHEMA_NAME           : SYS PL/SQL procedure successfully completed. --//很明显开始登陆以sys用户执行一些语句,其中包括update user$语句.