oracle 用户下建表进行加密（redact）实验

实验准备：oracle scott用户下建表进行加密（redact）实验

1. 加密准备工作：

REVOKE dba FROM SCOTT;

GRANT CONNECT, resource, unlimited tablespace TO SCOTT;

GRANT SELECT ON sys.redaction_policies TO SCOTT;

GRANT SELECT ON sys.redaction_columns TO SCOTT;

GRANT EXECUTE ONdbms_redact TO SCOTT;

查询约束和加密策略

SQL> selectpolicy_name from redaction_policies;

2. scott用户下创建2张表，并设置了主外键约束

create tablestudents(name varchar2(10) primary key not null,age varchar2(10),countvarchar2(10));

insert intostudents (name,age,count) values ('xxxxxx','vvvvvv','nnnnnn');

 

create tableclass(aa varchar2(10),name varchar2(10),cc varchar2(10));

insert intoclass (aa,name,cc) values ('zzzzzz','xxxxxx','cccccc');

alter tableclass ADD CONSTRAINT FK_name FOREIGN KEY(name) references students(name);

 

 

开始加密：

对scott用户下class表的name列进行加密：

 BEGIN

DBMS_REDACT.ADD_POLICY (

  object_schema          =>'SCOTT',

  object_name            =>'CLASS',

  policy_name            =>'REDACT_EMP',

  column_name            =>'NAME',

  function_type          =>DBMS_REDACT.PARTIAL,

  function_parameters    =>'VVVVVV,VVVVVV,*,3,6',

  expression             =>'1=1',

  enable                 => TRUE

   );

END;

/

 

添加一列policy，对class表的AA列进行加密

 BEGIN

 DBMS_REDACT.ALTER_POLICY(

 object_schema       => 'SCOTT',

  object_name         => 'CLASS',

 policy_name         =>'REDACT_EMP',

 action              =>DBMS_REDACT.ADD_COLUMN,

 column_name         => 'AA',

 function_type       =>DBMS_REDACT.PARTIAL,

 function_parameters    =>'VVVVVV,VVVVVV,*,1,6'

 );

END;

/

指定只对hr用户有效

BEGIN

DBMS_REDACT.ALTER_POLICY (

  object_schema          =>'SCOTT',

  object_name            =>'CLASS',

  policy_name            =>'REDACT_EMP',

  column_name            => 'AA',

  action               =>DBMS_REDACT.MODIFY_EXPRESSION,

  expression  => 'SYS_CONTEXT (''USERENV'',''SESSION_USER'' ) =''HR'''

);

END;

/

实验一：加密前查询下面语句，可以正常返回结果：

select distinct name from class order byname;

SQL>select distinct name from class order by name;

 

NAME

----------

xxxxxx

 

加密后错误，报错not a SELECTed expression:

SQL> select distinct name from class order by name;

select distinct name from class order by name

               *

ERROR at line 1:

ORA-01791:not a SELECTed expression

 

加密后正确语法：

SQL> select name from (select distinct name fromclass order by name);

NAME

----------

xxxxxx

 

实验二：加密前查询下面语句，可以正常返回结果：

select distinctclass.name,class.aa,students.age

 from class,students

 where students.name=class.name

  andclass.name<>'13'

  andclass.cc='cccccc'

 order by name asc;

 

SQL> select distinctclass.name,class.aa,students.age

  fromclass,students

  wherestudents.name=class.name

  andclass.name<>'13'

  andclass.cc='cccccc'

  order by nameasc;

  2    3   4    5    6 

NAME           AA             AGE

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

xxxxxx              zzzzzz     vvvvvv

 

 

加密后错误，报错not a SELECTed expression:

SQL> select distinctclass.name,class.aa,students.age

  fromclass,students

  wherestudents.name=class.name

  andclass.name<>'13'

  andclass.cc='cccccc'

  order by nameasc;

  2    3   4    5    6  selectdistinct class.name,class.aa,students.age

               *

ERROR at line 1:

ORA-01791:not a SELECTed expression

 

  加密后正确语法:

SQL> select name,aa,age

  from (selectdistinct class.name,class.aa,students.age

  fromclass,students

  wherestudents.name=class.name

  andclass.name<>'13'

  andclass.cc='cccccc')

  order by nameasc;

  2    3   4    5    6   7 

NAME           AA             AGE

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

xxxxxx              zzzzzz     vvvvvv

 

 

 

删除编写策略（包括policy本身）

BEGIN

 DBMS_REDACT.DROP_POLICY (

   object_schema  => 'SCOTT',

   object_name    => 'CLASS',

   policy_name    =>'REDACT_EMP');

END;

/

完。