IndicThreads

Oracle Database Security Overview – Part 2 -> Security Enhancements

In part two of this two part series on Oracle Database Security, Saurabh Gupta covers Oracle Database Security enhancements as well as features like Data Redaction, Real Application Security, and Privilege Analysis further strengthen the security offering in Oracle Database.

This article is an excerpt from the book Advanced Oracle PL/SQL Developer’s Guide – Second Edition, from Packt Publishing a handy technical reference for seasoned professionals in the database development space.

Oracle Database 12c Security enhancements

Oracle Database 12c introduced number of features and enhancements to strengthen the in-depth security collateral further. In this section, you will understand some of the key security enhancements and features in Oracle Database 12c. The following is a summary of the new security features:

A user with the CAPTURE_ADMIN role can kick-off a privilege analysis operation. Note that a SYS user cannot be analyzed under privilege analysis.

Oracle Database 12c Data Redaction

Oracle Database 12c Data Redaction prevents the exposure of sensitive data to the non-authorized users. The beauty of this feature lies in the fact that it enables dynamic masking, which means that the data is never changed on the storage or cache but gets redacted at runtime just before it is displayed to the user. The security policies are created in the database and are consistently invoked to hide sensitive information whenever requested by the applications. In addition, this feature has no impact on the integrity constraints and database operations like the backup, restore, export, import, grid, and cluster operations.

The typical use cases of Data Redaction would be a read-only application that fetches the data from a production warehouse. Similarly, the redaction policies can be created for a call center application. There will be many such cases where data view is based on the user authorization.

Note: The Oracle Database 12c Data Redaction feature has been back ported to Oracle Database 11.2.0.4.

Data Redaction features

Data Redaction is a part of the Advanced Security option, which can be purchased with Oracle Database Enterprise Edition. The following are the salient features of Data Redaction:

Data Redaction function types

Data Redaction can use the following methods of redaction:

Demonstration

Let’s continue the case study from the Virtual Private Database demonstration. Note that EMP is a Virtual Private Database protected table. With Data Redaction, the objective will be to mask the values from the SAL and COMM columns of the EMP table for the CLERK, MGR, and SALES users.

The following PL/SQL block creates a redaction policy on the SAL column of the EMP table. As the redaction is for the access from three users, that is, SALES, MGR, and CLERK, the predicate expression using the SYS_CONTEXT function has been included.

CONNECT sys/oracle AS SYSDBA

/*Grant execute privilege on DBMS_REDACT to scott */

GRANT EXECUTE ON sys.dbms_redact TO SCOTT

/

CONNECT scott/tiger

BEGIN

/*Add a policy to redact SAL column */

DBMS_REDACT.add_policy(

object_schema => ‘SCOTT’,

object_name => ‘EMP’,

column_name => ‘SAL’,

policy_name => ‘redact_employee_info’,

function_type => DBMS_REDACT.full,

expression => q’|sys_context(‘userenv’,’current_user’) IN (‘SALES’,’MGR’,’CLERK’)|’

);

END;

/

Let’s verify the impact of the redaction policy. The SAL column is fully redacted by replacing the original column values by a default value, zero. Note that the Virtual Private Database policy is still active and enforced:

CONN mgr/mgr

SELECT empno, ename, job, sal, deptno

FROM scott.emp

/

EMPNO ENAME JOB SAL DEPTNO

———- ———- ——— ———- ———

7566 JONES MANAGER 0 20

7698 BLAKE MANAGER 0 30

7782 CLARK MANAGER 0 10

For a full redaction policy type, the default values for the columns are picked up from the REDACTION_VALUES_FOR_TYPE_FULL dictionary view. You can alter a redaction policy using the ALTER_POLICY procedure from the DBMS_RLS package. The ACTION attribute is used to record the changes that you make while altering the policy.

Let’s alter the redaction policy and mask the HIREDATE column value. This time, we will use the partial redaction technique to mask the date value.

CONNECT scott/tiger

BEGIN

/*Alter policy to include HIREDATE column */

DBMS_REDACT.alter_policy (

object_schema => ‘SCOTT’,

object_name => ‘EMP’,

policy_name => ‘redact_employee_info’,

action => DBMS_REDACT.add_column,

column_name => ‘hiredate’,

function_type => DBMS_REDACT.partial,

function_parameters => ‘m1d1y1900’

);

END;

/

Now, let’s connect to the SALES user and check the masked values of the HIREDATE column:

CONN sales/sales

SELECT empno, ename, job, sal, hiredate

FROM scott.emp

/

EMPNO ENAME JOB SAL HIREDATE

———- ———- ——— ———- ———

7499 ALLEN SALESMAN 0 01-JAN-00

7521 WARD SALESMAN 0 01-JAN-00

7654 MARTIN SALESMAN 0 01-JAN-00

7844 TURNER SALESMAN 0 01-JAN-00

Let’s mask another column to demonstrate redaction using regular expression. We’ll alter the EMP table to add the EMAIL column:

CONNECT scott/tiger

ALTER TABLE emp

ADD (email VARCHAR2 (30))

/

UPDATE emp

SET email = LOWER (ename) || ‘@xyz.com’

/

COMMIT

/

The following PL/SQL block masks the EMAIL column using a regular expression:

BEGIN

/*Alter policy to include EMAIL column */

DBMS_REDACT.alter_policy (

object_schema => ‘SCOTT’,

object_name => ‘EMP’,

policy_name => ‘redact_employee_info’,

action => DBMS_REDACT.add_column,

column_name => ’email’,

function_type => dbms_redact.regexp,

regexp_pattern => dbms_redact.RE_PATTERN_EMAIL_ADDRESS,

regexp_replace_string => dbms_redact.RE_REDACT_EMAIL_NAME

);

END;

/

The preceding policy includes the EMAIL column and masks it using a regular expression. Note the regular expression pattern and replace the string constants. The DBMS_REDACT package includes a list of the standard security constructs that can be readily used while creating the redaction policy. Let’s connect to CLERK user and check the email column:

CONN clerk/clerk

SELECT empno, ename, job, sal, hiredate, email

FROM scott.emp

/

EMPNO ENAME JOB SAL HIREDATE EMAIL

———- ———- ——— —– ——— ————-

7369 SMITH CLERK 0 01-JAN-00 xxxx@xyz.com

7876 ADAMS CLERK 0 01-JAN-00 xxxx@xyz.com

7900 JAMES CLERK 0 01-JAN-00 xxxx@xyz.com

7934 MILLER CLERK 0 01-JAN-00 xxxx@xyz.com

Data Redaction metadata

You can query the following dictionary views to find the metadata information about the redaction policies:

The following two queries display the redaction policy details that were created in the preceding demonstration:

CONNECT sys/oracle AS SYSDBA

SELECT policy_name, expression

from REDACTION_POLICIES

/

POLICY_NAME EXPRESSION

——————– ——————————————–

redact_employee_info sys_context(‘userenv’,’current_user’) IN (‘SALES’,’MGR’,’CLERK’)

SELECT object_name, column_name,function_type,function_parameters

FROM redaction_columns

/

OBJECT_NAME COLUMN_NAME FUNCTION_TYPE FUNCTION_PARAMETERS

———— ———– ——————– ——————-

EMP HIREDATE PARTIAL REDACTION m1d1y1900

EMP SAL FULL REDACTION

EMP EMAIL REGEXP REDACTION

Conclusion

This two part article series covered the fundamentals of database security solutions from Oracle. Virtual Private Database enforces row-level security through the policies and restricts the data access for unauthorized users. Depending on a user’s identity and role, the application can set up multitenancy and ensure user isolation as well. Features like Data Redaction, Real Application Security, and Privilege Analysis further strengthen the security offering in Oracle Database 12c.


This article is an excerpt from the book Advanced Oracle PL/SQL Developer’s Guide – Second Edition, from Packt Publishing by Saurabh K. Gupta.