The user sec_admin must have 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:

1. 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.

2. From the Security menu, select Roles.

3. In the Roles page, select Create.

4. In the Create Role page Name field, enter SUPERVISOR and then click OK.

5. From the Schema menu, select Users.

6. In the Users Page, click Create.

7. 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: USERS

   • Temporary Tablespace: TEMP

   • Status: Unlocked

8. Select the System Privileges tab.

9. Select the Edit List button.

10. 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

11. Click OK.

12. In the Create User page, select the Roles tab, and then select the Edit List button.

13. In the Modify Roles page, double-click the SUPERVISOR role in the Available Roles list to move it to the Selected Roles list.

14. Click OK, and then click OK again to return to the Users page.

15. Select the Create button.

16. 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: USERS

    • Temporary Tablespace: TEMP

    • Status: Unlocked

17. Select the System Privileges tab.

18. Select the Edit List button.

19. In the Modify System Privileges list, double-click the CREATE SESSION system privilege to move it to Selected System Privileges list.

20. Click OK.

21. In the Users page, select the EABEL user.

22. From the Actions list, select the Create Like button and then click Go.

23. 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.

24. Click OK.

25. 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.

26. Select the Object Privileges tab.

27. In the Object Privileges tab, from the Select Object Type list, select Package, and then click Add.

28. 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.

29. Click OK, and then click Apply.

This table contains two columns that you must later on and on which you must create an Oracle Data Redaction policy.

To create and populate the sales_opps sales opportunities table:

1. Log into the database instance as user ezlotkey.

   sqlplus ezlotkey
   Enter password: password
   

2. 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);
   

3. 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);
   

4. 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

To create the sales_opps_pol Data Redaction policy:

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

   See Oracle Database 2 Day DBA for more information.

2. From the Security menu, select Oracle Data Redaction.

3. In the Data Redaction page, select the Policies tab.

4. Select the Create button.

5. 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.

6. 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.

7. 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

8. In the Create Data Redaction Policy page, click OK.

   The policy appears in the Data Redaction Policies list.

   Do not exit Enterprise Manager.

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:

1. Connect to SQL*Plus as user ezlotkey.

   connect ezlotkey
   Enter password: password
   

2. 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;
   

3. Connect as user eabel.

   connect eabel
   Enter password: password
   

4. 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.

5. 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.

6. 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.

7. Log out of SQL*Plus.

   EXIT

To remove the components for this tutorial:

1. In Enterprise Manager, ensure that you are logged in as user sec_admin.

2. In the Data Redaction Policies page, select the SALES_OPPS_POL policy.

   
   Description of the illustration redaction_drop.gif

3. Click the Delete button.

4. In the Confirmation page, click Yes.

5. Log out of Enterprise Manager and then log back in again as user SYS with the SYSDBA administrative privilege

6. From the Database home page, select Schema, then Users.

7. Select user AHUTTON, click Delete, and then in the Confirmation window, select Yes.

8. Select user EABEL, click Delete, and then in the Confirmation window, select Yes.

9. Select user EZLOTKEY, click Delete, and then in the Confirmation window, select Yes.

10. From the Security menu, select Roles.

11. Select the role SUPERVISOR, click Delete, and in the Confirmation window, select Yes.

12. In the Users page, select the SEC_ADMIN user and then click Edit.

13. In the Edit User: SEC_ADMIN page, select the Object Privileges tab.

14. Select the EXECUTE privilege for the DBMS_REDACT package, and then click Delete.

15. Click the Apply button.

16. Exit Enterprise Manager by clicking the Log Out button.