5.2 Redaction Management in Oracle SQL Developer

«« Previous
Next »»

1. Overview


Purpose

This tutorial shows you how to manage redaction policies in SQL Developer 4.0.

Time to Complete

Approximately 1 hour

Introduction

In this tutorial, you learn how to manage redaction in Oracle Database 12c or 11g Release 2 Patchset 3 (11.2.0.4) using SQL Developer. Topics include:
  • Creating  a redaction policy
  • Altering a redaction policy
  • Adding a Column to a redaction policy
  • Modifying an Expression in a redaction policy
  • Disabling a redaction policy
  • Dropping a redaction policy
Prerequisites

Before starting this tutorial, you should:
  • Understand Oracle Database concepts. More information is available on the Oracle Technology Network.
  • Have a previously created Oracle database with a table containing a field suitable for redaction (for example, an Employees table).

2. Creating a Redaction Policy


To create a redaction policy, perform the following steps:

1. Open Oracle SQL Developer by clicking the SQL Developer desktop icon.

Redaction Management in Oracle SQL Developer

The Oracle SQL Developer window is displayed.

Redaction Management in Oracle SQL Developer

2. On the Connections tab, right-click Connections and select New Connection.

Redaction Management in Oracle SQL Developer

The New / Select Database Connection dialog box is displayed.

Redaction Management in Oracle SQL Developer

3. Enter parameters for the new connection. In this example, the entries are:

Name: sys
Username: sys
Password: Enter the password for your system.
Save Password: Select this check box
Connection Type: Basic
Role: SYSDBA
Hostname: localhost
Port: 1521
SID: Enter SID or Service Name

4. Click Test and confirm a status of "Success."

Redaction Management in Oracle SQL Developer

5. Click Save and then close the New / Select Database Connection dialog box.

6. Right-click connection sys and select Open SQL Worksheet.

Redaction Management in Oracle SQL Developer

The sys worksheet is opened.

Redaction Management in Oracle SQL Developer

7. To create the redaction user, enter and execute the following statement in the worksheet:

create user redact_user identified by oracle;

Redaction Management in Oracle SQL Developer

User REDACT_USER is created.

Redaction Management in Oracle SQL Developer

8. Grant appropriate privilege to redact_user by entering and executing the following statements:

grant connect, resource, unlimited tablespace to redact_user;
grant select on Sys.redaction_policies to redact_user;
grant select on Sys.redaction_columns to redact_user;
grant execute on dbms_redact to redact_user;

Redaction Management in Oracle SQL Developer

9. Create a connection for redact_user.

Redaction Management in Oracle SQL Developer

Provide the following connection entries:

Name: redact_user
Username: redact_user
Password: Enter the password for your system.
Save Password: Select this check box
Connection Type: Basic
Role: default
Hostname: localhost
Port: 1521
SID: Enter SID or Service Name

Redaction Management in Oracle SQL Developer

10. Test and Save the connection. Then close the New/Select Database Connection dialog box.

Redaction Management in Oracle SQL Developer

Redaction Management in Oracle SQL Developer

11. Right-click connection redact_user and select Open SQL Worksheet.

Redaction Management in Oracle SQL Developer

The redact_user worksheet is opened.

Redaction Management in Oracle SQL Developer

12. Close the sys worksheet without saving changes.

Redaction Management in Oracle SQL Developer

13. Create the EMPLOYEES table and populate it with sample data by entering the following statements into the redact_user SQL worksheet:

CREATE TABLE "EMPLOYEES" ("EMPLOYEE_ID" NUMBER(6,0), "FIRST_NAME" VARCHAR2(20), "LAST_NAME" VARCHAR2(25), "SOCIAL_SECURITY" VARCHAR2(11), "SALARY" NUMBER(4,0))
/
REM INSERTING into EMPLOYEES
SET DEFINE OFF;
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SOCIAL_SECURITY,SALARY) values (100,'Steven','King','247-85-9056',7000);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SOCIAL_SECURITY,SALARY) values (101,'Neena','Kochhar','334-08-6578',5000);

Redaction Management in Oracle SQL Developer

14. In the SQL worksheet menu, click Run script.

Redaction Management in Oracle SQL Developer

Table EMPLOYEES is created.

Redaction Management in Oracle SQL Developer

15. Close the redact_user SQL worksheet without saving changes.

Redaction Management in Oracle SQL Developer

16. In the Connections panel, expand node redact_user and then expand Tables.

Redaction Management in Oracle SQL Developer

17. Double-click the EMPLOYEES node

Redaction Management in Oracle SQL Developer

SQL worksheet EMPLOYEES is displayed.

Redaction Management in Oracle SQL Developer

18. In the EMPLOYEES SQL worksheet, click the Data tab.

Redaction Management in Oracle SQL Developer

The Data tab is displayed. Since the Social Security Number (SSN) is sensitive data, you can apply a redaction policy to mask the data.

Redaction Management in Oracle SQL Developer

19. In the navigation tree, right-click table EMPLOYEES and select Redaction > Add Redaction Policy.

Redaction Management in Oracle SQL Developer

The Create Redaction dialog box is displayed.

Redaction Management in Oracle SQL Developer

20. Enter (or select) the following parameters for the new redaction and then click Apply:

Policy Name: Redaction
Policy Description: SSN redaction (optional)
Column Name: SOCIAL_SECURITY (select)
Function Type: Full
Redaction Management in Oracle SQL Developer

21. When the success message is displayed, click OK.

Redaction Management in Oracle SQL Developer

22. On the Data tab, click Refresh.

Redaction Management in Oracle SQL Developer

The entire SSN is masked in the worksheet.

Redaction Management in Oracle SQL Developer

23. Optional:. In the connections navigator, collapse the EMPLOYEES table.

3. Altering a Redaction Policy


You can alter the redaction policy to apply a partial redaction to the SOCIAL_SECURITY column. Perform the following steps to replace the first five numbers with an asterisk (*) while preserving the hyphens (-) between the numbers:

1. In the Connections tree, right-click EMPLOYEES and select Redaction > Alter Redaction Policy.

Redaction Management in Oracle SQL Developer

The Alter Redaction dialog box is displayed.

Redaction Management in Oracle SQL Developer

2. In the Action list, select Modify Column.

Redaction Management in Oracle SQL Developer

3. For Function Type, select Partial.

Redaction Management in Oracle SQL Developer

4. Confirm that Column Name is SOCIAL_SECURITY.

Redaction Management in Oracle SQL Developer

5. In the Function Parameters text box, make the following entry to format for a character data type and then click Apply:

VVVFVVFVVVV,VVV-VV-VVVV,*,1,5

Redaction Management in Oracle SQL Developer

6. When the success message is displayed, click OK.

Redaction Management in Oracle SQL Developer

7. On the Data tab, click Refresh.

Redaction Management in Oracle SQL Developer

In the SOCIAL_SECURITY column, the first five numbers of the SSNs are replaced with asterisks (*) and the hyphenation is preserved.

Redaction Management in Oracle SQL Developer

Note: If SOCIAL_SECURITY is a NUMBER data type, use numeric function parameters. For example, function parameters 5,1,5 modify SSN 788910026 to display as 555550026.

4. Adding a Column to a Redaction Policy


You can apply different masking types to different columns. For example, to display random numbers in the SALARY column, first add the SALARY column to the redaction policy.

1. Right-click EMPLOYEES and select Redaction > Alter Redaction Policy.

Redaction Management in Oracle SQL Developer

2. In the Alter Redaction dialog box, select Add Column from the Action list.

Redaction Management in Oracle SQL Developer

3. In the Column Name drop-down list, select SALARY.

Redaction Management in Oracle SQL Developer

4. In the Function Type list, select Random.

Redaction Management in Oracle SQL Developer

5. Click Apply. When the success message is displayed, click OK.

Redaction Management in Oracle SQL Developer

6. In the Data tab, click Refresh.

Redaction Management in Oracle SQL Developer

The SALARY column is now redacted as a random number.

Redaction Management in Oracle SQL Developer


5. Modifying an Expression in a Redaction Policy


You can control who is exempt from the redaction policy. To set the Human Resources (HR) session user as exempt, perform the following tasks:

1. Right-click EMPLOYEES and select Redaction > Alter Redaction Policy.

Redaction Management in Oracle SQL Developer

2. In the Alter Redaction dialog box, select Modify Expression from the Action list.

Redaction Management in Oracle SQL Developer

3. In the Column Name list, select SOCIAL_SECURITY.

Redaction Management in Oracle SQL Developer

4. Click the pencil icon to build an expression.  

Redaction Management in Oracle SQL Developer

The Policy Expression Builder dialog box is displayed.

Redaction Management in Oracle SQL Developer

5. Select Assisted for the expression.

Redaction Management in Oracle SQL Developer

6. Additional selections are displayed. In the User Environment list, select Session User.

Redaction Management in Oracle SQL Developer

7. In the Condition list, select is.

Redaction Management in Oracle SQL Developer

8. In the Another User text box enter HR and click Apply.

Redaction Management in Oracle SQL Developer

9. In the Alter Redaction dialog box, click Apply.

Redaction Management in Oracle SQL Developer

10. When the success message is displayed, click OK.

Redaction Management in Oracle SQL Developer

An HR user who views the EMPLOYEES table will be able to see the Social Security Number.

Keep the EMPLOYEES Data tab open for the next section.

6. Disabling a Redaction Policy


To disable a redaction policy, perform the following tasks:

1. Right-click EMPLOYEES and select Redaction > Enable/Disable Redaction Policy.

Redaction Management in Oracle SQL Developer

The Enable/Disable Redaction dialog box is displayed.

Redaction Management in Oracle SQL Developer

2. Deselect Enabled and then click Apply.

Redaction Management in Oracle SQL Developer

3. When the success message is displayed, click OK.

4. On the Data tab, click Refresh.  

Redaction Management in Oracle SQL Developer

The Redaction policy is disabled, and the data is now unmasked.

Redaction Management in Oracle SQL Developer

7. Dropping a Redaction Policy


To drop a redaction policy, perform the following tasks:

1. Right-click EMPLOYEES and select Redaction > Drop Redaction Policy.

Redaction Management in Oracle SQL Developer

The Drop Redaction dialog box is displayed.

Redaction Management in Oracle SQL Developer

2. Confirm the redaction properties and then click Apply. You can drop both enabled and disabled redaction policies.

Redaction Management in Oracle SQL Developer

3. When the success message is displayed, click OK.

«« Previous
Next »»