Oracle使用者及角色的許可權管理[Oracle基礎]

1.檢視所有使用者：
  select * from dba_users;
  select * from all_users;
  select * from user_users;
2.檢視使用者或角色系統許可權(直接賦值給使用者或角色的系統許可權)：
  select * from dba_sys_privs;
  select * from user_sys_privs;
3.檢視角色(只能檢視登陸使用者擁有的角色)所包含的許可權
sql>select * from role_sys_privs;
4.檢視使用者物件許可權：
  select * from dba_tab_privs;
  select * from all_tab_privs;
  select * from user_tab_privs;
5.檢視所有角色：
  select * from dba_roles;
6.檢視使用者或角色所擁有的角色：
  select * from dba_role_privs;
  select * from user_role_privs;
7.檢視哪些使用者有sysdba或sysoper系統許可權(查詢時需要相應許可權)
    SQL> select * from dba_role_privs where grantee='CX_ZJ_ROS';                  -------------使用者所擁有的角色
  GRANTEE      GRANTED_ROLE   ADM  DEF
  ----------  --------------- ----- ----
  CX_ZJ_ROS ZHRO NO  YES
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='ZHRO';       --------這裡查詢的是使用者和自定義角色所擁有的許可權                                                                      
GRANTEE   PRIVILEGE      ADM
-------- ------------ -------------------
ZHRO   CREATE SEQUENCE   NO
ZHRO   CREATE SESSION    NO
ZHRO    CREATE TABLE     NO
ZHRO   UNLIMITED TABLESPACE  NO  
------------這裡的UNLIMITED TABLESPACE許可權其實是不能透過角色的方式授予的                                                                      5 rows selected.      
SQL> select * from role_sys_privs where role='CONNECT';    
-------這裡查詢的是系統角色所擁有的許可權
ROLE       PRIVILEGE        ADM
--------- ---------------- ----------
CONNECT    CREATE SESSION   NO
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='ILOG_RTS';
GRANTEE    PRIVILEGE             ADM
--------- --------------------- -------------
ILOG_RTS   UNLIMITED TABLESPACE  NO
SQL> select * from dba_role_privs where grantee='ILOG_RTS';
GRANTEE    GRANTED_ROLE        ADM DEF
---------- -------------       --- ---
ILOG_RTS   CONNECT              NO  YES
ILOG_RTS    RESOURCE            NO  YES
select * from V$PWFILE_USERS
TABLE_NAME            COMMENTS
-----------------   -------------------------------------
DBA_CONNECT_ROLE_GRANTEES      Information regarding which users are granted CONNECT
DBA_ROLES                      All Roles which exist in the database
DBA_ROLE_PRIVS                 Roles granted to users and roles
DBA_SCHEDULER_JOB_ROLES        All scheduler jobs in the database by database role
USER_ROLE_PRIVS                Roles granted to current user
ROLE_ROLE_PRIVS                Roles which are granted to roles
ROLE_SYS_PRIVS                 System privileges granted to roles
ROLE_TAB_PRIVS                 Table privileges granted to roles
SESSION_ROLES                  Roles which the user currently has enabled.


TABLE_NAME               COMMENTS
-------------------     -------------------------------------
DBA_AQ_AGENT_PRIVS
DBA_COL_PRIVS                  All grants on columns in the database
DBA_ROLE_PRIVS                 Roles granted to users and roles
DBA_RSRC_CONSUMER_GROUP_PRIVS  Switch privileges for consumer groups
DBA_RSRC_MANAGER_SYSTEM_PRIVS  system privileges for the resource manager
DBA_SYS_PRIVS                  System privileges granted to users and roles
DBA_TAB_PRIVS                  All grants on objects in the database
USER_COL_PRIVS                 Grants on columns for which the user is the owner, grantor or grantee
USER_COL_PRIVS_MADE            All grants on columns of objects owned by the user
    USER_COL_PRIVS_RECD            Grants on columns for which the user is the grantee
USER_ROLE_PRIVS                Roles granted to current user
USER_RSRC_CONSUMER_GROUP_PRIVS Switch privileges for consumer groups for the user
USER_RSRC_MANAGER_SYSTEM_PRIVS system privileges for the resource manager for the user
USER_SYS_PRIVS                 System privileges granted to current user
USER_TAB_PRIVS                 Grants on objects for which the user is
                               the owner, grantor or grantee
USER_TAB_PRIVS_MADE            All grants on objects owned by the user
USER_TAB_PRIVS_RECD            Grants on objects for which the user is the grantee
ALL_COL_PRIVS                  Grants on columns for which the user is
                               the grantor, grantee, owner,or an enabled role or PUBLIC is the grantee

ALL_COL_PRIVS_MADE             Grants on columns for which the user is owner or grantor
ALL_COL_PRIVS_RECD             Grants on columns for which the user, PUBLIC or enabled role is the grantee
ALL_TAB_PRIVS                  Grants on objects for which the user is the grantor, grantee,
                               owner,or an enabled role or PUBLIC is the grantee

ALL_TAB_PRIVS_MADE             User's grants and grants on user's objects
ALL_TAB_PRIVS_RECD             Grants on objects for which the user, PUBLIC or enabled role is the grantee
ROLE_ROLE_PRIVS                Roles which are granted to roles
ROLE_SYS_PRIVS                 System privileges granted to roles
ROLE_TAB_PRIVS                 Table privileges granted to roles
SESSION_PRIVS                  Privileges which the user currently hasset
GV$ENABLEDPRIVS                Synonym for GV_$ENABLEDPRIVS
V$ENABLEDPRIVS                 Synonym for V_$ENABLEDPRIVS

set linesize 120
col username for a20
col ACCOUNT_STATUS for a30
col CREATED for a30
set pagesize 600
col DEFAULT_TABLESPACE for a30
select username,ACCOUNT_STATUS,CREATED,DEFAULT_TABLESPACE from dba_users order by CREATED,ACCOUNT_STATUS;


col GRANTEE for a30
col GRANTED_ROLE for a30
col ADMIN_OPTION for a20
col DEFAULT_ROLE for a20
-------------這裡查詢的是使用者角色所擁有的角色
select * from dba_role_privs where grantee in (select username from dba_users where username not in ('SYS','SYSTEM') AND ACCOUNT_STATUS='OPEN') order by GRANTEE,GRANTED_ROLE;
-------------這裡查詢的是使用者和自定義角色所擁有的許可權
select distinct GRANTEE,PRIVILEGE,ADMIN_OPTION from (SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION FROM DBA_SYS_PRIVS WHERE GRANTEE in (select GRANTED_ROLE from dba_role_privs where grantee in (select username from dba_users where username not in ('SYS','SYSTEM') AND ACCOUNT_STATUS='OPEN')) union SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION FROM DBA_SYS_PRIVS WHERE GRANTEE in (select username from dba_users where username not in ('SYS','SYSTEM') AND ACCOUNT_STATUS='OPEN')) order by GRANTEE,PRIVILEGE;

----------------------------End-----------------------------------------------------------------------