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

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

1. SQL> CREATE USER TEST IDENTIFIED BY "Test#13579";
3. User created.
5. SQL> GRANT CONNECT TO TEST;
7. Grant succeeded.
9. SQL> GRANT CREATE TABLE, CREATE VIEW TO TEST;
11. Grant succeeded.
13. SQL>
15. ---创建相关表对象等语句略过
16. .................................................
17. .................................................

复制代码

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

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

1. SQL> CREATE USER TEST1 IDENTIFIED BY "Test#24680";
3. User created.
5. SQL> GRANT CONNECT TO TEST1;
7. Grant succeeded.
9. SQL> GRANT CREATE TABLE, CREATE VIEW,CREATE SYNONYM TO TEST1;
11. Grant succeeded.
13. SQL> GRANT SELECT ,UPDATE, DELETE ON TEST.T1 TO TEST1;
15. Grant succeeded.
17. SQL> GRANT UPDATE(OBJECT_ID, OBJECT_NAME) ON TEST.T2 TO TEST1;
19. Grant succeeded.
21. SQL> GRANT SELECT ON TEST.V_T1 TO TEST1;
23. Grant succeeded.
25. SQL>

复制代码

然后，我们此时查询一下账号TEST1授予的相关权限，如下截图所示：

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

1. /*-*****************************************************************************************************************
2. Script Name     :    find_user_right_info.sql
3. Author          :    潇湘隐者
4. Script Function :    查看某个用户被授予的所有权限.
5. Description     :    如果你想找出某一个用户授予的相关权限,那么可以使用这个脚本.
6. ********************************************************************************************************************
7. Parameters      :                                    参数说明
8. --------------------------------------------------------------------------------------------------------------------
9. &USERNAME            数据库用户/账号
10. ********************************************************************************************************************
11. 注意事项：
13.     1: 请用sys/system账号运行脚本.
14. ********************************************************************************************************************
15. Modified Date    Modified User     Version                 Modified Reason
16. --------------------------------------------------------------------------------------------------------------------
17. 2024-06-28        潇湘隐者          1.0                    创建此脚本。
18. 2025-01-06        潇湘隐者          1.1                    脚本输出结果格式优化/调优
19. 2025-08-28        潇湘隐者          1.2                    增加列权限输出
20. ********************************************************************************************************************/
21. SET LINESIZE 720
22. SET PAGESIZE 60
23. COL PRIV_TYPE FOR A9
24. COL PRIVILEGE FOR A16
25. COL OBJ_OWNER FOR A10
26. COL OBJ_NAME FOR A30
27. COL USERNAME FOR A14
28. COL GRANT_SOURCES FOR A16
29. COL ADMIN_OPTION FOR A10
30. COL HIERARCHY FOR A10
31. SELECT
32.     PRIV_TYPE,
33.     PRIVILEGE,
34.     OBJ_OWNER,
35.     OBJ_NAME,
36.     LISTAGG(GRANT_TARGET, ',') WITHIN GROUP (ORDER BY GRANT_TARGET) AS GRANT_SOURCES, -- Lists the sources of the permission
37.     USERNAME,
38.     MAX(ADMIN_OPTION) AS ADMIN_OPTION,    -- MAX acts as a Boolean OR by picking 'YES' over 'NO'
39.     MAX(HIERARCHY) AS HIERARCHY           -- MAX acts as a Boolean OR by picking 'YES' over 'NO'
40. FROM (
41.     -- gets all roles a user has, even inherited ones
42.     WITH RU AS (
43.         SELECT DISTINCT CONNECT_BY_ROOT GRANTEE AS GRANTED_USER, GRANTED_ROLE
44.         FROM DBA_ROLE_PRIVS
45.         CONNECT BY GRANTEE = PRIOR GRANTED_ROLE
46.     )
47.     SELECT
48.           PRIV_TYPE,
49.           PRIVILEGE,
50.           OBJ_OWNER,
51.           OBJ_NAME,
52.           USERNAME,
53.           REPLACE(GRANT_TARGET, USERNAME, 'Direct to user') AS GRANT_TARGET,
54.           ADMIN_OPTION,
55.           HIERARCHY
56.     FROM (
57.         -- system privileges granted directly to users
58.         SELECT 'SYSTEM'         AS PRIV_TYPE
59.              , PRIVILEGE        AS PRIVILEGE
60.              , '---'            AS OBJ_OWNER
61.              , '---'            AS OBJ_NAME
62.              , GRANTEE          AS USERNAME
63.              , GRANTEE          AS GRANT_TARGET
64.              , ADMIN_OPTION     AS ADMIN_OPTION
65.              , NULL             AS HIERARCHY
66.         FROM DBA_SYS_PRIVS
67.         WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
68.         UNION ALL
69.         -- system privileges granted users through roles
70.         SELECT 'ROLE'           AS PRIV_TYPE
71.              , GRANTED_ROLE     AS PRIVILEGE
72.              , '---'            AS OBJ_OWNER
73.              , '---'            AS OBJ_NAME
74.              , GRANTEE          AS USERNAME
75.              , GRANTEE          AS GRANT_TARGET
76.              , ADMIN_OPTION     AS ADMIN_OPTION
77.              , NULL             AS HIERARCHY
78.         FROM DBA_ROLE_PRIVS RP
79.         INNER JOIN DBA_ROLES R ON RP.GRANTED_ROLE = R.ROLE
80.         --FROM DBA_SYS_PRIVS
81.         --JOIN RU ON RU.GRANTED_ROLE = DBA_SYS_PRIVS.GRANTEE
82.         UNION ALL
83.         -- object privileges granted directly to users
84.         SELECT P.TYPE           AS PRIV_TYPE
85.              , P.PRIVILEGE      AS PRIVILEGE
86.              , P.OWNER          AS OBJ_OWNER
87.              , P.TABLE_NAME     AS OBJ_NAME
88.              , P.GRANTEE        AS USERNAME
89.              , P.GRANTEE        AS GRANT_TARGET
90.              , P.GRANTABLE      AS ADMIN_OPTION
91.              , P.HIERARCHY      AS HIERARCHY
92.         FROM DBA_TAB_PRIVS P
93.         WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
94.         UNION ALL
95.         -- object privileges granted users through roles
96.         SELECT 'ROLE'           AS PRIV_TYPE
97.              , PRIVILEGE        AS PRIVILEGE
98.              , OWNER            AS OBJ_OWNER
99.              , TABLE_NAME       AS OBJ_NAME
100.              , RU.GRANTED_USER  AS USERNAME
101.              , RU.GRANTED_ROLE  AS GRANT_TARGET
102.              , GRANTABLE        AS ADMIN_OPTION
103.              , HIERARCHY        AS HIERARCHY
104.         FROM DBA_TAB_PRIVS
105.         JOIN RU ON RU.GRANTED_ROLE = DBA_TAB_PRIVS.GRANTEE
106.         UNION ALL
107.         -- column privileges granted directly to users
108.         SELECT  'COLUMN'        AS PRIV_TYPE
109.                ,PRIVILEGE       AS PRIVILEGE
110.                ,OWNER           AS OBJ_OWNER
111.                ,TABLE_NAME||'(' || COLUMN_NAME ||')'
112.                                 AS OBJ_NAME
113.                ,GRANTEE         AS USERNAME
114.                ,GRANTEE         AS GRANT_TARGET
115.                ,GRANTABLE       AS ADMIN_OPTION
116.                ,INHERITED       AS INHERITED
117.         FROM DBA_COL_PRIVS
118.         WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
119.     ) ALL_USER_PRIVS
120.     WHERE USERNAME = UPPER(TRIM('&USERNAME'))
121. ) DISTINCT_USER_PRIVS
122. GROUP BY
123.     PRIV_TYPE,
124.     PRIVILEGE,
125.     OBJ_OWNER,
126.     OBJ_NAME,
127.     USERNAME
128. ;

复制代码

扫描上面二维码关注我如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
来源：豆瓜网用户自行投稿发布，如果侵权，请联系站长删除