ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (精细策略dbms_rls)

oracle实验记录 (精细策略dbms_rls)

原创 Linux操作系统 作者:fufuh2o 时间:2009-07-28 12:12:21 0 删除 编辑

实验开始
SQL> select * from test;

        ID NAME
---------- ----------
         1 xh
         2 hr
         3 cc
         4 dd


SQL> conn zz/a850624
Connected.
SQL> conn yy/a666666
Connected.
SQL> select * from xh.test;

        ID NAME
---------- ----------
         1 xh
         2 hr
         3 cc
         4 dd

SQL> conn zz/a850624
Connected.
SQL> select * from xh.test;

        ID NAME
---------- ----------
         1 xh
         2 hr
         3 cc
         4 dd
要求:YY只可以访问  xh.test ID=1的信息  user zz 只可访问 xh.test id=2,3的信息,当USER 为ZZ时候可以更新ID=3的 信息
其他USER  不能更新任何, (sysdba可以 )

介绍下应用环境:
应用环境 就是一组属性
default 应用环境为 userenv

SQL> select sys_context('userenv','session_user') from dual;

SYS_CONTEXT('USERENV','SESSION_USER')
--------------------------------------------------------------------------------

SYS~~~~~~~~~~~~~~~~简单的查看了下当前应用环境中user
还可以查看db_domail,db_name,os_user,language,host,网络协议等

要精细访问就得自定义环境,自定义环境 要create any context权限
SQL> create or replace context actest using XH.test_pkg;

Context created.
环境名actest,它的所有属性都是由 sys.test_pkg 包设置的


下面建立xh.test_pkg 来设置环境属性
SQL> create or replace package xh.test_pkg as
  2  procedure set_test;
  3  end;
  4  /

SQL> ed
Wrote file afiedt.buf


  1  create or replace package body xh.test_pkg
  2  as
  3  procedure set_test is
  4  begin
  5  if sys_context('userenv','session_user')='YY' then
  6  dbms_session.set_context('actest','yy_attr',1);
  7  elsif sys_context('userenv','session_user')='ZZ' then
  8  dbms_session.set_context('actest','zz_attr1',2);
  9  dbms_session.set_context('actest','zz_attr2',3);
 10  end if;
 11  end;
 12* end;
SQL> /

Package body created.

 定义应用环境属性 dbms_session.set_context('环境名字'属性名,属性值)


接着要创建一个安全策略函数

SQL> conn / as sysdba
Connected.
SQL> create or replace package xh.test_p as
  2  function p_select(object_schema varchar2,object_name varchar2) return varch
ar2;
  3  function p_update(object_schema varchar2,object_name varchar2) return varch
ar2;
  4  end;
  5  /

Package created.

SQL> create or replace package body xh.test_p as
  2  function p_select(object_schema varchar2,object_name varchar2) return varch
ar2   is
  3  rtn_predicate varchar2(500);
  4  begin
  5  rtn_predicate :='1=1';~~~~~~~~~~~~~~~~~总真
  6  if user='YY' then
  7  rtn_predicate := 'id =sys_context("actest","yy_attr")';
  8  elsif user='ZZ' then
  9  rtn_predicate := 'id=sys_context("actest","zz_attr1")'||'or'||'id=sys_conte
xt("actest","zz_attr2")';
 10  end if;
 11  return rtn_predicate;
 12  end;
 13  function p_update(object_schema varchar2,object_name varchar2) return varch
ar2 is
 14  rtn_predicate varchar2(500);
 15  begin
 16  rtn_predicate:='1=2';~~~~~~~~~~~~~~~~~~~~~总false 这就表示 其他USER 不允许,返回false
 17  if user='ZZ' then
 18  rtn_predicate :='id=sys_context("actest","zz_attr2")';
 19  end if;
 20  return rtn_predicate;
 21  end;
 22  end;
 23  /

Package body created.                             rtn_predicate:PKG中函数 返回附加到SQL 语句where 后字符串

SQL> select * from xh.test where 1=1
  2  ;

        ID NAME
---------- ----------
         1 xh
         2 hr
         3 cc
         4 dd

SQL> select * from xh.test where 1=2;

no rows selected
~~~~~~~~~~~~~~~*************************************************


SQL> desc dbms_rls~~~~~~~~~~~~~~~~~~~~~~~~~~~用RLS

PROCEDURE ADD_POLICY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OBJECT_SCHEMA                  VARCHAR2                IN     DEFAULT
 OBJECT_NAME                    VARCHAR2                IN
 POLICY_NAME                    VARCHAR2                IN
 FUNCTION_SCHEMA                VARCHAR2                IN     DEFAULT
 POLICY_FUNCTION                VARCHAR2                IN
 STATEMENT_TYPES                VARCHAR2                IN     DEFAULT
 UPDATE_CHECK                   BOOLEAN                 IN     DEFAULT
 ENABLE                         BOOLEAN                 IN     DEFAULT
 STATIC_POLICY                  BOOLEAN                 IN     DEFAULT
 POLICY_TYPE                    BINARY_INTEGER          IN     DEFAULT
 LONG_PREDICATE                 BOOLEAN                 IN     DEFAULT
 SEC_RELEVANT_COLS              VARCHAR2                IN     DEFAULT
 SEC_RELEVANT_COLS_OPT          BINARY_INTEGER          IN     DEFAULT


增加策略
SQL> exec dbms_rls.add_policy('xh','test','sel_policy','xh','test_p.p_select','s
elect');

PL/SQL procedure successfully completed.


SQL> exec dbms_rls.add_policy('xh','test','upd_policy','xh','test_p.p_update','i
nsert,update,delete');

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.
创建一个 logon trigger

SQL> create or replace trigger logon_t
  2  after logon on database call xh.test_pkg.set_test
  3  /

Trigger created.

SQL> select * from xh.test;
select * from xh.test
                 *
ERROR at line 1:
ORA-28113: policy predicate has error


SQL> conn zz/a850624
Connected.
SQL> select * from xh.test;
select * from xh.test
                 *
ERROR at line 1:
ORA-28113: policy predicate has error~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~OH  雪特  看看trace user_dump_file

Error information for ORA-28113:
Logon user     : YY
Table/View     : XH.TEST
Policy name    : SEL_POLICY
Policy function: XH.TEST_P.P_SELECT
RLS predicate  :
id =sys_context("actest","yy_attr")
ORA-00904: "yy_attr": invalid identifier


Error information for ORA-28113:
Logon user     : ZZ
Table/View     : XH.TEST
Policy name    : SEL_POLICY
Policy function: XH.TEST_P.P_SELECT
RLS predicate  :
id=sys_context("actest","zz_attr1")orid=sys_context("actest","zz_attr2")
ORA-00907: missing right parenthesis~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~PLSQL不熟悉 " 不行,要用'' ~哎 ~还需要多看看 PLSQL的书籍太生

SQL> ed
Wrote file afiedt.buf

  1   create or replace package body xh.test_p as
  2   function p_select(object_schema varchar2,object_name varchar2) return varc
har2
  3   is
  4    rtn_predicate varchar2(500);
  5     begin
  6    rtn_predicate :='1=1';
  7    if user='YY' then
  8    rtn_predicate := 'id =sys_context(''actest'',''yy_attr'')';
  9    elsif user='ZZ' then
 10     rtn_predicate := 'id=sys_context(''actest'',''zz_attr1'')'||'or'||'id=sy
s_conte
 11  xt(''actest'',''zz_attr2'')';
 12   end if;
 13    return rtn_predicate;
 14     end;
 15    function p_update(object_schema varchar2,object_name varchar2) return var
char2 is
 16    rtn_predicate varchar2(500);
 17    begin
 18    rtn_predicate:='1=2';
 19   if user='ZZ' then
 20    rtn_predicate :='id=sys_context(''actest'',''zz_attr2'')';
 21    end if;
 22    return rtn_predicate;
 23    end;
 24*   end;
SQL> /

Package body created.

SQL> conn yy/a666666~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~测试OK
Connected.
SQL> select * from xh.test;

        ID NAME
---------- ----------
         1 xh

SQL> update xh.test set name='a';~~~~~~~~~~~~~~~~~~由于test_p.p_update这个 函数 其他USER 不能更新

0 rows updated.
conn / as sysdba
SQL> update xh.test set name='a';

4 rows updated.

SQL> roll back;
Rollback complete.
SQL> conn xh/a831115
Connected.
SQL> update test set name='a';

0 rows updated.~~~~~~~~~~~~~~~~~~由于test_p.p_update这个 函数 其他USER 不能更新


SQL> conn zz/a850624
Connected.
SQL> select * from xh.test;
select * from xh.test
                 *
ERROR at line 1:
ORA-28113: policy predicate has error

SQL> select * from xh.test;
select * from xh.test~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~需特 太雪特了
                 *
ERROR at line 1:
ORA-28113: policy predicate has error

Error information for ORA-28113:
Logon user     : ZZ
Table/View     : XH.TEST
Policy name    : SEL_POLICY
Policy function: XH.TEST_P.P_SELECT
RLS predicate  :
id=sys_context('actest','zz_attr1')orid=sys_conte~~~~~~~~~~~~~~~~~~~~~~~~~~~~~orid 低级错误
xt('actest','zz_attr2')
ORA-00907: missing right parenthesis

 1     create or replace package body xh.test_p as
 2      function p_select(object_schema varchar2,object_name varchar2) return
rchar2
 3        is
 4        rtn_predicate varchar2(500);
 5       begin
 6       rtn_predicate :='1=1';
 7      if user='YY' then
 8     rtn_predicate := 'id =sys_context(''actest'',''yy_attr'')';
 9     elsif user='ZZ' then
10      rtn_predicate := 'id=sys_context(''actest'',''zz_attr1'')'||' or '||'i~~~~~~~~~~~~~~~~~~~~~~~~~低级错误or没空格
sys_context(''actest'',''zz_attr2'')';
11       end if;
12        return rtn_predicate;
13       end;
14       function p_update(object_schema varchar2,object_name varchar2) return
archar2 is
15     rtn_predicate varchar2(500);
16        begin
17       rtn_predicate:='1=2';
18      if user='ZZ' then
19      rtn_predicate :='id=sys_context(''actest'',''zz_attr2'')';
20      end if;
21      return rtn_predicate;
22     end;
23*   end;
QL> /

ackage body created.
SQL> select * from xh.test;

        ID NAME
---------- ----------
         2 hr
         3 cc

SQL> conn zz/a850624
Connected.
SQL> select * from xh.test;

        ID NAME
---------- ----------
         2 hr
         3 cc~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~可查2

SQL> update xh.test set name='hh';

1 row updated.
commit;

conn xh/a831115
SQL> select * from test;

        ID NAME
---------- ----------
         1 xh
         2 hr
         3 hh~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~只能更新1
         4 dd

~~~~~~~~~~~~~~~~~~~~~~********************************************实际应该运用中比这复杂的多,函数建立也麻烦的多,基本思路就是这样 DBMS_RLS 具体使用查下联机文

档 (还可以建立成 policy group) or desc  下使用看看就知道了

SQL> desc dba_policies~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~查询现有策略
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 OBJECT_OWNER                              NOT NULL VARCHAR2(30)
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 POLICY_GROUP                              NOT NULL VARCHAR2(30)
 POLICY_NAME                               NOT NULL VARCHAR2(30)
 PF_OWNER                                  NOT NULL VARCHAR2(30)
 PACKAGE                                            VARCHAR2(30)
 FUNCTION                                  NOT NULL VARCHAR2(30)
 SEL                                                VARCHAR2(3)
 INS                                                VARCHAR2(3)
 UPD                                                VARCHAR2(3)
 DEL                                                VARCHAR2(3)
 IDX                                                VARCHAR2(3)
 CHK_OPTION                                         VARCHAR2(3)
 ENABLE                                             VARCHAR2(3)
 STATIC_POLICY                                      VARCHAR2(3)
 POLICY_TYPE                                        VARCHAR2(24)
 LONG_PREDICATE                                     VARCHAR2(3)

SQL> col  object_owner format a10
SQL> col  object_name format a10
SQL> col   package format a10
SQL> col   function format a10
SQL> col   policy_name format a10
SQL> select object_owner,object_name,package,function,policy_name from dba_polic
ies where object_name='TEST';~

OBJECT_OWN OBJECT_NAM PACKAGE    FUNCTION   POLICY_NAM
---------- ---------- ---------- ---------- ----------
XH         TEST       TEST_P     P_UPDATE   UPD_POLICY
XH         TEST       TEST_P     P_SELECT   SEL_POLICY


PROCEDURE DROP_POLICY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OBJECT_SCHEMA                  VARCHAR2                IN     DEFAULT
 OBJECT_NAME                    VARCHAR2                IN
 POLICY_NAME                    VARCHAR2                IN
SQL> exec dbms_rls.drop_policy('xh','test','sel_policy');

PL/SQL procedure successfully completed.

SQL> exec dbms_rls.drop_policy('xh','test','upd_policy');

PL/SQL procedure successfully completed.

SQL> select object_owner,object_name,package,function,policy_name from dba_polic
ies where object_name='TEST';

no rows selected

总结:感觉 所要的结果用VIEW 就能实现,而这个太麻烦了,也许在某些地方 某些情况下 它的某些功能十分有效

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12020513/viewspace-610626/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    426927