概述
Oracle Data Redaction是Oracle安全加密类的高级功能，可用于对于敏感数据的加密处理，加密配置处理均在Oracle层面实现。这是一项和安全相关的技术类别，对于指定的用户可以限制某些表的某些列显示被加密改过的值。对于Redaction之前，可能需要自定义加密函数、创建特定的视图，或者在存储到数据库的时候就用加密算法进行加密。而Redaction可以直接对数据进行加密，不会影响到数据真实的存储，对应用透明，不需要改动。
– 以下可能与Oracle 版本有关，11.2.0.4.0测试未发现由此现象
对于权限，Redaction不能对sys和system用户进行数据的加密。因为他们都有EXP_FULL_DATABASE这个角色, 而这个角色又包含了EXEMPT REDACTION POLICY系统权限。同时，也不能直接赋予用户dba权限，dba自动包含EXP_FULL_DATABASE角色。测试过程中发现，对于拥有dba权限的用户来说，表的数据可以加密操作，但没有实际加密效果。
参考文档
Configuring Oracle Data Redaction Policies
环境信息
实践过程
创建测试用户和表
create user TEST_REDECT identified by 123456;
grant connect,resource to TEST_REDECT;
conn TEST_REDECT/123456
create table t_redect (id int, name varchar(20),age number(3),ctime date);
insert into t_redect values(1,'zhang',20,sysdate);
insert into t_redect values(1,'li',25,sysdate);
insert into t_redect values(1,'yuan',30,sysdate);
commit;
alter session set nls_date_format='DD-MON-YYYY hh24:mi:ss';
select * from t_redect;
grant select on test_redect.t_redect to ZSDBA;
加密NAME列
DBMS_REDACT.ADD_POLICY的function_type有很多加密选项，可参考《附加信息》
加密用户需要有执行 dbms_redact 的权限，这里演示使用sys用户进行。
- 加密前
SQL> conn TEST_REDECT/123456
Connected.
SQL> show user
USER is "TEST_REDECT"
SQL> select * from t_redect;
ID NAME AGE CTIME
---------- -------------------- ---------- ---------
1 zhang 20 17-JAN-24
1 li 25 17-JAN-24
1 yuan 30 17-JAN-24
- 使用sys用户test_redect.t_redect的 name 列加密
SQL> show user
USER is "SYS"
SQL>
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'TEST_REDECT',
object_name => 'T_REDECT',
column_name => 'NAME',
policy_name => 'redect_test_name',
function_type => DBMS_REDACT.FULL,
expression => '1=1');
END;
/
- 验证加密效果
重新使用test_redect用户登录查询name列，可以看到查询 name列为空，
已进行加密，由于使用的全用户加密，所以zsdba用户同样查询不到数据
SQL> conn TEST_REDECT/123456
Connected.
SQL> show user
USER is "TEST_REDECT"
SQL> select * from t_redect;
ID NAME AGE CTIME
---------- -------------------- ---------- ---------
1 20 17-JAN-24
1 25 17-JAN-24
1 30 17-JAN-24
SQL> conn zsdba/123456
Connected.
SQL> show user
USER is "ZSDBA"
SQL> select * from TEST_REDECT.t_redect;
ID NAME AGE CTIME
---------- -------------------- ---------- ---------
1 20 17-JAN-24
1 25 17-JAN-24
1 30 17-JAN-24
添加加密列AGE
- 使用sys用户test_redect.t_redect的 age 列加密
SQL> SHOW USER
USER is "SYS"
SQL>
BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => 'TEST_REDECT',
object_name => 'T_REDECT',
policy_name => 'redect_test_name',
action => DBMS_REDACT.ADD_COLUMN,
column_name => 'AGE',
function_type => DBMS_REDACT.FULL,
expression => '1=1');
END;
/
- 加密验证
重新使用test_redect用户登录查询age列，可以看到查询age列为0，已进行加密，由于使用的全用户加密，所以zsdba用户同样查询不到数据
SQL> conn TEST_REDECT/123456
Connected.
SQL> show user
USER is "TEST_REDECT"
SQL> select * from t_redect;
ID NAME AGE CTIME
---------- -------------------- ---------- ---------
1 0 17-JAN-24
1 0 17-JAN-24
1 0 17-JAN-24
SQL> conn zsdba/123456
Connected.
SQL> show user
USER is "ZSDBA"
SQL> select * from TEST_REDECT.t_redect;
ID NAME AGE CTIME
---------- -------------------- ---------- ---------
1 0 17-JAN-24
1 0 17-JAN-24
1 0 17-JAN-24
常用功能
DBMS_REDACT相关视图
--策略总览
select * from REDACTION_POLICIES;
--策略详细信息
select * from REDACTION_COLUMNS;
启用DBMS_REDACT策略
BEGIN
DBMS_REDACT.ENABLE_POLICY (
object_schema => 'TEST_REDECT',
object_name => 't_redect',
policy_name => 'redect_test_name');
END;
/
禁用DBMS_REDACT策略
BEGIN
DBMS_REDACT.DISABLE_POLICY (
object_schema => 'TEST_REDECT',
object_name => 't_redect',
policy_name => 'redect_test_name');
END;
/
删除DBMS_REDACT策略
begin
dbms_redact.drop_policy(
object_schema => 'TEST_REDECT',
object_name => 't_redect',
policy_name => 'redect_test_name');
end;
/
删除指定列DBMS_REDACT策略
BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => 'TEST_REDECT',
object_name => 'T_REDECT',
policy_name => 'redect_test_name',
action => DBMS_REDACT.DROP_COLUMN,
column_name => 'AGE');
END;
/
指定HR用户可以查询加密列
BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => 'hr',
object_name => 'employees',
policy_name => 'hr_employees_pol',
action => DBMS_REDACT.MODIFY_EXPRESSION,
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR''');
END;
/
指定角色可以查询加密列
下面语句指定MGR角色可以查看加密列
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'mavis',
object_name => 'cust_info',
column_name => 'user_id',
policy_name => 'redact_cust_user_ids',
function_type => DBMS_REDACT.FULL,
expression => 'SYS_CONTEXT(''SYS_SESSION_ROLES'',''MGR'') = ''FALSE''');
END;
/
附加信息
DBMS_REDACT 包含函数
| Procedure | Description |
|---|---|
DBMS_REDACT.ADD_POLICY |
Adds a Data Redaction policy to a table or view |
DBMS_REDACT.ALTER_POLICY |
Modifies a Data Redaction policy |
DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES |
Globally updates the full redaction value for a given data type. You must restart the database instance before the updated values can be used. |
DBMS_REDACT.ENABLE_POLICY |
Enables a Data Redaction policy |
DBMS_REDACT.DISABLE_POLICY |
Disables a Data Redaction policy |
DBMS_REDACT.DROP_POLICY |
Drops a Data Redaction policy |
DBMS_REDACT.ADD_POLICY的function_type字段选项
function_type: Specifies a function that sets the type of redaction. See the following sections for more information:
DBMS_REDACT.ALTER_POLICY的action字段选项
action: Enter one of the following values to define the kind of action to use:
-
DBMS_REDACT.MODIFY_COLUMNif you plan to change thecolumn_namevalue. -
DBMS_REDACT.ADD_COLUMNif you plan to add a new column (in addition to columns that are already protected by the policy) for redaction. This setting is the default for theactionparameter. -
DBMS_REDACT.DROP_COLUMNif you want to remove redaction from a column. -
DBMS_REDACT.MODIFY_EXPRESSIONif you plan to change theexpressionvalue. Each policy can have only one policy expression. In other words, when you modify the policy expression, you are replacing the existing policy expression with a new policy expression. -
DBMS_REDACT.SET_POLICY_DESCRIPTIONif you want to change the description of the policy. -
DBMS_REDACT.SET_COLUMN_DESCRIPTIONif you want to change the description of the column.
