This topic explains how you can grant privileges and roles to users to restrict access to data. It also explains the importance of the concept of least privilege, introduces secure application roles as a way to automatically filter out users who attempt to log in to your applications.

A user privilege is the right to perform an action, such as updating or deleting data. You can grant users privileges to perform these actions. A role is named collection of privileges that are grouped together, usually to enable users to perform a set of tasks related to their jobs. For example, a role called clerk can enable clerks to do things like create, update, and delete files. The clerk_mgr role can include the clerk role, plus some additional privileges such as approving the clerks' expense reports or managing their performance appraisals.

When you grant privileges to users, apply the principle of least privilege: Grant users only the privileges that they need. If possible, do not directly grant the user a privilege. Instead, create a role that defines the set of privileges the user needs and then grant the user this role. For example, grant user fred the CREATE SESSION privilege so that he can log in to a database session. But for the privileges that he needs for his job, such as the UPDATE TABLE privilege, grant him a role that has those privileges.

You can design your applications to automatically grant a role to the user who is trying to log in, provided the user meets criteria that you specify. To do so, you create a secure application role, which is a role that is associated with a PL/SQL procedure (or PL/SQL package that contains multiple procedures). The procedure validates the user: if the user fails the validation, then the user cannot log in. If the user passes the validation, then the procedure grants the user a role so that he or she can use the application. The user has this role only while he or she is logged in to the application. When the user logs out, the role is revoked.

Example 6-1 shows a secure application role procedure that allows the user to log in during business hours (8 a.m. to 5 p.m.) from a specific set of work stations. If the user passes these checks, then the user is granted the hr_admin role and then is able to log in to the application.

CREATE OR REPLACE PROCEDURE hr_admin_role_check
 AUTHID CURRENT_USER 
 AS 
 BEGIN 
  IF (SYS_CONTEXT ('userenv','ip_address') 
    BETWEEN '192.0.2.10' and '192.0.2.20'
     AND
    TO_CHAR (SYSDATE, 'HH24') BETWEEN 8 AND 17)
  THEN
    EXECUTE IMMEDIATE 'SET ROLE hr_admin'; 
  END IF;
 END;
/

To automate database logins, you create a logon trigger to run a PL/SQL procedure that can validate a user who is attempting to log in to an application. When the user logs in, the trigger executes. Logon triggers can perform multiple actions, such as generating an alert if the user fails the validation, displaying error messages, and so on.

Example 6-2 shows a simple logon trigger that executes a PL/SQL procedure.

CREATE OR REPLACE TRIGGER run_logon_trig AFTER LOGON ON DATABASE
 BEGIN
  sec_mgr.check_user_proc;
 END;

There are several ways that you can control the level of access users have to data from your applications.

• Oracle Virtual Private Database (VPD): VPD enables you to create policies that can restrict database access at the row and column level. Essentially, VPD adds a dynamic WHERE clause to a SQL statement that is issued against the table, view, or synonym to which an VPD security policy was applied.

  For example, suppose the user enters the SELECT * FROM OE.ORDERS statement. A VPD policy can transform this statement to the following statement instead, so that only the sales representative who owns the order can view this data:

  SELECT * FROM OE.ORDERS 
   WHERE SALES_REP_ID = SYS_CONTEXT('USERENV','SESSION_USER'); 
  

• Oracle Data Redaction: Oracle Data Redaction masks data at run time, at the moment the user attempts to access the data (that is, at query-execution time). This solution works well in a dynamic production system in which data is constantly changing. During the time that the data is being redacted, all data processing is performed normally, and the back-end referential integrity constraints are preserved. You typically redact sensitive data, such as credit card or Social Security numbers.

  You can mask the data in the following ways:

  • Full redaction, in which the entire data is masked. For example, the number 37828224 can be displayed as a zero.

  • Partial redaction, in which only a portion of the data is redacted. With this type, the number 37828224 can be displayed as *****224.

  • Random redaction, in which the data is displayed as randomized data. Here, the number 37828224 can appear as 93204857.

  • Regular expressions, in which you redact data based on a search pattern. You can use regular expressions in both full and partial redaction. For example, you can redact the user name of email addresses, so that only the domain shows: jsmith in the email address jsmith@example.com can be replaced with [redacted] so that the email address appears as [redacted]@example.com.

  • No redaction, which 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.

• Oracle Label Security: Oracle Label Security secures your database tables at the row level, and assigns these rows different levels of security based on the needs of your site. Rows that contain highly sensitive data can be assigned a label entitled HIGHLY SENSITIVE; rows that are less sensitive can be labeled as SENSITIVE, and so on. Rows that all users can have access to can be labeled PUBLIC. You can create as many labels as you need, to fit your site's security requirements.

  For example, when user fred, who is a low-level employee, logs in, he would see only data that is available to all users, as defined by the PUBLIC label. Yet when his director, hortensia, logs in, she can see all the sensitive data that has been assigned the HIGHLY SENSITIVE label.

• Oracle Database Vault: Oracle Database Vault enables you to restrict administrative access to your data. By default, administrators (such as user SYS with the SYSDBA privilege) have access to all data in the database. Administrators typically must perform tasks such performance tuning, backup and recovery, and so on. However, they do not need access to your salary records. Database Vault enables you to create policies that restrict the administrator's actions yet not prevent him or her from performing normal administrative activities.

  A typical Database Vault policy could, for example, prevent an administrator from accessing and modifying the HR.EMPLOYEES table. You can create fine-tuned policies that impose restrictions such as limiting the hours the administrator can log in, which computers he can use, whether he can log in to the database using dynamic tools, and so on. Furthermore, you can create policies that generate alerts if the administrator tries to violate a policy.

Topics:

• What Are Invoker's Rights and Definer's Rights?

• Protecting Users Who Run Invoker's Rights Procedures and Functions

• How Default Rights Are Handled for Java Stored Procedures

For security reasons, Oracle external procedures run in a process that is physically separate from Oracle Database. When you invoke an external procedure, Oracle Database creates the extproc operating system process (or agent), by using the operating system privileges of the user that started the listener for the database instance.

You can configure the extproc agent to run as a designated operating system credential. To use this functionality, you define a credential to be associated with the extproc process, which then can authenticate impersonate (that is, run on behalf of the supplied user credential) before loading a user-defined shared library and executing a function. To configure the extproc user credential, you use the PL/SQL package DBMS_CREDENTIAL and the PL/SQL statement CREATE LIBRARY.

You can create audit policies to audit specific actions in the database. Oracle Database then records these actions in an audit trail. The database mandatorily audits some actions and writes these to the audit trail as well. The audit policies that you create can be simple, such as auditing all actions by a specific user, or complex, such as testing for specific conditions and sending email alerts if these conditions are violated.

When you install an Oracle Database, you can choose how your database is audited.

• Unified auditing: In unified auditing, all audit trails are written to a single audit trail, viewable by the V$UNIFIED_AUDIT_TRAIL and GV$UNIFIED_AUDIT_TRAIL dynamic views. This audit trail encompasses not only Oracle Database-specific actions, but also actions performed in Oracle Real Application Security, Oracle Recovery Manager, Oracle Database Vault, and Oracle Label Security environments. The audit records from these sources are presented in a consistent, uniform format. You can create named audit policies and enable and disable them as necessary. If you want to use unified auditing, then you must migrate your databases to it.

• Mixture of unified auditing and pre-Release 12c auditing: For the most part, this option enables you to use the pre-Release 12c auditing, in which audit records are written to different locations using their own formats. However, Release 12c functionality, such as using auditing in a multitenant environment, is available. This type of auditing is the default for both new and upgraded databases.

In both cases, when you upgrade your databases to Oracle Database 12c Release 1 (12.1.0.1), the audit records from the previous release are preserved. If you decide to migrate to use unified auditing fully, you can archive these earlier records and then purge the audit trail. After you complete the migration, the new audit records are written to the unified audit trail.