Oralce public access 許可權的回收後影響分析

公司近期要回收賦予public的許可權,因為以前的表一幫都賦予了 grant select on table_name to public.
這樣是很不安全的,但是回收會引起程式出錯,或不能直接訪問表. 所以寫了程式來進行分析:
主要針對三種情況:
1. 直接被賦予select any table 許可權的使用者是不受影響的.
2. 透過role具有select any table 的許可權,如果是select table是沒有問題的,但是如果在物件,如package, view 中引用物件,就會出錯.
3. 我們已經具有的一個select 的role,可以查詢所有應用schema 的表, 這種情況的分析和情況2類似.

所以按以下步驟來處理:
a ) 寫了以下函式進行判斷和分析.
CREATE OR REPLACE FUNCTION JUDGEPUBLIC(P_USERNAME DBA_USERS.USERNAME%TYPE) RETURN VARCHAR2 IS
       L_RESULT VARCHAR2(200) := ' ';
       LN_PRI_SELECT_ANY NUMBER(1) := 0;
       LN_APP_SELECT NUMBER(1) := 0;
BEGIN
       --判斷是否具有SELECT ANY TABLE 的許可權
       SELECT COUNT(A.PRIVILEGE)
         INTO LN_PRI_SELECT_ANY
         FROM DBA_SYS_PRIVS A
        WHERE A.GRANTEE=P_USERNAME
          AND A.PRIVILEGE='SELECT ANY TABLE' ;
        
        IF LN_PRI_SELECT_ANY > 0 THEN
          L_RESULT:='N: 具有SELECT ANY TABLE 的許可權,不影響';
          RETURN(L_RESULT);
        END IF;
        
        --判斷它是否屬於一個ROLE, 而這個ROLE又具有SELECT ANY TABLE 的許可權,
        SELECT COUNT(*)
          INTO LN_PRI_SELECT_ANY
          FROM DBA_SYS_PRIVS T
         WHERE T.GRANTEE IN
        (SELECT A.GRANTED_ROLE
                  FROM DBA_ROLE_PRIVS A
                 START WITH A.GRANTEE=P_USERNAME    --遞迴查詢,因為ROLE可能會層層巢狀
                 CONNECT BY PRIOR A.GRANTED_ROLE=A.GRANTEE )
          AND PRIVILEGE IN ('SELECT ANY TABLE') ;
        IF LN_PRI_SELECT_ANY > 0 THEN
          L_RESULT:='N: 透過ROLE具有SELECT ANY TABLE 的許可權, 如果不透過物件引用,則不影響;否則,則有影響';
          RETURN(L_RESULT);
        END IF;
   
        --判斷此帳號是否屬於直接屬於ROLE APP_SELECT, 或透過別的ROLE間接屬於ROLE  
        SELECT COUNT(*)
          INTO LN_APP_SELECT
          FROM
         (SELECT *
          FROM DBA_ROLE_PRIVS A
          START WITH A.GRANTEE=P_USERNAME    --遞迴查詢,因為ROLE可能會層層巢狀
           CONNECT BY PRIOR A.GRANTED_ROLE=A.GRANTEE)
          WHERE GRANTED_ROLE='APP_SELECT';
                          
         IF LN_APP_SELECT >0 THEN
            L_RESULT:='N: 屬於ROLE APP_SELECT, 如果不透過物件引用,則不影響;否則,則有影響';
            RETURN(L_RESULT);
         END IF;
         
         --如果既不具有select any table 許可權,也沒有透過role具有select any table 許可權, 同時不直接和間接屬於APP_SELECT 角色
         L_RESULT:='Y: 既不具有select any table 許可權,也沒有透過role具有select any table 許可權, 同時不直接和間接屬於APP_SELECT 角色,有影響';
         RETURN L_RESULT;
         
END JUDGEPUBLIC;
/
b) 執行SQL得到分析結果:
   SELECT Q.username,judgepublic(P_USERNAME => USERNAME) impact FROM DBA_USERS Q ORDER BY 1;
   
c) 結果如下:
username             impact
AGT                      N: 透過ROLE具有SELECT ANY TABLE 的許可權, 如果不透過物件引用,則不影響;否則,則有影響
ANONYMOUS        Y: 既不具有select any table 許可權,也沒有透過role具有select any table 許可權, 同時不直接和間接屬於WII_SELECT 角色,有影響
APEX_030200      Y: 既不具有select any table 許可權,也沒有透過role具有select any table 許可權, 同時不直接和間接屬於WII_SELECT 角色,有影響
A_TEST                N: 具有SELECT ANY TABLE 的許可權,不影響
BIGDATA              N: 具有SELECT ANY TABLE 的許可權,不影響