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 t he following topics:

• Authenticati ng Users Through mod_plsql
• Deauthenticating U sers
• Protecting the PL/SQL Procedures Granted to PUBLIC
  < /tr>

  See Also: For more information about mod_plsql configuration parameters, refer to the Oracle HTTP Server Administrator's Guide.

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

3.1.1 Basic (D atabase Controlled Authentication)

The module, mod_plsql, supports a uthentication 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:

3 .1.2 Oracle HTTP Server mod_plsql Basic Authentication Mode

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

Basic Authentication Mode

mod_plsql supports basic authentication. Oracle HTTP Ser ver 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 lev el. Authorization is performed by invoking a user-written authorization function.

Custom au thentication is done using OWA_CUSTOM and cannot be combined with dynamic username/password authentication. Custom authentication nee ds to have a static username/password stored in the DAD configuration file. mod_plsql uses this DAD username/password to login to the database. Once mod_plsql is logged in, authentication control is passed back to the application, by calling an application-level PL/ SQL hook. This callback function is implemented by the application developers. The value returned by the callback function determines the success or failure of authentication. The value TRUE means success and FALSE means failure.

Depending on the kind of custom authentication needed, you can place the authentication function in d ifferent locations:

• Global OWA enables you to invoke the same authentication function for all users and procedures.
• Custom OWA enables you t o 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 specifi c package or for anonymous procedures.

For example, when using Custom OWA, an authori zation function might verify that a user has logged in as user guest with password welcome, or it might check the user's IP address to determine access.

Table 3-2 summarizes the parameter values.

Table 3-2 Custom Authentication Modes and Callback Fun ctions

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 ow n logout procedure, which simulates a logout and redirects the user to a sign-off page.

Cre ate 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:20
00/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 vu lnerability when they are executed through a Web browser. Such packages are intended only for the PL/SQL application developer.

3.3.1 Using th e 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 pa ckages that are specific to applications. The PlsqlExclusionList directive specifies a pattern for procedure, package, and schema names that are forbidden from being directly executed from a browser. This is a multiline directive in which eac h 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_*< /code>, utl_*, and owa_util.*.

PlsqlExclusionList sys.*

PlsqlExclusionList dbms_*
PlsqlExclusionList utl_*
PlsqlExclusionL
ist owa_util.*
PlsqlExclusionList owa.*
PlsqlExclusionList htp.*
Pl
sqlExclusionList htf.*
PlsqlExclusionList myschema.mypackage*