Oracle10g 预定义主要角色

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> set pages 50
SQL> select * from dba_roles;

ROLE PASSWORD
------------------------------ --------
CONNECT NO
RESOURCE NO
DBA NO
SELECT_CATALOG_ROLE NO
EXECUTE_CATALOG_ROLE NO
DELETE_CATALOG_ROLE NO
EXP_FULL_DATABASE NO
IMP_FULL_DATABASE NO
RECOVERY_CATALOG_OWNER NO
GATHER_SYSTEM_STATISTICS NO
LOGSTDBY_ADMINISTRATOR NO
AQ_ADMINISTRATOR_ROLE NO
AQ_USER_ROLE NO
GLOBAL_AQ_USER_ROLE GLOBAL
SCHEDULER_ADMIN NO
HS_ADMIN_ROLE NO
OEM_ADVISOR NO
OEM_MONITOR NO
MGMT_USER NO
PLUSTRACE NO

20 rows selected.

SQL>

1. CONNECT
2. RESOURCE
3. DBA
4. EXP_FULL_DATABASE
5. IMP_FULL_DATABASE
6. DELETE_CATALOG_ROLE
7. EXECUTE_CATALOG_ROLE
8. SELECT_CATALOG_ROLE

1-3是为了同ORACLE老版本中的概念相兼容而提供的，不能只依赖于这些ROLE
4-5是为了使用Import和Export实用程序的方便而提供的
6-8是为了数据字典视图和包的卸载而提供的

1.CONNECT 角色, 是授予最终用户的典型权利，最基本的

SQL> select * from dba_sys_privs where grantee = 'CONNECT'; --系统权限

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO

SQL> select * from dba_tab_privs where grantee = 'CONNECT'; --对象权限

no rows selected

SQL> select * from dba_role_privs where grantee = 'CONNECT'; --角色权限

no rows selected

SQL>

2.RESOURCE 角色, 是授予开发人员的

SQL> select * from dba_sys_privs where grantee = 'RESOURCE';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO

8 rows selected.

SQL> select * from dba_tab_privs where grantee = 'RESOURCE';

no rows selected

SQL> select * from dba_role_privs where grantee = 'RESOURCE';

no rows selected

SQL>

3.DBA 角色, 拥有系统所有系统级权限

SQL> select count(*) from dba_sys_privs where grantee = 'DBA';

COUNT(*)
----------
160

SQL> select count(*) from dba_tab_privs where grantee = 'DBA';

COUNT(*)
----------
38

SQL> select * from dba_role_privs where grantee = 'DBA';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
DBA SCHEDULER_ADMIN YES YES
DBA EXECUTE_CATALOG_ROLE YES YES
DBA DELETE_CATALOG_ROLE YES YES
DBA PLUSTRACE YES YES
DBA SELECT_CATALOG_ROLE YES YES
DBA EXP_FULL_DATABASE NO YES
DBA GATHER_SYSTEM_STATISTICS NO YES
DBA IMP_FULL_DATABASE NO YES

8 rows selected.

SQL>

4.IMP_FULL_DATABASE 角色

SQL> select count(*) from dba_sys_privs where grantee = 'IMP_FULL_DATABASE';

COUNT(*)
----------
68

SQL> select count(*) from dba_tab_privs where grantee = 'IMP_FULL_DATABASE';

COUNT(*)
----------
14

SQL> select * from dba_role_privs where grantee = 'IMP_FULL_DATABASE';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
IMP_FULL_DATABASE EXECUTE_CATALOG_ROLE NO YES
IMP_FULL_DATABASE SELECT_CATALOG_ROLE NO YES

SQL>

5.EXP_FULL_DATABASE 角色

SQL> select * from dba_sys_privs where grantee = 'EXP_FULL_DATABASE';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
EXP_FULL_DATABASE READ ANY FILE GROUP NO
EXP_FULL_DATABASE RESUMABLE NO
EXP_FULL_DATABASE EXECUTE ANY PROCEDURE NO
EXP_FULL_DATABASE EXECUTE ANY TYPE NO
EXP_FULL_DATABASE SELECT ANY TABLE NO
EXP_FULL_DATABASE ADMINISTER RESOURCE MANAGER NO
EXP_FULL_DATABASE BACKUP ANY TABLE NO
EXP_FULL_DATABASE SELECT ANY SEQUENCE NO

8 rows selected.

SQL> select count(*) from dba_tab_privs where grantee = 'EXP_FULL_DATABASE';

COUNT(*)
----------
39

SQL> select * from dba_role_privs where grantee = 'EXP_FULL_DATABASE';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
EXP_FULL_DATABASE EXECUTE_CATALOG_ROLE NO YES
EXP_FULL_DATABASE SELECT_CATALOG_ROLE NO YES

SQL>

6.DELETE_CATALOG_ROLE 角色

这个角色是Oracle8新增加的，如果授予用户这个角色，用户就可以从表sys.aud$和FGA_LOG$中删除记录，sys.aud$和FGA_LOG$表中记录着审计后的记录，使用这个角色可以简化审计踪迹管理。

SQL> select * from dba_sys_privs where grantee = 'DELETE_CATALOG_ROLE';

no rows selected

SQL> select owner, table_name, privilege, grantable from dba_tab_privs where grantee = 'DELETE_CATALOG_ROLE';

OWNER TABLE_NAME PRIVILEGE GRA
------------------------------ ------------------------------ ---------------------------------------- ---
SYS AUD$ DELETE NO
SYS FGA_LOG$ DELETE NO

SQL> select * from dba_role_privs where grantee = 'DELETE_CATALOG_ROLE';

no rows selected

SQL>

7.SELECT_CATALOG_ROLE 角色, 具有从数据字典查询的权利

SQL> select * from dba_sys_privs where grantee = 'SELECT_CATALOG_ROLE';

no rows selected

SQL> select count(*) from dba_tab_privs where grantee = 'SELECT_CATALOG_ROLE';

COUNT(*)
----------
1671

SQL> select * from dba_role_privs where grantee = 'SELECT_CATALOG_ROLE';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
SELECT_CATALOG_ROLE HS_ADMIN_ROLE NO YES

--进一步看看HS_ADMIN_ROLE角色

SQL> select * from dba_sys_privs where grantee = 'HS_ADMIN_ROLE';

no rows selected

SQL> select owner, table_name, privilege, grantable from dba_tab_privs where grantee = 'HS_ADMIN_ROLE';

OWNER TABLE_NAME PRIVILEGE GRA
------------------------------ ------------------------------ ---------------------------------------- ---
SYS HS_FDS_CLASS SELECT NO
SYS HS_FDS_INST SELECT NO
SYS HS_BASE_CAPS SELECT NO
SYS HS_CLASS_CAPS SELECT NO
SYS HS_INST_CAPS SELECT NO
SYS HS_BASE_DD SELECT NO
SYS HS_CLASS_DD SELECT NO
SYS HS_INST_DD SELECT NO
SYS HS_CLASS_INIT SELECT NO
SYS HS_INST_INIT SELECT NO
SYS HS_ALL_CAPS SELECT NO
SYS HS_ALL_DD SELECT NO
SYS HS_ALL_INITS SELECT NO
SYS HS_FDS_CLASS_DATE SELECT NO
SYS DBMS_HS EXECUTE NO

15 rows selected.

SQL> select * from dba_role_privs where grantee = 'HS_ADMIN_ROLE';

no rows selected

SQL>

8.EXECUTE_CATALOG_ROLE 角色, 具有从数据字典中执行部分过程和函数的权利

SQL> select * from dba_sys_privs where grantee = 'EXECUTE_CATALOG_ROLE';

no rows selected

SQL> select count(*) from dba_tab_privs where grantee = 'EXECUTE_CATALOG_ROLE';

COUNT(*)
----------
77

SQL> select * from dba_role_privs where grantee = 'EXECUTE_CATALOG_ROLE';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
EXECUTE_CATALOG_ROLE HS_ADMIN_ROLE NO YES

SQL>

--End--