关于Oracle权限管理的实用脚本

在工作中，可能会接触到很多的环境问题，对于权限问题，总是感觉心有余力而力不足，环境太多了，可能在赋予权限的时候会出差错，

在工作中，可能会接触到很多的环境问题，对于权限问题，总是感觉心有余力而力不足，环境太多了，可能在赋予权限的时候会出差错， 比如下面的场景，数据都存储在owner schema上，如果要访问这些数据，需要创建一些连接用户，所有的操作不能直接在owner schema下进行。

像下面的图形,我们可以根据访问的权限定义两个角色，通过角色来统一给赋予权限。比如TESTO_ALL可以赋予select,insert,delete,update的权限，而TESTL_SEL只能赋予SELECT的权限，这样在复杂的环境中就可以基本合理的控制权限。

这是我们需要努力的方向，使得权限管理更加清晰，但是使用的过程中总是会遇到一些问题，比如有些表重建之后，权限就会丢失，如果操作不规范，就可能导致一些权限丢掉，或者赋予了过多的权限。比如给只读用户赋予了delete权限，给需要做DML的用户只赋予了select权限，没有update权限。这些都会在使用中造成一些问题。
最近客户需要在环境中添加几个只读用户，但是在分配权限的时候，可能老是会丢掉一些权限，有时候涉及的表有上千个，由于连接用户有不少，在连接用户中创建完同义词，一个一个去验证也确实很费力，也不现实。因为环境已经被很多人动过了，可能有些权限本来就有问题，有些权限又丢失，开始的时候修复基本都是根据开发的反馈进行的。
不过这样确实比较被动，专门写了下面的脚本，专门来分析哪些权限丢失了，哪些权限是不应该赋予的。
假设表为owner schema为testo，表为test1,testo_sel应该只赋予select权限，如果赋予了delete,insert,update权限就不应该了。
检查是否有遗漏的select权限 --testo_sel

select t2.grantee,t1.owner,t1.table_name ,t2.privilege missing_role_privs from all_tables t1,dba_tab_privs t2 where t2.grantee =(select role from dba_roles where role='TESTO_SEL') and t2.privilege='SELECT' and t1.owner='TESTO' and t1.table_name=t2.table_name

检查是否有额外的权限 --testo_sel，排除Insert,delete,update权限
select t2.grantee,t1.owner,t1.table_name ,t2.privilege no_need_privs from all_tables t1,dba_tab_privs t2 where t1.owner='TESTO' and t1.table_name=t2.table_name and t2.grantee=(select role from dba_roles where role='TESTO_SEL') and t2.privilege in ('DELETE','INSERT','UPDATE')

对于testo_all的权限规则相对简单，只需要判断哪些权限应该赋予，但是却没有。思路简单，做起来稍微得绕个弯子。
从数据行中判断哪些数据行不全，，最后只得使用了with子句。
检查是否有遗漏的select,delete,update,insert权限
with TEMP_DML AS
(
select 'INSERT' temp_dml from dual
union all
select 'DELETE' temp_dml from dual
union all
select 'SELECT' temp_dml from dual
union all
select 'UPDATE' temp_dml from dual
)
select 'TESTO_ALL' grantee,t1.owner,t1.table_name ,TEMP_DML.temp_dml missing_role_privs from all_tables t1,TEMP_DML where t1.owner='TESTO'
minus
select t2.grantee,t1.owner,t1.table_name ,t2.privilege missing_role_privs from all_tables t1,dba_tab_privs t2 where t2.grantee =(select role from dba_roles where role='TESTO_ALL') and t2.privilege in('SELECT','DELETE','UPDATE','INSERT') and t1.owner='TESTO' and t1.table_name=t2.table_name

本文永久更新链接地址：