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