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