1. 创建procedure设置上下文
create or replace procedure sys.my_ctx_procedure
(
p_ctx_name varchar2,
p_ctx_value varchar2
)
IS
BEGIN
DBMS_SESSION.SET_CONTEXT('my_ctx', p_ctx_name, p_ctx_value);
END my_ctx_procedure;
/
2 创建上下文
CREATE OR REPLACE CONTEXT my_ctx USING sys.my_ctx_procedure;
3 创建policy使用的function
create or replace FUNCTION sys.my_security_fun (D1 VARCHAR2, D2 VARCHAR2) RETURN VARCHAR2
IS
D_predicate VARCHAR2 (2000);
BEGIN
D_predicate := 'id = SYS_CONTEXT(''my_ctx'', ''id'')';
RETURN D_predicate;
END my_security_fun;
/
grant execute on sys.my_security_fun to rman;
3 创建policy
BEGIN
DBMS_RLS.DROP_POLICY(policy_name=>'my_policy',object_schema=>'rman',object_name=>'test');
END;
/
BEGIN
DBMS_RLS.ADD_POLICY(object_schema=>'rman', object_name=>'test',
policy_name=>'my_policy', function_schema=>'sys',
policy_function=>'my_security_fun',
STATEMENT_TYPES=>'select,update,insert,delete',
update_check => true );
END;
/
grant execute on sys.my_ctx_procedure to rman;
4 测试上下文
conn rman/rman
exec sys.my_ctx_procedure ('id',1);
select * from test;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27143/viewspace-672630/ ，如需转载，请注明出处，否则将追究法律责任。
