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]