oracle实验记录（精细策略dbms_rls）

最新推荐文章于 2023-10-20 08:13:02 发布

cuiye1492

最新推荐文章于 2023-10-20 08:13:02 发布

阅读量320

点赞数

文章标签：数据库网络

实验开始
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-610626/