1、在SDE数据库中创建测试面状要素类testfeature，如下：
SQL> desc sde.testfeature;
Name Type Nullable Default Comments
-------- --------------- -------- ------- --------
OBJECTID INTEGER
TYPE NUMBER(5) Y
SHAPE SDE.ST_GEOMETRY Y
2 创建数据库访问角色和用户，并授权
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
CREATE USER user1 IDENTIFIED by password123
DEFAULT TABLESPACE SDE
TEMPORARY TABLESPACE TEMP;
GRANT CONNECT,RESOURCE to user1;
CREATE USER user2 IDENTIFIED by password123
DEFAULT TABLESPACE SDE
TEMPORARY TABLESPACE TEMP;
GRANT CONNECT,RESOURCE to user2;
ALTER USER user1 GRANT CONNECT THROUGH sde;
ALTER USER user2 GRANT CONNECT THROUGH sde;
CREATE ROLE role1 NOT IDENTIFIED;
GRANT SELECT ON SDE.testfeature TO role1;
GRANT role1 TO user1;
GRANT role1 TO user2;
|
ALTERUSERuser1 GRANTCONNECTTHROUGH sde;
ALTERUSERuser2 GRANTCONNECTTHROUGH sde;
或可以不用创建角色，直接给用户授权：
|
1
2
|
grant select on sde.testfeature TO user1;
grant select on sde.testfeature TO user2;
|
3 在sde用户中创建策略函数（以sde用户登录）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
CREATE OR REPLACE FUNCTION get_data(p_owner in varchar2, p_object in varchar2) return varchar2 IS
v_region varchar2(40);
begin
v_region := sys_context('USERENV','SESSION_USER');
IF v_region = 'USER1' then
return 'Type=1';
ELSIF v_region = 'USER2' then
return 'Type=2';
ELSIF v_region = 'USER3' then
return 'Type=3';
ELSE
return null;
end IF;
end;
|
4 在sys用户中应用策略
|
1
2
3
4
5
6
7
8
9
10
11
|
BEGIN
dbms_rls.add_policy(
object_schema => 'sde',
object_name => 'testfeature',
policy_name => 'testfeature_pol',
function_schema => 'sde',
policy_function => 'get_data',
Statement_Types =>'Select',
Enable =>True);
END;
|
5、 测试
（1）以sde用户连接数据库，加载要素类testfeature，结果如下：
备注：策略函数中sde用户返回值为null，即不加谓词。
（2）以user1用户连接数据库，加载要素类testfeature，结果如下：
备注：user1用户返回值为Type=1，即只显示Type=1的要素。
查看策略使用情况：
|
1
|
select object_owner,object_name,policy,predicate from V$VPD_POLICY;
|
清除所有数据：
|
1
2
3
4
5
6
7
8
9
10
11
|
drop user user1 cascade;
drop user user2 cascade;
drop role role1;
drop function sde.get_data;
BEGIN
DBMS_RLS.drop_policy (
object_schema => 'sde',
object_name => 'testfeature',
policy_name => 'testfeature_pol');
END;
commit;
|
本文转自stock0991 51CTO博客，原文链接：http://blog.51cto.com/qing0991/1374177，如需转载请自行联系原作者
