2. 首页 > Oracle教程 > 正文

Oracle教程FG396-PDB标签安全

教程整理：风哥教程  |  更新时间：2026-01-02  |  教程分类：Oracle教程  |  文档学习：2

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

Part01-基础概念与理论知识

1.1 PDB标签安全的概念

Oracle PDB标签安全（Oracle Label Security，OLS）是一种基于标签的访问控制解决方案，用于保护敏感数据。标签安全通过为数据行和用户分配安全标签，实现基于标签的访问控制。标签安全适用于需要严格访问控制的企业环境，如政府、军事、金融等。更多视频教程www.fgedu.net.cn

Oracle PDB标签安全的特点：

  • 基于标签的访问控制
  • 支持多级安全策略
  • 支持行级别安全
  • 支持数据隔离
  • 满足合规性要求

1.2 PDB标签安全的组件

Oracle PDB标签安全的组件包括：

  • 策略（Policy）：定义标签安全策略
  • 级别（Level）：定义安全级别
  • 范围（Compartment）：定义安全范围
  • 组（Group）：定义安全组
  • 标签（Label）：定义安全标签
  • 授权（Authorization）：定义用户授权

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. 检查标签安全是否已安装
$ sqlplus / as sysdba

SQL> select comp_name, status, version from dba_registry where comp_name like ‘%Label%’;

COMP_NAME STATUS VERSION
—————————— ——— ——————
Oracle Label Security VALID 19.0.0.0.0

# 2. 如果未安装，安装标签安全
# 使用Oracle Universal Installer安装Label Security选项

# 3. 连接到PDB
SQL> alter session set container=SALESPDB;

Session altered.

# 4. 在PDB中启用标签安全
SQL> @?/rdbms/admin/catols.sql

# 5. 验证标签安全安装
SQL> select comp_name, status, version from dba_registry where comp_name like ‘%Label%’;

COMP_NAME STATUS VERSION
—————————— ——— ——————
Oracle Label Security VALID 19.0.0.0.0

# 6. 创建标签安全管理员
SQL> create user lbsys identified by lbsys_password;

User created.

SQL> grant create session to lbsys;

Grant succeeded.

SQL> grant lbacsys to lbsys;

Grant succeeded.

# 7. 验证用户创建
SQL> select username, account_status from dba_users where username = ‘LBSYS’;

USERNAME ACCOUNT_STATUS
———- ————–
LBSYS OPEN

3.1.2 配置标签安全

# 1. 连接到标签安全管理员
SQL> conn lbsys/lbsys_password@SALESPDB

Connected.

# 2. 创建策略
SQL> begin
sa_policy_admin.create_policy(
policy_name => ‘SALES_POLICY’,
column_name => ‘OLS_LABEL’,
default_options => ‘READ_CONTROL,WRITE_CONTROL,UPDATE_CONTROL’
);
end;
/

PL/SQL procedure successfully completed.

# 3. 验证策略创建
SQL> select policy_name, column_name, status from sa_policies;

POLICY_NAME COLUMN_NAME STATUS
————- ————- ——–
SALES_POLICY OLS_LABEL ENABLED

# 4. 创建级别
SQL> begin
sa_label_admin.create_level(
policy_name => ‘SALES_POLICY’,
level_num => 100,
level_name => ‘PUBLIC’,
short_name => ‘PUB’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_level(
policy_name => ‘SALES_POLICY’,
level_num => 200,
level_name => ‘INTERNAL’,
short_name => ‘INT’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_level(
policy_name => ‘SALES_POLICY’,
level_num => 300,
level_name => ‘CONFIDENTIAL’,
short_name => ‘CONF’
);
end;
/

PL/SQL procedure successfully completed.

# 5. 验证级别创建
SQL> select level_num, level_name, short_name from sa_levels where policy_name = ‘SALES_POLICY’ order by level_num;

LEVEL_NUM LEVEL_NAME SHORT_NAME
———- ————– ———-
100 PUBLIC PUB
200 INTERNAL INT
300 CONFIDENTIAL CONF

# 6. 创建范围
SQL> begin
sa_label_admin.create_compartment(
policy_name => ‘SALES_POLICY’,
comp_num => 100,
comp_name => ‘SALES’,
short_name => ‘SAL’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_compartment(
policy_name => ‘SALES_POLICY’,
comp_num => 200,
comp_name => ‘FINANCE’,
short_name => ‘FIN’
);
end;
/

PL/SQL procedure successfully completed.

# 7. 验证范围创建
SQL> select comp_num, comp_name, short_name from sa_compartments where policy_name = ‘SALES_POLICY’ order by comp_num;

COMP_NUM COMP_NAME SHORT_NAME
———- ——— ———-
100 SALES SAL
200 FINANCE FIN

# 8. 创建组
SQL> begin
sa_label_admin.create_group(
policy_name => ‘SALES_POLICY’,
group_num => 100,
group_name => ‘SALES_TEAM’,
short_name => ‘ST’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_group(
policy_name => ‘SALES_POLICY’,
group_num => 200,
group_name => ‘FINANCE_TEAM’,
short_name => ‘FT’
);
end;
/

PL/SQL procedure successfully completed.

# 9. 验证组创建
SQL> select group_num, group_name, short_name from sa_groups where policy_name = ‘SALES_POLICY’ order by group_num;

GROUP_NUM GROUP_NAME SHORT_NAME
———- ———– ———-
100 SALES_TEAM ST
200 FINANCE_TEAM FT

3.2 PDB标签安全配置

3.2.1 配置标签

# 1. 创建标签
SQL> begin
sa_label_admin.create_label(
policy_name => ‘SALES_POLICY’,
label_tag => 1000,
label_name => ‘PUBLIC’,
data_label => TRUE,
label_type => ‘L’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_label(
policy_name => ‘SALES_POLICY’,
label_tag => 2000,
label_name => ‘INTERNAL’,
data_label => TRUE,
label_type => ‘L’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_label(
policy_name => ‘SALES_POLICY’,
label_tag => 3000,
label_name => ‘CONFIDENTIAL’,
data_label => TRUE,
label_type => ‘L’
);
end;
/

PL/SQL procedure successfully completed.

# 2. 验证标签创建
SQL> select label_tag, label_name, label_type from sa_labels where policy_name = ‘SALES_POLICY’ order by label_tag;

LABEL_TAG LABEL_NAME LABEL_TYPE
———- ————- ———-
1000 PUBLIC L
2000 INTERNAL L
3000 CONFIDENTIAL L

# 3. 创建组合标签
SQL> begin
sa_label_admin.create_label(
policy_name => ‘SALES_POLICY’,
label_tag => 2100,
label_name => ‘INTERNAL:SALES’,
data_label => TRUE,
label_type => ‘LC’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_label(
policy_name => ‘SALES_POLICY’,
label_tag => 3100,
label_name => ‘CONFIDENTIAL:SALES’,
data_label => TRUE,
label_type => ‘LC’
);
end;
/

PL/SQL procedure successfully completed.

# 4. 验证组合标签创建
SQL> select label_tag, label_name, label_type from sa_labels where policy_name = ‘SALES_POLICY’ order by label_tag;

LABEL_TAG LABEL_NAME LABEL_TYPE
———- —————– ———-
1000 PUBLIC L
2000 INTERNAL L
2100 INTERNAL:SALES LC
3000 CONFIDENTIAL L
3100 CONFIDENTIAL:SALES LC

3.2.2 配置用户授权

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

SQL> alter session set container=SALESPDB;

Session altered.

SQL> create user fgfgfgsales_user identified by fgfgfgsales_password;

User created.

SQL> create user finance_user identified by finance_password;

User created.

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

Grant succeeded.

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

Grant succeeded.

# 2. 创建测试表
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)
);

Table created.

# 3. 应用策略到表
SQL> conn lbsys/lbsys_password@SALESPDB

Connected.

SQL> begin
sa_policy_admin.apply_table_policy(
policy_name => ‘SALES_POLICY’,
schema_name => ‘SALES_USER’,
table_name => ‘CUSTOMERS’,
table_options => ‘LABEL_DEFAULT,READ_CONTROL,WRITE_CONTROL,UPDATE_CONTROL’
);
end;
/

PL/SQL procedure successfully completed.

# 4. 验证策略已应用
SQL> select policy_name, schema_name, table_name, status
from sa_table_policies
where policy_name = ‘SALES_POLICY’;

POLICY_NAME SCHEMA_NAME TABLE_NAME STATUS
————- ———– ———- ——–
SALES_POLICY SALES_USER CUSTOMERS ENABLED

# 5. 授予用户授权
SQL> begin
sa_user_admin.set_user_labels(
policy_name => ‘SALES_POLICY’,
user_name => ‘SALES_USER’,
max_read_label => ‘CONFIDENTIAL:SALES’,
max_write_label => ‘CONFIDENTIAL:SALES’,
min_write_label => ‘PUBLIC’,
default_label => ‘INTERNAL:SALES’,
row_label => ‘INTERNAL:SALES’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_user_admin.set_user_labels(
policy_name => ‘SALES_POLICY’,
user_name => ‘FINANCE_USER’,
max_read_label => ‘INTERNAL’,
max_write_label => ‘INTERNAL’,
min_write_label => ‘PUBLIC’,
default_label => ‘INTERNAL’,
row_label => ‘INTERNAL’
);
end;
/

PL/SQL procedure successfully completed.

# 6. 验证用户授权
SQL> select user_name, max_read_label, max_write_label, min_write_label, default_label
from sa_user_labels
where policy_name = ‘SALES_POLICY’
order by user_name;

USER_NAME MAX_READ_LABEL MAX_WRITE_LABEL MIN_WRITE_LABEL DEFAULT_LABEL
———— —————— —————— ————– ————–
FINANCE_USER INTERNAL INTERNAL PUBLIC INTERNAL
SALES_USER CONFIDENTIAL:SALES CONFIDENTIAL:SALES PUBLIC INTERNAL:SALES

3.3 PDB标签安全验证

3.3.1 验证标签安全配置

# 1. 查看策略配置
SQL> select policy_name, column_name, status from sa_policies;

POLICY_NAME COLUMN_NAME STATUS
————- ———– ——–
SALES_POLICY OLS_LABEL ENABLED

# 2. 查看级别配置
SQL> select level_num, level_name, short_name from sa_levels where policy_name = ‘SALES_POLICY’ order by level_num;

LEVEL_NUM LEVEL_NAME SHORT_NAME
———- ————– ———-
100 PUBLIC PUB
200 INTERNAL INT
300 CONFIDENTIAL CONF

# 3. 查看范围配置
SQL> select comp_num, comp_name, short_name from sa_compartments where policy_name = ‘SALES_POLICY’ order by comp_num;

COMP_NUM COMP_NAME SHORT_NAME
———- ——— ———-
100 SALES SAL
200 FINANCE FIN

# 4. 查看组配置
SQL> select group_num, group_name, short_name from sa_groups where policy_name = ‘SALES_POLICY’ order by group_num;

GROUP_NUM GROUP_NAME SHORT_NAME
———- ———– ———-
100 SALES_TEAM ST
200 FINANCE_TEAM FT

# 5. 查看标签配置
SQL> select label_tag, label_name, label_type from sa_labels where policy_name = ‘SALES_POLICY’ order by label_tag;

LABEL_TAG LABEL_NAME LABEL_TYPE
———- —————– ———-
1000 PUBLIC L
2000 INTERNAL L
2100 INTERNAL:SALES LC
3000 CONFIDENTIAL L
3100 CONFIDENTIAL:SALES LC

# 6. 查看用户授权
SQL> select user_name, max_read_label, max_write_label, min_write_label, default_label
from sa_user_labels
where policy_name = ‘SALES_POLICY’
order by user_name;

USER_NAME MAX_READ_LABEL MAX_WRITE_LABEL MIN_WRITE_LABEL DEFAULT_LABEL
———— —————— —————— ————– ————–
FINANCE_USER INTERNAL INTERNAL PUBLIC INTERNAL
SALES_USER CONFIDENTIAL:SALES CONFIDENTIAL:SALES PUBLIC INTERNAL:SALES

3.3.2 测试标签安全

# 1. 插入测试数据（使用fgfgfgsales_user）
SQL> conn fgfgfgsales_user/fgfgfgsales_password@SALESPDB

Connected.

SQL> insert into customers (id, name, email, phone, credit_card, ssn) values (1, ‘风哥1号’, ‘zhangsan@fgedu.net.cn’, ‘13800138000’, ‘1234567890123456’, ‘123456789’);

1 row created.

SQL> insert into customers (id, name, email, phone, credit_card, ssn) values (2, ‘风哥2号’, ‘lisi@fgedu.net.cn’, ‘13900139000’, ‘2345678901234567’, ‘234567890’);

1 row created.

SQL> insert into customers (id, name, email, phone, credit_card, ssn) values (3, ‘王五’, ‘wangwu@fgedu.net.cn’, ‘13700137000’, ‘3456789012345678’, ‘345678901’);

1 row created.

SQL> commit;

Commit complete.

# 2. 查看数据（使用fgfgfgsales_user）
SQL> select * from 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. 查看数据（使用finance_user）
SQL> conn finance_user/finance_password@SALESPDB

Connected.

SQL> select * from fgfgfgsales_user.customers;

no rows selected

# 4. 分析：finance_user无法查看数据，因为其max_read_label是INTERNAL，而数据的标签是INTERNAL:SALES

# 5. 修改finance_user的授权
SQL> conn lbsys/lbsys_password@SALESPDB

Connected.

SQL> begin
sa_user_admin.set_user_labels(
policy_name => ‘SALES_POLICY’,
user_name => ‘FINANCE_USER’,
max_read_label => ‘CONFIDENTIAL:SALES’,
max_write_label => ‘CONFIDENTIAL:SALES’,
min_write_label => ‘PUBLIC’,
default_label => ‘INTERNAL:SALES’,
row_label => ‘INTERNAL:SALES’
);
end;
/

PL/SQL procedure successfully completed.

# 6. 再次查看数据（使用finance_user）
SQL> conn finance_user/finance_password@SALESPDB

Connected.

SQL> select * from fgfgfgsales_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

风哥提示：标签安全是基于标签的访问控制解决方案，建议根据数据敏感程度合理设计策略和标签，并定期审计和测试。学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 PDB标签安全配置案例

在生产环境中配置PDB标签安全的完整案例：

4.1.1 场景描述

某企业需要为销售PDB中的客户数据配置标签安全，保护信用卡号、手机号、身份证号等敏感信息，确保只有授权用户才能访问。

4.1.2 配置步骤

# 1. 创建策略
SQL> conn lbsys/lbsys_password@SALESPDB

Connected.

SQL> begin
sa_policy_admin.create_policy(
policy_name => ‘CUSTOMER_POLICY’,
column_name => ‘OLS_LABEL’,
default_options => ‘READ_CONTROL,WRITE_CONTROL,UPDATE_CONTROL’
);
end;
/

PL/SQL procedure successfully completed.

# 2. 创建级别
SQL> begin
sa_label_admin.create_level(
policy_name => ‘CUSTOMER_POLICY’,
level_num => 100,
level_name => ‘PUBLIC’,
short_name => ‘PUB’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_level(
policy_name => ‘CUSTOMER_POLICY’,
level_num => 200,
level_name => ‘INTERNAL’,
short_name => ‘INT’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_level(
policy_name => ‘CUSTOMER_POLICY’,
level_num => 300,
level_name => ‘CONFIDENTIAL’,
short_name => ‘CONF’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_level(
policy_name => ‘CUSTOMER_POLICY’,
level_num => 400,
level_name => ‘SECRET’,
short_name => ‘SEC’
);
end;
/

PL/SQL procedure successfully completed.

# 3. 创建范围
SQL> begin
sa_label_admin.create_compartment(
policy_name => ‘CUSTOMER_POLICY’,
comp_num => 100,
comp_name => ‘SALES’,
short_name => ‘SAL’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_compartment(
policy_name => ‘CUSTOMER_POLICY’,
comp_num => 200,
comp_name => ‘FINANCE’,
short_name => ‘FIN’
);
end;
/

PL/SQL procedure successfully completed.

# 4. 创建组
SQL> begin
sa_label_admin.create_group(
policy_name => ‘CUSTOMER_POLICY’,
group_num => 100,
group_name => ‘SALES_TEAM’,
short_name => ‘ST’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_group(
policy_name => ‘CUSTOMER_POLICY’,
group_num => 200,
group_name => ‘FINANCE_TEAM’,
short_name => ‘FT’
);
end;
/

PL/SQL procedure successfully completed.

# 5. 创建标签
SQL> begin
sa_label_admin.create_label(
policy_name => ‘CUSTOMER_POLICY’,
label_tag => 1000,
label_name => ‘PUBLIC’,
data_label => TRUE,
label_type => ‘L’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_label(
policy_name => ‘CUSTOMER_POLICY’,
label_tag => 2000,
label_name => ‘INTERNAL’,
data_label => TRUE,
label_type => ‘L’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_label(
policy_name => ‘CUSTOMER_POLICY’,
label_tag => 3000,
label_name => ‘CONFIDENTIAL’,
data_label => TRUE,
label_type => ‘L’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_label(
policy_name => ‘CUSTOMER_POLICY’,
label_tag => 4000,
label_name => ‘SECRET’,
data_label => TRUE,
label_type => ‘L’
);
end;
/

PL/SQL procedure successfully completed.

# 6. 创建组合标签
SQL> begin
sa_label_admin.create_label(
policy_name => ‘CUSTOMER_POLICY’,
label_tag => 2100,
label_name => ‘INTERNAL:SALES’,
data_label => TRUE,
label_type => ‘LC’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_label(
policy_name => ‘CUSTOMER_POLICY’,
label_tag => 2200,
label_name => ‘INTERNAL:FINANCE’,
data_label => TRUE,
label_type => ‘LC’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_label(
policy_name => ‘CUSTOMER_POLICY’,
label_tag => 3100,
label_name => ‘CONFIDENTIAL:SALES’,
data_label => TRUE,
label_type => ‘LC’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_label(
policy_name => ‘CUSTOMER_POLICY’,
label_tag => 3200,
label_name => ‘CONFIDENTIAL:FINANCE’,
data_label => TRUE,
label_type => ‘LC’
);
end;
/

PL/SQL procedure successfully completed.

# 7. 验证配置
SQL> select policy_name, status from sa_policies;

POLICY_NAME STATUS
—————- ——–
CUSTOMER_POLICY ENABLED

SQL> select count(*) as level_count from sa_levels where policy_name = ‘CUSTOMER_POLICY’;

LEVEL_COUNT
———–
4

SQL> select count(*) as comp_count from sa_compartments where policy_name = ‘CUSTOMER_POLICY’;

COMP_COUNT
———-
2

SQL> select count(*) as group_count from sa_groups where policy_name = ‘CUSTOMER_POLICY’;

GROUP_COUNT
———–
2

SQL> select count(*) as label_count from sa_labels where policy_name = ‘CUSTOMER_POLICY’;

LABEL_COUNT
———–
7

4.2 PDB标签安全故障处理

在PDB标签安全过程中可能遇到的故障及处理方法：

4.2.1 故障现象：ORA-12406策略已存在

# 问题现象
SQL> begin
sa_policy_admin.create_policy(
policy_name => ‘CUSTOMER_POLICY’,
column_name => ‘OLS_LABEL’,
default_options => ‘READ_CONTROL,WRITE_CONTROL,UPDATE_CONTROL’
);
end;
/

ORA-12406: policy CUSTOMER_POLICY already exists

# 分析步骤

# 1. 查看现有策略
SQL> select policy_name, column_name, status from sa_policies;

POLICY_NAME COLUMN_NAME STATUS
—————- ———– ——–
CUSTOMER_POLICY OLS_LABEL ENABLED

# 2. 发现已存在策略

# 3. 解决方案：修改现有策略
SQL> begin
sa_policy_admin.alter_policy(
policy_name => ‘CUSTOMER_POLICY’,
default_options => ‘READ_CONTROL,WRITE_CONTROL,UPDATE_CONTROL,LABEL_DEFAULT’
);
end;
/

PL/SQL procedure successfully completed.

# 4. 验证策略已修改
SQL> select policy_name, column_name, status from sa_policies;

POLICY_NAME COLUMN_NAME STATUS
—————- ———– ——–
CUSTOMER_POLICY OLS_LABEL ENABLED

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

4.2.2 故障现象：用户无法查看数据

# 问题现象
SQL> conn finance_user/finance_password@SALESPDB

Connected.

SQL> select * from fgfgfgsales_user.customers;

no rows selected

# 分析步骤

# 1. 检查用户授权
SQL> conn lbsys/lbsys_password@SALESPDB

Connected.

SQL> select user_name, max_read_label, max_write_label, min_write_label, default_label
from sa_user_labels
where policy_name = ‘CUSTOMER_POLICY’ and user_name = ‘FINANCE_USER’;

USER_NAME MAX_READ_LABEL MAX_WRITE_LABEL MIN_WRITE_LABEL DEFAULT_LABEL
———— ————– ————— ————– ————–
FINANCE_USER INTERNAL INTERNAL PUBLIC INTERNAL

# 2. 检查数据标签
SQL> select to_char(OLS_LABEL) as label, count(*) as count
from fgfgfgsales_user.customers
group by OLS_LABEL;

LABEL COUNT
——————— ———-
INTERNAL:SALES 3

# 3. 发现用户max_read_label是INTERNAL，而数据标签是INTERNAL:SALES

# 4. 解决方案：修改用户授权
SQL> begin
sa_user_admin.set_user_labels(
policy_name => ‘CUSTOMER_POLICY’,
user_name => ‘FINANCE_USER’,
max_read_label => ‘CONFIDENTIAL:SALES’,
max_write_label => ‘CONFIDENTIAL:SALES’,
min_write_label => ‘PUBLIC’,
default_label => ‘INTERNAL:SALES’,
row_label => ‘INTERNAL:SALES’
);
end;
/

PL/SQL procedure successfully completed.

# 5. 验证用户授权已修改
SQL> select user_name, max_read_label, max_write_label, min_write_label, default_label
from sa_user_labels
where policy_name = ‘CUSTOMER_POLICY’ and user_name = ‘FINANCE_USER’;

USER_NAME MAX_READ_LABEL MAX_WRITE_LABEL MIN_WRITE_LABEL DEFAULT_LABEL
———— —————— —————— ————– ————–
FINANCE_USER CONFIDENTIAL:SALES CONFIDENTIAL:SALES PUBLIC INTERNAL:SALES

# 6. 测试访问
SQL> conn finance_user/finance_password@SALESPDB

Connected.

SQL> select * from fgfgfgsales_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

# 7. 预防措施
# – 定期检查用户授权
# – 监控用户访问情况
# – 文档记录授权配置

4.3 PDB标签安全优化

优化PDB标签安全配置的最佳实践：

4.3.1 优化标签安全配置

# 1. 创建多个策略
SQL> conn lbsys/lbsys_password@SALESPDB

Connected.

SQL> begin
sa_policy_admin.create_policy(
policy_name => ‘SALES_DATA_POLICY’,
column_name => ‘SALES_LABEL’,
default_options => ‘READ_CONTROL,WRITE_CONTROL,UPDATE_CONTROL’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_policy_admin.create_policy(
policy_name => ‘FINANCE_DATA_POLICY’,
column_name => ‘FINANCE_LABEL’,
default_options => ‘READ_CONTROL,WRITE_CONTROL,UPDATE_CONTROL’
);
end;
/

PL/SQL procedure successfully completed.

# 2. 创建多个级别
SQL> begin
sa_label_admin.create_level(
policy_name => ‘SALES_DATA_POLICY’,
level_num => 100,
level_name => ‘PUBLIC’,
short_name => ‘PUB’
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
sa_label_admin.create_level(
policy_name => ‘FINANCE_DATA_POLICY’,
level_num => 100,
level_name => ‘PUBLIC’,
short_name => ‘PUB’
);
end;
/

PL/SQL procedure successfully completed.

# 3. 配置标签安全审计
SQL> audit policy oracle_secure_config;

Audit succeeded.

# 4. 监控标签安全状态
SQL> select policy_name, status from sa_policies;

POLICY_NAME STATUS
——————— ——–
CUSTOMER_POLICY ENABLED
SALES_DATA_POLICY ENABLED
FINANCE_DATA_POLICY ENABLED

# 5. 创建标签安全监控脚本
$ vi /home/oracle/scripts/monitor_ols.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

# 检查策略状态
POLICY_COUNT=$(sqlplus -s lbsys/lbsys_password@SALESPDB << EOF set heading off feedback off pagesize 0 select count(*) from sa_policies where status = 'DISABLED';
exit;
EOF
)

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

# 检查用户授权
USER_COUNT=$(sqlplus -s lbsys/lbsys_password@SALESPDB << EOF set heading off feedback off pagesize 0 select count(*) from sa_user_labels where max_read_label is null;
exit;
EOF
)

if [ “$USER_COUNT” -gt 0 ]; then
echo “WARNING: $USER_COUNT users have no read label”
# 发送告警
echo “$USER_COUNT users have no read label” | mail -s “OLS User Alert” admin@fgedu.net.cn
fi

echo “OLS monitoring completed at $(date)”

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

# 每小时检查标签安全状态
0 * * * * /home/oracle/scripts/monitor_ols.sh >> /home/oracle/scripts/monitor_ols.log 2>&1

生产环境建议：标签安全优化需要综合考虑安全性、可用性和管理效率。建议合理设计策略和标签，定期审计和测试访问控制规则。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 PDB标签安全总结

Oracle PDB标签安全是基于标签的访问控制解决方案，具有以下特点：

  • 数据保护：保护敏感数据的安全性
  • 合规性：满足数据保护法规要求
  • 行级别安全：实现行级别访问控制
  • 数据隔离：实现数据隔离
  • 灵活控制：支持灵活的访问控制

5.2 PDB标签安全检查清单

Oracle PDB标签安全检查清单：

  • 配置检查：检查策略、级别、范围、组配置
  • 状态检查：检查策略状态
  • 标签检查：检查标签配置
  • 授权检查：检查用户授权
  • 审计检查：检查标签安全审计
  • 监控检查：检查标签安全监控

5.3 PDB标签安全工具推荐

Oracle PDB标签安全工具推荐：

  • SA_POLICY_ADMIN包：策略管理包
  • SA_LABEL_ADMIN包：标签管理包
  • SA_USER_ADMIN包：用户管理包
  • SQL*Plus：命令行工具，用于标签安全管理
  • SQL Developer：图形化工具，用于标签安全管理
风哥提示：PDB标签安全是基于标签的访问控制解决方案，特别适合需要严格访问控制的企业环境，如政府、军事、金融等。建议根据数据敏感程度合理设计策略和标签，并定期审计和测试。from:www.itpux.com www.fgedu.net.cn

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

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息