[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$语句.