公司近期要回收赋予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 的权限,不影响
这样是很不安全的,但是回收会引起程序出错,或不能直接访问表. 所以写了程序来进行分析:
主要针对三种情况:
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 的权限,不影响
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/674865/viewspace-1225045/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/674865/viewspace-1225045/