Oracle 11g用户权限查询介绍
/**(oracle 11g)用户对应的权限**/
--角色权限、系统权限
select a.grantee,
a.granted_role,
to_char(replace(wm_concat(b.privilege), ',', ' || ')) privilege,
a.admin_option,
a.default_role
from dba_role_privs a
left join role_sys_privs b
on a.GRANTED_ROLE = b.ROLE
where a.grantee = 'SBNI'
group by a.grantee, a.granted_role, a.admin_option, a.default_role
union all
select grantee, '' granted_role, privilege, admin_option, '' default_role
from dba_sys_privs
where grantee = 'SBNI';--对象权限
select GRANTEE,
OWNER,
TABLE_NAME,
GRANTOR,
to_char(wm_concat(PRIVILEGE)),
GRANTABLE,
HIERARCHY
from dba_tab_privs
where grantee = 'SBNI'
group by GRANTEE, OWNER, TABLE_NAME, GRANTOR, GRANTABLE, HIERARCHY;--汇总：角色权限、系统权限、用户权限
select a.grantee,
a.granted_role as "granted_role----table_name",
to_char(replace(wm_concat(b.privilege), ',', ' || ')) privilege,
'admin_option:' || a.admin_option as "option",
'default_role:' || a.default_role as "default_role----hierarchy"
from dba_role_privs a
left join role_sys_privs b
on a.granted_role = b.role
where a.grantee = 'SBNI'
group by a.grantee, a.granted_role, a.admin_option, a.default_role
union all
select grantee,
'' granted_role,
privilege,
'admin_option:' || admin_option as "option",
'' default_role
from dba_sys_privs
where grantee = 'SBNI'
union all
select grantee,
owner || '.' || table_name as "table_name",
--grantor,
to_char(wm_concat(privilege)) as "privilege",
'grant_option:' || grantable as "option",
'hierarchy:' || hierarchy
from dba_tab_privs
where grantee = 'SBNI'
group by grantee, owner, table_name, grantor, grantable, hierarchy;