oracle权限分为系统权限、角色权限、对象权限三个级别。
有时候梳理个别用户的权限需要把以上三类都查个遍才能完整知道该用户的权限信息。
-------系统权限,
直接通过grant create table to username此类赋权语句赋权。
select b.grantee, b.privilege, b.admin_optionfrom dba_users a, dba_sys_privs b
where a.created > to_date('2014', 'yyyy')
and a.username = b.grantee
order by b.grantee, b.privilege
------角色权限
通过grant connect,resource to username.此类语句赋权。
其中每个角色具体拥有什么权限,可以通过例如select * from dba_sys_privs where grantee = 'RESOURCE'进行查看。
select i.grantee, i.granted_role, i.admin_option, i.default_rolefrom dba_users h, dba_role_privs i
where h.created > to_date('2014', 'yyyy')
and h.username = i.grantee
order by i.grantee, i.granted_role
--------表级权限
通过grant select on tab1 to username此类语句赋权。
select p.*from dba_users o, dba_tab_privs p
where o.created > to_date('2014', 'yyyy')
and o.username = p.grantee
order by p.grantee, p.owner, p.table_name
以下是个实用例子,过滤用户是否有DDL权限。
------过滤DDL权限select b.grantee, b.privilege, b.admin_option
from dba_users a, dba_sys_privs b
where a.created > to_date('2014', 'yyyy')
and a.username = b.grantee
and b.privilege like 'ALTER%'
or b.privilege like 'CREATE%'
or b.privilege like 'DROP%'
order by b.grantee, b.privilege