Data masking with APEX
Background¶
A common customer requirement is to mask sensitive or personally identifiable data from APEX reports.
Oracle has a 'Data Masking and Subsetting' product that performs this task.
However, for smaller APEX projects, the full blown data masking product might be overkill as it needs familiarity with the product and configuration. This may be time consuming and expensive.
However, we are able to use the PL/SQL package DBMS_REDACT to achieve the same result.
Test Environment¶
- APEX 22.1.5 on-premise (September 2022)
- APEX 22.1.4 (AlwaysFree) (September 2022)
This functionality is available on on-premise APEX environments and the AlwaysFree Oracle Cloud environment.
The data masking functionality is not available on apex.oracle.com as it requires access to the DBMS_REDACT package which is owned by 'SYS'.
User accounts¶
Create two APEX users called MANAGER and INTERN.
Sample data¶
Create an employee table with a couple of sensitive fields for the test scenario.
create table gdpr_emp
(id number generated by default on null as identity,
first_name varchar(30),
last_name varchar(30),
ni_number varchar(10),
salary number,
email_address varchar(50),
date_of_birth date,
country varchar(30),
credit_card varchar(20)
);
Insert a sample record.
insert into gdpr_emp
(first_name,
last_name,
ni_number,
salary,
email_address,
date_of_birth,
country,
credit_card)
values
('Norman',
'Whiteside',
'NA564635I',
35275,
'norman@gmail.com',
to_date('01-JAN-1970', 'DD-MON-YYYY'),
'UK',
'4321123467899876')
;
APEX application¶
Create a APEX application named 'GDPR_DEMO'.
Create a page named 'Employees' with an interactive report based on the 'GDPR_EMP' table.
Run the GDPR_DEMO application and login as 'MANAGER' and 'INTERN'. All fields should be visible on the 'Employees' page.
Create the redaction policy¶
The APEX schema needs privileges to access the DBMS_REDACT package. Login as SYS and grant the privileges.
grant execute on sys.dbms_redact to <APEXDEMO>;
Create the redaction policy. The expression parameter defines which user accounts do not have access to the actual values of the redacted columns.
According to this redaction policy, if the user account is 'INTERN', the CREDIT_CARD column value should be redacted. This means the 'INTERN' user will see zeros in place of the actual values in this column.
begin
dbms_redact.add_policy(
object_schema => 'APEXDEMO',
object_name => 'GDPR_EMP',
policy_name => 'GDPR Demo',
expression => 'v(''APP_USER'') = ''INTERN''',
column_name => 'CREDIT_CARD',
function_type => dbms_redact.full
);
end;
/
Add the EMAIL_ADDRESS and SALARY columns by modifying the existing redaction policy.
begin
dbms_redact.alter_policy(
object_schema => 'APEXDEMO',
object_name => 'GDPR_EMP',
policy_name => 'GDPR Demo',
action => dbms_redact.add_column,
column_name => 'EMAIL_ADDRESS',
function_type => dbms_redact.full
);
dbms_redact.alter_policy(
object_schema => 'APEXDEMO',
object_name => 'GDPR_EMP',
policy_name => 'GDPR Demo',
action => dbms_redact.add_column,
column_name => 'SALARY',
function_type => dbms_redact.full
);
end;
/
You can query the current redaction configuration by querying the REDACTION_COLUMNS, REDACTION_POLICIES and REDACTION_VALUES_FOR_TYPE_FULL views (as 'SYS').
Testing¶
Now login to the GDPR_DEMO application as 'MANAGER'. You will see the full, unredacted data as normal.
Now login as 'INTERN'. You will note that the email address and credit fields are redacted (spaces are displayed while the 'Salary' field is displayed as 0 (zero).
Improvements¶
This is a simple example of data redaction.
Other possible solutions would be to use APEX authorisation schemes to completely hide the sensitive columns from the 'INTERN' user.
This has the advantage of preventing user confusion where the user can see a sensitive field but not the actual value which may be mistaken as a bug.
There could also be finer levels of granularity:
- SuperUser
- HR Manager
- Employee
- Contractor
- Intern
Cleanup¶
To remove the redaction policy
begin
dbms_redact.drop_policy (
object_schema => 'APEXDEMO',
object_name => 'GDPR_EMP',
policy_name => 'GDPR Demo');
end;
/
