5 Managing Security for Application Developers

Are Application Users Also Database Users?

Where possible, you should build applications in which application users are database users. In this way, you can leverage the intrinsic security mechanisms of the database.

For many commercial packaged applications, application users are not database users. For these applications, multiple users authenticate themselves to the application, and the application then connects to the database as a single, highly-privileged user. This is called the One Big Application User model.

Applications built in this way generally cannot use many of the intrinsic security features of the database, because the identity of the user is not known to the database.

Table 5-1 describes how the One Big Application User model affects various Oracle Database security features:

Is Security Better Enforced in the Application or in the Database?

Applications, whose users are also database users, can either build security into the application, or rely on intrinsic database security mechanisms such as granular privileges, virtual private databases (fine-grained access control with application context), roles, stored procedures, and auditing (including fine-grained auditing). Oracle recommends that applications use the security enforcement mechanisms of the database as much as possible.

When security is enforced in the database itself, rather than in the application, it cannot be bypassed. The main shortcoming of application-based security is that security is bypassed if the user bypasses the application to access data. For example, a user who has SQL*Plus access to the database can execute queries without going through the Human Resources application. The user, therefore, bypasses all of the security measures in the application.

Applications that use the One Big Application User model must build security enforcement into the application rather than use database security mechanisms. Because it is the application, and not the database, that recognizes users; the application itself must enforce security measures for each user.

This approach means that each application that accesses data must reimplement security. Security becomes expensive, because organizations must implement the same security policies in multiple applications, and each new application requires an expensive reimplementation.

General Guidelines for Securing Passwords in Applications

These guidelines are in the following categories:

• Platform-Specific Security Threats

• Designing Applications to Handle Password Input

• Configuring Password Formats and Behavior

• Handling Passwords in SQL*Plus and SQL Scripts

Securing Passwords Using an External Password Store

You can store password credentials for connecting to a database by using a client-side Oracle wallet. An Oracle wallet is a secure software container that stores the authentication and signing credentials needed for a user to log in.

See "Managing the Secure External Password Store for Password Credentials" for more information about the secure external password store. See also Oracle Database Advanced Security Administrator's Guide for information about using Oracle Wallet Manager to configure Oracle wallets.

Securing Passwords Using the orapwd Utility

You can create a password file for users who need to connect to an application using the SYSDBA or SYSOPER privileges over a network. To create the password file, use the ORAPWD utility. See Oracle Database Administrator's Guide for more information about creating and maintaining a password file.

Example of Reading Passwords in Java

Example 5-1 demonstrates how to create a Java package that can be used to read passwords.

// Change the following line to a name for your version of this package
package passwords.sysman.emSDK.util.signing;

import java.io.IOException;
import java.io.PrintStream;
import java.io.PushbackInputStream;
import java.util.Arrays;
 
/**
 * The static readPassword method in this class issues a password prompt
 * on the console output and returns the char array password
 * entered by the user on the console input.
 */
public final class ReadPassword {
  //----------------------------------
  /**
   * Test driver for readPassword method.
   * @param args the command line args
   */
  public static void main(String[] args) {
    char[] pass = ReadPassword.readPassword("Enter password: ");
    System.out.println("The password just entered is \""
      + new String(pass) + "\"");
    System.out.println("The password length is " + pass.length);
  }
   * Issues a password prompt on the console output and returns
   * the char array password entered by the user on the console input.
   * The password is not displayed on the console (chars are not echoed).
   * As soon as the returned char array is not needed,
   * it should be erased for security reasons (Arrays.fill(charArr, ' '));
   * A password should never be stored as a java String.
   *
   * Note that Java 6 has a Console class with a readPassword method,
   * but there is no equivalent in Java 5 or Java 1.4.
   * The readPassword method here is based on Sun's suggestions at
   * http://java.sun.com/developer/technicalArticles/Security/pwordmask.
   *
   * @param prompt the password prompt to issue
   * @return new char array containing the password
   * @throws RuntimeException if some error occurs
   */
  public static final char[] readPassword(String prompt)
  throws RuntimeException {
    try {
      StreamMasker masker = new StreamMasker(System.out, prompt);
      Thread threadMasking = new Thread(masker);
      int firstByte = -1;
      PushbackInputStream inStream = null;
      try {
        threadMasking.start();
        inStream = new PushbackInputStream(System.in);
        firstByte = inStream.read();
      } finally {
        masker.stopMasking();
      }
      try {
        threadMasking.join();
      } catch (InterruptedException e) {
        throw new RuntimeException("Interrupt occurred when reading password");
      }
      if (firstByte == -1) {
        throw new RuntimeException("Console input ended unexpectedly");
      }
      if (System.out.checkError()) {
        throw new RuntimeException("Console password prompt output error");
      }
      inStream.unread(firstByte);
      return readLineSecure(inStream);
    }
    catch (IOException e) {
      throw new RuntimeException("I/O error occurred when reading password");
    }
  }
  //----------------------------------
  /**
   * Reads one line from an input stream into a char array in a secure way
   * suitable for reading a password.
   * The char array will never contain a '\n' or '\r'.
   *
   * @param inStream the pushback input stream
   * @return line as a char array, not including end-of-line-chars;
   *  never null, but may be zero length array
   * @throws RuntimeException if some error occurs
   */
  private static final char[] readLineSecure(PushbackInputStream inStream)
  throws RuntimeException {
    if (inStream == null) {
      throw new RuntimeException("readLineSecure inStream is null");
    }
    try {
      char[] buffer = null;
      try {
        buffer = new char[128];
        int offset = 0;
        // EOL is '\n' (unix), '\r\n' (windows), '\r' (mac)
        loop:
        while (true) {
          int c = inStream.read();
          switch (c) {
          case -1:
          case '\n':
            break loop;
          case '\r':
            int c2 = inStream.read();
            if ((c2 != '\n') && (c2 != -1))
              inStream.unread(c2);
            break loop;
          default:
            buffer = checkBuffer(buffer, offset);
            buffer[offset++] = (char) c;
            break;
          }
        }
        char[] result = new char[offset];
        System.arraycopy(buffer, 0, result, 0, offset);
        return result;
      }
      finally {
        if (buffer != null)
          Arrays.fill(buffer, ' ');
      }
    }
    catch (IOException e) {
      throw new RuntimeException("I/O error occurred when reading password");
    }
  }
  //----------------------------------
  /**
   * This is a helper method for readLineSecure.
   *
   * @param buffer the current char buffer
   * @param offset the current position in the buffer
   * @return the current buffer if it is not yet full;
   *  otherwise return a larger buffer initialized with a copy
   *  of the current buffer and then erase the current buffer
   * @throws RuntimeException if some error occurs
   */
  private static final char[] checkBuffer(char[] buffer, int offset)
  throws RuntimeException
  {
    if (buffer == null)
      throw new RuntimeException("checkBuffer buffer is null");
    if (offset < 0)
      throw new RuntimeException("checkBuffer offset is negative");
    if (offset < buffer.length)
      return buffer;
    else {
      try {
        char[] bufferNew = new char[offset + 128];
        System.arraycopy(buffer, 0, bufferNew, 0, buffer.length);
        return bufferNew;
      } finally {
        Arrays.fill(buffer, ' ');
      }
    }
  }
  //----------------------------------
  /**
   * This private class prints a one line prompt
   * and erases reply chars echoed to the console.
   */
  private static final class StreamMasker
  extends Thread {
    private static final String BLANKS = StreamMasker.repeatChars(' ', 10);
    private String m_promptOverwrite;
    private String m_setCursorToStart;
    private PrintStream m_out;
    private volatile boolean m_doMasking;
    //----------------------------------
    /**
     * Constructor.
     * @throws RuntimeException if some error occurs
     */
    public StreamMasker(PrintStream outPrint, String prompt)
    throws RuntimeException {
      if (outPrint == null)
        throw new RuntimeException("StreamMasker outPrint is null");
      if (prompt == null)
        throw new RuntimeException("StreamMasker prompt is null");
      if (prompt.indexOf('\r') != -1)
        throw new RuntimeException("StreamMasker prompt contains a CR");
      if (prompt.indexOf('\n') != -1)
        throw new RuntimeException("StreamMasker prompt contains a NL");
      m_out = outPrint;
      m_setCursorToStart = StreamMasker.repeatChars('\010',
        prompt.length() + BLANKS.length());
      m_promptOverwrite = m_setCursorToStart + prompt + BLANKS
        + m_setCursorToStart + prompt;
    }
    //----------------------------------
    /**
     * Begin masking until asked to stop.
     * @throws RuntimeException if some error occurs
     */
    public void run()
    throws RuntimeException {
      int priorityOriginal = Thread.currentThread().getPriority();
      Thread.currentThread().setPriority(Thread.MAX_PRIORITY);
      try {
        m_doMasking = true;
        while (m_doMasking) {
          m_out.print(m_promptOverwrite);
          if (m_out.checkError())
            throw new RuntimeException("Console output error writing prompt");
          try {
            Thread.currentThread().sleep(1);
          } catch (InterruptedException ie) {
            Thread.currentThread().interrupt();
            return;
          }
        }
        m_out.print(m_setCursorToStart);
      } finally {
        Thread.currentThread().setPriority(priorityOriginal);
      }
    }
    //----------------------------------
    /**
     * Instructs the thread to stop masking.
     */
    public void stopMasking() {
      m_doMasking = false;
    }
    //----------------------------------
    /**
     * Returns a repeated char string.
     *
     * @param c the char to repeat
     * @param length the number of times to repeat the char
     * @throws RuntimeException if some error occurs
     */
    private static String repeatChars(char c, int length)
    throws RuntimeException {
      if (length < 0)
        throw new RuntimeException("repeatChars length is negative");
      StringBuffer sb = new StringBuffer(length);
      for (int i = 0; i < length; i++)
        sb.append(c);
      return sb.toString();
    }
  }
}

Step 1: Create the Secure Application Role

You create a secure application role by using the SQL statement CREATE ROLE with the IDENTIFIED USING clause. You must have the CREATE ROLE system privilege to execute this statement.

For example, to create a secure application role called hr_admin that is associated with the sec_mgr.hr_admin package, follow these steps:

1. Create the security application role as follows:

   CREATE ROLE hr_admin IDENTIFIED USING sec_mgr.hr_admin_role_check;
   

   This statement indicates the following:

   • The role hr_admin to be created is a secure application role.

   • The role can only be enabled by modules defined inside the PL/SQL procedure sec_mgr.hr_admin_role_check. At this stage, this procedure does not need to exist; "Step 2: Create a PL/SQL Package to Define the Access Policy for the Application" explains how to create the package or procedure.

2. Grant the security application role the privileges you would normally associate with this role.

   For example, to grant the hr_admin role SELECT, INSERT, UPDATE, and DELETE privileges on the HR.EMPLOYEES table, you enter the following statement:

   GRANT SELECT, INSERT, UPDATE, DELETE ON HR.EMPLOYEES TO hr_admin;
   

   Do not grant the role directly to the user. The PL/SQL procedure or package does that for you, assuming the user passes its security policies.

Step 2: Create a PL/SQL Package to Define the Access Policy for the Application

To enable or disable the secure application role, you create the security policies of the role within a PL/SQL package. You also can create an individual procedure to do this, but a package lets you group a set of procedures together. This lets you group a set of policies that, used together, present a solid security strategy to protect your applications. For users (or potential intruders) who fail the security policies, you can add auditing checks to the package to record the failure. Typically, you create this package in the schema of the security administrator.

The package or procedure must accomplish the following:

• It must use invoker's rights to enable the role.To create the package using invoker's rights, you must set the AUTHID property to CURRENT_USER. You cannot create the package by using definer's rights.

  For more information about invoker's rights and definer's rights, see Oracle Database PL/SQL Language Reference.

• It must include one or more security checks to validate the user. One way to validate users is to use the SYS_CONTEXT SQL function. See Oracle Database SQL Language Reference for more information about SYS_CONTEXT. To find session information for a user, you can use SYS_CONTEXT with an application context. See Chapter 6, "Using Application Contexts to Retrieve User Information" for details.

• It must issue a SET ROLE SQL statement or DBMS_SESSION.SET_ROLE procedure when the user passes the security checks. Because you create the package using invoker's rights, you must set the role by issuing the SET ROLE SQL statement or the DBMS_SESSION.SET_ROLE procedure. (However, you cannot use the SET ROLE ALL statement for this type of role enablement.) The PL/SQL embedded SQL syntax does not support the SET ROLE statement, but you can invoke SET ROLE by using dynamic SQL, for example, with EXECUTE IMMEDIATE.

  For more information about EXECUTE IMMEDIATE, see Oracle Database PL/SQL Language Reference.

Because of the way that you must create this package or procedure, you cannot use a logon trigger to enable or disable a secure application role. Instead, invoke the package directly from the application when the user logs in, before the user must use the privileges granted by the secure application role.

For example, suppose you wanted to restrict anyone using the hr_admin role to employees who are on site (that is, using certain terminals) and between the hours of 8 a.m. and 5 p.m. As the system or security administrator, follow these steps:

1. Create the procedure as follows:

   1 2 3 4 5 6 7 8 9 10 11 12 13

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

   
   

   In this example:

   • Line 2: Sets the AUTHID property to CURRENT_USER so that invoker's rights can be used.

   • Line 5: Validates the user by using the SYS_CONTEXT SQL function to retrieve the user session information.

   • Lines 6–8: Create a test to grant or deny access. The test restricts access to users who are on site (that is, using certain terminals) and working between the hours of 8:00 a.m. and 5:00 p.m. If the user passes this check, the hr_admin role is granted.

   • Lines 9–10: Assuming the user passes the test, grants the role to the user by issuing the SET ROLE statement using the EXECUTE IMMEDIATE command.

2. Grant EXECUTE permissions for the hr_admin_role_check procedure to any user who was assigned it.

   For example:

   GRANT EXECUTE ON hr_admin_role_check TO psmith;
   

To test the secure application role, log in to SQL*Plus as the user, try to enable the role, and then try to perform an action that requires the privileges the role grants.

For example:

CONNECT PSMITH 
Enter password: password

EXECUTE sec_admin.hr_admin_role_check;

-- actions requiring privileges granted by the role

Why Users Should Only Have the Privileges of the Current Database Role

A single user can use many applications and associated roles. However, you should ensure that the user has only the privileges associated with the current database role. Consider the following scenario:

• The ORDER role (for an application called Order) contains the UPDATE privilege for the INVENTORY table.

• The INVENTORY role (for an application called Inventory) contains the SELECT privilege for the INVENTORY table.

• Several order entry clerks were granted both the ORDER and INVENTORY roles.

In this scenario, an order entry clerk who was granted both roles can use the privileges of the ORDER role when running the INVENTORY application to update the INVENTORY table. The problem is that updating the INVENTORY table is not an authorized action for the INVENTORY application. It is an authorized action for the ORDER application. To avoid this problem, use the SET ROLE statement as explained in the following section.

Using the SET ROLE Statement to Automatically Enable or Disable Roles

Use a SET ROLE statement at the beginning of each application to automatically enable its associated role and to disable all others. This way, each application dynamically enables particular privileges for a user only when required.

The SET ROLE statement simplifies privilege management. You control what information users can access and when they can access it. The SET ROLE statement also keeps users operating in a well-defined privilege domain. If a user obtains privileges only from roles, then the user cannot combine these privileges to perform unauthorized operations.

Protecting Database Objects in a Unique Schema

You can think of most schemas as user names: the accounts that enable users to connect to a database and access the database objects. However, a unique schema does not allow connections to the database, but is used to contain a related set of objects. Schemas of this sort are created as typical users, and yet are not granted the CREATE SESSION system privilege (either explicitly or through a role). However, you must temporarily grant the CREATE SESSION and RESOURCE privilege to a unique schema if you want to use the CREATE SCHEMA statement to create multiple tables and views in a single transaction.

For example, a given schema might own the schema objects for a specific application. If application users have the privileges to do so, then they can connect to the database using typical database user names and use the application and the corresponding objects. However, no user can connect to the database using the schema set up for the application. This configuration prevents access to the associated objects through the schema, and provides another layer of protection for schema objects. In this case, the application could issue an ALTER SESSION SET CURRENT_SCHEMA statement to connect the user to the correct application schema.

Protecting Database Objects in a Shared Schema

For many applications, users do not need their own accounts or schemas in a database. These users only need to access an application schema. For example, users John, Firuzeh, and Jane are all users of the Payroll application, and they need access to the payroll schema on the finance database. None of them need to create their own objects in the database. They need to only access the payroll objects. To address this issue, Oracle Advanced Security provides the enterprise users, which are schema-independent users.

Enterprise users, users managed in a directory service, do not need to be created as database users because they use a shared database schema. To reduce administration costs, you can create an enterprise user once in the directory, and point the user at a shared schema that many other enterprise users can also access.

For more information about managing enterprise users, see Oracle Database Enterprise User Security Administrator's Guide.

What Application Developers Need to Know About Object Privileges

End users are typically granted object privileges. An object privilege allows a user to perform a particular action on a specific table, view, sequence, procedure, function, or package.

Table 5-2 summarizes the object privileges available for each type of object.

^Footnote 1 Standalone stored procedures, functions, and public package constructs

^Footnote 2 Privilege that cannot be granted to a role

^Footnote 3 Can also be granted for snapshots

See also "Auditing Schema Objects" for detailed information about how schema objects can be audited.

SQL Statements Permitted by Object Privileges

As you implement and test your application, you should create each necessary role. Test the usage scenario for each role to ensure that the users of your application will have proper access to the database. After completing your tests, coordinate with the administrator of the application to ensure that each user is assigned the proper roles.

Table 5-3 lists the SQL statements permitted by the object privileges shown in Table 5-2.

See "About Privileges and Roles" for a discussion of object privileges. See also "Auditing SQL Statements" for detailed information about how SQL statements can be audited.

Reporting Bad Packets Received on the Database from Protocol Errors

Networking communication utilities such as Oracle Call Interface (OCI) or Two-Task Common (TTC) can generate a large disk file containing the stack trace and heap dump when the server receives a bad packet, out-of-sequence packet, or a private or an unused remote procedure call. Typically, this disk file can grow quite large. An intruder can potentially cripple a system by repeatedly sending bad packets to the server, which can result in disk flooding and denial of service. An unauthenticated client can also mount this type of attack.

You can prevent these attacks by setting the SEC_PROTOCOL_ERROR_TRACE_ACTION initialization parameter to one of the following values:

• None: Configures the server to ignore the bad packets and does not generate any trace files or log messages. Use this setting if the server availability is overwhelmingly more important than knowing that bad packets are being received.

  For example:

  SEC_PROTOCOL_ERROR_TRACE_ACTION = None
  

• Trace (default setting): Creates the trace files, but it is useful for debugging purposes, for example, when a network client is sending bad packets as a result of a bug.

  For example:

  SEC_PROTOCOL_ERROR_TRACE_ACTION = Trace
  

• Log: Writes a short, one-line message to the server trace file. This choice balances some level of auditing with system availability.

  For example:

  SEC_PROTOCOL_ERROR_TRACE_ACTION = Log
  

• Alert: Writes a short, one-line error message to the server trace file and alert log.

  For example:

  SEC_PROTOCOL_ERROR_TRACE_ACTION = Alert
  

Terminating or Resuming Server Execution After Receiving a Bad Packet

After Oracle Database detects a client or server protocol error, it must continue execution. However, this could subject the server to further bad packets, which could lead to disk flooding or denial-of-service attacks.

You can control the further execution of a server process when it is receiving bad packets from a potentially malicious client by setting the SEC_PROTOCOL_ERROR_FURTHER_ACTION initialization parameter to one of the following values:

• Continue (default setting): Continues the server execution. However, be aware that the server may be subject to further attacks.

  For example:

  SEC_PROTOCOL_ERROR_FURTHER_ACTION = Continue
  

• Delay,m: Delays the client m seconds before the server can accept the next request from the same client connection. This setting prevents malicious clients from excessively using server resources while legitimate clients experience a degradation in performance but can continue to function.

  For example:

  SEC_PROTOCOL_ERROR_FURTHER_ACTION = Delay,3
  

• Drop,n: Forcefully terminates the client connection after n bad packets. This setting enables the server to protect itself at the expense of the client, for example, loss of a transaction. However, the client can still reconnect, and attempt the same operation again.

  For example:

  SEC_PROTOCOL_ERROR_FURTHER_ACTION = Drop,10
  

Configuring the Maximum Number of Authentication Attempts

With Oracle Database, a server process is first started, and then the client authenticates with this server process. An intruder could start a server process first, and then issue an unlimited number of authenticated requests with different user names and passwords in an attempt to gain access to the database.

You can limit the number of failed login attempts for application connections by setting the SEC_MAX_FAILED_LOGIN_ATTEMPTS initialization parameter to restrict the number of authentication attempts on a connection. After the specified number of authentication attempts fail, the database process drops the connection. By default, SEC_MAX_FAILED_LOGIN_ATTEMPTS is set to 10.

Remember that the SEC_MAX_FAILED_LOGIN_ATTEMPTS initialization parameter is designed to prevent potential intruders from attacking your applications; it does not apply to valid users. The sqlnet.ora INBOUND_CONNECT_TIMEOUT parameter and the FAILED_LOGIN_ATTEMPTS initialization parameter also restrict failed logins, but the difference is that these two parameters only apply to valid user accounts.

For example, to limit the maximum attempts to 5, set SEC_MAX_FAILED_LOGIN_ATTEMPTS as follows in the initsid.ora initialization parameter file:

SEC_MAX_FAILED_LOGIN_ATTEMPTS = 5

Controlling the Display of the Database Version Banner

Detailed product version information should not be accessible before a client connection (including an Oracle Call Interface client) is authenticated. An intruder could use the database version to find information about security vulnerabilities that may be present in the database software.

You can restrict the display of the database version banner to unauthenticated clients by setting the SEC_RETURN_SERVER_RELEASE_BANNER initialization parameter in the initsid.ora initialization parameter file to either YES or NO. By default, SEC_RETURN_SERVER_RELEASE_BANNER is set to NO.

For example, if you set it to YES, the Oracle Database displays the full correct database version:

Oracle Database 11g Enterprise Edition Release 11.2.0.0 - Production

In the future, if you install Oracle Database 11.2.0.2, for example, it will display the following banner:

Oracle Database 11g Enterprise Edition Release 11.2.0.2 - Production

However, if in that same release, you set it to NO, then Oracle Database restricts the banner to display the following fixed text starting with Release 11.2:

Oracle Database 11g Release 11.2.0.0.0 - Production

Configuring Banners for Unauthorized Access and Auditing User Actions

You should create and configure banners to warn users against unauthorized access and possible auditing of user actions. The notices are available to the client application when it logs into the database.

To configure these banners to display, set the following sqlnet.ora parameters on the database server side to point to a text file that contains the banner information:

• SEC_USER_UNAUTHORIZED_ACCESS_BANNER. For example:

  SEC_USER_UNAUTHORIZED_ACCESS_BANNER = /opt/Oracle/11g/dbs/unauthaccess.txt
  

• SEC_USER_AUDIT_ACTION_BANNER. For example:

  SEC_USER_AUDIT_ACTION_BANNER = /opt/Oracle/11g/dbs/auditactions.txt
  

By default, these parameters are not set.

After you set these parameters, the Oracle Call Interface application must use the appropriate OCI APIs to retrieve these banners and present them to the end user.