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

關於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