|Oracle 9i Application Server Using the PL/SQL Gateway
Release 1 (v22.214.171.124)
Part Number A90099-01
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).
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.
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:
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.
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:
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:
To enable custom authentication:
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:
owa_custom.authorizefunction in the schema that contains the PL/SQL Web Toolkit, which is SYS.
|Mode||Access control scope||Callback function|
owa_custom.authorize in the OWA package schema
owa_custom.authorize in the user's schema, or, if not found, in the OWA package schema
packageName.authorize in the user's schema, or anonymous.authorize is called.
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.
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.
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:
For NT, it is located at:
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.
This example disables all protection. This is recommended for debugging purposes only. This is not recommended for production environments.
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.
Verify that the DAD parameter error_style is not set to "GatewayDebug" in a production environment. This setting produces information as specified by the "Gateway" setting and server configuration related information. This mode is only meant for debugging purposes. Displaying server internal variables could produce a security risk.
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:
wdbsvr.app. The location of your Oracle9i Application Server installation is <ORACLE_HOME>. For UNIX, the configuration file is located at:
Only adminuser1 and adminuser2 can access the Admin Pages.
Everyone can access the Admin Pages. This is only recommended for development environments.
[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.
You are then prompted by a Basic HTTP Authentication dialog box to enter your username and password.
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:
wdbsvr.app. The location of your Oracle9i Application Server installation is <ORACLE_HOME>.For UNIX, the configuration file is located at:
[WVGATEWAY]section, typically located at the top of the file, locate the
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
wdbsvr.appfile, the Oracle Portal 3.0 gateway security parameters are displayed similar to the following:
administrators = all
adminPath = /admin_/
admindad = portal30
portal30with the name of your Oracle Portal DAD.