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;
