Oracle 用户权限

2023年 11月 17日 92.5k 0

Oracle 用户权限

– 查看用户拥有的系统角色及权限
SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE=UPPER(’&USERNAME’)
UNION ALL
SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE=UPPER(’&USERNAME’);

– 查看用户拥有的系统权限明细
SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE=UPPER(’&USERNAME’)
UNION ALL
SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE=UPPER(’&USERNAME’) );

– 查看用户拥有的系统权限及对象权限明细
SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE=UPPER(’&USERNAME’)
UNION ALL
SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE=UPPER(’&USERNAME’) )
UNION ALL
SELECT ‘OBJECT PRIVILEGES: ‘||T.PRIVILEGE||’ ON ‘||T.OWNER||’.’||T.TABLE_NAME||’ WITH GRANTABLE ‘||T.GRANTABLE FROM DBA_TAB_PRIVS T WHERE GRANTEE=UPPER(’&USERNAME’);

GRANT SELECT ON CAPITAL.BI_BALANCE_USD TO QUERY;
REVOKE CONNECT,RESOURCE FROM QUERY;
REVOKE SELECT ON CAPITAL.SYS_CORP FROM QUERY;

– 查看DBA角色用户
SELECT GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE=‘DBA’;

– 查看用户拥有的角色
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE=‘用户名’;

– 查看某个角色所拥有的权限
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE IN (‘CONNECT’,‘RESOURCE’);

– 数据库里面包含的角色
SELECT * FROM DBA_ROLES;

– 数据库包含的系统权限
SELECT NAME FROM SYS.SYSTEM_PRIVILEGE_MAP;

– 将用户A数据查询权限授权给用户B(主要就是表和视图)
SELECT ‘GRANT SELECT ON ‘||OWNER||’.’||OBJECT_NAME||’ TO &B;’ FROM DBA_OBJECTS WHERE OWNER=’&A’ AND OBJECT_TYPE IN (‘TABLE’,‘VIEW’);

– 对象权限相关视图
SELECT * FROM DBA_TAB_PRIVS;
SELECT * FROM ALL_TAB_PRIVS;
SELECT * FROM USER_TAB_PRIVS;

– 当前用户所拥有的全部权限
SELECT * FROM SESSION_PRIVS;
– 当前用户的系统权限
SELECT * FROM USER_SYS_PRIVS;
– 当前用户的对象权限
SELECT * FROM USER_TAB_PRIVS;
– 查询某个用户所拥有的系统权限
SELECT * FROM DBA_SYS_PRIVS;
– 查看角色(只能查看登陆用户拥有的角色)所包含的权限
SELECT * FROM ROLE_SYS_PRIVS;

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论