公司近期要回收赋予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 的权限,不影响