7 Limiting Access to Sensitive Data Using Oracle Data Redaction

Oracle Data Redaction limits access to sensitive data by redacting this data in real time.

Topics:

About Oracle Data Redaction

Oracle Data Redaction enables you to redact (mask) column data.

You can redact data using one of the following methods:

Full redaction. You redact all the contents of the column data. The redacted value returned to the querying user depends on the data type of the column. For example, columns of the NUMBER data type are redacted with a zero (0) and character data types are redacted with a blank space.
Partial redaction. You redact a portion of the column data. For example, you can redact most of a credit card number with asterisks (*), except for the last four digits.
Regular expressions. You can use regular expressions in both full and partial redaction. This enables you to redact data based on a search pattern for the data. For example, you can use regular expressions to redact specific phone numbers or email addresses in your data.
Random redaction. The redacted data presented to the querying user appears as randomly-generated values each time it is displayed.
No redaction. This option enables you to test the internal operation of your redaction policies, with no effect on the results of queries against tables with policies defined on them. You can use this option to test the redaction policy definitions before applying them to a production environment.

Data Redaction performs the redaction at run time, that is, the moment that the user tries to view the data. This functionality is ideally suited for dynamic production systems in which data constantly changes. While the data is being redacted, Oracle Database can process all data normally and preserve the back-end referential integrity constraints. Data redaction can help you to comply with industry regulations such as Payment Card Industry Data Security Standard (PCI DSS) and the Sarbanes-Oxley Act.

Tutorial: Redacting Data for a Select Group of Users

You can redact data for a select group of users, when one group of users must have very restricted access to the data and other groups have greater access to the same data.

Topics:

About Redacting Data for a Select Group of Users

The scenario for this tutorial is a sales office in which the sales manager, user ezlotkey, must be able to see all the data in the sales_opps table, which describes various sales opportunities.

However, the sale representatives under this sales manager, users ahutton and eabel, must have limited access to the sales_opps table columns that describe account names and closing dates.

To solve this problem, you will create an Oracle Data Redaction policy that accomplishes these needs by performing the following actions:

The policy redacts data in two columns, each using a different redaction style.
The effect of the policy is the display of the query result with either the actual data or the redacted data based on the enabled roles of the querying user.

For this tutorial, you will interact directly with the database by using database user accounts. This is for simplicity. The intended use scenarios for Oracle Data Redaction are twofold: redact database applications and redact direct database access. Oracle Data Redaction on its own is a good solution for redacting sensitive data from packaged and custom applications. After completing the tutorial, you will have the knowledge necessary to apply what you have learned (using the database user accounts) for scenarios that involve actual application users. When direct database access is the target scenario, you should couple Oracle Data Redaction with preventive and detective controls that provide security for privileged database users (for example, Oracle Database Vault, Oracle Label Security, Oracle Audit Vault, and Oracle Database Firewall).

Step 1: Create User Accounts and Grant Them the Necessary Privileges

First, you must create the necessary user accounts and then grant user sec_admin the EXECUTE privilege on the DBMS_REDACT PL/SQL package, which is required to create Oracle Data Redaction policies.

To create user accounts for this tutorial:

In Enterprise Manager, access the Database home page for your target database as user SYS with the SYSDBA administrative privilege.

See Oracle Database 2 Day DBA for more information.
From the Administration menu, select Security, and then select Roles.
In the Roles page, select Create.
In the Create Role page Name field, enter SUPERVISOR and then click OK.
From the Administration menu, select Security, and then select Users.
In the Users Page, click Create.
In the Create User page, enter the following information:
- Name: EZLOTKEY (to create the user account for Eleni Zlotkey)
- Profile: DEFAULT
- Authentication: Password
- Enter Password and Confirm Password: Enter a password that meets the requirements in "Requirements for Creating Passwords".
- Default Tablespace: EXAMPLE
- Temporary Tablespace: TEMP
- Status: Unlocked
Select the System Privileges tab.
Select the Edit List button.
In the Modify System Privileges list, select the following privileges and then move them to the Selected System Privileges list.
- CREATE SESSION
- CREATE TABLE
- UNLIMITED TABLESPACE
Click OK.
In the Create User page, select the Roles tab, and then select the Edit List button.
In the Modify Roles page, double-click the SUPERVISOR role in the Available Roles list to move it to the Selected Roles list.
Click OK, and then click OK again to return to the Users page.
Select the Create button.
In the Create User page, enter the following information:
- Name: EABEL (to create the user account for Ellen Abel)
- Profile: DEFAULT
- Authentication: Password
- Enter Password and Confirm Password: Enter a password that meets the requirements in "Requirements for Creating Passwords".
- Default Tablespace: EXAMPLE
- Temporary Tablespace: TEMP
- Status: Unlocked
Select the System Privileges tab.
Select the Edit List button.
In the Modify System Privileges list, double-click the CREATE SESSION system privilege to move it to Selected System Privileges list.
Click OK.
In the Users page, select the EABEL user.
From the Actions list, select the Create Like button and then click Go.
In the Create User page, enter the following information:
- Name: Enter AHUTTON (for user Alyssa Hutton).
- Enter Password and Confirm Password: Enter a password that meets the requirements in "Requirements for Creating Passwords".
Note that user ezlotkey has been granted a role, supervisor, but the users eabel and ahutton are not granted any roles.
Click OK.
In the Users page, select the SEC_ADMIN user and then click the Edit button.

If user SEC_ADMIN does not exist, then you can quickly create this user in SQL*Plus by entering the following statement:
```
GRANT CREATE PROCEDURE, CREATE ROLE, CREATE SESSION, INHERIT ANY PRIVILEGES, SELECT ANY DICTIONARY TO sec_admin IDENTIFIED BY password;
```
See also "Step 2: Create a Security Administrator Account" to create this account.
Select the Object Privileges tab.
In the Object Privileges tab, from the Select Object Type list, select Package, and then click Add.
In the Select Package Objects field, enter SYS.DBMS_REDACT, and then in the Available Privileges list, move the EXECUTE privilege to the Selected Privileges list.
Click OK, and then click Apply.

Step 2: Create and Populate the SALES_OPPS Sales Opportunities Table

The sales_opps sales contains information for small businesses that are sales opportunities. It contains two columns that you must later on create an Oracle Data Redaction policy for.

To create and populate the sales_opps sales opportunities table:

Log into the database instance as user ezlotkey.
```
sqlplus ezlotkey
Enter password: password
```

Create the sales_opps table.

CREATE TABLE sales_opps (
 account varchar2(30), 
 region varchar2(20), 
 product varchar2(20), 
 sales_rep varchar2(15), 
 close_date date, 
 price number, 
 quantity number);

Populate the sales_opps table with some data.

INSERT INTO sales_opps VALUES ('Rising Dough Bakery', 'north-east', 'AA1 AccountPro', 'ahutton', '07-JUL-12', 400.00, 4);
INSERT INTO sales_opps VALUES ('Shear Madness Hair Salon', 'south-west', 'AA1 AccountPro', 'eabel', '20-APR-12', 400.00, 1);
INSERT INTO sales_opps VALUES ('Doublecheck Accounting', 'north-east', 'AA1 AccountPro', 'ahutton', '14-MAR-12', 400.00, 12);
INSERT INTO sales_opps VALUES ('State of Art Framing', 'south-west', 'AA1 TaxPro', 'eabel', '21-MAY-12', 300.00, 2);
INSERT INTO sales_opps VALUES ('Shady Trees Arborists', 'north-east', 'AA1 AccountPro', 'ahutton', '17-JUN-12', 400.00, 16);

Query the account and close_date columns of the sales_opps table, to see the data that must be redacted.

SELECT account, close_date, product, quantity FROM sales_opps;

ACCOUNT                        CLOSE_DAT PRODUCT                QUANTITY
------------------------------ --------- -------------------- ----------
Rising Dough Bakery            07-JUL-12 AA1 AccountPro                4
Shear Madness Hair Salon       20-APR-12 AA1 AccountPro                1
Doublecheck Accounting         14-MAR-12 AA1 AccountPro               12
State of Art Framing           21-MAY-12 AA1 TaxPro                    2
Shady Trees Arborists          17-JUN-12 AA1 AccountPro               16

Step 3: Create the SALES_OPPS_POL Oracle Data Redaction Policy

As user sec_admin, create the sales_opps_pol Oracle Data Redaction policy.

To create the sales_opps_pol Data Redaction policy:

In Enterprise Manager, log out, and then log back in again as user sec_admin.

See Oracle Database 2 Day DBA for more information.
From the Administration menu, select Security, and then select Oracle Data Redaction.
In the Data Redaction page, select the Create button.
In the Create Data Redaction Policy page, enter the following information to design the basics of the policy:
- Schema: Enter EZLOTKEY (in capital letters).
- Table/View: Enter SALES_OPPS (in capital letters).
- Policy Name: Enter SALES_OPPS_POL.
- Policy Expression: Enter the following expression:
```
SYS_CONTEXT('SYS_SESSION_ROLES', 'SUPERVISOR') = 'FALSE'
```
  The expression translates to "Redact the data in the account column for any user who does not have the supervisor role enabled." In other words, only the supervisor, ezlotkey, will be able to see the data in the account column.
Still in the Data Redaction page, apply the sales_opps_policy policy to a column, as follows:
1. Select the Add button.
2. In the Add window, from the Column list, select ACCOUNT.
  
  The Column Datatype field displays the data type of ACCOUNT, which is VARCHAR2.
3. From the Redaction Template list, select Custom (which should be the default selection.)
4. From the Redaction Function list, select FULL.
  
  FULL means all the characters in the account column will be redacted. Because the data type of the account column is VARCHAR2, the data will appear as a blank space.
5. Click OK.
Apply the sales_opps_pol policy to a second column, as follows:
1. Select the Add button.
2. In the Add window, from the Column list, select CLOSE_DATE.
  
  The Column Datatype field determines that the data type for CLOSE_DATE is DATE.
3. From the Redaction Function list, select PARTIAL.
  
  In a moment, the Add window expands to show the Function Attributes area.
4. In the Date Redaction Format field, enter the following attribute:
```
m06d7YHMS
```
  This setting redacts the month and day of each date, setting them to appear as 07-JUNE-12.
5. Click OK.
  
  The Create Data Redaction Policy page should appear as follows:
  
  Description of the illustration redaction_create.gif
In the Create Data Redaction Policy page, click OK.

The policy appears in the Data Redaction Policies list.

Do not exit Enterprise Manager.

Step 5: Test the SALES_OPPS_POL Oracle Data Redaction Policy

To test the sales_opps_pol Oracle Data Redaction policy, log in as the users you created earlier and query the redacted columns in the sales_opps table.

To test the sales_opps_pol policy:

Connect to SQL*Plus as user ezlotkey.

connect ezlotkey
Enter password: password

Grant the sales representatives the SELECT privilege for the sales_opps table.

GRANT SELECT ON sales_opps TO eabel;
GRANT SELECT ON sales_opps TO ahutton;

Connect as user eabel.
```
connect eabel
Enter password: password
```

Query the sales_opps tables as follows:

SELECT account, close_date, product, quantity FROM ezlotkey.sales_opps;

Output similar to the following should appear:

ACCOUNT                        CLOSE_DAT PRODUCT                QUANTITY
------------------------------ --------- -------------------- ----------
                               07-JUN-12 AA1 AccountPro                4
                               07-JUN-12 AA1 AccountPro                1
                               07-JUN-12 AA1 AccountPro               12
                               07-JUN-12 AA1 TaxPro                    2
                               07-JUN-12 AA1 AccountPro               16

No data appears in the account column because it has been redacted to display a blank space for each row. The close_date column shows dates, but they are all partial date values. The product and quantity columns show their data, as expected, because the Data Redaction policy does not apply to them.

Connect as user ahutton and perform the same query.

connect ahutton
Enter password: password

SELECT account, close_date, product, quantity FROM ezlotkey.sales_opps;

ACCOUNT                        CLOSE_DAT PRODUCT                QUANTITY
------------------------------ --------- -------------------- ----------
                               07-JUN-12 AA1 AccountPro                4
                               07-JUN-12 AA1 AccountPro                1
                               07-JUN-12 AA1 AccountPro               12
                               07-JUN-12 AA1 TaxPro                    2
                               07-JUN-12 AA1 AccountPro               16

The data is redacted for user ahutton as well.

Connect as user ezlotkey and perform the same query.

connect ezlotkey
Enter password: password

SELECT account, close_date, product, quantity FROM sales_opps;

ACCOUNT                        CLOSE_DAT PRODUCT                QUANTITY
------------------------------ --------- -------------------- ----------
Rising Dough Bakery            07-JUL-12 AA1 AccountPro                4
Shear Madness Hair Salon       20-APR-12 AA1 AccountPro                1
Doublecheck Accounting         14-MAR-12 AA1 AccountPro               12
State of Art Framing           21-MAY-12 AA1 TaxPro                    2
Shady Trees Arborists          17-JUN-12 AA1 AccountPro               16

The sales_opps_pol Data Redaction policy shows the actual data for user ezlotkey because she has the supervisor role enabled. However, if this role is disabled for ezlotkey, then when she queries this table, the account and close_date columns will be redacted, even though she created and owns the sales_opps table.

Log out of SQL*Plus.
```
EXIT
```

Step 6: Optionally, Remove the Components for This Tutorial

You can remove the components that you created for this tutorial if you no longer need them.

To remove the components for this tutorial:

In Enterprise Manager, ensure that you are logged in as user sec_admin.
In the Data Redaction Policies page, select the SALES_OPPS_POL policy.

Description of the illustration redaction_drop.gif
Click the Delete button.
In the Confirmation page, click Yes.
Log out of Enterprise Manager and then log back in again as user SYS with the SYSDBA administrative privilege
From the Database home page, select Security, then Administration, and then Users.
Select user AHUTTON, click Delete, and then in the Confirmation window, select Yes.
Select user EABEL, click Delete, and then in the Confirmation window, select Yes.
Select user EZLOTKEY, click Delete, and then in the Confirmation window, select Yes.
From the Administration menu, select Security, and then select Roles.
Select the role SUPERVISOR, click Delete, and in the Confirmation window, select Yes.
In the Users page, select the SEC_ADMIN user and then click Edit.
In the Edit User: SEC_ADMIN page, select the Object Privileges tab.
Select the EXECUTE privilege for the DBMS_REDACT package, and then click Delete.
Click the Apply button.
Exit Enterprise Manager by clicking the Log Out button.