Security

This chapter explains some fundamentals of designing security into the database and database applications.

Topics:

Enabling User Access with Grants, Roles, and Least Privilege

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 they can use the application. The user has this role only while they are logged in to the application. When the user logs out, the role is revoked.

See Also:

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

Example 5-1 Secure Application Role Procedure to Restrict Access to Business Hours

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;
/

Automating Database Logins

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 5-2 shows a simple logon trigger that executes a PL/SQL procedure.

Example 5-2 Creating a Logon Trigger

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

See Also:

Controlling User Access with Fine-Grained Access Control

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

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

See Also:

Using Invoker’s and Definer’s Rights for Procedures and Functions

Topics:

What Are Invoker’s Rights and Definer’s Rights?

When you create a procedure or function (that is, a program unit), you can design it so that it runs with either the privileges of the owner (you) or the privileges of the person who is invoking it. Definer’s rights run the program unit using the owner’s privileges and invoker’s rights run the program unit using the privileges of the person who runs it. For example, suppose user harold creates a procedure that updates the table orders. User harold then grants the EXECUTE privilege on this procedure to user hazel. If harold had created the procedure with definer’s rights, then the procedure would expect the orders table to be in harold’s schema. If he created it with invoker’s rights, then the procedure would look for the orders table in hazel’s schema.

To designate a program unit as definer’s rights or invokers rights, use the AUTHID clause in the creation statement. If you omit the AUTHID clause, then the program unit is created with definer’s rights. Example 5-3 shows how to use the AUTHID clause in a CREATE PROCEDURE statement to specify definer’s rights or invoker’s rights.

Example 5-3 Creating Program Units with Definer’s Rights or Invoker’s Rights

CREATE PROCEDURE my_def_proc AUTHID DEFINER -- Definer's rights procedure
  AS ...

CREATE PROCEDURE my_inv_proc AUTHID CURRENT_USER -- Invoker's rights procedure
  AS ...

See Also:

Protecting Users Who Run Invoker’s Rights Procedures and Functions

An important consideration when you create an invoker’s rights program unit is the level of privilege that the invoking users have. Suppose user harold is a low-ranking employee who has few privileges and hazel is an executive with many privileges. When hazel runs harold’s invoker’s rights procedure, the procedure temporarily inherits hazel’s privileges (all of them). But because harold owns this procedure, he can modify it without her knowing it to behave in ways that take advantage of hazel’s privileges, such as giving harold a raise. To help safeguard against this type of scenario, after she has ensured that harold is trustworthy, user hazel can grant harold permission so that his invoker’s rights procedures and functions have access to hazel’s privileges when she runs them. To do so, she must grant him the INHERIT PRIVILEGES privilege. Example 5-4 shows how invoking user hazel can grant user harold the INHERIT PRIVILEGES privilege.

Example 5-4 Granting a Program Unit Creating the INHERIT PRIVILEGES Privilege

GRANT INHERIT PRIVILEGES ON hazel TO harold;

If harold proves untrustworthy, hazel can revoke the INHERIT PRIVILEGES privilege from him.

Administrators such as user SYS and SYSTEM have the INHERIT ANY PRIVILEGES privilege, which enable these users’ invoker’s rights procedures to have access to the privileges of any invoking users. As with all ANY privileges, grant this privilege only to trusted users.

See Also: Oracle Database Security Guide for more information about managing security for definer’s rights and invoker’s rights procedures and functions

How Default Rights Are Handled for Java Stored Procedures

By default, Java class schema objects run with the privileges of their invoker, not with definer’s rights. If you want your Java schema objects to run with definer’s rights, then when you load them by using the loadjava tool, specify the -definer option. Example 5-5 shows how to use the -definer option in a loadjava command.

Example 5-5 Loading a Java Class with Definer’s Rights

loadjava -u joe -resolve -schema TEST -definer ServerObjects.jar
Password: password

You can use the -definer option on individual classes. Be aware that different definers may have different privileges. Apply the -definer option with care, so that you can achieve the desired results. Doing so helps to ensure that your classes run with no more than the privileges that they need.

See Also:

Managing External Procedures for Your Applications

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.

See Also: Oracle Database Security Guide for more information about securing external procedures

Auditing User Activity

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.

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.

See Also: