Oracle教程FG400-PDB数据脱敏

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

Part01-基础概念与理论知识

1.1 PDB数据脱敏的概念

Oracle PDB数据脱敏（Data Masking）是一种数据保护技术，用于在生产环境中隐藏敏感数据。数据脱敏通过替换、加密或删除敏感数据，保护数据隐私。数据脱敏适用于需要保护敏感数据的企业环境。更多视频教程www.fgedu.net.cn

Oracle PDB数据脱敏的特点：

保护敏感数据
保持数据格式
支持多种脱敏方式
灵活配置
减少数据泄露风险

1.2 PDB数据脱敏的组件

Oracle PDB数据脱敏的组件包括：

脱敏策略（Masking Policy）：定义脱敏规则
脱敏列（Masking Column）：定义脱敏的列
脱敏类型（Masking Type）：定义脱敏类型
脱敏格式（Masking Format）：定义脱敏格式
脱敏函数（Masking Function）：定义脱敏函数

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> create user fgfgfgsales_user identified by fgfgfgsales_password;

User created.

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

Grant succeeded.

# 3. 创建测试表
SQL> create table fgfgfgsales_user.customers (
id number primary key,
name varchar2(100),
email varchar2(100),
phone varchar2(20),
credit_card varchar2(20),
ssn varchar2(20),
balance number
);

Table created.

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

1 row created.

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

1 row created.

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

1 row created.

SQL> commit;

Commit complete.

# 5. 验证数据
SQL> select * from fgfgfgsales_user.customers;

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

3.1.2 创建数据脱敏视图

# 1. 创建脱敏视图
SQL> create or replace view fgfgfgsales_user.customers_masked as
select
id,
name,
regexp_replace(email, ‘(.{2})(.*)(@.*)’, ‘\1****\3’) as email,
regexp_replace(phone, ‘(.{3})(.{4})(.{4})’, ‘\1****\3’) as phone,
‘****-****-****-‘ || substr(credit_card, -4) as credit_card,
‘***-**-‘ || substr(ssn, -4) as ssn,
balance
from fgfgfgsales_user.customers;

View created.

# 2. 验证脱敏视图
SQL> select * from fgfgfgsales_user.customers_masked;

ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE
———- ———- ———————- ————— —————— ———- ———-
1 风哥1号 zh****@fgedu.net.cn 138****8000 ****-****-****-3456 ***-**-6789 10000
2 风哥2号 li****@fgedu.net.cn 139****9000 ****-****-****-4567 ***-**-7890 20000
3 王五 wa****@fgedu.net.cn 137****7000 ****-****-****-5678 ***-**-8901 30000

# 3. 创建脱敏函数
SQL> create or replace function fgfgfgsales_user.mask_email(p_email in varchar2) return varchar2 is
v_masked_email varchar2(100);
begin
v_masked_email := regexp_replace(p_email, ‘(.{2})(.*)(@.*)’, ‘\1****\3’);
return v_masked_email;
end mask_email;
/

Function created.

SQL> create or replace function fgfgfgsales_user.mask_phone(p_phone in varchar2) return varchar2 is
v_masked_phone varchar2(20);
begin
v_masked_phone := regexp_replace(p_phone, ‘(.{3})(.{4})(.{4})’, ‘\1****\3’);
return v_masked_phone;
end mask_phone;
/

Function created.

SQL> create or replace function fgfgfgsales_user.mask_credit_card(p_cc in varchar2) return varchar2 is
v_masked_cc varchar2(20);
begin
v_masked_cc := ‘****-****-****-‘ || substr(p_cc, -4);
return v_masked_cc;
end mask_credit_card;
/

Function created.

SQL> create or replace function fgfgfgsales_user.mask_ssn(p_ssn in varchar2) return varchar2 is
v_masked_ssn varchar2(20);
begin
v_masked_ssn := ‘***-**-‘ || substr(p_ssn, -4);
return v_masked_ssn;
end mask_ssn;
/

Function created.

# 4. 验证脱敏函数
SQL> select fgfgfgsales_user.mask_email(‘zhangsan@fgedu.net.cn’) as masked_email from dual;

MASKED_EMAIL
—————-
zh****@fgedu.net.cn

SQL> select fgfgfgsales_user.mask_phone(‘13800138000’) as masked_phone from dual;

MASKED_PHONE
—————-
138****8000

SQL> select fgfgfgsales_user.mask_credit_card(‘1234567890123456’) as masked_cc from dual;

MASKED_CC
—————-
****-****-****-3456

SQL> select fgfgfgsales_user.mask_ssn(‘123456789’) as masked_ssn from dual;

MASKED_SSN
—————-
***-**-6789

3.2 PDB数据脱敏策略创建

3.2.1 创建基于角色的脱敏策略

# 1. 创建角色
SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> create role fgfgfgsales_manager_role;

Role created.

SQL> create role fgfgfgsales_clerk_role;

Role created.

# 2. 授予权限
SQL> grant select on fgfgfgsales_user.customers to fgfgfgsales_manager_role;

Grant succeeded.

SQL> grant select on fgfgfgsales_user.customers_masked to fgfgfgsales_clerk_role;

Grant succeeded.

# 3. 创建用户
SQL> create user manager_user identified by manager_password;

User created.

SQL> create user clerk_user identified by clerk_password;

User created.

SQL> grant create session, fgfgfgsales_manager_role to manager_user;

Grant succeeded.

SQL> grant create session, fgfgfgsales_clerk_role to clerk_user;

Grant succeeded.

# 4. 测试脱敏策略
SQL> conn manager_user/manager_password@SALESPDB

Connected.

SQL> select * from fgfgfgsales_user.customers;

SQL> conn clerk_user/clerk_password@SALESPDB

Connected.

SQL> select * from fgfgfgsales_user.customers_masked;

3.2.2 创建基于VPD的脱敏策略

# 1. 创建VPD脱敏函数
SQL> create or replace function fgfgfgsales_user.masking_vpd_func(
p_schema in varchar2,
p_object in varchar2
) return varchar2 is
v_username varchar2(100);
v_predicate varchar2(200);
begin
v_username := sys_context(‘USERENV’, ‘SESSION_USER’);

if v_username = ‘MANAGER_USER’ then
v_predicate := ‘1=1’;
else
v_predicate := ‘1=0’;
end if;

return v_predicate;
end masking_vpd_func;
/

Function created.

# 2. 创建VPD策略
SQL> begin
dbms_rls.add_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘MASKING_POLICY’,
function_schema => ‘SALES_USER’,
policy_function => ‘MASKING_VPD_FUNC’,
statement_types => ‘SELECT’,
enable => true
);
end;
/

PL/SQL procedure successfully completed.

# 3. 验证VPD策略
SQL> select object_schema, object_name, policy_name, enabled
from dba_policies
where object_schema = ‘SALES_USER’ and object_name = ‘CUSTOMERS’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENABLED
————- ———— —————– ——–
SALES_USER CUSTOMERS MASKING_POLICY YES

# 4. 测试VPD策略
SQL> conn manager_user/manager_password@SALESPDB

Connected.

SQL> select * from fgfgfgsales_user.customers;

SQL> conn clerk_user/clerk_password@SALESPDB

Connected.

SQL> select * from fgfgfgsales_user.customers;

no rows selected

SQL> select * from fgfgfgsales_user.customers_masked;

3.3 PDB数据脱敏验证

3.3.1 验证数据脱敏配置

# 1. 查看脱敏视图
SQL> select view_name, text from user_views where view_name = ‘CUSTOMERS_MASKED’;

VIEW_NAME TEXT
—————– ————————————————
CUSTOMERS_MASKED select id, name, regexp_replace(email, ‘(.{2})(.*)(@.*)’, ‘\1****\3’) as email,
regexp_replace(phone, ‘(.{3})(.{4})(.{4})’, ‘\1****\3’) as phone,
‘****-****-****-‘ || substr(credit_card, -4) as credit_card,
‘***-**-‘ || substr(ssn, -4) as ssn, balance
from fgfgfgsales_user.customers

# 2. 查看脱敏函数
SQL> select object_name, object_type, status from user_objects where object_name like ‘MASK_%’;

OBJECT_NAME OBJECT_TYPE STATUS
——————– ————- ——-
MASK_EMAIL FUNCTION VALID
MASK_PHONE FUNCTION VALID
MASK_CREDIT_CARD FUNCTION VALID
MASK_SSN FUNCTION VALID
MASKING_VPD_FUNC FUNCTION VALID

# 3. 查看VPD策略
SQL> select object_schema, object_name, policy_name, enabled
from dba_policies
where object_schema = ‘SALES_USER’ and object_name = ‘CUSTOMERS’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENABLED
————- ———— —————– ——–
SALES_USER CUSTOMERS MASKING_POLICY YES

# 4. 验证脱敏效果
SQL> conn manager_user/manager_password@SALESPDB

Connected.

SQL> select id, name, email, phone, credit_card, ssn from fgfgfgsales_user.customers where id = 1;

ID NAME EMAIL PHONE CREDIT_CARD SSN
———- ———- ———————- ————— —————— ———-
1 风哥1号 zhangsan@fgedu.net.cn 13800138000 1234567890123456 123456789

SQL> conn clerk_user/clerk_password@SALESPDB

Connected.

SQL> select id, name, email, phone, credit_card, ssn from fgfgfgsales_user.customers_masked where id = 1;

ID NAME EMAIL PHONE CREDIT_CARD SSN
———- ———- ———————- ————— —————— ———-
1 风哥1号 zh****@fgedu.net.cn 138****8000 ****-****-****-3456 ***-**-6789

3.3.2 管理数据脱敏策略

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

SQL> alter session set container=SALESPDB;

Session altered.

SQL> begin
dbms_rls.enable_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘MASKING_POLICY’,
enable => false
);
end;
/

PL/SQL procedure successfully completed.

# 2. 验证策略已禁用
SQL> select object_schema, object_name, policy_name, enabled
from dba_policies
where object_schema = ‘SALES_USER’ and object_name = ‘CUSTOMERS’;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENABLED
————- ———— —————– ——–
SALES_USER CUSTOMERS MASKING_POLICY NO

# 3. 启用VPD策略
SQL> begin
dbms_rls.enable_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘MASKING_POLICY’,
enable => true
);
end;
/

PL/SQL procedure successfully completed.

# 4. 删除VPD策略
SQL> begin
dbms_rls.drop_policy(
object_schema => ‘SALES_USER’,
object_name => ‘CUSTOMERS’,
policy_name => ‘MASKING_POLICY’
);
end;
/

PL/SQL procedure successfully completed.

# 5. 验证策略已删除
SQL> select object_schema, object_name, policy_name, enabled
from dba_policies
where object_schema = ‘SALES_USER’ and object_name = ‘CUSTOMERS’;

no rows selected

风哥提示：数据脱敏是数据保护技术，建议根据数据敏感程度合理设计脱敏策略和类型，并定期审计和测试。学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 PDB数据脱敏配置案例

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

4.1.1 场景描述

某企业需要为销售PDB中的客户数据配置数据脱敏，保护客户的敏感信息（如信用卡号、SSN等），普通员工只能看到脱敏后的数据。

4.1.2 配置步骤

# 1. 创建测试表
SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> create user fgfgfgsales_admin identified by fgfgfgsales_password;

User created.

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

Grant succeeded.

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

Table created.

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

1 row created.

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

1 row created.

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

1 row created.

SQL> insert into fgfgfgsales_admin.customers values (4, ‘赵六’, ‘zhaoliu@fgedu.net.cn’, ‘13600136000’, ‘4567890123456789’, ‘456789012’, 40000, ‘INACTIVE’);

1 row created.

SQL> insert into fgfgfgsales_admin.customers values (5, ‘钱七’, ‘qianqi@fgedu.net.cn’, ‘13500135000’, ‘5678901234567890’, ‘567890123’, 50000, ‘ACTIVE’);

1 row created.

SQL> commit;

Commit complete.

# 3. 创建脱敏视图
SQL> create or replace view fgfgfgsales_admin.customers_masked as
select
id,
name,
regexp_replace(email, ‘(.{2})(.*)(@.*)’, ‘\1****\3’) as email,
regexp_replace(phone, ‘(.{3})(.{4})(.{4})’, ‘\1****\3’) as phone,
‘****-****-****-‘ || substr(credit_card, -4) as credit_card,
‘***-**-‘ || substr(ssn, -4) as ssn,
balance,
status
from fgfgfgsales_admin.customers;

View created.

# 4. 验证脱敏视图
SQL> select * from fgfgfgsales_admin.customers_masked;

ID NAME EMAIL PHONE CREDIT_CARD SSN BALANCE STATUS
———- ———- ———————- ————— —————— ———- ———- ———-
1 风哥1号 zh****@fgedu.net.cn 138****8000 ****-****-****-3456 ***-**-6789 10000 ACTIVE
2 风哥2号 li****@fgedu.net.cn 139****9000 ****-****-****-4567 ***-**-7890 20000 ACTIVE
3 王五 wa****@fgedu.net.cn 137****7000 ****-****-****-5678 ***-**-8901 30000 ACTIVE
4 赵六 zh****@fgedu.net.cn 136****6000 ****-****-****-6789 ***-**-9012 40000 INACTIVE
5 钱七 qi****@fgedu.net.cn 135****5000 ****-****-****-7890 ***-**-0123 50000 ACTIVE

# 5. 创建角色
SQL> create role admin_role;

Role created.

SQL> create role user_role;

Role created.

# 6. 授予权限
SQL> grant select on fgfgfgsales_admin.customers to admin_role;

Grant succeeded.

SQL> grant select on fgfgfgsales_admin.customers_masked to user_role;

Grant succeeded.

# 7. 创建用户
SQL> create user admin_user identified by admin_password;

User created.

SQL> create user user_user identified by user_password;

User created.

SQL> grant create session, admin_role to admin_user;

Grant succeeded.

SQL> grant create session, user_role to user_user;

Grant succeeded.

# 8. 测试脱敏策略
SQL> conn admin_user/admin_password@SALESPDB

Connected.

SQL> select id, name, email, phone, credit_card, ssn from fgfgfgsales_admin.customers where id = 1;

SQL> conn user_user/user_password@SALESPDB

Connected.

SQL> select id, name, email, phone, credit_card, ssn from fgfgfgsales_admin.customers_masked where id = 1;

4.2 PDB数据脱敏故障处理

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

4.2.1 故障现象：脱敏视图返回错误数据

# 问题现象
SQL> conn user_user/user_password@SALESPDB

Connected.

SQL> select id, name, email, phone, credit_card, ssn from fgfgfgsales_admin.customers_masked where id = 1;

# 分析步骤

# 1. 检查脱敏视图定义
SQL> conn / as sysdba

SQL> alter session set container=SALESPDB;

Session altered.

SQL> select view_name, text from user_views where view_name = ‘CUSTOMERS_MASKED’;

VIEW_NAME TEXT
—————– ————————————————
CUSTOMERS_MASKED select id, name, email, phone, credit_card, ssn, balance, status
from fgfgfgsales_admin.customers

# 2. 发现视图定义错误，没有应用脱敏函数

# 3. 解决方案：重新创建脱敏视图
SQL> create or replace view fgfgfgsales_admin.customers_masked as
select
id,
name,
regexp_replace(email, ‘(.{2})(.*)(@.*)’, ‘\1****\3’) as email,
regexp_replace(phone, ‘(.{3})(.{4})(.{4})’, ‘\1****\3’) as phone,
‘****-****-****-‘ || substr(credit_card, -4) as credit_card,
‘***-**-‘ || substr(ssn, -4) as ssn,
balance,
status
from fgfgfgsales_admin.customers;

View created.

# 4. 验证脱敏视图
SQL> conn user_user/user_password@SALESPDB

Connected.

SQL> select id, name, email, phone, credit_card, ssn from fgfgfgsales_admin.customers_masked where id = 1;

# 5. 预防措施
# – 定期检查脱敏视图定义
# – 测试脱敏函数效果
# – 文档记录脱敏配置

4.2.2 故障现象：脱敏函数返回错误结果

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

Connected.

SQL> select regexp_replace(‘zhangsan@fgedu.net.cn’, ‘(.{2})(.*)(@.*)’, ‘\1****\3’) as masked_email from dual;

MASKED_EMAIL
—————-
zhangsan@fgedu.net.cn

# 分析步骤

# 1. 检查脱敏函数
SQL> select text from user_source where name = ‘MASK_EMAIL’ order by line;

TEXT
——————————————————————————–
function mask_email(p_email in varchar2) return varchar2 is
v_masked_email varchar2(100);
begin
v_masked_email := regexp_replace(p_email, ‘(.{2})(.*)(@.*)’, ‘\1****\3’);
return v_masked_email;
end mask_email;

# 2. 测试正则表达式
SQL> select regexp_replace(‘zhangsan@fgedu.net.cn’, ‘(.{2})(.*)(@.*)’, ‘\1****\3’) as masked_email from dual;

MASKED_EMAIL
—————-
zhangsan@fgedu.net.cn

# 3. 发现正则表达式有问题

# 4. 解决方案：修正正则表达式
SQL> select regexp_replace(‘zhangsan@fgedu.net.cn’, ‘(.{2}).*(@.*)’, ‘\1****\2’) as masked_email from dual;

MASKED_EMAIL
—————-
zh****@fgedu.net.cn

# 5. 重新创建脱敏视图
SQL> create or replace view fgfgfgsales_admin.customers_masked as
select
id,
name,
regexp_replace(email, ‘(.{2}).*(@.*)’, ‘\1****\2’) as email,
regexp_replace(phone, ‘(.{3}).*(.{4})’, ‘\1****\2’) as phone,
‘****-****-****-‘ || substr(credit_card, -4) as credit_card,
‘***-**-‘ || substr(ssn, -4) as ssn,
balance,
status
from fgfgfgsales_admin.customers;

View created.

# 6. 验证脱敏视图
SQL> select id, name, email, phone, credit_card, ssn from fgfgfgsales_admin.customers_masked where id = 1;

# 7. 预防措施
# – 测试正则表达式
# – 验证脱敏函数
# – 定期检查脱敏效果

4.3 PDB数据脱敏优化

优化PDB数据脱敏配置的最佳实践：

4.3.1 优化数据脱敏配置

# 1. 创建多种脱敏类型
SQL> create or replace view fgfgfgsales_admin.customers_masked_full as
select
id,
name,
regexp_replace(email, ‘(.{2}).*(@.*)’, ‘\1****\2’) as email,
regexp_replace(phone, ‘(.{3}).*(.{4})’, ‘\1****\2’) as phone,
‘****-****-****-‘ || substr(credit_card, -4) as credit_card,
‘***-**-‘ || substr(ssn, -4) as ssn,
balance,
status
from fgfgfgsales_admin.customers;

View created.

SQL> create or replace view fgfgfgsales_admin.customers_masked_partial as
select
id,
name,
regexp_replace(email, ‘(.{3}).*(@.*)’, ‘\1***\2’) as email,
regexp_replace(phone, ‘(.{4}).*(.{4})’, ‘\1***\2’) as phone,
substr(credit_card, 1, 4) || ‘******’ || substr(credit_card, -4) as credit_card,
substr(ssn, 1, 3) || ‘***’ || substr(ssn, -2) as ssn,
balance,
status
from fgfgfgsales_admin.customers;

View created.

# 2. 验证多种脱敏类型
SQL> select id, name, email, phone, credit_card, ssn from fgfgfgsales_admin.customers_masked_full where id = 1;

SQL> select id, name, email, phone, credit_card, ssn from fgfgfgsales_admin.customers_masked_partial where id = 1;

ID NAME EMAIL PHONE CREDIT_CARD SSN
———- ———- ———————- ————— —————— ———-
1 风哥1号 zha***@fgedu.net.cn 1380***8000 1234******3456 123***89

# 3. 创建脱敏审计
SQL> audit select on fgfgfgsales_admin.customers by access;

Audit succeeded.

SQL> audit select on fgfgfgsales_admin.customers_masked by access;

Audit succeeded.

# 4. 监控脱敏访问
SQL> select db_user, object_name, action_name, timestamp
from dba_audit_trail
where object_name in (‘CUSTOMERS’, ‘CUSTOMERS_MASKED’)
order by timestamp desc;

DB_USER OBJECT_NAME ACTION_NAME TIMESTAMP
———— ——————– ———– —————–
ADMIN_USER CUSTOMERS SELECT 31-MAR-26 10:00:00
USER_USER CUSTOMERS_MASKED SELECT 31-MAR-26 10:00:00

# 5. 创建脱敏监控脚本
$ vi /home/oracle/scripts/monitor_dm.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

# 检查脱敏视图状态
VIEW_COUNT=$(sqlplus -s / as sysdba << EOF set heading off feedback off pagesize 0 alter session set container=SALESPDB;
select count(*) from user_views where view_name like ‘%MASKED%’;
exit;
EOF
)

echo “Masking views count: $VIEW_COUNT”

# 检查脱敏函数状态
FUNCTION_COUNT=$(sqlplus -s / as sysdba << EOF set heading off feedback off pagesize 0 alter session set container=SALESPDB;
select count(*) from user_objects where object_type = ‘FUNCTION’ and object_name like ‘MASK_%’;
exit;
EOF
)

echo “Masking functions count: $FUNCTION_COUNT”

# 检查脱敏访问
ACCESS_COUNT=$(sqlplus -s / as sysdba << EOF set heading off feedback off pagesize 0 alter session set container=SALESPDB;
select count(*) from dba_audit_trail where object_name like ‘%MASKED%’ and timestamp > sysdate – 1;
exit;
EOF
)

echo “Masking access in last 24 hours: $ACCESS_COUNT”

echo “Data masking monitoring completed at $(date)”

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

# 每小时检查数据脱敏状态
0 * * * * /home/oracle/scripts/monitor_dm.sh >> /home/oracle/scripts/monitor_dm.log 2>&1

生产环境建议：数据脱敏优化需要综合考虑安全性、可用性和管理效率。建议合理设计脱敏策略和类型，定期审计和测试。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 PDB数据脱敏总结

Oracle PDB数据脱敏是数据保护技术，具有以下特点：

数据保护：保护敏感数据的安全性
格式保持：保持数据格式
灵活配置：支持灵活的脱敏配置
多种方式：支持多种脱敏方式
减少风险：减少数据泄露风险

5.2 PDB数据脱敏检查清单

Oracle PDB数据脱敏检查清单：

配置检查：检查数据脱敏配置
视图检查：检查脱敏视图
函数检查：检查脱敏函数
效果检查：检查脱敏效果
审计检查：检查数据脱敏审计
监控检查：检查数据脱敏监控

5.3 PDB数据脱敏工具推荐

Oracle PDB数据脱敏工具推荐：

SQL*Plus：命令行工具，用于数据脱敏管理
SQL Developer：图形化工具，用于数据脱敏管理
Enterprise Manager：企业级管理工具
Oracle Data Masking：Oracle数据脱敏工具
第三方工具：第三方数据脱敏工具

风哥提示：PDB数据脱敏是数据保护技术，特别适合需要保护敏感数据的企业环境。建议根据数据敏感程度合理设计脱敏策略和类型，并定期审计和测试。from:www.itpux.com www.fgedu.net.cn

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

Oracle教程FG400-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 创建基于VPD的脱敏策略

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 故障现象：脱敏视图返回错误数据

4.2.2 故障现象：脱敏函数返回错误结果

4.3 PDB数据脱敏优化

4.3.1 优化数据脱敏配置

Part05-风哥经验总结与分享

5.1 PDB数据脱敏总结

5.2 PDB数据脱敏检查清单

5.3 PDB数据脱敏工具推荐

相关推荐

联系我们