Many Oracle Database customers currently prevent the display of sensitive data to end users by performing redacting in each application. Oracle Data Redaction moves this functionality from the application to the database. This approach has several benefits over redacting data in the application tier, and it is useful in a variety of application scenarios.
Oracle Data Redaction is a transparent, flexible, and simple solution. It modifies sensitive data columns contained in SQL query results on-the-fly right before the results are returned to applications. The columns are redacted according to flexible policies that provide conditional redaction. The policies are managed directly within the database. For maximum transparency, redaction preserves the returned column data type and formatting, and it does not alter the underlying data blocks on disk or in cache. Oracle Data Redaction is designed to be fast so that it can be used on production systems. In addition, it is embedded in the database management system, so no separate installation is required.
- On-the-fly redaction based on username, IP address, application context, and other factors
- Transparent, consistent enforcement in the database
- High performance for production applications
- Appropriate for call centers, decision support systems, and systems with PII, PHI, and PCI data
- This is example Oracle Data Redaction to redact values of shielded columns of the HR.EMPLOYEES table.
SQL> CREATE USER atgwork IDENTIFIED BY ATGWORK;
SQL> GRANT create session TO atgwork;
SQL> GRANT select ON hr.employees TO atgwork;
- Execute the following query to see salary for each employee.
SQL> SELECT employee_id, last_name, salary, commission_pct FROM hr.employees WHERE department_id = 500;
- Define a redaction policy for the HR.EMPLOYEES table specifying full redacting for the SALARY column. SALARY is defined as NUMBER(8,2). In this example, by setting EXPRESSION to 1=1, redaction is always performed because the expression always evaluates to true. The policy is enabled by default.
(object_schema => ‘HR’,
object_name => ‘EMPLOYEES’,
policy_name => ‘EMP_POLICY’,
column_name => ‘SALARY’,
function_type => DBMS_REDACT.FULL,
expression => ‘1=1’);
- Query REDACTION_POLICIES to verify that the policy has been created and is enabled. This view also shows under what condition the redaction will be performed as shown in the EXPRESSION column.
SQL> SELECT * FROM redaction_policies;
- Display which columns will be redacted and what type of redaction will take place.
SQL> SELECT object_owner, object_name, column_name, function_type, function_parameters FROM redaction_columns;
- Now query the HR.EMPLOYEES table again and note that the value of the SALARY column is 0 for all displayed rows.
First grant the SELECT privilege to user abc.
SQL> GRANT select ON hr.employees TO abc;
Connect as user abc
SQL> CONNECT abc/abc
- SQL> SELECT employee_id, last_name, salary, commission_pct FROM hr.employees WHERE department_id = 500;