Where possible, build applications in which application users are database users, so that you can use 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. However, you can use client identifiers to perform some types of tracking. For example, the OCI_ATTR_CLIENT_IDENTIFIER attribute of the Oracle Call Interface method OCIAttrSet can be used to enable auditing and monitoring of client connections. Client identifiers can be used to gather audit trail data on individual Web users, apply rules that restrict data access by Web users, and monitor and trace applications that each Web user users.

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

Oracle recommends that applications use the security enforcement mechanisms of the database as much as possible.

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).

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 re-implement security. Security becomes expensive, because organizations must implement the same security policies in multiple applications, and each new application requires an expensive reimplementation.

Guidelines for securing passwords in applications cover areas such as platform-specific security threats.

• Platform-Specific Security Threats
  You should be aware of potential security threats, which may not be obvious.
• Guidelines for Designing Applications to Handle Password Input
  Oracle provides guidelines for designing applications to handle password input.
• Guidelines for Configuring Password Formats and Behavior
  Oracle Database provides guidelines for configuring password formats and behavior.
• Guidelines for Handling Passwords in SQL Scripts
  Oracle provides guidelines for handling passwords in SQL scripts.

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.

SYSDBA or SYSOPER users can use password files to connect to an application over a network.

You can create Java packages that can be used to read passwords.

Example 10-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();
    }
  }
}

For safety reasons, Oracle external procedures run in a process that is physically separate from the database.

In most cases, you configure this process to execute as a user other than the Oracle software account. When your application invokes this external procedure—such as when a library of .dll or .so files must be accessed—then Oracle Database creates an operating system process called extproc. By default, the extproc process communicates directly through your server process. In other words, if you do not use a credential, then Oracle Database creates an extproc process for you in the default Oracle Database server configuration, and runs extproc as the oracle software account. Alternatively, it can communicate through the Oracle Database listener.

For better security, you can configure the extproc process to be authenticated through a credential.

The general process is as follows:

1. You create a credential.

   The credential is in an encrypted container. Both public and private synonyms can refer to this credential. Configuring Authentication for External Procedures describes how to create this credential and configure your database to use it.

2. You make your initial connection to the database, which you are running in either a dedicated server or a shared server process.

3. Your application makes a call to an external procedure.

   If this is the first call, then Oracle Database creates an extproc process. Note that if you want to use a credential for extproc, then you cannot use the Oracle listener to spawn the extproc process.

4. The extproc process impersonates (that is, it runs on behalf of your supplied credential), loads the requisite .dll, .so, .sl, or .a file, and then sends your data between SQL and C.

The extproc process has a set of behaviors for authentication and impersonation.

Table 10-2 describes the expected behaviors of an extproc process based on possible authentication and impersonation scenarios.

^Footnote 1 GLOBAL_EXTPROC_CREDENTIAL is a reserved credential name for the default credential if the credential is not explicitly specified and the ENFORCE_CREDENTIAL environment variable is set to TRUE. Therefore, Oracle strongly recommends that you create a credential by the that name if ENFORCE_CREDENTIAL is set to TRUE.

^Footnote 2

If only the GLOBAL_EXTPROC_CREDENTIAL credential is in use, then the EXECUTE privilege on this global credential is automatically granted to all users implicitly.

^Footnote 3

If both the PL/SQL library and the GLOBAL_EXTPROC_CREDENTIAL settings have credentials defined, then the credential of the PL/SQL library takes precedence.

To configure a credential for extproc processes, you can use the DBMS_CREDENTIAL PL/SQL package.

1. Log in to SQL*Plus as a user who has been granted the CREATE CREDENTIAL or CREATE ANY CREDENTIAL privilege.

   sqlplus psmith
   Enter password: password
   Connected.
   

   In a multitenant environment, you must connect to the appropriate pluggable database (PDB). For example:

   sqlplus psmith@hpdb
   Enter password: password
   Connected.
   

   In addition, ensure that you also have the CREATE LIBRARY or CREATE ANY LIBRARY privilege, and the EXECUTE object privilege on the library that contains the external calls.

2. Using the DBMS_CREDENTIAL PL/SQL package, create a new credential.

   For example:

   BEGIN
     DBMS_CREDENTIAL.CREATE_CREDENTIAL (
       credential_name   => 'smith_credential', 
       user_name         => 'tjones',
       password          => 'password')
   END;
   /

   In this example:

   • credential_name: Enter the name of the credential. Optionally, prefix it with the name of a schema (for example, psmith.smith_credential). If the ENFORCE_CREDENTIAL environment variable is set to TRUE, then you should create a credential with credential_name GLOBAL_EXTPROC_CREDENTIAL.

   • user_name: Enter a valid operating system user name to be to used to run as the user.

   • password: Enter the password for the user_name user.

3. Associate the credential with a PL/SQL library.

   For example:

   CREATE OR REPLACE LIBRARY ps_lib  
    AS 'smith_lib.so' IN DLL_LOC
    CREDENTIAL smith_credential;
   

   In this example, DLL_LOC is a directory object that points to the $ORACLE_HOME/bin directory. Oracle does not recommend using absolute paths to the DLL.

   When the PL/SQL library is loaded by an external procedure call through the extproc process, extproc now can authenticate and impersonate on behalf of the defined smith_credential credential.

4. Register the external procedure by creating a PL/SQL procedure or function that tells PL/SQL how to call the external procedure and what arguments to pass to it.

   For example, to create a function that registers an external procedure that was written in C, only use the AS LANGUAGE C, LIBRARY, and NAME clauses of the CREATE FUNCTION statement, as follows:

   CREATE OR REPLACE FUNCTION getInt (x VARCHAR2, y BINARY_INTEGER)
   RETURN BINARY_INTEGER
   AS LANGUAGE C
   LIBRARY ps_lib
   NAME "get_int_vals"
   PARAMETERS (x STRING, y int);

For maximum security, set the ENFORCE_CREDENTIAL environment variable to TRUE.

However, if you must accommodate backward compatibility, then set ENFORCE_CREDENTIAL to FALSE. FALSE enables the extproc process to authenticate, impersonate, and perform user-defined callout functions on behalf of the supplied credential when either of the following occurs:

• The credential is defined with a PL/SQL library.

• The credential is not defined but the GLOBAL_EXTPROC_CREDENTIAL credential exists.

If neither of these credential definitions is in place, then setting the ENFORCE_CREDENTIAL parameter to FALSE sets the extproc process to be authenticated by the operating system privilege of the owners of the Oracle listener or Oracle server process.

For legacy applications that run on top of extproc processes, ideally you should change the legacy application code to associate all alias libraries with credentials. If you cannot do this, then Oracle Database uses the GLOBAL_EXTPROC_CREDENTIAL credential to determine how authentication will be handled. If the GLOBAL_EXTPROC_CREDENTIAL credential is not defined, then the extproc process is authenticated by the operating system privilege of the owners of the Oracle listener or Oracle server process.

The CREATE ROLE statement with the IDENTIFIED USING clause creates a secure application role.

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.

You can create a PL/SQL package that defines the access policy for your application.

• About Creating a PL/SQL Package to Define the Access Policy for an Application
  To enable or disable the secure application role, you must create the security policies of the role within a PL/SQL package.
• Creating a PL/SQL Package or Procedure to Define the Access Policy for an Application
  The PL/SQL package or procedure that you create must use invoker’s rights to define the access policy.
• Testing the Secure Application Role
  As a user who has been granted the secure application role, try performing an action that requires the privileges the role grants.

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.

You can 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.

Think of most schemas as user names: the accounts that enable users to connect to a database and access the database objects.

For many applications, users only need access to an application schema; they do not need their own accounts or schemas in the database.

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 Database 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.

Object privileges enable end users to perform actions on objects such as tables, views, sequences, procedures, functions, or packages.

Table 10-3 summarizes the object privileges available for each type of object.

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

^Footnote 5

Privilege that cannot be granted to a role

^Footnote 6

Can also be granted for snapshots

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 10-4 lists the SQL statements permitted by the object privileges shown in Table 10-3.

The SEC_PROTOCOL_ERROR_TRACE_ACTION initialization parameter controls how trace files are managed when protocol errors are generated.

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 (DOS) attacks. 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: Sends an alert message to a database administrator or monitoring console.

  For example:

  SEC_PROTOCOL_ERROR_TRACE_ACTION = Alert

The SEC_PROTOCOL_ERROR_FURTHER_ACTION initialization parameter controls server execution after the server receives a bad packet.

The SEC_MAX_FAILED_LOGIN_ATTEMPTS initialization parameter sets the number of authentication attempts before the database will drop a failed connection.

As part of connection creation, the listener starts the server process and attaches it to the client. Using this physical connection, the client is this able to authenticate the connection. After a server process starts, client authenticates with this server process. An intruder could start a server process, 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 and the server process is terminated. By default, SEC_MAX_FAILED_LOGIN_ATTEMPTS is set to 3.

Remember that the SEC_MAX_FAILED_LOGIN_ATTEMPTS initialization parameter is designed to prevent potential intruders from attacking your applications, as well as valid users who have forgotten their passwords. The sqlnet.ora INBOUND_CONNECT_TIMEOUT parameter and the FAILED_LOGIN_ATTEMPTS profile 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

The SEC_RETURN_SERVER_RELEASE_BANNER initialization parameter can be used to prevent the display of detailed product information during authentication.

Oracle Database 19c Enterprise Edition Release 19.1.0.0 - Production

Oracle Database 19c Enterprise Edition Release 19.1.0.1 - Production

Oracle Database 19c Release 19.1.0.0.0 - Production

The SEC_USER_UNAUTHORIZED_ACCESS_BANNER and SEC_USER_AUDIT_ACTION_BANNER initialization parameters control the display of banners for unauthorized access and for auditing users.