瀟湘隱者

在平時的數(shù)據(jù)庫運維管理過程中，我們可能會遇到很多權(quán)限管理相關(guān)的需求，例如，有時候需要給一個用戶授予相關(guān)權(quán)限或回收相關(guān)權(quán)限，那么可能先要把用戶授予的權(quán)限查詢/例舉出來， 有時候需要對比DEV/UAT環(huán)境，兩個相同賬號的權(quán)限是否不一致......,其實各種權(quán)限相關(guān)需求還是非常多的，這里就不一一例舉了。工欲善其事必先利其器，我們就需要一個SQL語句能夠方便、快速，且全面列出用戶相關(guān)權(quán)限(角色、系統(tǒng)權(quán)限，對象權(quán)限...)。下面分享、介紹這樣的一個SQL ，希望這個SQL能滿足你的需求。

我們在測試環(huán)境創(chuàng)建一個用戶TEST,然后在這個用戶下面創(chuàng)建一些表，如下所示

SQL> CREATE USER TEST IDENTIFIED BY "Test#13579";

User created.

SQL> GRANT CONNECT TO TEST;

Grant succeeded.

SQL> GRANT CREATE TABLE, CREATE VIEW TO TEST;

Grant succeeded.

SQL> 

---創(chuàng)建相關(guān)表對象等語句略過
.................................................
.................................................

如下所示，我們來查詢一下賬戶TEST授予的相關(guān)權(quán)限，如下截圖所示：

然后我們創(chuàng)建一個用戶TEST1，授予下面相關(guān)權(quán)限，如下所示

SQL> CREATE USER TEST1 IDENTIFIED BY "Test#24680";

User created.

SQL> GRANT CONNECT TO TEST1;

Grant succeeded.

SQL> GRANT CREATE TABLE, CREATE VIEW,CREATE SYNONYM TO TEST1;

Grant succeeded.

SQL> GRANT SELECT ,UPDATE, DELETE ON TEST.T1 TO TEST1;

Grant succeeded.

SQL> GRANT UPDATE(OBJECT_ID, OBJECT_NAME) ON TEST.T2 TO TEST1;

Grant succeeded.
 
SQL> GRANT SELECT ON TEST.V_T1 TO TEST1;

Grant succeeded.

SQL>

然后，我們此時查詢一下賬號TEST1授予的相關(guān)權(quán)限，如下截圖所示：

如上兩個例子所示，這個腳本還是非常方便、明了的。腳本find_user_right_info.sql的定義如下所示：

/*-*****************************************************************************************************************
Script Name     :    find_user_right_info.sql
Author          :    瀟湘隱者
Script Function :    查看某個用戶被授予的所有權(quán)限.
Description     :    如果你想找出某一個用戶授予的相關(guān)權(quán)限,那么可以使用這個腳本.
********************************************************************************************************************
Parameters      :                                    參數(shù)說明
--------------------------------------------------------------------------------------------------------------------
&USERNAME            數(shù)據(jù)庫用戶/賬號
********************************************************************************************************************
注意事項：

    1: 請用sys/system賬號運行腳本.
********************************************************************************************************************
 Modified Date    Modified User     Version                 Modified Reason
--------------------------------------------------------------------------------------------------------------------
2024-06-28        瀟湘隱者          1.0                    創(chuàng)建此腳本。
2025-01-06        瀟湘隱者          1.1                    腳本輸出結(jié)果格式優(yōu)化/調(diào)優(yōu)
2025-08-28        瀟湘隱者          1.2                    增加列權(quán)限輸出
********************************************************************************************************************/
SET LINESIZE 720
SET PAGESIZE 60
COL PRIV_TYPE FOR A9
COL PRIVILEGE FOR A16
COL OBJ_OWNER FOR A10
COL OBJ_NAME FOR A30
COL USERNAME FOR A14
COL GRANT_SOURCES FOR A16
COL ADMIN_OPTION FOR A10
COL HIERARCHY FOR A10
SELECT
    PRIV_TYPE,
    PRIVILEGE,
    OBJ_OWNER,
    OBJ_NAME,
    LISTAGG(GRANT_TARGET, ',') WITHIN GROUP (ORDER BY GRANT_TARGET) AS GRANT_SOURCES, -- Lists the sources of the permission
    USERNAME,
    MAX(ADMIN_OPTION) AS ADMIN_OPTION,    -- MAX acts as a Boolean OR by picking 'YES' over 'NO'
    MAX(HIERARCHY) AS HIERARCHY           -- MAX acts as a Boolean OR by picking 'YES' over 'NO'
FROM (
    -- gets all roles a user has, even inherited ones
    WITH RU AS (
        SELECT DISTINCT CONNECT_BY_ROOT GRANTEE AS GRANTED_USER, GRANTED_ROLE
        FROM DBA_ROLE_PRIVS
        CONNECT BY GRANTEE = PRIOR GRANTED_ROLE
    )
    SELECT
          PRIV_TYPE,
          PRIVILEGE,
          OBJ_OWNER,
          OBJ_NAME,
          USERNAME,
          REPLACE(GRANT_TARGET, USERNAME, 'Direct to user') AS GRANT_TARGET,
          ADMIN_OPTION,
          HIERARCHY
    FROM (
        -- system privileges granted directly to users
        SELECT 'SYSTEM'         AS PRIV_TYPE
             , PRIVILEGE        AS PRIVILEGE
             , '---'            AS OBJ_OWNER
             , '---'            AS OBJ_NAME
             , GRANTEE          AS USERNAME
             , GRANTEE          AS GRANT_TARGET
             , ADMIN_OPTION     AS ADMIN_OPTION
             , NULL             AS HIERARCHY
        FROM DBA_SYS_PRIVS
        WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
        UNION ALL
        -- system privileges granted users through roles
        SELECT 'ROLE'           AS PRIV_TYPE
             , GRANTED_ROLE     AS PRIVILEGE
             , '---'            AS OBJ_OWNER
             , '---'            AS OBJ_NAME
             , GRANTEE          AS USERNAME
             , GRANTEE          AS GRANT_TARGET
             , ADMIN_OPTION     AS ADMIN_OPTION
             , NULL             AS HIERARCHY
        FROM DBA_ROLE_PRIVS RP 
        INNER JOIN DBA_ROLES R ON RP.GRANTED_ROLE = R.ROLE 
        --FROM DBA_SYS_PRIVS
        --JOIN RU ON RU.GRANTED_ROLE = DBA_SYS_PRIVS.GRANTEE
        UNION ALL
        -- object privileges granted directly to users
        SELECT P.TYPE           AS PRIV_TYPE
             , P.PRIVILEGE      AS PRIVILEGE
             , P.OWNER          AS OBJ_OWNER
             , P.TABLE_NAME     AS OBJ_NAME
             , P.GRANTEE        AS USERNAME
             , P.GRANTEE        AS GRANT_TARGET
             , P.GRANTABLE      AS ADMIN_OPTION
             , P.HIERARCHY      AS HIERARCHY
        FROM DBA_TAB_PRIVS P
        WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
        UNION ALL
        -- object privileges granted users through roles
        SELECT 'ROLE'           AS PRIV_TYPE
             , PRIVILEGE        AS PRIVILEGE
             , OWNER            AS OBJ_OWNER
             , TABLE_NAME       AS OBJ_NAME
             , RU.GRANTED_USER  AS USERNAME
             , RU.GRANTED_ROLE  AS GRANT_TARGET
             , GRANTABLE        AS ADMIN_OPTION
             , HIERARCHY        AS HIERARCHY
        FROM DBA_TAB_PRIVS
        JOIN RU ON RU.GRANTED_ROLE = DBA_TAB_PRIVS.GRANTEE
        UNION ALL 
        -- column privileges granted directly to users 
        SELECT  'COLUMN'        AS PRIV_TYPE
               ,PRIVILEGE       AS PRIVILEGE
               ,OWNER           AS OBJ_OWNER
               ,TABLE_NAME||'(' || COLUMN_NAME ||')'
                                AS OBJ_NAME
               ,GRANTEE         AS USERNAME
               ,GRANTEE         AS GRANT_TARGET
               ,GRANTABLE       AS ADMIN_OPTION
               ,INHERITED       AS INHERITED
        FROM DBA_COL_PRIVS
        WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
    ) ALL_USER_PRIVS
    WHERE USERNAME = UPPER(TRIM('&USERNAME'))
) DISTINCT_USER_PRIVS
GROUP BY
    PRIV_TYPE,
    PRIVILEGE,
    OBJ_OWNER,
    OBJ_NAME,
    USERNAME
;