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--

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]