admin 管理员组文章数量: 887032
2023年12月24日发(作者：关于数据库sql面试题)
使用ORACLE透明敏感数据保护(TSDP)
目录
关于TSDP ......................................................................................................................................... 2
限制条件........................................................................................................................................... 2
使用TSDP的步骤 ............................................................................................................................ 2
需要的权限....................................................................................................................................... 3
涉及表/视图 ..................................................................................................................................... 3
创建TSDP-ODR策略（脱敏效果） ................................................................................................ 4
显示效果 ................................................................................................................................... 4
说明........................................................................................................................................... 4
配置策略 ................................................................................................................................... 4
1. 创建敏感类型 .............................................................................................................. 4
2. 标识敏感列 .................................................................................................................. 5
3. 创建TSDP保护策略 ................................................................................................... 6
4. 关联策略与类型 .......................................................................................................... 7
5. 启用策略保护 .............................................................................................................. 7
6. 删除策略 ...................................................................................................................... 8
创建TSDP-VPD策略（权控效果） ................................................................................................ 8
显示效果 ................................................................................................................................... 8
说明........................................................................................................................................... 8
配置策略 ................................................................................................................................. 10
创建TSDP参数化脱敏策略 .......................................................................................................... 12
效果......................................................................................................................................... 12
配置策略 ................................................................................................................................. 12
测试语句如下 ......................................................................................................................... 14
注意：..................................................................................................................................... 14
创建TSDP-TDECE(列加密)策略 ..................................................................................................... 14
说明......................................................................................................................................... 14
策略配置 ................................................................................................................................. 16
创建TSDP-Unified Auditing(统一审计)策略 ................................................................................. 18
审计效果 ................................................................................................................................. 18
说明......................................................................................................................................... 18
配置策略 ................................................................................................................................. 20
可能错误 ................................................................................................................................. 22
PLS-00302&PLS-00201:必须声明‘PARENT_SCHEMA’组件 ...................................... 22
ORA-45618：未能对一个或多个列强制执行策略 ...................................................... 22
ORA-00905: missing keyword during CREATE AUDIT POLICY .......................................... 23
创建TSDP-FGA审计策略............................................................................................................... 23
参考 ................................................................................................................................................ 23
附件一 ............................................................................................................................................ 24
关于TSDP
透明敏感数据保护(Transparent Sensitive Data Protection)是一种查找和分类包含敏感列信息的方法。
此功能使您能够在数据库中快速找到包含敏感数据的表列，对这些数据进行分类，然后创建一个策略来保护给定分类的全部数据。例如信用卡号码或社会保险号码。
然后，TSDP策略使用Oracle Data Redaction 或者 Oracle Virtual Private Database来保护这些敏感数据。TSDP策略作用于特定的数据类型的列上，例如所有包含信用卡信息的NUMBER类型数据列。可以为某一分类的所有数据创建一个统一的TSDP策略，然后根据需要修改此策略即可。也可以导出TSDP策略以在其他数据库中使用。
TSDP策略的好处是巨大的：您可以轻松地在具有许多数据库的大型组织中创建和应用TSDP策略。这使审计人员能够估计TSDP策略所针对的数据的保护。TSDP对于政府环境特别有用，在这种环境中，您可能有许多具有类似安全限制的数据，并且必须一致地对所有这些数据应用策略。策略可以是对其进行修订、加密、控制对它的访问、审核对它的访问，并在审核跟踪中屏蔽它。如果没有TSDP，则必须逐列配置访问控制策略、列级加密配置和虚拟专用数据库策略。
限制条件
仅支持oracle12c及以上的企业版
不能限制sysdba
使用TSDP的步骤
要在Oracle Data Redaction中使用TSDP，必须遵循以下常规步骤。
1. 创建敏感类型以对要保护的列类型进行分类。
例如，您可以创建一个敏感类型，用于对所有社会保险号码或信用卡号码进行分类。要创建敏感类型，请使用DBMS_TSDP__SENSITIVE_TYPE 存储过程或使用Enterprise Manager云控制应用程序数据模型。要在应用程序数据模型的一个操作中添加多个敏感类型，可以使用
DBMS_TSDP__SENSITIVE_TYPES存储过程。
2. 标识与敏感类型关联的敏感列的列表。
要确定并生成此列表，可以使用以下任一方法：
* DBMS_TSDP__SENSITIVE_COLUMN过程单独标识敏感列。
* Oracle Enterprise Manager云控制应用程序数据模型使您能够标识一组敏感 列。然后它以XML格式保存敏感列的列表，然后将其导入数据库。
3. 如果在步骤2中使用应用程序数据模型，则使用DBMS_TSDP__DISCOVERY_RESULT过程将敏感列列表从应用程序数据模型导入数据库。
4. 通过在定义要使用的数据编校或虚拟专用数据库设置的匿名块中使用DBMS_TSDP__POLICY策略过程创建TSDP策略。
5. 使用ate-policy过程将TSDP策略与一个或多个敏感类型关联。
6. 使用
DBMS_TSDP__PROTECTION_SOURCE
DBMS_TSDP__PROTECTION_COLUMN或
DBMS_TSDP__PROTECTION_TYPE过程启用TSDP策略保护。
7. 或者，通过使用Oracle数据泵执行完整的数据库导出，将TSDP策略导出到其他数据库。（不能单独导出TSDP策略。）
需要的权限
需要以下程序包的执行权限
GRANT EXECUTE ON DBMS_TSDP_MANAGE TO USER1;
GRANT EXECUTE ON DBMS_TSDP_PROTECT TO USER1;
GRANT EXECUTE ON DBMS_REDACT TO USER1;
GRANT EXECUTE ON DBMS_RLS TO USER1;
GRANT AUDIT SYSTEM TO USER1;
涉及表/视图
View name
DBA_DISCOVERY_SOURCE
DBA_SENSITIVE_COLUMN_TYPES
DBA_SENSITIVE_DATA
DBA_TSDP_IMPORT_ERRORS
Description
Describes discovery import information
with regard to transparent sensitive data
protection policies
Describes the sensitive column types
that have been defined for the current
database
Describes the sensitive columns in the
database
Shows information regarding the errors
encountered during import of discovery
result. It shows information with regard
to the error code, schema name, table
DBA_TSDP_POLICY_CONDITION
DBA_TSDP_POLICY_FEATURE
DBA_TSDP_POLICY_PARAMETER
DBA_TSDP_POLICY_PROTECTION
DBA_TSDP_POLICY_TYPE
name, column name, and sensitive type.
Describes the transparent sensitive data
protection policy and condition mapping.
This view also lists the property-value
pairs for the condition.
Shows the transparent sensitive data
protection policy security feature
mapping. (At this time, only Oracle Data
Redaction and Oracle Virtual Private
Database are supported.)
Describes the parameters of transparent
sensitive data protection policies
Shows the list of columns that have been
protected through transparent sensitive
data protection
Shows the policy to sensitive column
type mapping
创建TSDP-ODR策略（脱敏效果）
显示效果
原始数据 test2查看为脱敏效果
说明
如果TSDP与Oracle Data Redaction来结合使用，则需要有DBMS_REDACT
包的执行权限，使用DBMS_REDACT包的相关参数来创建策略
security_feature设为DBMS_TSDP_
policy_enable_options可指定的参数 参考链接
配置策略
1. 创建敏感类型
--创建敏感类型
--sensitive_type:
敏感类型名称，该名称区分大小写，
--该类型创建后可在DBA_SENSITIVE_COLUMN_TYPES视图查询
--user_comment:敏感类型备注
BEGIN
DBMS_TSDP__SENSITIVE_TYPE (
sensitive_type => 'credit_card_num_type',
user_comment => 'Type for credit card columns using a number data
type');
END;
--删除敏感类型
BEGIN
DBMS_TSDP__SENSITIVE_TYPE(sensitive_type =>
'credit_card_num_type')
END;
2. 标识敏感列
标识敏感列之前，你必须先确认你要保护的敏感列，并且已定义敏感
确定以后，可以使用DBMS_TSDP__SENSITIVE_COLUMN过程标识敏感列，使用DBMS_TSDP__SENSITIVE_COLUMN过程取消标识
--添加敏感类型与数据列关联
--schema_name,table_name,columm_name默认为‘%’
BEGIN
DBMS_TSDP__SENSITIVE_COLUMN(
schema_name => 'TEST1',
table_name => 'TT',
column_name => 'CREDIT_CARD',
sensitive_type => 'credit_card_num_type',
user_comment => 'Sensitive column addition of
credit_card_num_type');
END;
--删除敏感类型与数据列关联
BEGIN
DBMS_TSDP__SENSITIVE_COLUMN(
schema_name => 'TEST',
table_name => 'TT',
column_name => 'CREDIT_CARD')
END;
如果你使用Application Data Model（ADM）生成了一个敏感列的列表，那么你可以通过DBMS_TSDP__DISCOVERY_RESULT过程进行批量导入
例如，详情参考
BEGIN
DBMS_TSDP__DISCOVERY_RESULT (
discovery_result => xml_adm_result,
discovery_source => 'ADM_Demo');
END;
3. 创建TSDP保护策略
使用DBMS_TSDP__POLICY过程添加保护策略，创建策略时必须指定使用Virtual Private Database 或 Oracle Data Redaction
此处使用Oracle Data Redaction
--添加策略
DECLARE
redact_feature_options DBMS_TSDP_E_OPTIONS;
policy_conditions DBMS_TSDP__CONDITIONS;
BEGIN
redact_feature_options ('expression') :=
'SYS_CONTEXT(''USERENV'',''SESSION_USER'') =''TEST2''';
--以下两项值设置可参考DBMS_REDACT
redact_feature_options ('function_type') := 'DBMS_L';
redact_feature_options ('function_parameters') := '0,1,6';
policy_conditions(DBMS_TSDP_PE) := 'NUMBER';
policy_conditions(DBMS_TSDP_) := '16';
DBMS_TSDP__POLICY (
'redact_partial_cc', DBMS_TSDP_,
redact_feature_options,policy_conditions);
END;
--删除策略
BEGIN
DBMS_TSDP__POLICY(
policy_name => 'redact_partial_cc');
END;
4. 关联策略与类型
--策略关联敏感类型
BEGIN
DBMS_TSDP_ATE_POLICY(
policy_name => 'redact_partial_cc',
sensitive_type => 'credit_card_num_type',
associate => true);
END;
5. 启用策略保护
--启用策略
BEGIN
DBMS_TSDP__PROTECTION_TYPE(
sensitive_type => 'credit_card_num_type');
END;
--禁用策略
BEGIN
DBMS_TSDP_E_PROTECTION_TYPE(
sensitive_type => 'credit_card_num_type');
END;
BEGIN
DBMS_TSDP_E_PROTECTION_COLUMN (
schema_name => 'TEST1',
table_name => 'TT',
column_name => 'CREDIT_CARD'
policy_name => 'redact_partial_cc');--19c支持项
END;
6. 删除策略
--删除策略
BEGIN
DBMS_TSDP__SENSITIVE_COLUMN (
schema_name => 'TEST1',
table_name => 'TT',
column_name => 'CREDIT_CARD');
DBMS_TSDP__SENSITIVE_TYPE(
sensitive_type => 'credit_card_num_type');
DBMS_TSDP__POLICY(
policy_name => 'redact_partial_cc');
END;
创建TSDP-VPD策略（权控效果）
显示效果
本节使用Virtual Private Database技术
TEST1查看数据为脱敏效果
TEST2查看数据为正常数据
说明
TSDP可以与Oracle Virtual Private Database(VPD)进行结合，必须有DBMS_TSDP_PROTECT 和 DBMS_RLS 的执行权限，策略创建后会在数据库内部生成一个策略，可以在DBA_POLICIES系统视图中查看该策略，策略命名为“ORA$VPD_随机字符串”如ORA$VPD_6J6L3RSJSN2VAN0XF
参数列表
Parameter Description Default
function_schema
NULL
Schema of the policy function (current
default schema, if
NULL). If no
function_schema is specified, then the
current user's schema is assumed.
policy_function
NULL
Name of a function that generates a
statement_types
update_check
predicate for the policy. If the function is
defined within a package, then you must
include the name of the package (for
example,
my__function).
Statement types to which the policy applies.
NULL
It can be any combination of
INDEX,
SELECT,
INSERT,
UPDATE, or
DELETE. The default is to
apply to most of these types except
INDEX.
FALSE
Optional argument for
INSERT or
UPDATE
statement types. Setting
update_check to
TRUE sets Oracle Database to check the
policy against the value after an
INSERT or
UPDATE operation.
The check applies only to the security
relevant columns that are included in the
policy definition. In other words, the
INSERT
or
UPDATE operation will fail only if the
security relevant column that is defined in
the policy is added or updated in the
INSERT
or
UPDATE statement.
FALSE
If you set this value to
TRUE, then Oracle
Database assumes that the policy function
for the static policy produces the same
predicate string for anyone accessing the
object, except for
SYS or the privileged user
who has the
EXEMPT ACCESS POLICY
privilege.
Default is
NULL, which means
policy_type is
NULL
decided by the value of the
static_policy
parameter. Specifying any of these policy
types overrides the value of
static_policy.
FALSE
Default is
FALSE, which means the policy
function can return a predicate with a length
of up to 4000 bytes.
TRUE means the
predicate text string length can be up to 32K
bytes. Policies existing before the availability
of the
long_predicate parameter retain a
32K limit.
NULL
If you specify this parameter, then
transparent sensitive data protection inputs
the sensitive column on which the protection
is enabled to the
sec_relevant_cols
parameter of the
DBMS__POLICY
static_policy
policy_type
long_predicate
sec_relevant_cols_opt
procedure.
Allowed values are for
sec_relevant_cols_opt are as follows:
* NULL enables the filtering defined with
sec_relevant_cols to take effect.
* DBMS__ROWS displays all rows, but
with sensitive column values, which are
filtered by the
sec_relevant_cols
parameter, they display as
NULL.
配置策略
--创建敏感类型
--sensitive_type:
敏感类型名称，该名称区分大小写，
--该类型创建后可在DBA_SENSITIVE_COLUMN_TYPES视图查询
--user_comment:敏感类型备注
BEGIN
DBMS_TSDP__SENSITIVE_TYPE (
sensitive_type => 'credit_card_num_type',
user_comment => 'Type for credit card columns using a number data
type');
END;
--添加敏感类型与数据列关联
--schema_name,table_name,columm_name默认为‘%’
BEGIN
DBMS_TSDP__SENSITIVE_COLUMN(
schema_name => 'TEST1',
table_name => 'TT',
column_name => 'CREDIT_CARD',
sensitive_type => 'credit_card_num_type',
user_comment => 'Sensitive column addition of
credit_card_num_type');
END;
--删除敏感类型与数据列关联
BEGIN
DBMS_TSDP__SENSITIVE_COLUMN(
schema_name => 'TEST',
table_name => 'TT',
column_name => 'CREDIT_CARD');
END;
--创建VPD函数
CREATE OR REPLACE FUNCTION vpd_function (
v_schema IN VARCHAR2,
v_objname IN VARCHAR2)
RETURN VARCHAR2 AS
BEGIN
RETURN 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''TEST2''';
END vpd_function;
--添加策略
DECLARE
vpd_feature_options DBMS_TSDP_E_OPTIONS;
policy_conditions DBMS_TSDP__CONDITIONS;
BEGIN
vpd_feature_options ('policy_function') := 'vpd_function';
vpd_feature_options ('sec_relevant_cols_opt') := 'DBMS__ROWS';
dbms_tsdp__policy('tsdp_vpd', DBMS_TSDP_,
vpd_feature_options, policy_conditions);
END;
--策略关联敏感类型
BEGIN
DBMS_TSDP_ATE_POLICY(
policy_name => 'tsdp_vpd',
sensitive_type => 'credit_card_num_type',
associate => TRUE);
END;
--启用策略保护
BEGIN
DBMS_TSDP__PROTECTION_TYPE(
sensitive_type => 'credit_card_num_type');
END;
--禁用策略保护
BEGIN
DBMS_TSDP_E_PROTECTION_TYPE(
sensitive_type => 'credit_card_num_type');
END;
--删除策略
BEGIN
DBMS_TSDP__SENSITIVE_COLUMN (
schema_name => 'TEST1',
table_name => 'TT',
column_name => 'CREDIT_CARD');
DBMS_TSDP__SENSITIVE_TYPE(
sensitive_type => 'credit_card_num_type');
DBMS_TSDP__POLICY(
policy_name => 'tsdp_vpd');
END;
创建TSDP参数化脱敏策略
效果
数据库会自动生成REDACT_AUDIT策略，此策略可完成对敏感列进行审计且该策略会保证审计，trace文件，V$SQL_BIND_DATA内保存的语句如果涉及敏感列会将参数变成*，但经过验证，只有参数化执行才能被*代替
禁用策略效果
开启策略效果
配置策略
--创建敏感类型
--sensitive_type:
敏感类型名称，该名称区分大小写，
--该类型创建后可在DBA_SENSITIVE_COLUMN_TYPES视图查询
--user_comment:敏感类型备注
BEGIN
DBMS_TSDP__SENSITIVE_TYPE (
sensitive_type => 'cn_type',
user_comment => 'Test data type');
END;
--添加敏感类型与数据列关联
--schema_name,table_name,columm_name默认为‘%’
BEGIN
DBMS_TSDP__SENSITIVE_COLUMN(
schema_name => 'DBSEC',
table_name => 'T2',
column_name => 'CN',
sensitive_type => 'cn_type',
user_comment => 'Sensitive column addition of cn_type');
END;
--将策略与敏感类型关联
BEGIN
DBMS_TSDP_ATE_POLICY(
policy_name => 'REDACT_AUDIT',
sensitive_type => 'cn_type',
associate => TRUE);
END;
--启用策略保护
BEGIN
DBMS_TSDP__PROTECTION_TYPE(
sensitive_type => 'cn_type');
END;
--启用REDACT_AUDIT策略
begin
dbms_tsdp__PROTECTION_COLUMN(policy =>
'REDACT_AUDIT');
end;
--禁用REDACT_AUDIT策略，REDACT_AUDIT只能启用或禁用，不能修改和删除
begin
dbms_tsdp_E_PROTECTION_COLUMN(policy =>
'REDACT_AUDIT');
end;
测试语句如下
注意：
* 此参数被*覆盖功能只针对sql_bind,如果直接使用值操作，将没有效果
创建TSDP-TDECE(列加密)策略
说明
此功能将TSDP策略与TDECE功能结合，在启用此策略时需要已正确设置钱包并保持钱包为打开状态，否则可能报出如下错误
钱包相关命令参考：
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'F:Oracleadminorclwallet'
IDENTIFIED BY schina;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY schina;
ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY schina WITH
BACKUP USING 'emp_key_backup';
SELECT * FROM V$ENCRYPTION_WALLET;
参数列表
Parameter
encrypt_algorithm
Description
Available values
3DES168
AES128
AES192
AES256
ARIA128
ARIA192
ARIA256
SEED128
GOST256
Default
AES192
salt
integrity_algorithm
Available values:
SALT
NO SALT
Available values:
SHA-1
NOMAC
SALT
SHA-1
策略启用，内部将生成一个名称为“ORA$TDECE_随机字符串“的策略，
，，如ORA$TDECE_ObODOzFfBZp3a3PB0RAX4GJJbBQVvC607SLqlglH4m0ZberA7mm9pye18f5dsyZ33YFYd9jSXHSNODmyfgvc1Q6wbbajLvfJ4AbDMcAmAG5MnNhz0fxOlw，将在
DBA_TSDP_POLICY_PROTECTION系统视图中查到相关记录
select * from DBA_TSDP_POLICY_PROTECTION
设置成功以后可查询DBA_ENCRYPTED_COLUMNS字段加密信息
策略配置
--创建敏感类型
--sensitive_type:
敏感类型名称，该名称区分大小写，
--该类型创建后可在DBA_SENSITIVE_COLUMN_TYPES视图查询
--user_comment:敏感类型备注
BEGIN
DBMS_TSDP__SENSITIVE_TYPE (
sensitive_type => 'cn_type',
user_comment => 'Test data type');
END;
--添加敏感类型与数据列关联
--schema_name,table_name,columm_name默认为‘%’
BEGIN
DBMS_TSDP__SENSITIVE_COLUMN(
schema_name => 'DBSEC',
table_name => 'T2',
column_name => 'CN',
sensitive_type => 'cn_type',
user_comment => 'Sensitive column addition of cn_type');
END;
--添加策略
DECLARE
redact_feature_options DBMS_TSDP_E_OPTIONS;
policy_conditions DBMS_TSDP__CONDITIONS;
BEGIN
redact_feature_options ('encrypt_algorithm') := 'AES128';
redact_feature_options ('salt') := 'NO SALT';
redact_feature_options ('integrity_algorithm') := 'SHA-1';
DBMS_TSDP__POLICY (
'tde_CN', DBMS_TSDP__ENCRYPTION,
redact_feature_options,policy_conditions);
END;
--策略关联敏感类型
BEGIN
DBMS_TSDP_ATE_POLICY(
policy_name => 'tde_CN',
sensitive_type => 'cn_type',
associate => TRUE);
END;
--启用策略保护
BEGIN
DBMS_TSDP__PROTECTION_TYPE(
sensitive_type => 'cn_type');
END;
--禁用策略保护
BEGIN
DBMS_TSDP_E_PROTECTION_TYPE(
sensitive_type => 'cn_type');
END;
--删除策略
BEGIN
DBMS_TSDP__SENSITIVE_COLUMN (
schema_name => 'DBSEC',
table_name => 'T2',
column_name => 'CN');
DBMS_TSDP__SENSITIVE_TYPE(
sensitive_type => 'cn_type');
DBMS_TSDP__POLICY(policy_name => 'tde_CN');
END;
创建TSDP-Unified Auditing(统一审计)策略
审计效果
说明
通过此功能可以实现对象操作统一审计的设置，指定特定参数项可实现CREATE AUDIT
POLICY, ALTER AUDIT POLICY, AUDIT POLICY, 和 COMMENT SQL功能，通常是针对数据表的操作，如INSERT,SELECT,UPDATE,DELETE等Standard操作，不支持审计如创建Oracle Label Security策略或其数据库功能
参数列表如下
Parameter Description Default
ACTION_AUDIT_OPTIONS
ALL
A string containing a
comma-separated list of SQL
actions.
Valid actions are:
ALTER,
AUDIT,
COMMENT,
DELETE,
FLASHBACK,
GRANT,
INDEX,
INSERT,
LOCK,
RENAME,
SELECT,
UPDATE
To configure the policy to audit
all of these actions, specify the
keyword
ALL.
NULL
SYS_CONTEXT (namespace,
attribute)
operation
value-list
In this syntax, operation can be
any of the following operators:
IN,|
NOT IN,
=,
<,
>, or
<>
If the audit condition contains a
single quotation mark, then
AUDIT_CONDITION
specify two single quotation
marks instead of one, and
enclose the
SYS_CONTEXT in
single quotations. For example:
'SYS_CONTEXT(''USERENV'',
''CLIENT_IDENTIFIER'') =
''myclient'''
Can be one of the following:
STATEMENT
SESSION
INSTANCE
A string that contains a
comma-separated list of users or
roles. If you omit this parameter,
then the audit policy is enabled
for all users.
Applies only if the
ENTITY_NAME parameter is used.
It specifies if the
ENTITY_NAME is
a
BY user list, an
EXCEPT user list,
or a
BY USERS WITH GRANTED
ROLES role list. Valid settings are:
BY
EXCEPT
BY USERS WITH GRANTED ROLES
UNIFIED_AUDIT_POLICY_COMMENT
EVALUATE_PER
STATEMENT
ENTITY_NAME
NULL (that
is, all
database
users)
BY
ENABLE_OPTION
A string that describes the
unified audit policy that will be
created
NULL
策略名称可在AUDIT_UNIFIED_POLICIES、AUDIT_UNIFIED_ENABLED_POLICIES获取到，命名格式为“ORA$UNIFIED_AUDIT_ 随机字符串”如ORA$UNIFIED_AUDIT_6J6L3RSJSN2VAN0XF，
审计记录可在UNIFIED_AUDIT_TRAIL中查询,如
SELECT
AUDIT_TYPE ,SESSIONID ,PROXY_SESSIONID,OS_USERNAME ,USERHOST ,
TERMINAL,INSTANCE_ID ,DBID ,AUTHENTICATION_TYPE ,DBUSERNAME,
CLIENT_PROGRAM_NAME,ENTRY_ID ,STATEMENT_ID,EVENT_TIMESTAMP,
ACTION_NAME,RETURN_CODE,OS_PROCESS,TRANSACTION_ID,SCN ,
EXECUTION_ID ,OBJECT_SCHEMA ,OBJECT_NAME ,SQL_TEXT,CURRENT_USER,
UNIFIED_AUDIT_POLICIES
FROM UNIFIED_AUDIT_TRAIL t ORDER BY event_timestamp DESC
配置策略
--创建敏感类型
--sensitive_type:
敏感类型名称，该名称区分大小写，
--该类型创建后可在DBA_SENSITIVE_COLUMN_TYPES视图查询
--user_comment:敏感类型备注
BEGIN
DBMS_TSDP__SENSITIVE_TYPE (
sensitive_type => 'cn_type',
user_comment => 'Test data type');
--添加敏感类型与数据列关联
--schema_name,table_name,columm_name默认为‘%’
DBMS_TSDP__SENSITIVE_COLUMN(
schema_name => 'DBSEC',
table_name => 'T2',
column_name => 'CN',
sensitive_type => 'cn_type',
user_comment => 'Sensitive column addition of cn_type');
END;
--添加策略
DECLARE
audit_feature_options DBMS_TSDP_E_OPTIONS;
BEGIN
audit_feature_options ('ACTION_AUDIT_OPTIONS') := 'ALL';
--audit_feature_options ('AUDIT_CONDITION') :=
'SYS_CONTEXT(''''USERENV'''', ''''SESSION_USER'''') IN
(''''DBSEC'''')';
--使用如上SYS_CONTEXT时，需要转义单引号，否则触发Oracle bug，详见附件一
audit_feature_options ('EVALUATE_PER') := 'STATEMENT';
audit_feature_options ('ENTITY_NAME') := 'DBSEC';
audit_feature_options ('ENABLE_OPTION') := 'BY';
dbms_tsdp__policy (
policy_name => 'TSDP_AUDIT_POLICY',
security_feature => DBMS_TSDP_D_AUDIT,
policy_enable_options => audit_feature_options);
END;
--策略关联敏感类型
BEGIN
DBMS_TSDP_ATE_POLICY(
policy_name => 'TSDP_AUDIT_POLICY',
sensitive_type => 'cn_type',
associate => TRUE);
END;
--启用该类型涉及的所有列的策略保护
BEGIN
DBMS_TSDP__PROTECTION_TYPE(
sensitive_type => 'cn_type');
END;
--或者只启用某一列
BEGIN
DBMS_TSDP__PROTECTION_COLUMN(
schema_name => 'DBSEC',
table_name => 'T2',
column_name => 'CN');
END;
--禁用策略保护
BEGIN
DBMS_TSDP_E_PROTECTION_TYPE(
sensitive_type => 'cn_type');
END;
--删除策略
BEGIN
DBMS_TSDP__SENSITIVE_COLUMN (
schema_name => 'DBSEC',
table_name => 'T2',
column_name => 'CN');
DBMS_TSDP__SENSITIVE_TYPE(
sensitive_type => 'cn_type');
DBMS_TSDP__POLICY(policy_name => 'TSDP_AUDIT_POLICY');
END;
可能错误
在尝试中可能遇到以下错误
PLS-00302&PLS-00201:必须声明‘PARENT_SCHEMA’组件
问题描述
根据官方文档如果为“policy_apply_condition”参数添加“DBMS_TSDP_PROPERTY”属性如“DBMS_TSDP__SCHEMA”会报错如下
原因是官方文档中描述与DBMS_TSDP_PROTECT系统包定义不一致，属性名称应为
DBMS_TSDP__NAME
DBMS_TSDP__TABLE应为DBMS_TSDP__NAME
ORA-45618：未能对一个或多个列强制执行策略
问题描述
在调用DBMS_TSDP__PROTECTION_TYPE启用策略时则可能报：ORA-45618：未能对一个或多个列强制执行策略
问题原因
可能是因为当前设置策略的数据库用户没有AUDIT SYSTEM权限
解决方法
授予该权限GRANT AUDIT SYSTEM TO USER1;
ORA-00905: missing keyword during CREATE AUDIT POLICY
问题现象：
在调用DBMS_TSDP__PROTECTION_TYPE启用策略时则可能报：
ORA-00905: missing keyword during CREATE AUDIT POLICY
问题原因：
触发Oracle
BUG:27326938 - TSDP: AUDIT_FEATURE_OPTIONS NEEDS EXTRA SINGLE
QUOTES
解决方法：
参见附件一
创建TSDP-FGA审计策略
说明
在创建策略(DBMS_TSDP__POLICY)时，报错如下,怀疑是 Oracle bug
参考
/en/database/oracle/oracle-database/19/dbseg/#GUID-2C55E6BF-8FD9-4537-B649-88AB788002A5
/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9533552
BUG:27326938 - TSDP: AUDIT_FEATURE_OPTIONS NEEDS EXTRA SINGLE QUOTES
/knowledge/Oracle%20Database%20Products/2344126_
附件一
BUG:27326938 - TSDP: AUDIT_FEATURE_OPTIONS NEEDS EXTRA SINGLE QUOTES
DBMS_TSDP__PROTECTION_TYPE fails with
"ORA-45618: policy enforcement failure on one or more
columns" due to incorrect AUDIT_CONDITION (Doc ID
2344126.1)
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
NOTE: In the images and/or the document content below, the user
information and data used represents fictitious data from the Oracle
sample schema(s) or Public Documentation delivered with an Oracle
database product. Any similarity to actual persons, living or dead, is
purely coincidental and not intended in any manner.
When using Transparent Sensitive Data Protection with Unified Auditing,
DBMS_TSDP__PROTECTION_TYPE may fail with:
ORA-45618: policy enforcement failure on one or more columns
Tracing the error reveals:
ORA-00905: missing keyword during CREATE AUDIT POLICY
Cause
There are not enough single quotes being used when initializing AUDIT_CONDITION
in the AUDIT_FEATURE_OPTIONS array.
When populating this array, one uses syntax like the following example:
declare
audit_feature_options DBMS_TSDP_E_OPTIONS;
begin
audit_feature_options ('ACTION_AUDIT_OPTIONS') := 'ALL';
audit_feature_options ('AUDIT_CONDITION') :=
'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') IN
(''HR'',''TEST_SCHEMA'')';
audit_feature_options ('EVALUATE_PER') := 'STATEMENT';
dbms_tsdp__policy (
policy_name => 'TSDP_AUDIT_POLICY',
security_feature => DBMS_TSDP_D_AUDIT,
policy_enable_options => audit_feature_options,
end;
This causes the following value to be stored in AUDIT_CONDITION for use with the
CREATE AUDIT POLICY command:
SYS_CONTEXT('USERENV', 'SESSION_USER') IN ('HR','TEST_SCHEMA')
According to the documentation for CREATE AUDIT POLICY: "If the audit_condition
contains a single quotation mark, then specify two single quotation marks
instead." Because the values in AUDIT_CONDITION within the call to SYS_CONTEXT
are stored with only single quotation mark, the CREATE AUDIT POLICY command
then becomes (for example):
CREATE AUDIT POLICY ... ACTIONS ALL ON ... WHEN
'SYS_CONTEXT('USERENV', 'SESSION_USER') IN ('HR','TEST_SCHEMA')'
instead of:
CREATE AUDIT POLICY ... ACTIONS ALL ON ... WHEN
'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') IN
(''HR'',''TEST_SCHEMA'')'
As a result, the CREATE AUDIT POLICY command throws ORA-00905, which leads to
ORA-45618 when executing DBMS_TSDP__PROTECTION_TYPE.
Solution
When populating AUDIT_FEATURE_OPTIONS, escape each of the single quotes
within the call to SYS_CONTEXT an additional time. For example:
declare
audit_feature_options DBMS_TSDP_E_OPTIONS;
begin
audit_feature_options ('ACTION_AUDIT_OPTIONS') := 'ALL';
audit_feature_options ('AUDIT_CONDITION') :=
'SYS_CONTEXT(''''USERENV'''', ''''SESSION_USER'''') IN
(''''HR'''',''''TEST_SCHEMA'''')';
audit_feature_options ('EVALUATE_PER') := 'STATEMENT';
dbms_tsdp__policy (
policy_name => 'TSDP_AUDIT_POLICY',
security_feature => DBMS_TSDP_D_AUDIT,
policy_enable_options => audit_feature_options,
end;
This will cause the audit policy to be created correctly.
References
BUG:27326938 - TSDP: AUDIT_FEATURE_OPTIONS NEEDS EXTRA SINGLE QUOTES
版权声明：本文标题：使用ORACLE透明敏感数据保护(TSDP) 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.freenas.com.cn/free/1703406719h449952.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论