2
Securing applications through the PL/SQL Gateway

There are essentially two types of PL/SQL Gateway security. One is authentication, which establishes who the user(s) is. The second is protection, which determines the privileges of the user(s).

2.1 Authenticating Users

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

You can enable different authentication modes using the Authentication Mode parameter on the Gateway Configuration pages.

• Basic - authentication is performed using Basic HTTP Authentication. Most applications use Basic Authentication.

• Global Owa - authentication is performed in the schema containing the PL/SQL Web Toolkit packages.

• Custom Owa - authentication is performed using packages and procedures in the user's schema, 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

• Single Sign-On - authentication is performed using the Oracle Single Sign-On feature of the Login Server. Use this mode only if your application works with the Login Server (Oracle Portal only).

2.1.1 Basic (Database Controlled Authentication)

The PL/SQL Gateway 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 via the browser's Basic HTTP Authentication dialog box. The end user must provide a username and password in the dialog box.

Oracle Application Server (OAS) Basic Authentication Mode

OAS has a different mechanism for the Basic Authentication Mode. The username and password must be stored in the DAD. OAS 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.

The PL/SQL Gateway supports OAS Basic Authentication. The Oracle HTTP Server that comes with Oracle9i Application Server can authenticate users' credentials against a password file on the file system. This functionality is provided by a module called mod_auth.

2.1.1.1 Deauthentication

The PL/SQL Gateway allows users to log off (clear HTTP authentication information) programatically through a PL/SQL procedure without having to exit all browser instances. This feature is supported on Netscape 3.0 or higher and 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

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

Implementing the authorize function

Custom authentication uses a static username/password that is stored in the DAD. It cannot be combined with dynamic username/password authentication.

The syntax of the authorize function is:

function authorize return boolean;

To enable custom authentication:

1. Set the level of authentication on the DAD Configuration page.

2. Implement the authorize function.

The PL/SQL Gateway uses the username/password provided in the DAD to log into the database. Once the login is complete, authentication control is passed to the application. Application-level PL/SQL hooks (callback functions) are then called. The implementations for these callback functions are left to the application developers. The return value of the callback function determines if the authentication succeeded or failed: if the function returns TRUE, authentication succeeded. If it returns FALSE, authentication failed and code in the application is not executed.

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

• To invoke the same authentication function for all users and procedures, choose Global OWA in the Authentication Mode list on the DAD Configuration Page. Then, implement the owa_custom.authorize function in the schema that contains the PL/SQL Web Toolkit, which is SYS.

• To invoke a different authentication function for each user and for all procedures, choose Custom OWA in the Authentication Mode list on the DAD Configuration Page. Then implement the owa_custom.authorize function in each user's schema. For users who do not have that function, the owa_custom.authorize function in the PL/SQL Web Toolkit package schema is invoked instead.

• To invoke the authentication function for all users but only for procedures in a specific package or for anonymous procedures, choose Per Package in the Authentication Mode list on the DAD Configuration Page. Then, implement the authorize function in that package in each user's schema. If the procedure is not in a package, then the anonymous authorize function is called instead. The following table summarizes the parameter values:

  Table 2-1 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 package	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.

2.1.3 REMOTE_USER CGI Environment Variable

The REMOTE_USER CGI environment variable has different username values depending on the authentication mode. The following list explains how this variable is derived:

Basic: If the username and password is stored in the DAD, then it is same username. If the username and password is empty in the DAD, then it is the username which the user entered in the HTTP authentication dialog box.

• OAS's Basic: The username that the user entered in the HTTP Authentication dialog box.

Global OWA, Custom OWA, and PerPackage: The username that the user entered in the HTTP authentication dialog box.

Single Sign-On: The username of the lightweight user that was authenticated by the login server.

2.2 Protecting the PL/SQL Procedures Granted to PUBLIC

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 hole when they are executed through the Web browser. Such packages are intended only for the PL/SQL application developer. Some procedures in the dbms_% packages allow access to sensitive information.

The PL/SQL Gateway provides a DAD parameter to protect the execution of these PL/SQL packages and other application specific packages.

The DAD parameter is called exclusion_list. This is a comma separated list of procedure/package/schema names which are forbidden to be directly executed from a browser. Each string in the list is case-insensitive and can accept wildcards such as *, ?, and [a-z]. If this parameter is not specified, the default list is sys.*, dbms_*, utl_*, owa_util.showsource, and owa_util.cellsprint. Setting this parameter to #NONE# allows all procedures to be accessed. If the user changes the value of this parameter, then there are no system defaults. Therefore, to add more procedures, you have to add the original list as well.

Access exclusion_list through the PL/SQL Gateway configuration file (wdbsvr.app). The location of your Oracle9i Application Server installation is <ORACLE_HOME> .For UNIX, the configuration file is located at:

<ORACLE_HOME>/Apache/modplsql/cfg/wdbsvr.app

For NT, it is located at:

<ORACLE_HOME>\Apache\modplsql\cfg\wdbsvr.app

Example 1:

exclusion_list=testschema.testpkg.*,sys.*,dbms_*,pkg?.*

This example protects all procedures in testschema.testpkg package, all procedures in the sys schema, all procedures in packages that match dbms_, and all procedures in packages that start with pkg and one character that can be a wildcard.

Example 2:

exclusion_list=#NONE#

This example disables all protection. This is recommended for debugging purposes only. This is not recommended for production environments.

2.3 Protecting the Administration pages

DBAs responsible for granting privileges to the DAD Administration pages need to protect these pages from public access. You can protect the admin pages through Basic Authentication or through "Protecting the Admin pages through the Oracle Portal (Single Sign-On)" if you are using Oracle Portal.

2.3.1 Protecting the Admin pages through Basic Authentication

Using Basic Authentication, the PL/SQL Gateway uses the DAD entered in the URL to connect to a database and authenticate the user's credentials. The user enters the username and password information into the Basic HTTP Authentication dialog box from the browser to gain access to the Admin pages.

To enable this type of protection, edit the configuration file (wdbsvr.app) as follows:

1. Open the file named wdbsvr.app. The location of your Oracle9i Application Server installation is <ORACLE_HOME>. For UNIX, the configuration file is located at:
   
   <ORACLE_HOME>/Apache/modplsql/cfg/wdbsvr.app
   
   For NT, it is located at:

   <ORACLE_HOME>\Apache\modplsql\cfg\wdbsvr.app

2. In the WVGATEWAY section, located at the top of the file, locate the administrators parameter.

3. Enter valid usernames for the people who need to access the Admin pages. This is a comma separated list.
   
   Example 1:

   administrators=adminuser1,adminuser2
   

   
   Only adminuser1 and adminuser2 can access the Admin Pages.
   
   Example 2:

   administrators=all
   

   
   Everyone can access the Admin Pages. This is only recommended for development environments.

4. Verify the admindad parameter is set to blank as shown below:

   admindad= 
   
   

5. Create a new DAD which admin page authentication will use to connect to a database. Leave the username and password parameters blank. Set the enablesso parameter to No. Set the other parameters to blank or comment them out.
   
   Example:

   [DAD_MyDADName] 
   connect_string   =  my_connect_string 
   password   = 
   username   = 
   default_page   = 
   document_table   = 
   document_path   = 
   document_proc   = 
   upload_as_long_raw   = 
   upload_as_blob   = 
   name_prefix   = 
   always_describe   = 
   after_proc   = 
   before_proc   = 
   reuse   =  yes 
   pathalias   = 
   pathaliasproc   = 
   enablesso   =  No 
   ;sncookiename   = 
   ;stateful   = 
   ;custom_auth   = 
   ;response_array_size   = 
   ; 
   
   
   

   Now, only usernames that appear in the administrators parameter list have permission to access Admin pages.

6. Type the following URL:

   http://hostname:port/pls/MyDADName/admin_/gateway.htm
   

   You are then prompted by a Basic HTTP Authentication dialog box to enter your username and password.

   • If the credentials are verified against the MyDADName DAD and the username is part of the administrators list, you can access the Admin Pages.

   • If the credentials are not verified against the MyDADName DAD or the username is not in the administrators list, you cannot access the Admin pages. An error message is displayed.

2.3.2 Protecting the Admin pages through the Oracle Portal (Single Sign-On)

DBAs responsible for granting privileges to the DAD Administration pages need to protect these pages from public access. Only the DBA or users with DBA-level privilege in Oracle Portal can access the DAD pages if the configuration file is edited as follows:

1. Open the PL/SQL Gateway configuration file named wdbsvr.app. The location of your Oracle9i Application Server installation is <ORACLE_HOME>.For UNIX, the configuration file is located at:
   
   <ORACLE_HOME>/Apache/modplsql/cfg/wdbsvr.app
   
   For NT, it is located at:

   <ORACLE_HOME>\Apache\modplsql\cfg\wdbsvr.app

2. In the [WVGATEWAY] section, typically located at the top of the file, locate the admindad parameter.

3. Enter a valid DAD name for a schema that has Single Sign-On enabled (enablesso=yes). Usually this name is set to the name of the DAD in which your Oracle Portal objects are installed. By default, the name is portal30.
   
   In the wdbsvr.app file, the Oracle Portal 3.0 gateway security parameters are displayed similar to the following:
   
   administrators = all
adminPath = /admin_/
admindad = portal30

   The name of the schema containing your Oracle Portal installation is portal30.

4. Replace portal30 with the name of your Oracle Portal DAD.
   
   When a user tries to access the DAD administration pages, authentication is performed through the Single Sign-On.
   • If the user is authorized to access these pages, the main DAD configuration page is displayed.

   • If the user is not authorized, the Single Sign-On page prompts for a user name and password. If this information does not authorize the user to access these pages, an error message is displayed.