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.
|
1. CONNECT
2. RESOURCE
3. DBA
1-3是為了同ORACLE老版本中的概念相相容而提供的，不能只依賴於這些ROLE
4. EXP_FULL_DATABASE
5. IMP_FULL_DATABASE
4-5是為了使用Import和Export實用程式的方便而提供的
6. DELETE_CATALOG_ROLE
7. EXECUTE_CATALOG_ROLE
8. SELECT_CATALOG_ROLE
6-8是為了資料字典檢視和包的解除安裝而提供的
1.CONNECT 角色, 是授予終端使用者的典型權利，最基本的
SQL> select * from dba_sys_privs where grantee = 'CONNECT'; --系統許可權
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO
CREATE SESSION --建立會話
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.
CREATE TRIGGER --建立觸發器
CREATE SEQUENCE --建立序列
CREATE CLUSTER --建立聚簇
CREATE TYPE --建立型別
CREATE PROCEDURE --建立過程
CREATE OPERATOR
CREATE INDEXTYPE
CREATE TABLE --建表
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>