ORACLE如何用一个脚本找出一个用户的授权信息？

在平时的数据库运维管理过程中，我们可能会遇到很多权限管理相关的需求，例如，有时候需要给一个用户授予相关权限或回收相关权限，那么可能先要把用户授予的权限查询/例举出来， 有时候需要对比DEV/UAT环境，两个相同账号的权限是否不一致......,其实各种权限相关需求还是非常多的，这里就不一一例举了。工欲善其事必先利其器，我们就需要一个SQL语句能够方便、快速，且全面列出用户相关权限(角色、系统权限，对象权限...)。下面分享、介绍这样的一个SQL ，希望这个SQL能满足你的需求。

我们在测试环境创建一个用户TEST,然后在这个用户下面创建一些表，如下所示

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> 

---创建相关表对象等语句略过
.................................................
.................................................

如下所示，我们来查询一下账户TEST授予的相关权限，如下截图所示：

然后我们创建一个用户TEST1，授予下面相关权限，如下所示

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授予的相关权限，如下截图所示：

如上两个例子所示，这个脚本还是非常方便、明了的。脚本find_user_right_info.sql的定义如下所示：

/*-*****************************************************************************************************************
Script Name     :    find_user_right_info.sql
Author          :    潇湘隐者
Script Function :    查看某个用户被授予的所有权限.
Description     :    如果你想找出某一个用户授予的相关权限,那么可以使用这个脚本.
********************************************************************************************************************
Parameters      :                                    参数说明
--------------------------------------------------------------------------------------------------------------------
&USERNAME            数据库用户/账号
********************************************************************************************************************
注意事项：

    1: 请用sys/system账号运行脚本.
********************************************************************************************************************
 Modified Date    Modified User     Version                 Modified Reason
--------------------------------------------------------------------------------------------------------------------
2024-06-28        潇湘隐者          1.0                    创建此脚本。
2025-01-06        潇湘隐者          1.1                    脚本输出结果格式优化/调优
2025-08-28        潇湘隐者          1.2                    增加列权限输出
********************************************************************************************************************/
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
;