fine-grained access control功能笔记

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博客 ” ，链接：http://blog.itpub.net/27143/viewspace-672630/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27143/viewspace-672630/