1）scott用户下创建测试表vpd_tab
SCOTT@ORCL>create table vpd_tab(x number(2),y number(2),username varchar2(30));
Table created.
SCOTT@ORCL>
2）插入测试数据，使Y列为0时，username列为‘XXF',Y列为1时，username为’XZQ'
SCOTT@ORCL>insert into vpd_tab select rownum,mod(rownum,2),decode(mod(rownum,2),1,'XXF','XZQ') from dual connect by rownum<=20;
20 rows created.
SCOTT@ORCL>commit;
Commit complete.
SCOTT@ORCL>
3）查看插入数据后的效果
SCOTT@ORCL>select * from vpd_tab;
X Y USERNAME
---------- ---------- ------------------------------
1 1 XXF
2 0 XZQ
3 1 XXF
4 0 XZQ
5 1 XXF
6 0 XZQ
7 1 XXF
8 0 XZQ
9 1 XXF
10 0 XZQ
11 1 XXF
12 0 XZQ
13 1 XXF
14 0 XZQ
15 1 XXF
16 0 XZQ
17 1 XXF
18 0 XZQ
19 1 XXF
20 0 XZQ
20 rows selected.
SCOTT@ORCL>
4）scott用户下创建访问控制所需函数，函数的效果为，当访问scott.vpd_tab时，如果‘XXF’用户访问，那么，只返回vpd_tab表中username为'XXF‘的那些记录，如果’XZQ‘用户访问，那么，只返回vpd_tab表中username为'XZQ‘的那些记录，如果是除了XXF和XZQ之外的用户访问，那么返回空。
注：sys用户有上帝的权限，他不受控于访问控制。
SCOTT@ORCL>
CREATE OR REPLACE FUNCTION fun_vpd_tab_lq(p_schema IN VARCHAR2,
p_object IN VARCHAR2)
RETURN VARCHAR2 IS
v_username VARCHAR2(30);
v_return VARCHAR2(100);
BEGIN
SELECT USER
INTO v_username
FROM dual;
IF v_username = upper('XXF') THEN
v_return := 'username=''XXF''';
ELSIF v_username = upper('XZQ') THEN
v_return := 'username=''XZQ''';
ELSE
v_return := '1=2';
END IF;
RETURN v_return;
END;
/
Function created.
SCOTT@ORCL>
5）在sys用户下创建策略，使函数fun_vpd_tab_lq应用于测试表vpd_tab。
注：如果在scott用户下创建策略，那么需要给scott赋权，如下：
grant execute on dbms_rls to scott;
SCOTT@ORCL>conn / as sysdba
Connected.
SYS@ORCL>
BEGIN
DBMS_RLS.ADD_POLICY (object_schema => 'scott',
object_name => 'vpd_tab',
policy_name => 'p_vpd_tab_lq',
function_schema => 'scott',
policy_function => 'fun_vpd_tab_lq');
END;
/
PL/SQL procedure successfully completed.
SYS@ORCL>
6）创建测试用户xxf和xzq，并将scott用户下的测试表vpd_tab的读权限赋予xxf和xzq
SYS@ORCL>grant connect,resource to xxf identified by oracle;
Grant succeeded.
SYS@ORCL>grant connect,resource to xzq identified by oracle;
Grant succeeded.
SYS@ORCL>
SYS@ORCL>conn scott/tiger
Connected.
SCOTT@ORCL>grant select on vpd_tab to xxf,xzq;
Grant succeeded.
SCOTT@ORCL>
7）验证1：xxf用户只允许访问username为'XXF'的记录（也就是y字段为1的记录）
SCOTT@ORCL>conn xxf/oracle
Connected.
XXF@ORCL>select * from scott.vpd_tab;
X Y USERNAME
---------- ---------- ------------------------------
1 1 XXF
3 1 XXF
5 1 XXF
7 1 XXF
9 1 XXF
11 1 XXF
13 1 XXF
15 1 XXF
17 1 XXF
19 1 XXF
10 rows selected.
XXF@ORCL>
7）验证2：xzq用户只允许访问username为'XZQ'的记录（也就是y字段为0的记录）
XXF@ORCL>conn xzq/xzq
Connected.
XZQ@ORCL>
XZQ@ORCL>select * from scott.vpd_tab;
X Y USERNAME
---------- ---------- ------------------------------
2 0 XZQ
4 0 XZQ
6 0 XZQ
8 0 XZQ
10 0 XZQ
12 0 XZQ
14 0 XZQ
16 0 XZQ
18 0 XZQ
20 0 XZQ
10 rows selected.
XZQ@ORCL>
7）验证3：SYSTEM用户访问测试表，返回空，即函数中“1=2”这个条件起作用。
XZQ@ORCL>conn system/oracle
Connected.
SYSTEM@ORCL>select * from scott.vpd_tab;
no rows selected
SYSTEM@ORCL>
8）验证4：SYS用户访问测试表，返回所有记录，拥有上帝权限的效果。
SYSTEM@ORCL>conn / as sysdba
Connected.
SYS@ORCL>select * from scott.vpd_tab;
X Y USERNAME
---------- ---------- ------------------------------
1 1 XXF
2 0 XZQ
3 1 XXF
4 0 XZQ
5 1 XXF
6 0 XZQ
7 1 XXF
8 0 XZQ
9 1 XXF
10 0 XZQ
11 1 XXF
12 0 XZQ
13 1 XXF
14 0 XZQ
15 1 XXF
16 0 XZQ
17 1 XXF
18 0 XZQ
19 1 XXF
20 0 XZQ
20 rows selected.
SYS@ORCL>
小结：
细粒度访问控制的核心在那个函数上，本测试只是一个简单的测试，在工作中，也许会有很多稀奇古怪的需求，那么，也许这个功能可以帮助我们开阔一下思路。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/685769/viewspace-741664/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
【SQL*Plus】中&非绑定变量
请登录后发表评论
登录
全部评论
