Oracle用户的权限查询、授予、撤销

#查询用户基本信息如创建日期
SELECT * FROM ALL_USERS WHERE USERNAME = 'DEV';

#查询用户的系统权限‌，这些权限通常是对数据库整体操作的权限，如CREATE TABLE、DROP ANY TABLE等    
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE ='DEV'; 

#查询当前用户有权访问‌的对象权限‌，不需要DBA权限，普通用户即可查询。
SELECT * FROM ALL_TAB_PRIVS WHERE GRANTEE = 'DEV'; 
#查询用户对整个数据库被授予的对象权限，对表、视图、序列等具体对象的SELECT、INSERT、UPDATE、DELETE等操作权限，该视图需要DBA权限才能访问如SELECT ANY DICTIONARY。
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'DEV';

#查询用户拥有的角色 
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'DEV';

#查询角色拥有的权限
SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'RESOURCE';

#查询用户对OPS的所有表的权限 
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'DEV' AND OWNER = 'OPS';

#查询OPS表空间下的表和所有者 
SELECT TABLE_NAME, OWNER FROM ALL_TABLES WHERE TABLESPACE_NAME ='OPS';

#查询用户对某个表的权限
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'DEV' AND OWNER = 'OPS' AND TABLE_NAME = 'A1'; 

#确认表的类型及所有者
SELECT OWNER,TABLE_NAME,TEMPORARY FROM ALL_TABLES WHERE OWNER='OPS' AND TABLE_NAME='A1';

#授予DEV用户对单个表的查询权限
GRANT SELECT ON OPS.A2 TO DEV;

#授予DEV用户查询SCHEMA_OWNER(OPS)用户下所有表的权限,先生成授权命令、再执行授权命令。

SELECT 'GRANT SELECT ON OPS.' || table_name || ' TO DEV;' 
FROM all_tables 
WHERE owner = 'OPS';

GRANT SELECT ON OPS.A3 TO DEV;
GRANT SELECT ON OPS.A2 TO DEV;
GRANT SELECT ON OPS.A1 TO DEV;
GRANT SELECT ON OPS.HIS TO DEV;
GRANT SELECT ON OPS.HIS_CONFIG_INFO TO DEV;
GRANT SELECT ON OPS.HH TO DEV;

#查询用户'DEV'被授予的对用户'OPS'所拥有对象的SELECT权限
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'DEV' AND OWNER = 'OPS' AND PRIVILEGE = 'SELECT';

---------------------------------------------------------------------------------------------------------------------------------
#针对单个表撤销权限
REVOKE SELECT ON OPS.A1 FROM DEV;

#一次性撤销用户对所有表的SELECT权限
REVOKE SELECT ANY TABLE FROM DEV;

#撤销DEV用户RESOURCE角色
REVOKE RESOURCE FROM DEV;

#使用动态SQL批量撤销权限，实现了批量撤销DEV用户对OPS用户下所有表的SELECT权限，BEGIN
  FOR t IN (SELECT table_name FROM all_tables WHERE owner = 'OPS') LOOP
    EXECUTE IMMEDIATE 'REVOKE SELECT ON OPS.' || t.table_name || ' FROM DEV';
  END LOOP;
END;
/

#撤销后查询用户'DEV'被授予的对用户'OPS'所拥有对象的SELECT权限
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'DEV' AND OWNER = 'OPS' AND PRIVILEGE = 'SELECT';