ORACLE数据库用户赋权和相应权限是否赋成功检查

授予角色

GRANT CONNECT TO USER1;  

授予某个用户CONNECT角色的权限

查看该用户是否有此角色

SELECT * FROM user_role_privs WHERE username = 'USER1';

查看此角色所包含的系统权限

SELECT * FROM role_sys_privs t WHERE t.ROLE = 'CONNECT';

GRANT RESOURCE TO USER1;

SELECT * FROM user_role_privs WHERE username = 'USER1';

SELECT * FROM role_sys_privs t WHERE t.ROLE = 'RESOURCE';

SELECT * FROM user_sys_privs WHERE username = 'USER1';

或者 SELECT * FROM dba_sys_privs WHERE grantee = 'USER1';

 

GRANT exp_full_database TO USER1;

--具有数据库逻辑备份时的数据导出权限

GRANT imp_full_database TO USER1;

--具有数据库逻辑备份时的数据导入权限

SELECT * FROM user_role_privs WHERE username = 'USER1';

GRANT DBA TO USER1;

--拥有系统的所有系统权限

 

授予系统权限

GRANT CREATE ANY DIRECTORY TO USER1;
GRANT DROP ANY DIRECTORY TO USER1;

--创建DIRECTORY的权限：数据泵导入导出使用

SELECT * FROM user_sys_privs WHERE username = 'USER1';

GRANT SELECT ANY DICTIONARY TO DIMPLE;

--允许查询以"DBA_"开头的数据字典：数据泵导入导出使用

SELECT * FROM user_sys_privs WHERE username = 'USER1';

GRANT CREATE TABLESPACE TO USER1;
GRANT ALTER TABLESPACE TO USER1;
GRANT DROP TABLESPACE TO USER1;

--创建/修改/删除表空间

GRANT CREATE USER TO USER1;
GRANT ALTER USER TO USER1;
GRANT DROP USER TO USER1;

--创建/修改/删除用户

 

GRANT CREATE PUBLIC SYNONYM TO USER1;
GRANT DROP PUBLIC SYNONYM TO USER1;

创建/删除公共同义词

 

 

GRANT EXECUTE ON SYS.DBMS_LOCK TO USER1 WITH GRANT OPTION;
GRANT EXECUTE ON SYS.DBMS_PIPE TO USER1 WITH GRANT OPTION;
GRANT EXECUTE ON SYS.DBMS_SYSTEM TO USER1 WITH GRANT OPTION;
GRANT EXECUTE ON sys.utl_recomp TO USER1;

--允许用户授权这些给其他用户

SELECT * FROM user_tab_privs t WHERE t.table_name IN ('DBMS_LOCK','DBMS_PIPE','DBMS_SYSTEM','UTL_RECOMP');

 

GRANT SYSDBA TO USER1;

系统管理员权限 可开关数据库等

SELECT * FROM v$pwfile_users t WHERE username = 'USER1';

权限相关数据字典视图

 

DBA_TAB_PRIVS 包含数据库所有的对象权限信息

DBA_SYS_PRIVS 包含数据库中所有的系统权限信息

SESSION_PRIVS 包含当前数据库用户可以使用的权限信息：包含因角色继承的权限

DBA_COL_PRIVS 包含数据库中所有授予表列上的对象权限信息

ALL_COL_PRIVS_MADE 包含当前用户作为对象权限的授予者，在所有列上的对象权限信息

ALL_COL_PRIVS_RECD 包含当前用户作为对象权限的接收者，在所有列上的对象权限信息

 

场景一：

--DROP用户 
DROP USER ULOG_TEST CASCADE;
--新建用户 
CREATE USER ULOG_TEST PROFILE "DEFAULT" IDENTIFIED BY ULOG DEFAULT TABLESPACE TBS_ULOG  TEMPORARY TABLESPACE TBS_TEMP;
--赋CONNECT权限
GRANT CONNECT TO ULOG_TEST;
--无记录
SELECT * FROM dba_sys_privs t WHERE t.grantee = 'ULOG_TEST';
--赋RESOURCE权限
GRANT RESOURCE TO ULOG_TEST;
--有一条 UNLIMITED TABLESPACE 权限
SELECT * FROM dba_sys_privs t WHERE t.grantee = 'ULOG_TEST';
--------------------------测试场景---------------------------------
--赋DBA权限
GRANT DBA TO ULOG_TEST;
--仍有一条 UNLIMITED TABLESPACE 权限 
SELECT * FROM dba_sys_privs t WHERE t.grantee = 'ULOG_TEST';
--收回DBA权限
REVOKE DBA FROM ULOG_TEST;
--UNLIMITED TABLESPACE 的权限也收回了
SELECT * FROM dba_sys_privs t WHERE t.grantee = 'ULOG_TEST';