瀟湘隱者

昨天遇到了一個需求,需要找出A用戶只授予了B用戶查詢權限的所有表. 實際情況中, A用戶可能將一些表的SELECT,UPDATE, INSERT,DELETE等權限授予用戶B,現在我們需要找出A用戶下只授予了SELECT權限給B用戶的表,那么SQL腳本如何實現呢? 這個是我當時寫的一個腳本.分享如下所示:

SET LINESIZE WINDOW
COL OWNER FOR A8
COL OBJECT_NAME FOR A30
COL TYPE FOR A16
COL GRANTEE FOR A12
COL GRANTOR FOR A8
COL PRIVILEGE FOR A16
COL GRANTABLE FOR A8
SELECT T.OWNER
      ,T.TABLE_NAME AS OBJECT_NAME
      ,T.TYPE
      ,T.GRANTOR
      ,T.PRIVILEGE
      ,T.GRANTEE
      ,T.GRANTABLE
FROM DBA_TAB_PRIVS T
INNER JOIN (
    SELECT OWNER
         , TABLE_NAME
         , GRANTEE
         , COUNT(*) 
    FROM  DBA_TAB_PRIVS
    WHERE OWNER=UPPER(TRIM('&grantor_user'))
    GROUP BY OWNER, TABLE_NAME, GRANTEE
    HAVING(COUNT(*)) =1
) L ON T.OWNER= L.OWNER AND T.TABLE_NAME= L.TABLE_NAME AND L.GRANTEE= T.GRANTEE
WHERE T.PRIVILEGE='SELECT' 
AND T.TYPE='TABLE'
AND T.GRANTEE=UPPER(TRIM('&grantee_user'));

注意:: Oracle 18c以下版本,需要用命令SET LINESIZE n 替換SET LINESIZE WINDOW. 這個命令只支持Oracle 18c或以上版本.