查询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;SQL> select * from user_sys_privs;USERNAME                       PRIVILEGE                                ADM------------------------------ ---------------------------------------- ---ZDZQ                           UNLIMITED TABLESPACE                     NO3.查看角色(只能查看登陆用户拥有的角色)所包含的权限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_roles6.查看用户或角色所拥有的角色：  select * from dba_role_privs;  select * from user_role_privs;--查询拥有DBA权限的用户SQL> select * from dba_role_privs where granted_role='DBA';GRANTEE                        GRANTED_ROLE                   ADM DEF------------------------------ ------------------------------ --- ---SYS                            DBA                            YES YESSYSTEM                         DBA                            YES YESZSZQ                           DBA                            NO  YESKSWORK

7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)select * from V$PWFILE_USERS比如我要查看用户 wzsb的拥有的角色： SQL> select * from dba_sys_privs where grantee='ZSZQ';GRANTEE                        PRIVILEGE                                ADM------------------------------ ---------------------------------------- ---ZSZQ                           UNLIMITED TABLESPACE                     NO查看一个用户所有的权限及角色select privilege  from dba_sys_privs where grantee = 'ZSZQ'unionselect privilege  from dba_sys_privs where grantee in             (select granted_role from dba_role_privs where grantee = 'ZSZQ');SQL> select * from dba_sys_privs where grantee='ZSZQ';GRANTEE                        PRIVILEGE                                ADM------------------------------ ---------------------------------------- ---ZSZQ                           UNLIMITED TABLESPACE                     NO8、查看RESOURCE具有那些权限 SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='RESOURCE';GRANTEE                        PRIVILEGE                                ADM------------------------------ ---------------------------------------- ---RESOURCE                       CREATE TRIGGER                           NORESOURCE                       CREATE SEQUENCE                          NORESOURCE                       CREATE TYPE                              NORESOURCE                       CREATE PROCEDURE                         NORESOURCE                       CREATE CLUSTER                           NORESOURCE                       CREATE OPERATOR                          NORESOURCE                       CREATE INDEXTYPE                         NORESOURCE                       CREATE TABLE                             NO已选择8行。SQL> select * from role_sys_privs t1 where t1.role = 'RESOURCE';ROLE                           PRIVILEGE                                ADM------------------------------ ---------------------------------------- ---RESOURCE                       CREATE SEQUENCE                          NORESOURCE                       CREATE TRIGGER                           NORESOURCE                       CREATE CLUSTER                           NORESOURCE                       CREATE PROCEDURE                         NORESOURCE                       CREATE TYPE                              NORESOURCE                       CREATE OPERATOR                          NORESOURCE                       CREATE TABLE                             NORESOURCE                       CREATE INDEXTYPE                         NO已选择8行。

9.查看scott用户的默认表空间、临时表空间select username, default_tablespace, temporary_tablespace  from dba_users where username = 'SCOTT';10.查看scott用户的系统权限select username,privilege,admin_option from user_sys_privs where username = 'SCOTT';SQL> select username,privilege,admin_option  2  from user_sys_privs  3  where username = 'SCOTT';USERNAME                       PRIVILEGE                                ADM------------------------------ ---------------------------------------- ---SCOTT                          CREATE VIEW                              NOSCOTT                          UNLIMITED TABLESPACE                     NO11.查看赋予scott用户的对象权限select grantee,owner, table_name, t.grantor, t.privilege, t.grantable, t.hierarchyfrom dba_tab_privs twhere t.grantee = 'SCOTT'; 12.查看授予了scott的角色权限select t.grantee, t.granted_role, t.admin_option, t.default_role  from dba_role_privs t where t.grantee = 'SCOTT';SQL> select t.grantee, t.granted_role, t.admin_option, t.default_role  2    from dba_role_privs t  3   where t.grantee = 'SCOTT';GRANTEE                        GRANTED_ROLE                   ADM DEF------------------------------ ------------------------------ --- ---SCOTT                          RESOURCE                       NO  YESSCOTT                          CONNECT                        NO  YESSQL> select * from user_role_privs t;USERNAME                       GRANTED_ROLE                   ADM DEF OS_------------------------------ ------------------------------ --- --- ---ZSZQ                           CONNECT                        NO  YES NOZSZQ                           DBA                            NO  YES NOZSZQ                           EXP_FULL_DATABASE              NO  YES NOZSZQ                           IMP_FULL_DATABASE              NO  YES NOZSZQ                           RESOURCE                       NO  YES NO13.查看scott用户使用了哪些表空间select t.table_name, t.tablespace_namefrom  dba_all_tables twhere t.owner = 'SCOTT' ;14.查看当前用户拥有的权限select t.privilege from session_privs t;SQL> select t.privilege from session_privs t;PRIVILEGE----------------------------------------CREATE SESSIONUNLIMITED TABLESPACECREATE TABLECREATE CLUSTERCREATE VIEWCREATE SEQUENCECREATE PROCEDURECREATE TRIGGERCREATE TYPECREATE OPERATORCREATE INDEXTYPE已选择11行。15.查看角色(DBA)被赋予的角色权限select * from role_role_privs t where t.role = 'DBA';查看角色(DBA)被赋予的对象权限16.select * from role_tab_privs t1  where t1.role = 'DBA';