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.

Part 1 -> Virtual Private Database

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:

Real Application Security(RAS): Oracle Database 12c introduces a data authorization solution to provide end-to-end security in a multitier application architecture. You can now declare and enforce the application-level security policies in the database kernel. The RAS security model understands the application-level security policy constructs, such as application users, privileges, and roles within the database, and enforces the security policies in the context of the application. Not just the data access, but RAS can help the applications to secure the access control operations of an application user.
Oracle Data Redaction: The Data Redaction feature masks the sensitive data on-the-fly before it is displayed to the user. This feature provides a rich library for masking the sensitive information while the data in the storage remains in an actual, that is, unmasked format.
Role and privilege analysis: Privilege analysis helps to enhance operational security by identifying the least set of privileges to run an application. During application development, you may have granted system privileges SELECT ANY TABLE to a user or object privileges to the PUBLIC role. However, before moving it to production, you must verify the security aspect of the application by retaining the required privileges and revoking the unnecessary ones. You can now analyze the roles, users or contexts, sessions or database to detect used and unused privileges. A privilege is considered as unused if it is not utilized for compilation or run-time operations.

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.

Unified Auditing: Oracle Database 12c introduces a unified audit trail which resolves the pain to maintain different stores of audit information. The new UNIFIED_AUDIT_TRAIL data dictionary view replaces the previous auditing views and consolidates the audit information from all the sources. The new AUDIT_VIEWER and AUDIT_ADMIN roles enable the separation of duty and provide easy management of auditing within the database. The new auditing framework supports conditional auditing to prevent the auditing of irrelevant operations.
New administrative privileges like SYSBACKUP, SYSDG, and SYSKM: These are the new privileges for dedicated tasks like backup, data guard operations, and key management in order to reduce dependency on SYSDBA and enable the separation of duties.
The READ object privilege and the READ ANY TABLE system privilege: You can now grant the READ object privilege that allows a user to query tables, views, materialized views, and synonyms. The difference between the SELECT and READ object privileges is that SELECT allows a user to lock the table data using the LOCK statement or the FOR UPDATE clause, while READ is only for query purposes.
The ADMIN and DELEGATE options for code-based access control: You can now protect your PL/SQL programs by granting roles to the program units and not to the users. You can grant the roles with the ADMIN or DELEGATE option to a program unit. With the DELEGATE option, the grantee is not allowed to grant the role to the other programs.

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:

The SYS and SYSTEM user is always exempt from the redaction policies.
The virtual columns cannot be redacted.
The editioning views cannot be redacted.
Users with the EXEMPT REDACTION POLICY system privilege are exempt from the redaction policies.
You cannot perform the CTAS (CREATE TABLE AS SELECT) operation on a redacted table.
A redaction policy can be created using the DBMS_REDACT package.A user must have the EXECUTE privilege on the package.
The redaction policy includes all the information—the object to be redacted, redaction method, and conditions when to redact the object data.
The Data Redaction policies can be created from Enterprise Manager Cloud Control 12c.
Data redaction can be used in conjunction with Oracle Virtual Private Database and Oracle Label Security. You must make sure that the Virtual Private Database predicates do not conflict with the redaction expressions.

Data Redaction function types

Data Redaction can use the following methods of redaction:

Full redaction: Full redaction refers to the complete replacement of a column value. A number type value will be replaced with zero, or a dummy date can replace a date. For example, 15/01/2015 (and all the date values) can be redacted as 01/01/00.
Partial redaction: Partial redaction refers to the masking of a portion of a column value. For example, the first few digits of telephone numbers or social security numbers are replaced with X. The masking format shown in the preceding figure depicts partial redaction.
Regular expressions: You can use regular expressions to mask the data based on a pattern and not on the basis of a fixed length of the characters. For example, email addresses or credit card numbers can be masked based on the position of the special character.
Random redaction: You can direct the redaction policy to generate a random value with a matching data type for the column carrying the sensitive data.
No redaction: This is used for testing purposes and can be used before deploying the security policies during production.

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 [email protected]

7876 ADAMS CLERK 0 01-JAN-00 [email protected]

7900 JAMES CLERK 0 01-JAN-00 [email protected]

7934 MILLER CLERK 0 01-JAN-00 [email protected]

Data Redaction metadata

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

REDACTION_POLICIES: This view stores the redaction policy details.
REDACTION_COLUMNS: This view stores the details of redacted columns in the database.

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.