Entender a definição de Dynamic vs. Static data masking é muito bom para entender o funcionamento do Redaction, o site do link apresenta uma otima explicação dos dois conceitos;
Para Dynamic Maskig uma solução proposta da Oracle é o Oracle Redaction
Para Static Masking uma solução proposta é o Oracle Data Masking
Restrições
- Somente é possivel ter uma politica ativa por tabela
- Somente é possivel criar uma regra pra uma coluna, posteriormente é possivel adicionar um nova regra na tabela alterando a politica
- CTS (Create Table as select ), gera erro ORA-28081
- Colunas Virtual não aceitam Redact
Exceções
Os seguintes contextos não se aplicam as regras de mascaramento
- Role sysdba
- Role DBA
- Rman Backup
- Export/Import
- EXEMPT REDACTION POLICY Privilege
- Replicação
- Upgrade
Package
DBMS_READACT
ADD_POLICY
ALTER_POLICY
ADD_COLUMN
DROP_COLUMN
MODIFY_EXPRESSION
MODIFY_COLUMN
SET_POLICY_DESCRIPTION
SET_COLUMN_DESCRIPTION
UPDATE_FULL_READACTION_VALUES
FUNCTION TYPE
NONE
FULL
PARTIAL
RANDON
REGEXP
Views
- REDACTION_POLICIES
- REDACTION_COLUMNS
- REDACTION_VALUES_FOR_TYPE_FULL
Privilegios
- EXEC DBMS_READACT
- EXEMPT REDACTION POLICY
Exemplo
Adicionando Regra:
FULL
BEGIN
DBMS_REDACT.ADD_POLICY
(POLICY_NAME => 'EMPSAL_POLICY',
OBJECT_SCHEMA => 'HR',
OBJECT_NAME => 'EMPLOYEES',
COLUMN_NAME => 'SALARY',
EXPRESSION =>
'SYS_CONTEXT(''USERENV'',''SESSION_USER'')!=''HR-VP''',
FUNCTION_TYPE => DBMS_REDACT.FULL);
end;
/
PARTIAL
BEGIN
DBMS_REDACT.ADD_POLICY
(POLICY_NAME => 'EMPNAME_POLICY',
OBJECT_SCHEMA => 'HR',
OBJECT_NAME => 'EMPLOYEES',
COLUMN_NAME => 'FIRST_NAME',
FUNCTION_TYPE => DBMS_REDACT.PARTIAL,
FUNCTION_PARAMETERS => 'VVVVVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVVVVV,$,3,10',
EXPRESSION =>
'SYS_CONTEXT(''USERENV'',''SESSION_USER'')!=''HR-VP'''
);
end;
/
RANDON
BEGIN
DBMS_REDACT.ADD_POLICY
(POLICY_NAME => 'EMPPHONE_POLICY',
OBJECT_SCHEMA => 'HR',
OBJECT_NAME => 'EMPLOYEES',
COLUMN_NAME => 'PHONE_NUMBER',
FUNCTION_TYPE => DBMS_REDACT.RANDOM,
EXPRESSION => '1=1'
);
end;
/
Alterando Politica
BEGIN
DBMS_REDACT.alter_policy (
POLICY_NAME => 'EMPPHONE_POLICY',
OBJECT_SCHEMA => 'HR',
OBJECT_NAME => 'EMPLOYEES',
ACTION => DBMS_REDACT.ADD_COLUMN,
COLUMN_NAME => 'SALARY',
FUNCTION_TYPE => DBMS_REDACT.PARTIAL,
FUNCTION_PARAMETERS => '1,1,3'
);
END;
/
BEGIN
DBMS_REDACT.alter_policy (
POLICY_NAME => 'EMPPHONE_POLICY',
OBJECT_SCHEMA => 'HR',
OBJECT_NAME => 'EMPLOYEES',
ACTION => DBMS_REDACT.MODIFY_COLUMN,
COLUMN_NAME => 'SALARY',
FUNCTION_TYPE => DBMS_REDACT.PARTIAL,
FUNCTION_PARAMETERS => '0,1,5'
);
END;
/
BEGIN
DBMS_REDACT.alter_policy (
POLICY_NAME => 'EMPPHONE_POLICY',
OBJECT_SCHEMA => 'HR',
OBJECT_NAME => 'EMPLOYEES',
ACTION => DBMS_REDACT.MODIFY_EXPRESSION,
EXPRESSION =>
'SYS_CONTEXT(''USERENV'',''SESSION_USER'')!=''HR-VP'''
);
END;
/
DROP
BEGIN
DBMS_REDACT.drop_policy (
policy_name => 'EMPSAL_POLICY',
object_schema => 'HR',
object_name => 'EMPLOYEES'
);
END;
/
Referencias:
https://www.realdbamagic.com/oracle-data-redaction/
https://oracle-base.com/articles/12c/data-redaction-12cr1
http://docs.oracle.com/database/121/ARPLS/d_redact.htm#ARPLS73813
http://www.oracle.com/technetwork/pt/articles/idm/funcionalidade-data-redaction-12c-2209076-ptb.html