/**(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;
如对本文有疑问， 点击进行留言回复！！
Oracle 基本概念 Dadabase,schema,user,table...
dbeaver连接Oracle中文乱码的解决方案--druid
Oracle常用语句（时间格式、去重、去特殊符号、查询表空间等）
Navicate 如何导出数据库中的存储过程、事件、视图等？
每日一记：Oracle升级2020年4月份的数据库补丁Database Patch Set Update : 11.2.0.4.200414 (30670774)
我要评论
网友评论