oracle创建表分区权限,分区表访问权限 - Oracle数据库管理 - ITPUB论坛－中国专业的IT技术社区...

下面我测试的过程：

(1)建立测试数据表(TEST_POLICY_T):

conn xigua/xigua

CREATE TABLE TEST_POLICY_T

(

region        VARCHAR2(10),

name          VARCHAR2(10)

);

insert into TEST_POLICY_T values('nj','aa1');

insert into TEST_POLICY_T values('nj','aa2');

insert into TEST_POLICY_T values('sz','bb1');

insert into TEST_POLICY_T values('sz','bb2');

insert into TEST_POLICY_T values('wx','cc1');

insert into TEST_POLICY_T values('wx','cc2');

commit;

(2)创建3个用户

要求：

nj只能 select region='nj'

sz只能 select region='sz'

wx只能 select region='wx'

conn /as sysdba

create user sz identified by nj ;

create user sz identified by sz ;

create user sz identified by wx ;

grant select on xigua.test_policy_t to nj ;

grant select on xigua.test_policy_t to sz ;

grant select on xigua.test_policy_t to wx ;

(3)建立policy的函数:

conn xigua/xigua

create or replace function select_func(object_schema In Varchar2,object_name In Varchar2) return varchar2 is

rtn_prediate varchar2(1000);

begin

rtn_prediate := '1=1';

if user = 'NJ' then

rtn_prediate := 'region = ''nj'''

|| ' or '

|| 'region = ''sz'''; --这里是为了实现多个条件做测试特意添加的

elsif user = 'SZ' then

rtn_prediate := 'region = ''sz''';

elsif user = 'WX' then

rtn_prediate := 'region = ''wx''';

end if ;

return(rtn_prediate);

end select_func;

/

(4)将表加入policy:

conn /as sysdba

grant execute on dbms_rls to xigua ;

conn xigua/xigua

exec dbms_rls.add_policy( -

Object_Schema =>'XIGUA',  -

Object_Name =>'TEST_POLICY_T', -

Policy_Name =>'Test_Policy', -

Function_Schema =>'XIGUA',  -

Policy_Function =>'select_func', -

Statement_Types =>'Select,Insert,Update,Delete', -

Update_Check =>True, -

Enable =>True    -

);

(5)验证效果

SQL> conn sz/sz

Connected.

SQL> select * from xigua.test_policy_t ;

REGION     NAME

---------- ----------

sz         bb1

sz         bb2

SQL> conn wx/wx

Connected.

SQL> insert into xigua.test_policy_t values ('wx','dgiue') ;

1 row created.

SQL> commit;

Commit complete.

SQL> insert into xigua.test_policy_t values ('nj','df123');

insert into xigua.test_policy_t values ('nj','df123')

*

ERROR at line 1:

ORA-28115: policy with check option violation

SQL> conn xigua/xigua

Connected.

SQL> select * from xigua.test_policy_t ;

REGION     NAME

---------- ----------

wx         dgiue

nj         aa1

nj         aa2

sz         bb1

sz         bb2

wx         cc1

wx         cc2

7 rows selected.