实验准备: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;
/
完。