關於許可權管理的實用指令碼

在工作中，可能會接觸到很多的環境問題，對於許可權問題，總是感覺心有餘力而力不足，環境太多了，可能在賦予許可權的時候會出差錯，
比如下面的場景，資料都儲存在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
with TEMP_DML AS 
(
select 'SELECT' temp_dml from dual
)
select upper('TESTO_SEL') grantee,t1.owner,t1.table_name ,TEMP_DML.temp_dml missing_role_privs from all_tables t1,TEMP_DML where  t1.owner=upper('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_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