oracle實驗記錄 （精細策略dbms_rls）

實驗開始
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/，如需轉載，請註明出處，否則將追究法律責任。