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

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

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

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

这是我们需要努力的方向,使得权限管理更加清晰,但是使用的过程中总是会遇到一些问题,比如有些表重建之后,权限就会丢失,如果操作不规范,就可能导致一些权限丢掉,或者赋予了过多的权限。比如给只读用户赋予了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

相关推荐