Oracle教程FG393-PDB数据编辑

本文档风哥主要介绍Oracle PDB数据编辑（Data Redaction）相关知识，包括PDB数据编辑的概念、PDB数据编辑的类型、PDB数据编辑的配置、PDB数据编辑策略创建、PDB数据编辑验证、PDB数据编辑故障处理等内容，由风哥教程参考Oracle官方文档Security内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 PDB数据编辑的概念

Oracle PDB数据编辑（Data Redaction）是一种数据保护技术，用于在查询结果中动态隐藏敏感数据。数据编辑可以在不修改实际数据的情况下，对查询结果进行脱敏处理，保护敏感信息不被未授权用户访问。数据编辑适用于开发、测试、报告等场景，确保数据安全。更多视频教程www.fgedu.net.cn

Oracle PDB数据编辑的特点：

动态脱敏，不修改实际数据
基于用户、角色或会话的编辑策略
支持多种编辑类型
支持表达式编辑
不影响应用程序逻辑

1.2 PDB数据编辑的类型

Oracle PDB数据编辑的类型包括：

完全编辑（Full Redaction）：将所有数据替换为固定值
部分编辑（Partial Redaction）：保留部分数据，编辑其他部分
随机编辑（Random Redaction）：将数据替换为随机值
正则表达式编辑（Regular Expression Redaction）：使用正则表达式编辑数据
自定义编辑（Custom Redaction）：使用自定义函数编辑数据

1.3 PDB数据编辑的优势

Oracle PDB数据编辑的优势：

数据保护：保护敏感数据不被未授权访问
合规性：满足数据保护法规要求
灵活性：支持多种编辑类型和策略
不影响应用：不修改实际数据，不影响应用程序
易于管理：集中管理编辑策略

风哥提示：PDB数据编辑是保护敏感数据的重要手段，特别适合开发、测试、报告等场景。建议根据数据敏感程度选择合适的编辑类型。

Part02-生产环境规划与建议

2.1 PDB数据编辑规划

Oracle PDB数据编辑规划要点：

# 数据编辑规划
– 敏感数据识别：识别需要编辑的敏感数据
– 编辑类型选择：根据数据类型选择编辑类型
– 编辑策略配置：配置编辑策略和规则
– 用户角色管理：管理编辑策略的用户和角色

# 编辑类型规划
– 完全编辑：适用于高度敏感数据
– 部分编辑：适用于需要保留部分信息的数据
– 随机编辑：适用于测试环境
– 正则表达式编辑：适用于格式化数据
– 自定义编辑：适用于复杂编辑需求

# 编辑策略规划
– 基于用户的编辑：根据用户身份编辑数据
– 基于角色的编辑：根据用户角色编辑数据
– 基于会话的编辑：根据会话属性编辑数据
– 表级别编辑：对整个表的列进行编辑
– 列级别编辑：对特定列进行编辑

2.2 PDB数据编辑安全策略

Oracle PDB数据编辑安全策略：

最小权限原则：只授予必要的编辑权限
定期审计：审计编辑策略的使用
策略验证：验证编辑策略的有效性
数据分类：根据数据敏感程度分类
权限管理：管理编辑策略的权限

# 安全配置建议
– 识别敏感数据
– 选择合适的编辑类型
– 配置编辑策略
– 验证编辑效果
– 定期审计编辑策略
– 管理编辑权限

2.3 PDB数据编辑最佳实践

Oracle PDB数据编辑最佳实践：

数据分类：根据数据敏感程度分类
选择合适编辑类型：根据数据类型选择编辑类型
验证编辑效果：验证编辑策略的有效性
定期审计：审计编辑策略的使用
文档记录：记录编辑策略配置

生产环境建议：数据编辑是保护敏感数据的重要手段，建议根据数据敏感程度选择合适的编辑类型，并定期审计和验证编辑策略。学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 PDB数据编辑配置

3.1.1 启用数据编辑功能

# 1. 连接到PDB
$ sqlplus / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

# 2. 查看数据编辑权限
SQL> select * from dba_sys_privs where privilege like ‘%REDACT%’;

GRANTEE PRIVILEGE
———- —————————————-
SYS EXEMPT REDACTION POLICY

# 3. 授予数据编辑权限
SQL> grant create any redaction policy to redaction_admin;

Grant succeeded.

SQL> grant execute on dbms_redact to redaction_admin;

Grant succeeded.

SQL> grant exempt redaction policy to redaction_admin;

Grant succeeded.

# 4. 创建测试表
SQL> create user test_user identified by test_password;

User created.

SQL> grant create session, connect, resource to test_user;

Grant succeeded.

SQL> create table test_user.customers (
id number primary key,
name varchar2(100),
email varchar2(100),
phone varchar2(20),
credit_card varchar2(20),
ssn varchar2(20)
);

Table created.

# 5. 插入测试数据
SQL> insert into test_user.customers values (1, ‘风哥1号’, ‘zhangsan@fgedu.net.cn’, ‘13800138000’, ‘1234567890123456’, ‘123456789’);

1 row created.

SQL> insert into test_user.customers values (2, ‘风哥2号’, ‘lisi@fgedu.net.cn’, ‘13900139000’, ‘2345678901234567’, ‘234567890’);

1 row created.

SQL> insert into test_user.customers values (3, ‘王五’, ‘wangwu@fgedu.net.cn’, ‘13700137000’, ‘3456789012345678’, ‘345678901’);

1 row created.

SQL> commit;

Commit complete.

# 6. 验证数据
SQL> select * from test_user.customers;

ID NAME EMAIL PHONE CREDIT_CARD SSN
———- ———- ———————- ————— —————— ———-
1 风哥1号 zhangsan@fgedu.net.cn 13800138000 1234567890123456 123456789
2 风哥2号 lisi@fgedu.net.cn 13900139000 2345678901234567 234567890
3 王五 wangwu@fgedu.net.cn 13700137000 3456789012345678 345678901

3.1.2 创建完全编辑策略

# 1. 创建完全编辑策略
SQL> begin
dbms_redact.add_policy(
object_schema => ‘TEST_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘CREDIT_CARD_REDACTION’,
column_name => ‘CREDIT_CARD’,
function_type => dbms_redact.full,
expression => ‘1=1’
);
end;
/

PL/SQL procedure successfully completed.

# 2. 验证编辑策略
SQL> select object_schema, object_name, policy_name, column_name, function_type
from redaction_policies;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME COLUMN_NAME FUNCTION_TYPE
————- ————– ———————- ————- ————–
TEST_USER CUSTOMERS CREDIT_CARD_REDACTION CREDIT_CARD FULL REDACTION

# 3. 测试编辑效果
SQL> conn test_user/test_password@SALESPDB

Connected.

SQL> select id, name, credit_card from customers;

ID NAME CREDIT_CARD
———- ———- ——————
1 风哥1号 ************
2 风哥2号 ************
3 王五 ************

# 4. 使用EXEMPT REDACTION POLICY权限查看原始数据
SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> select id, name, credit_card from test_user.customers;

ID NAME CREDIT_CARD
———- ———- ——————
1 风哥1号 1234567890123456
2 风哥2号 2345678901234567
3 王五 3456789012345678

# 5. 查看编辑策略详情
SQL> select object_schema, object_name, policy_name, column_name,
function_type, function_parameters, enable
from redaction_policies;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME COLUMN_NAME FUNCTION_TYPE FUNCTION_PARAMETERS ENABLE
————- ————– ———————- ————- —————— ——————- —–
TEST_USER CUSTOMERS CREDIT_CARD_REDACTION CREDIT_CARD FULL REDACTION NULL YES

3.2 PDB数据编辑策略创建

3.2.1 创建部分编辑策略

# 1. 创建部分编辑策略（编辑信用卡号，保留前4位和后4位）
SQL> begin
dbms_redact.add_policy(
object_schema => ‘TEST_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘CREDIT_CARD_PARTIAL_REDACTION’,
column_name => ‘CREDIT_CARD’,
function_type => dbms_redact.partial,
function_parameters => ‘VVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVV,*,1,4’,
expression => ‘1=1’
);
end;
/

PL/SQL procedure successfully completed.

# 2. 验证编辑策略
SQL> select object_schema, object_name, policy_name, column_name, function_type
from redaction_policies;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME COLUMN_NAME FUNCTION_TYPE
————- ————– ——————————– ————- ————–
TEST_USER CUSTOMERS CREDIT_CARD_REDACTION CREDIT_CARD FULL REDACTION
TEST_USER CUSTOMERS CREDIT_CARD_PARTIAL_REDACTION CREDIT_CARD PARTIAL REDACTION

# 3. 测试编辑效果
SQL> conn test_user/test_password@SALESPDB

Connected.

SQL> select id, name, credit_card from customers;

ID NAME CREDIT_CARD
———- ———- ——————
1 风哥1号 1234********3456
2 风哥2号 2345********4567
3 王五 3456********5678

# 4. 创建手机号部分编辑策略
SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> begin
dbms_redact.add_policy(
object_schema => ‘TEST_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘PHONE_REDACTION’,
column_name => ‘PHONE’,
function_type => dbms_redact.partial,
function_parameters => ‘VVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVV,*,3,4’,
expression => ‘1=1’
);
end;
/

PL/SQL procedure successfully completed.

# 5. 测试手机号编辑效果
SQL> conn test_user/test_password@SALESPDB

Connected.

SQL> select id, name, phone from customers;

ID NAME PHONE
———- ———- —————
1 风哥1号 138****8000
2 风哥2号 139****9000
3 王五 137****7000

3.2.2 创建随机编辑策略

# 1. 创建随机编辑策略
SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> begin
dbms_redact.add_policy(
object_schema => ‘TEST_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘SSN_RANDOM_REDACTION’,
column_name => ‘SSN’,
function_type => dbms_redact.random,
expression => ‘1=1’
);
end;
/

PL/SQL procedure successfully completed.

# 2. 验证编辑策略
SQL> select object_schema, object_name, policy_name, column_name, function_type
from redaction_policies;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME COLUMN_NAME FUNCTION_TYPE
————- ————– —————————- ————- ————–
TEST_USER CUSTOMERS CREDIT_CARD_REDACTION CREDIT_CARD FULL REDACTION
TEST_USER CUSTOMERS CREDIT_CARD_PARTIAL_REDACTION CREDIT_CARD PARTIAL REDACTION
TEST_USER CUSTOMERS PHONE_REDACTION PHONE PARTIAL REDACTION
TEST_USER CUSTOMERS SSN_RANDOM_REDACTION SSN RANDOM REDACTION

# 3. 测试随机编辑效果
SQL> conn test_user/test_password@SALESPDB

Connected.

SQL> select id, name, ssn from customers;

ID NAME SSN
———- ———- ———-
1 风哥1号 987654321
2 风哥2号 876543210
3 王五 765432109

SQL> select id, name, ssn from customers;

ID NAME SSN
———- ———- ———-
1 风哥1号 543210987
2 风哥2号 432109876
3 王五 321098765

# 4. 验证原始数据未改变
SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> select id, name, ssn from test_user.customers;

ID NAME SSN
———- ———- ———-
1 风哥1号 123456789
2 风哥2号 234567890
3 王五 345678901

3.3 PDB数据编辑验证

3.3.1 验证编辑策略

# 1. 查看所有编辑策略
SQL> select object_schema, object_name, policy_name, column_name,
function_type, enable
from redaction_policies;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME COLUMN_NAME FUNCTION_TYPE ENABLE
————- ————– —————————- ————- —————— —–
TEST_USER CUSTOMERS CREDIT_CARD_REDACTION CREDIT_CARD FULL REDACTION YES
TEST_USER CUSTOMERS CREDIT_CARD_PARTIAL_REDACTION CREDIT_CARD PARTIAL REDACTION YES
TEST_USER CUSTOMERS PHONE_REDACTION PHONE PARTIAL REDACTION YES
TEST_USER CUSTOMERS SSN_RANDOM_REDACTION SSN RANDOM REDACTION YES

# 2. 查看编辑策略表达式
SQL> select object_schema, object_name, policy_name, expression
from redaction_policies;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME EXPRESSION
————- ————– —————————- ———-
TEST_USER CUSTOMERS CREDIT_CARD_REDACTION 1=1
TEST_USER CUSTOMERS CREDIT_CARD_PARTIAL_REDACTION 1=1
TEST_USER CUSTOMERS PHONE_REDACTION 1=1
TEST_USER CUSTOMERS SSN_RANDOM_REDACTION 1=1

# 3. 查看编辑策略参数
SQL> select object_schema, object_name, policy_name, column_name,
function_type, function_parameters
from redaction_policies;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME COLUMN_NAME FUNCTION_TYPE FUNCTION_PARAMETERS
————- ————– —————————- ————- —————— ——————-
TEST_USER CUSTOMERS CREDIT_CARD_REDACTION CREDIT_CARD FULL REDACTION NULL
TEST_USER CUSTOMERS CREDIT_CARD_PARTIAL_REDACTION CREDIT_CARD PARTIAL REDACTION VVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVV,*,1,4
TEST_USER CUSTOMERS PHONE_REDACTION PHONE PARTIAL REDACTION VVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVV,*,3,4
TEST_USER CUSTOMERS SSN_RANDOM_REDACTION SSN RANDOM REDACTION NULL

# 4. 测试编辑效果
SQL> conn test_user/test_password@SALESPDB

Connected.

SQL> select * from customers;

ID NAME EMAIL PHONE CREDIT_CARD SSN
———- ———- ———————- ————— —————— ———-
1 风哥1号 zhangsan@fgedu.net.cn 138****8000 1234********3456 987654321
2 风哥2号 lisi@fgedu.net.cn 139****9000 2345********4567 876543210
3 王五 wangwu@fgedu.net.cn 137****7000 3456********5678 765432109

3.3.2 管理编辑策略

# 1. 禁用编辑策略
SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> begin
dbms_redact.alter_policy(
object_schema => ‘TEST_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘SSN_RANDOM_REDACTION’,
action => dbms_redact.disable
);
end;
/

PL/SQL procedure successfully completed.

# 2. 验证策略已禁用
SQL> select object_schema, object_name, policy_name, column_name, enable
from redaction_policies;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME COLUMN_NAME ENABLE
————- ————– —————————- ————- —–
TEST_USER CUSTOMERS CREDIT_CARD_REDACTION CREDIT_CARD YES
TEST_USER CUSTOMERS CREDIT_CARD_PARTIAL_REDACTION CREDIT_CARD YES
TEST_USER CUSTOMERS PHONE_REDACTION PHONE YES
TEST_USER CUSTOMERS SSN_RANDOM_REDACTION SSN NO

# 3. 启用编辑策略
SQL> begin
dbms_redact.alter_policy(
object_schema => ‘TEST_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘SSN_RANDOM_REDACTION’,
action => dbms_redact.enable
);
end;
/

PL/SQL procedure successfully completed.

# 4. 删除编辑策略
SQL> begin
dbms_redact.drop_policy(
object_schema => ‘TEST_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘CREDIT_CARD_REDACTION’
);
end;
/

PL/SQL procedure successfully completed.

# 5. 验证策略已删除
SQL> select object_schema, object_name, policy_name, column_name, enable
from redaction_policies;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME COLUMN_NAME ENABLE
————- ————– —————————- ————- —–
TEST_USER CUSTOMERS CREDIT_CARD_PARTIAL_REDACTION CREDIT_CARD YES
TEST_USER CUSTOMERS PHONE_REDACTION PHONE YES
TEST_USER CUSTOMERS SSN_RANDOM_REDACTION SSN YES

风哥提示：数据编辑是保护敏感数据的重要手段，建议根据数据敏感程度选择合适的编辑类型，并定期审计和验证编辑策略。学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 PDB数据编辑配置案例

在生产环境中配置PDB数据编辑的完整案例：

4.1.1 场景描述

某企业需要为销售PDB中的客户数据配置数据编辑，保护信用卡号、手机号、身份证号等敏感信息，确保开发测试人员无法查看完整敏感数据。

4.1.2 配置步骤

# 1. 连接到PDB
$ sqlplus / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

# 2. 创建测试表
SQL> create user fgfgfgsales_user identified by fgfgfgsales_password;

User created.

SQL> grant create session, connect, resource to fgfgfgsales_user;

Grant succeeded.

SQL> create table fgfgfgsales_user.customers (
id number primary key,
name varchar2(100),
email varchar2(100),
phone varchar2(20),
credit_card varchar2(20),
id_card varchar2(20),
address varchar2(200)
);

Table created.

# 3. 插入测试数据
SQL> insert into fgfgfgsales_user.customers values (
1, ‘风哥1号’, ‘zhangsan@fgedu.net.cn’, ‘13800138000’,
‘1234567890123456’, ‘110101199001011234’, ‘北京市朝阳区’
);

1 row created.

SQL> insert into fgfgfgsales_user.customers values (
2, ‘风哥2号’, ‘lisi@fgedu.net.cn’, ‘13900139000’,
‘2345678901234567’, ‘110101199002022345’, ‘上海市浦东新区’
);

1 row created.

SQL> insert into fgfgfgsales_user.customers values (
3, ‘王五’, ‘wangwu@fgedu.net.cn’, ‘13700137000’,
‘3456789012345678’, ‘110101199003033456’, ‘广州市天河区’
);

1 row created.

SQL> commit;

Commit complete.

# 4. 创建信用卡号部分编辑策略
SQL> begin
dbms_redact.add_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘CREDIT_CARD_REDACTION’,
column_name => ‘CREDIT_CARD’,
function_type => dbms_redact.partial,
function_parameters => ‘VVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVV,*,1,4’,
expression => ‘1=1’
);
end;
/

PL/SQL procedure successfully completed.

# 5. 创建手机号部分编辑策略
SQL> begin
dbms_redact.add_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘PHONE_REDACTION’,
column_name => ‘PHONE’,
function_type => dbms_redact.partial,
function_parameters => ‘VVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVV,*,3,4’,
expression => ‘1=1’
);
end;
/

PL/SQL procedure successfully completed.

# 6. 创建身份证号部分编辑策略
SQL> begin
dbms_redact.add_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘ID_CARD_REDACTION’,
column_name => ‘ID_CARD’,
function_type => dbms_redact.partial,
function_parameters => ‘VVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVV,*,1,4’,
expression => ‘1=1’
);
end;
/

PL/SQL procedure successfully completed.

# 7. 创建邮箱部分编辑策略
SQL> begin
dbms_redact.add_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘EMAIL_REDACTION’,
column_name => ‘EMAIL’,
function_type => dbms_redact.partial,
function_parameters => ‘VVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVV,*,1,3’,
expression => ‘1=1’
);
end;
/

PL/SQL procedure successfully completed.

# 8. 验证编辑策略
SQL> select object_schema, object_name, policy_name, column_name, function_type
from redaction_policies;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME COLUMN_NAME FUNCTION_TYPE
————- ————– ——————— ————- ————–
SALES_USER CUSTOMERS CREDIT_CARD_REDACTION CREDIT_CARD PARTIAL REDACTION
SALES_USER CUSTOMERS PHONE_REDACTION PHONE PARTIAL REDACTION
SALES_USER CUSTOMERS ID_CARD_REDACTION ID_CARD PARTIAL REDACTION
SALES_USER CUSTOMERS EMAIL_REDACTION EMAIL PARTIAL REDACTION

# 9. 测试编辑效果
SQL> conn fgfgfgsales_user/fgfgfgsales_password@SALESPDB

Connected.

SQL> select * from customers;

ID NAME EMAIL PHONE CREDIT_CARD ID_CARD ADDRESS
———- ———- ——————– ————— —————— —————— ——————
1 风哥1号 zha***@fgedu.net.cn 138****8000 1234********3456 1101********1234 北京市朝阳区
2 风哥2号 lis***@fgedu.net.cn 139****9000 2345********4567 1101********2345 上海市浦东新区
3 王五 wan***@fgedu.net.cn 137****7000 3456********5678 1101********3456 广州市天河区

# 10. 验证原始数据未改变
SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> select * from fgfgfgsales_user.customers;

ID NAME EMAIL PHONE CREDIT_CARD ID_CARD ADDRESS
———- ———- ———————- ————— —————— ———————- ——————
1 风哥1号 zhangsan@fgedu.net.cn 13800138000 1234567890123456 110101199001011234 北京市朝阳区
2 风哥2号 lisi@fgedu.net.cn 13900139000 2345678901234567 110101199002022345 上海市浦东新区
3 王五 wangwu@fgedu.net.cn 13700137000 3456789012345678 110101199003033456 广州市天河区

4.2 PDB数据编辑故障处理

在PDB数据编辑过程中可能遇到的故障及处理方法：

4.2.1 故障现象：ORA-28031编辑策略已存在

# 问题现象
SQL> begin
dbms_redact.add_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘CREDIT_CARD_REDACTION’,
column_name => ‘CREDIT_CARD’,
function_type => dbms_redact.partial,
function_parameters => ‘VVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVV,*,1,4’,
expression => ‘1=1’
);
end;
/

ORA-28031: maximum of 1 redaction policy already applied on column SALES_USER.CUSTOMERS.CREDIT_CARD

# 分析步骤

# 1. 查看现有编辑策略
SQL> select object_schema, object_name, policy_name, column_name, enable
from redaction_policies
where object_schema = ‘SALES_USER’ and object_name = ‘CUSTOMERS’ and column_name = ‘CREDIT_CARD’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME COLUMN_NAME ENABLE
————- ————– ——————— ————- —–
SALES_USER CUSTOMERS CREDIT_CARD_REDACTION CREDIT_CARD YES

# 2. 发现已存在编辑策略

# 3. 解决方案：修改现有策略
SQL> begin
dbms_redact.alter_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘CREDIT_CARD_REDACTION’,
column_name => ‘CREDIT_CARD’,
action => dbms_redact.modify_column,
function_type => dbms_redact.partial,
function_parameters => ‘VVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVV,*,1,4’
);
end;
/

PL/SQL procedure successfully completed.

# 4. 验证策略修改
SQL> select object_schema, object_name, policy_name, column_name, function_type, function_parameters
from redaction_policies
where object_schema = ‘SALES_USER’ and object_name = ‘CUSTOMERS’ and column_name = ‘CREDIT_CARD’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME COLUMN_NAME FUNCTION_TYPE FUNCTION_PARAMETERS
————- ————– ——————— ————- —————— ——————-
SALES_USER CUSTOMERS CREDIT_CARD_REDACTION CREDIT_CARD PARTIAL REDACTION VVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVV,*,1,4

# 5. 预防措施
# – 在创建策略前检查是否已存在
# – 使用唯一的策略名称
# – 定期检查现有策略

4.2.2 故障现象：编辑策略不生效

# 问题现象
SQL> conn fgfgfgsales_user/fgfgfgsales_password@SALESPDB

Connected.

SQL> select * from customers;

# 发现数据未被编辑

# 分析步骤

# 1. 检查编辑策略状态
SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> select object_schema, object_name, policy_name, column_name, enable
from redaction_policies;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME COLUMN_NAME ENABLE
————- ————– ——————— ————- —–
SALES_USER CUSTOMERS CREDIT_CARD_REDACTION CREDIT_CARD NO
SALES_USER CUSTOMERS PHONE_REDACTION PHONE NO
SALES_USER CUSTOMERS ID_CARD_REDACTION ID_CARD NO
SALES_USER CUSTOMERS EMAIL_REDACTION EMAIL NO

# 2. 发现编辑策略被禁用

# 3. 解决方案：启用编辑策略
SQL> begin
dbms_redact.alter_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘CREDIT_CARD_REDACTION’,
action => dbms_redact.enable
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
dbms_redact.alter_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘PHONE_REDACTION’,
action => dbms_redact.enable
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
dbms_redact.alter_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘ID_CARD_REDACTION’,
action => dbms_redact.enable
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
dbms_redact.alter_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘EMAIL_REDACTION’,
action => dbms_redact.enable
);
end;
/

PL/SQL procedure successfully completed.

# 4. 验证策略已启用
SQL> select object_schema, object_name, policy_name, column_name, enable
from redaction_policies;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME COLUMN_NAME ENABLE
————- ————– ——————— ————- —–
SALES_USER CUSTOMERS CREDIT_CARD_REDACTION CREDIT_CARD YES
SALES_USER CUSTOMERS PHONE_REDACTION PHONE YES
SALES_USER CUSTOMERS ID_CARD_REDACTION ID_CARD YES
SALES_USER CUSTOMERS EMAIL_REDACTION EMAIL YES

# 5. 测试编辑效果
SQL> conn fgfgfgsales_user/fgfgfgsales_password@SALESPDB

Connected.

SQL> select * from customers;

# 6. 预防措施
# – 定期检查编辑策略状态
# – 监控编辑策略使用情况
# – 文档记录编辑策略配置

4.3 PDB数据编辑优化

优化PDB数据编辑配置的最佳实践：

4.3.1 优化编辑策略配置

# 1. 创建基于用户的编辑策略
SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

# 2. 创建开发用户
SQL> create user dev_user identified by dev_password;

User created.

SQL> grant create session, connect, resource to dev_user;

Grant succeeded.

SQL> grant select on fgfgfgsales_user.customers to dev_user;

Grant succeeded.

# 3. 创建管理员用户
SQL> create user admin_user identified by admin_password;

User created.

SQL> grant create session, connect, resource to admin_user;

Grant succeeded.

SQL> grant select on fgfgfgsales_user.customers to admin_user;

Grant succeeded.

SQL> grant exempt redaction policy to admin_user;

Grant succeeded.

# 4. 修改编辑策略表达式，仅对开发用户编辑
SQL> begin
dbms_redact.alter_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘CREDIT_CARD_REDACTION’,
action => dbms_redact.modify_expression,
expression => ‘SYS_CONTEXT(”USERENV”, ”SESSION_USER”) = ”DEV_USER”’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
dbms_redact.alter_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘PHONE_REDACTION’,
action => dbms_redact.modify_expression,
expression => ‘SYS_CONTEXT(”USERENV”, ”SESSION_USER”) = ”DEV_USER”’
);
end;
/

PL/SQL procedure successfully completed.

# 5. 测试开发用户编辑效果
SQL> conn dev_user/dev_password@SALESPDB

Connected.

SQL> select id, name, phone, credit_card from fgfgfgsales_user.customers;

ID NAME PHONE CREDIT_CARD
———- ———- ————— ——————
1 风哥1号 138****8000 1234********3456
2 风哥2号 139****9000 2345********4567
3 王五 137****7000 3456********5678

# 6. 测试管理员用户查看原始数据
SQL> conn admin_user/admin_password@SALESPDB

Connected.

SQL> select id, name, phone, credit_card from fgfgfgsales_user.customers;

ID NAME PHONE CREDIT_CARD
———- ———- ————— ——————
1 风哥1号 13800138000 1234567890123456
2 风哥2号 13900139000 2345678901234567
3 王五 13700137000 3456789012345678

# 7. 配置编辑策略审计
SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> audit policy oracle_secure_config;

Audit succeeded.

# 8. 监控编辑策略使用
SQL> select object_schema, object_name, policy_name, column_name, enable
from redaction_policies;

# 9. 创建编辑策略监控脚本
$ vi /home/oracle/scripts/monitor_redaction.sh

#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# 编辑策略监控脚本

export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH

# 检查编辑策略状态
STATUS=$(sqlplus -s / as sysdba << EOF set heading off feedback off pagesize 0 alter session set container=SALESPDB;
select count(*) from redaction_policies where enable = ‘NO’;
exit;
EOF
)

if [ “$STATUS” -gt 0 ]; then
echo “WARNING: $STATUS redaction policies are disabled”
# 发送告警
echo “$STATUS redaction policies are disabled” | mail -s “Redaction Policy Alert” admin@fgedu.net.cn
fi

echo “Redaction monitoring completed at $(date)”

# 10. 设置定期监控
$ crontab -e

# 每小时检查编辑策略状态
0 * * * * /home/oracle/scripts/monitor_redaction.sh >> /home/oracle/scripts/monitor_redaction.log 2>&1

生产环境建议：数据编辑优化需要综合考虑安全性、可用性和管理效率。建议基于用户角色配置编辑策略，定期审计和监控编辑策略使用情况。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 PDB数据编辑总结

Oracle PDB数据编辑是保护敏感数据的重要手段，具有以下特点：

数据保护：保护敏感数据不被未授权访问
合规性：满足数据保护法规要求
灵活性：支持多种编辑类型和策略
不影响应用：不修改实际数据，不影响应用程序
易于管理：集中管理编辑策略

5.2 PDB数据编辑检查清单

Oracle PDB数据编辑检查清单：

配置检查：检查编辑策略配置
状态检查：检查编辑策略状态
效果检查：检查编辑效果
权限检查：检查编辑权限
审计检查：检查编辑策略审计
监控检查：检查编辑策略监控

5.3 PDB数据编辑工具推荐

Oracle PDB数据编辑工具推荐：

DBMS_REDACT包：数据编辑管理包
SQL*Plus：命令行工具，用于编辑策略管理
SQL Developer：图形化工具，用于编辑策略管理
Enterprise Manager：企业级管理工具
Oracle Audit Vault：审计管理工具

风哥提示：PDB数据编辑是保护敏感数据的重要手段，建议根据数据敏感程度选择合适的编辑类型，并定期审计和验证编辑策略。from:www.itpux.com www.fgedu.net.cn

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

Oracle教程FG393-PDB数据编辑

Part01-基础概念与理论知识

1.1 PDB数据编辑的概念

1.2 PDB数据编辑的类型

1.3 PDB数据编辑的优势

Part02-生产环境规划与建议

2.1 PDB数据编辑规划

2.2 PDB数据编辑安全策略

2.3 PDB数据编辑最佳实践

Part03-生产环境项目实施方案

3.1 PDB数据编辑配置

3.1.1 启用数据编辑功能

3.1.2 创建完全编辑策略

3.2 PDB数据编辑策略创建

3.2.1 创建部分编辑策略

3.2.2 创建随机编辑策略

3.3 PDB数据编辑验证

3.3.1 验证编辑策略

3.3.2 管理编辑策略

Part04-生产案例与实战讲解

4.1 PDB数据编辑配置案例

4.1.1 场景描述

4.1.2 配置步骤

4.2 PDB数据编辑故障处理

4.2.1 故障现象：ORA-28031编辑策略已存在

4.2.2 故障现象：编辑策略不生效

4.3 PDB数据编辑优化

4.3.1 优化编辑策略配置

Part05-风哥经验总结与分享

5.1 PDB数据编辑总结

5.2 PDB数据编辑检查清单

5.3 PDB数据编辑工具推荐

相关推荐

联系我们