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