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  

 5 rows selected. 

------------這裡的UNLIMITED TABLESPACE權限事實上是不能通過角色的方式授予的     

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

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;

本文轉自mfrbuaa部落格園部落格，原文連結：http://www.cnblogs.com/mfrbuaa/p/5346894.html，如需轉載請自行聯系原作者