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