https://docs.oracle.com/cd/E82638_01/asoag/configuring-oracle-data-redaction-policies.html
git clone https://github.com/oracle-samples/db-sample-schemas.git
cd db-sample-schemas
sqlplus test/test@pdb11
@./customer_orders/co_install.sql
@./human_resources/hr_install.sql
@./sales_history/sh_install.sql
cd sales_history
vi sh_populate.sql
下記 LOAD文の後にcommit; 追加
LOAD costs costs.csv
LOAD customers customers.csv
LOAD promotions promotions.csv
LOAD sales sales.csv
LOAD times times.csv
LOAD supplementary_demographics supplementary_demographics.csv
bash /u01/app/oracle/product/19.0.0/dbhome_1/sqldeveloper/sqldeveloper/bin/sql sh/sh@pdb11
alter session set nls_date_format='YYYY-MM-DD';
truncate table CHANNELS;
truncate table COUNTRIES;
truncate table PRODUCTS;
@sh_populate.sql
select count(*) from sales;
select count(*) from times;
select count(*) from customers;
select count(*) from channels;
sqlplus test/test@pdb11
select * from REDACTION_POLICIES;
select * from REDACTION_COLUMNS;
select * from REDACTION_EXPRESSIONS;
select * from REDACTION_VALUES_FOR_TYPE_FULL;
完全リダクション
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'sh',
object_name => 'SALES',
column_name => 'TIME_ID',
policy_name => 'reduct_pol_1',
function_type => DBMS_REDACT.FULL,
expression => '1=1');
END;
/
select * from sh.sales where rownum < 10;
BEGIN
DBMS_REDACT.DROP_POLICY (
object_schema => 'sh',
object_name => 'SALES',
policy_name => 'reduct_pol_1');
END;
/
部分リダクション
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'sh',
object_name => 'SALES',
column_name => 'AMOUNT_SOLD',
policy_name => 'reduct_pol_2',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => '7,1,5',
expression => '1=1',
policy_description => 'Partially redacts Social Security numbers',
column_description => 'ssn contains numeric Social Security numbers');
END;
/
select * from sh.sales where rownum < 10;
BEGIN
DBMS_REDACT.DROP_POLICY (
object_schema => 'sh',
object_name => 'SALES',
policy_name => 'reduct_pol_2');
END;
/
正規表現リダクション
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'sh',
object_name => 'COUNTRIES',
column_name => 'COUNTRY_SUBREGION',
policy_name => 'reduct_pol_3',
function_type => DBMS_REDACT.REGEXP,
function_parameters => NULL,
expression => '1=1',
regexp_pattern => '*North*',
regexp_replace_string => 'South',
policy_description => 'Regular expressions to redact credit card numbers',
column_description => 'cc_num contains customer credit card numbers');
END;
/
select * from sh.COUNTRIES where rownum < 2;
BEGIN
DBMS_REDACT.DROP_POLICY (
object_schema => 'sh',
object_name => 'COUNTRIES',
policy_name => 'reduct_pol_3');
END;
/
ランダムリダクション
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'sh',
object_name => 'COUNTRIES',
column_name => 'COUNTRY_SUBREGION',
policy_name => 'reduct_pol_4',
function_type => DBMS_REDACT.RANDOM,
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''SH''');
END;
/
select * from sh.COUNTRIES where rownum < 2;
BEGIN
DBMS_REDACT.DROP_POLICY (
object_schema => 'sh',
object_name => 'COUNTRIES',
policy_name => 'reduct_pol_4');
END;
/
