查看Oracle用户权限及角色权限

查询Oracle用户表权限、角色权限、对象权限

查看用户拥有的角色及权限:
set linesize 500
set pagesize 500
col role for a20
col username for a23
col privilege for a30
col default_tablespace for a23
col temporary_tablespace for a23
col profile for a14
select a.grantee username, a.granted_role role,b.privilege,c.default_tablespace,c.temporary_tablespace,c.profile from dba_role_privs a,role_sys_privs b,dba_users c where a.granted_role=b.role and a.grantee='WXQYHUSR' and a.grantee=c.username union select grantee username,(select 'N/A' from dual) role,privilege,d.default_tablespace,d.temporary_tablespace,profile from dba_sys_privs,dba_users d where grantee='WXQYHUSR' and grantee=d.username  order by username;


select * from role_sys_privs where role='DATAROLE';  



set linesize 500
set pagesize 500
col role for a20
col username for a23
col privilege for a30
col default_tablespace for a23
col temporary_tablespace for a23
col profile for a14
select a.grantee username, a.granted_role role,b.privilege,c.default_tablespace,c.temporary_tablespace,c.profile from dba_role_privs a,role_sys_privs b,dba_users c where a.granted_role=b.role and a.grantee like 'LAMS%' and a.grantee=c.username union select grantee username,(select 'N/A' from dual) role,privilege,d.default_tablespace,d.temporary_tablespace,profile from dba_sys_privs,dba_users d where grantee like 'LAMS%' and grantee=d.username  order by username;


查看角色有哪些权限:
select * from role_sys_privs where role='APPSROLE';


查看用户拥有访问对象(表，视图，同义词)权限：
set linesize 500
set pagesize 500
col username for a25
col owner for a10
col table_owner for a20
col table_name for a30
col grantor for a20
col privilege for a20
select grantee username,privilege,owner table_owner,table_name,grantor,type from dba_tab_privs where grantee='PASUSR';

查看表权限:
col table_name for a20
col GRANTEE for a15
col PRIVILEGE for a30
col owner for a20
col GRANTOR for a15
select grantee,table_name,object_type type,owner table_owner,grantor,privilege from user_tab_privs  ,user_objects   where  object_name = table_name and  table_name in ('WP_JOB');




select owner,table_name,grantor,privilege,grantee from  dba_tab_privs where table_name in('WP_JOB');

set linesize 500
set pagesize 500
col username for a25
col owner for a10
col table_owner for a20
col table_name for a30
col grantor for a20
col privilege for a20
select grantee username,privilege,owner table_owner,table_name,grantor from dba_tab_privs where grantee='PASUSR';



同义词:
select * from dba_synonyms;
select * from user_synonyms;


set linesize 500
set pagesize 500
col table_owner for a25
col table_name for a25
col synonym_name for a25
select table_owner,table_name,synonym_name from  user_synonyms;

查看用户系统权限:
select * from dba_sys_privs where grantee='ADMIN';

查看用户对象(表，视图，同义词)权限:
select * from dba_tab_privs;
select * from user_tab_privs;

查看用户所拥有的角色权限:
select * from role_sys_privs where role='DATAROLE';  



select * from dba_role_privs where granted_role='ECPDATA_RW_ROLE';



查看表空间限额:  
-1是代表没有限制,其它值多少就是多少了. 

SQL> col tablespace_name for a20
SQL> col username for a20
SQL> select tablespace_name,username from  dba_ts_quotas;

TABLESPACE_NAME      USERNAME
-------------------- --------------------
MVOIDX               MVODEVUSR
MVODATA              MVODEVUSR
MVODATA              MVODATA
MVOIDX               MVODATA
SYSAUX               APPQOSSYS
MVOIDX               MVOUSR

6 rows selected.

SQL> select tablespace_name,username,max_blocks from  dba_ts_quotas;

TABLESPACE_NAME      USERNAME             MAX_BLOCKS
-------------------- -------------------- ----------
MVOIDX               MVODEVUSR                    -1
MVODATA              MVODEVUSR                    -1
MVODATA              MVODATA                      -1
MVOIDX               MVODATA                      -1
SYSAUX               APPQOSSYS                    -1
MVOIDX               MVOUSR                       -1

6 rows selected.