3 Securing Application Database Access Through mod_plsql

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

• Authenticating Users Through mod_plsql

• Deauthenticating Users

• Protecting the PL/SQL Procedures Granted to PUBLIC

  See Also: For more information about mod_plsql, refer to the Oracle Application Server 10g PL/SQL Web Toolkit Reference in the Oracle Application Server Documentation Library.

3.1 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 3-1.

Table 3-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 Sign-on	Authentication is performed using Oracle Application Server Single Sign-On. Use this mode only if your application works with Oracle Application Server Single Sign-On.

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

3.1.2 Oracle Application Server Basic Authentication Mode

Oracle Application Server has a different mechanism for the basic authentication mode. The user name and password must be stored in the DAD. Oracle 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.

Oracle Application Server Basic Authentication Mode

mod_plsql supports Oracle 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.

3.1.3 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 3-2 summarizes the parameter values.

Table 3-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.

3.2 Deauthenticating Users

For DADs using dynamic authentication (no username/password in the DAD), 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.

Deauthentication can be done programatically by creating your own logout procedure which simulates a logout and redirects the user to a sign-off page.

Create or replace procedure MyLogOffProc as follows:

BEGIN
   -- Open the HTTP header
   owa_util.mime_header('text/html', FALSE, NULL);

   -- Send a cookie to logout
   owa_cookie.send('WDB_GATEWAY_LOGOUT', 'YES', path=>'/');

   -- Close the HTTP header
   owa_util.http_header_close;

   -- Generate the page
   htp.p('You have been logged off from the WEBSITE');
   htp.anchor( 'http://www.abc.com', 'click here');
   htp.p('<BR>bye');
END;

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

http://www.abc.com:2000/pls/myDAD/logmeoff

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

3.3.1 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 an active Web site. Only use this setting for debugging purposes.

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

3.3.2 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 Oracle Application Server 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 3-1.

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