9
Configuring Secure Database Access by Oracle9i Application Server

This chapter introduces features and practices which enable clients to securely access the database in an Internet environment. By using these approaches to set up the database and PL/SQL, you can avoid a number of known security problems.

• Providing for Secure Access to the Oracle9i Database Server

• Securing Application Database Access Through mod_plsql

• Providing Secure Database Connections with JDBC

Providing for Secure Access to the Oracle9i Database Server

This section describes Oracle9i Database Server tools which Oracle9i Application Server (Oracle9iAS) can use to facilitate secure deployment of the system.

• Proxy Authentication with Oracle9iAS

• Auditing for Multitier Applications

• Secure Application Roles

• Application Query Rewrite: Virtual Private Database (VPD)

• Selective Encryption of Stored Data

• Middle-Tier Connection Management

• Java Security Implementation in the Database

Proxy Authentication with Oracle9iAS

The proxy authentication feature is designed so that a specific middle tier can be restricted to acting on behalf of a specified set of users. Once the middle tier has authenticated itself to the database, it can establish a lightweight session on behalf of those users without submitting user-specific authentication information such as passwords. Moreover, Oracle9i can be configured so that a specific middle tier can assume a specific set of database roles when acting at the database on behalf of a specific user. In other words, the database uses both middle-tier identity and client user identity when determining what privileges to grant a middle tier acting for a user through a lightweight session.

In Oracle9i Application Server, this feature supports Java Database Connectivity (JDBC) access to the database. A middle-tier server can now access the Oracle9i database on behalf of a client user by establishing a lightweight session for that user through JDBC-OCI.

Oracle9i extends proxy authentication to include additional credential proxy of either the Distinguished Name (DN) or full X.509 certificate to the database. This provides strong, multitier security by enabling an SSL credential--an X.509 certificate or DN--to be passed to the database for purpose of identifying (but not authenticating) the user. (SSL cannot be used to authenticate a user through multiple tiers, because it is a point-to-point protocol rather than an end-to-end protocol.) For example, a user can authenticate to a middle tier using SSL, the middle tier can extract the DN from the certificate and pass it (or the full certificate) to the database. As an additional benefit, the DN or certificate is available in the lightweight session and the elements contained therein can be used with Virtual Private Database to limit access. For example, an organization could restrict data access based on the Organizational Unit (OU) element in a user certificate presented to the database. Application user proxy authentication is thus particularly valuable in e-business applications with thousands of users, as it supports data access control by user while meeting user scalability requirements.

The database can use the DN or certificate to look up a user in Oracle Internet Directory or other LDAP-based directory certified for enterprise user security (an Oracle Advanced Security feature). Integration of proxy authentication with enterprise user security enables the user identity to be maintained throughout all tiers of an application, yet the user need only be created once, in the directory. This also enables enterprise user security to be used in multitier applications, instead of merely client/server.

Auditing for Multitier Applications

Many multitier applications authenticate users to the middle tier, and then the transaction processing monitor or application server connects as super-privileged user, and performs all activity on behalf of all users. But it is important to be able to preserve the identity of the real client over the middle tier and enforce "least privilege" through a middle tier. In addition, you may want to audit actions taken on behalf of the user by the middle tier. Auditing user activity, whether users are connected through a middle tier or directly to the data server, enhances user accountability, and thus the overall security of multitier systems.

With Oracle9i Application Server, you can do all of these things. Oracle9i audit records capture both the logged-in user (that is, the middle tier) who initiated the connection, and the user on whose behalf an action is taken.

Secure Application Roles

A long-standing security problem has been that of limiting how users access data, to prevent users from bypassing application logic to access data directly. For example, in Web-based applications, even if users are known to the database, it may not be desirable to allow them to have direct access to data. To date, this has been a very difficult security problem to solve, because there has been no secure way to validate which application is used to access data. For example, a malicious user could write a program that appears to be a valid human resources application.

One way to address this challenge is through a secure application role: a role implemented by a package. The package can perform any desired validation to ensure that the appropriate conditions are met before the user can exercise privileges granted to the role in the database. The database ensures that it is only the trusted package implementing the role that determines the correct access conditions.

This feature, unique to Oracle9i, enables you to base use of roles on user-defined criteria. A secure application role is a role which is implemented by a package. For example, you could write a package permitting use of a role by a user connecting only from a particular IP address, or accessing the database only through a particular middle tier. A secure application role is used by an application, can only be enabled by the application, and does not need a password.

In multitier systems using proxy authentication, the package can validate that the user session was created by a middle tier, and thus that the user is accessing the database through the correct application. The secure application role can also ensure that a user connecting directly to the database is not able to access any data. A secure application role can enforce other security conditions, as well; for example, the user may not be permitted to access especially sensitive human resources data from the Internet.

A secure application role enhances the native strong authentication and fine-grained access control of the database to prevent users from assuming any privileges unless the correct access conditions are met. Secure application role thus solves a very difficult security issue and supports secure Web-based application data access.

Application Query Rewrite: Virtual Private Database (VPD)

Virtual Private Database is the ability to perform query modification based on a security policy you have defined in a package, and associated with a table or view. Virtual private database provides fine-grained access control which is data-driven, context-dependent, and row-based. It is a key enabling technology in building multitier systems which expose mission-critical resources to customers and partners.

Selective Encryption of Stored Data

For certain applications, you may decide to encrypt data as an additional measure of security. Most issues of data security can be handled by appropriate authentication and access control, ensuring that only properly identified and authorized users can access data. Data in the database, however, cannot normally be secured against the database administrator's access, because a DBA has all privileges. Likewise, organizations may have concerns about securing sensitive data stored off-line, such as backup files stored with a third party. They may want to guard against intruders accessing the data where it is physically stored on the database.

Although encryption is not a substitute for effective access control, you can obtain an additional measure of security by selectively encrypting sensitive data before it is stored in the database. Information which may be especially sensitive and warrant encryption could include credit card numbers, national identity numbers in countries with strict privacy laws, or trade secrets, such as industrial formulas. Applications for which a user is authenticated to the application, rather than to the database, may also use encryption to protect the application user password or cookie.

Middle-Tier Connection Management

You can configure a middle tier that manages the connections of very large user populations. To support a large number of users, you can configure multiple instances of Oracle Connection Manager. This product multiplexes multiple client network sessions through a single network connection to the database, increasing the total number of connections.

It is also possible to filter on source, destination, and host name. Thus you can ensure that connections only come from a physically secure terminal, or from an application Web server with a known IP address. (IP address alone is not enough for authentication, because it can be faked.) You thus could allow connections from IP address FOO, connecting to host BAR for PAYROLL.

Java Security Implementation in the Database

Oracle9i contains a Java security implementation in the server. The Java virtual machine (JVM) is the Java interpreter that converts the compiled Java bytecode into the machine language of the platform and runs it. JVMs can run on a client, in a browser, in a middle tier, on a Web, on an application server such as Oracle9i Application Server, or in a database server such as Oracle9i.

Class Execution

In the Oracle9i JVM implementation, the right to execute code in classes is controlled by execute privileges on the classes themselves. This is the same database privilege as execute privilege on a PL/SQL package, and is managed in the same way.

SecurityManager Class

The Oracle9i JVM starts with the class java.lang.SecurityManager installed. The Oracle9i database is based on the Java Developer's Kit 1.2 release from Sun Microsystems, and implements the security features of that release. In this implementation, permissions are controlled by the contents of a database table. The table is normally managed by PL/SQL procedures (and Java methods). The table can be used to grant permissions to either users or roles, and the "code source" of a class is identified with the user in whose schema the class has been loaded. Specific Oracle permissions control the right to update the table and perform other security sensitive operations.

Securing Application Database Access Through mod_plsql

This section describes how to set up the database and PL/SQL to avoid known security problems. It covers the following topics:

• Introduction to mod_plsql

• Authenticating Users Through mod_plsql

• Protecting the PL/SQL Procedures Granted to PUBLIC

• Providing Secure Database Connections with JDBC

  See Also: For more information about mod_plsql, refer to the mod_plsql User's Guide and the PL/SQL Web Toolkit Reference in the Oracle9iAS Documentation Library.

Introduction to mod_plsql

There are essentially two types of mod_plsql security. One is authentication, which establishes who the user is. The second is protection, which determines the privileges of the user.

In the context of Oracle HTTP Server, the Web listener authenticates its users and controls database access by means of the mod_plsql module. The front end of mod_plsql appears as an HTTP server module, and the back end appears as a client which obtains the correct level of access to the database on behalf of the user. The mod_plsql client connects to the database, gets results, and transmits the results back to the HTTP server.

Authenticating Users Through mod_plsql

mod_plsql provides different levels of authentication in addition to those provided by the Oracle HTTP Server. The Oracle HTTP Server protects documents, virtual paths and so forth, while mod_plsql protects users logging into the database or running a PL/SQL Web application.

You can enable different authentication modes, as described in Table 9-1.

Table 9-1 Authentication Modes Used with mod_plsql

Authentication Mode	Approach
Basic	Authentication is performed using basic HTTP authentication. Most applications use basic authentication.
Global Owa	Authentication is performed using the owa_custom.authorize procedure in the schema containing the PL/SQL Web Toolkit packages.
Custom Owa	Authentication is performed using packages and procedures in the user's schema (owa_customize.authorize), or if not found, in the schema containing the PL/SQL Web Toolkit packages.
PerPackage	Authentication is performed using packages and procedures in the user's schema (packageName.authorize).
Single Signon	Authentication is performed using Oracle9iAS Single Sign-On. Use this mode only if your application works with Oracle9iAS Single Sign-On.

Basic (Database Controlled Authentication)

The module, mod_plsql, supports authentication at the database level. It uses HTTP basic authentication but authenticates credentials by using them to attempt to log on to the database. Authentication is verified against a user database account, using user names and passwords that are either

• stored in the DAD. The end user is not required to log in. This method is useful for Web pages that provide public information.

• provided by the users by means of the browser's Basic HTTP Authentication dialog box. The user must provide a user name and password in the dialog box.

Oracle9i Application Server Basic Authentication Mode

Oracle9i Application Server has a different mechanism for the basic authentication mode. The user name and password must be stored in the DAD. Oracle9i Application Server uses HTTP basic authentication where the credentials are stored in a password file on the file system. Authentication is verified against the users listed in that file.

Oracle9iAS Basic Authentication Mode

mod_plsql supports Oracle9i Application Server basic authentication. Oracle HTTP Server authenticates users' credentials against a password file on the file system. This functionality is provided by a module called mod_auth.

See Also: "Using Basic Authentication and Authorization with mod_auth" for more information about mod_auth.

Deauthentication

mod_plsql allows users to log off (clear HTTP authentication information) programmatically through a PL/SQL procedure without having to exit all browser instances. This feature is supported on Netscape 3.0 or higher and on Microsoft Internet Explorer. On other browsers, the user may have to exit the browser to deauthenticate.

Another method of deauthentication is to add /logmeoff after the DAD in the URL. For example:

http://myhost:2000/pls/myDAD/logmeoff

Global OWA, Custom OWA, and Per Package (Custom Authentication)

Custom authentication enables applications to authenticate users within the application itself, not at the database level. Authorization is performed by invoking a user-written authorization function. Custom authentication uses a static user name and password that is stored in the DAD. It cannot be combined with dynamic user name and password authentication.

You can place the authentication function in different locations, depending on when it is to be invoked:

• Global OWA enables you to invoke the same authentication function for all users and procedures.

• Custom OWA enables you to invoke a different authentication function for each user and for all procedures.

• Per Package authentication enables you to invoke the authentication function for all users, but only for procedures in a specific package or for anonymous procedures.

Table 9-2 summarizes the parameter values.

Table 9-2 Custom Authentication Modes and Callback Functions

Mode	Access control scope	Callback function
Global OWA	All packages	owa_custom.authorize in the OWA package schema
Custom OWA	All packages	owa_custom.authorize in the user's schema, or, if not found, in the OWA package schema
Per package	Specified package	packageName.authorize in the user's schema, or anonymous.authorize is called

Protecting the PL/SQL Procedures Granted to PUBLIC

Every database package granted to public can be directly executed using the following URL:

http://hostname:port/pls/dad/schema.package.procedure

With the different levels of authentication, you must protect the execution of the PL/SQL procedures granted to PUBLIC in the database. These procedures (in the dbms_% packages, utl_% packages, and all packages under the SYS schema) pose a security vulnerability when they are executed through a Web browser. Such packages are intended only for the PL/SQL application developer.

Using the PlsqlExclusionList Directive in mod_plsql

mod_plsql provides a DAD parameter directive called PlsqlExclusionList to protect the execution of these PL/SQL packages and other packages that are specific to applications. The PlsqlExclusionList directive specifies a pattern for procedure, package, and schema names that are forbidden to be directly executed from a browser. This is a multiline directive in which each pattern is specified on one line. The pattern is not case sensitive and it accepts simple wildcards such as *, ?, and [a-z]. The default patterns that are not accessible from a direct URL are sys.*, dbms_*, utl_*, and owa_util.*.

Caution: Setting the PlsqlExclusionList directive to #NONE# will disable all protection. It is not recommended for a live Web site. Only use this setting for debugging purposes.

If the PlsqlExclusionList directive is overridden, then the default settings do not apply. In this case, you must add the default list to the list of excluded patterns.

Accessing the PlsqlExclusionList Directive

You can set the PlsqlExclusionList directive in the mod_plsql configuration file called dads.conf. This configuration file is located in the following directories:

• (UNIX) ORACLE_HOME/Apache/modplsql/conf/

• (Windows) ORACLE_HOME\Apache\modplsql\conf

Where ORACLE_HOME is the location of your Oracle9iAS Portal installation.

To ensure the best security for PL/SQL procedures that are granted to PUBLIC, specify the system default settings with the PlsqlExclusionList directive in the dads.conf file as shown in Example 9-1.

Example 9-1 System Default Settings Specified with the PlsqlExclusionList Directive

PlsqlExclusionList sys.*
PlsqlExclusionList dbms_*
PlsqlExclusionList utl_*
PlsqlExclusionList owa_util.*
PlsqlExclusionList portal.wwmon*

In addition to the patterns that are specified with this directive, mod_plsql also disallows any URLs that contain special characters such as tabs, newlines, carriage returns, single quotation marks ('), or reverse slashes (\). You cannot change this.

Providing Secure Database Connections with JDBC

This section describes use of Java Database Connectivity (JDBC) in the context of Oracle9i Application Server to ensure that access to the database is secure. It contains the following topics:

• Introduction to Java Database Connectivity (JDBC)

• Java Encryption Features of Oracle Advanced Security

Introduction to Java Database Connectivity (JDBC)

You can use Java to transmit data securely in a multitier environment. JDBC (Java Database Connectivity) is an industry-standard API (Applications Programming Interface) which allows Java programs to send SQL statements to an object-relational database such as Oracle. Java Database Connectivity enables a middle-tier server to access a database on behalf of a client user by establishing a lightweight session for the user.

Java applets can thus transmit data over secure channels. You can have secure connections from middle tier servers with Java Server Pages (JSPs) to the database. This enhances security because:

• Every protocol can be secured.

• JDBC-OCI and thin clients can be supported.

• Multitier architectures can be supported.

There are basically two ways to implement Java security and negotiate algorithms:

• Hard code it into your JDBC client application

• Configure it like native network cryptography

Java Encryption Features of Oracle Advanced Security

Sun Microsystems, Inc. defined the Java Database Connectivity (JDBC) standard, and Oracle Corporation, as an individual provider, implements and extends the standard with its own JDBC drivers. Oracle offers 4 kinds of JDBC driver:

• JDBC-OCI built on top of OCI libraries and Oracle Net Services client

• JDBC thin for both applets and applications (that do not have client installation and want maximum portability)

• JDBC server-side thin for connecting to remote Oracle databases from inside the target database

• Server-side internal driver for code such as stored procedures and functions executing inside the database.

JDBC-Oracle Call Interface Driver

The JDBC-Oracle Call Interface (JDBC-OCI) driver can be used for client-side use with an Oracle client installation. Because the JDBC-OCI driver uses the full Oracle Net Services communications stack on both client and server, it can take advantage of existing Oracle Advanced Security encryption and authentication mechanisms. In Oracle9i, proxy authentication has been extended to Java Database Connectivity (JDBC-OCI), which enables a middle-tier server to access the Oracle9i database on behalf of a client user by establishing a lightweight session for the user.

JDBC Thin Driver for Applets and Application

Because the thin JDBC driver is designed to be used with applets that are downloaded over the Internet, Oracle9i includes a 100% Java implementation of Oracle Advanced Security encryption and integrity algorithms for use with thin clients. Several benefits enable e-businesses deploying Oracle and other components to securely transmit a variety of information over a variety of channels:

• Data encryption for privacy of communications

• Data integrity checking to safeguard against data modification, replay, and eavesdropping

• Secure connections from thin JDBC clients to the Oracle9i database

• Ability for developers to build applets that transmit data over a secure communication channel

• Secure connections from Oracle9i databases to older versions of Oracle databases that are configured to use Oracle Advanced Security

• Secure connections from middle-tier servers with Java Server Pages (JSP) to the Oracle RDBMS

The Oracle JDBC Thin driver implements the Oracle password protocol for authentication. It does not support Oracle Advanced Security SSL implementation, nor does it support third-party authentication features such as RADIUS or Kerberos. The Oracle JDBC-OCI driver supports all Oracle Advanced Security features.

Oracle Advanced Security continues to encrypt and provide integrity checking of Oracle Net Services traffic between Oracle Net Services clients and Oracle servers using algorithms written in C. The Oracle Advanced Security Java implementation for Thin JDBC provides Java versions of the following encryption algorithms:

• RC4_256

• RC4_128

• RC4_56

• RC4_40

• DES56

• DES40

JDBC Server-Side Thin Driver

The JDBC Thin driver is a Type 4 (100% pure Java) driver that uses Java sockets to connect directly to a database server. It has a lightweight Java implementation of Oracle Net called Java Net.

The Thin driver does not require Oracle software on the client side. It does need a TCP/IP listener on the server side. Use this driver in regular Oracle Net listener Java applets that are downloaded into a Web browser. The Thin driver is self-contained, but it opens a Java socket, and thus can only run in a browser that supports sockets.

Oracle Java SSL

Oracle Java SSL is an implementation of Java Secure Socket Extension (JSSE) that is suitable for commercial use. In order to create a secure, fast implementation of SSL, Oracle Java SSL uses native code to improve the performance of critical components. In addition to the functionality included in the JSSE specifications, Oracle Java SSL supports the following:

• Multiple cryptographic algorithms

• Certificate and key management using Oracle Wallet Manager

• SSL-specific session capabilities, including authentication, that can be used by applications built on top of Oracle Java SSL

Secure Connections for Virtually Any Client

On the server, the negotiation of algorithms and the generation of keys function exactly the same as Oracle Advanced Security Oracle Net Services encryption, thus enabling backward and forward compatibility of clients and servers. On the clients, the algorithm negotiation and key generation occur in exactly the same manner as C-based Oracle Advanced Security encryption. The client and server negotiate encryption algorithms, generate random numbers, use Diffie-Hellman to exchange session keys, and use the Oracle Password Protocol, in the same manner as traditional Oracle Net Services clients. Thin JDBC contains a complete implementation of an Oracle Net Services client in pure Java. Consistent with other encryption implementations, the Java implementation of Oracle Advanced Security prevents access to the cryptographic algorithms, makes it impossible to double encrypt data, and encrypts data as it passes through the network. Users cannot alter the keyspace nor alter the encryption algorithms themselves.