This chapter describes how to set up the database and PL/SQL, for best security. It covers the following topics:
See Also:
For more information about mod_plsql configuration parameters, refer to the Oracle Fusion Middleware Administrator's Guide for Oracle HTTP Server.mod_plsql is a PL/SQL gateway that can be used to access any procedure to which your database account has rights. Since this also includes access to all packages granted to PUBLIC, it is the responsibility of the PL/SQL application to ensure that mod_plsql is not used to access unauthorized packages or procedures. By default, mod_plsql does not allow access to:
Sensitive schemas. For example, SYS.*.
Packages known to be sensitive, but to which PUBLIC has access. For example, OWA_*, DBMS_*, and UTL_* packages.
Procedure names that contain special characters in the URL.
This default model of explicitly denying access to specific packages from being executed directly using mod_plsql is sufficient for most correctly designed PL/SQL applications. If this level of security is not adequate for your PL/SQL application, it is highly recommended that you follow one or more of the following techniques to ensure that your PL/SQL application is secure:
Defining the PlsqlRequestValidationFunction Directive in mod_plsql
Adding More Rules to the PlsqlExclusionList Directive in mod_plsql
mod_plsql provides a DAD parameter directive called PlsqlRequestValidationFunction which enables you to allow or disallow further processing of a requested procedure. This is useful for implementing tight security for your PL/SQL application by blocking package or procedure calls that should not be allowed to run from the DAD.
The function defined by this parameter must have the following prototype:
boolean function_name (procedure_name IN varchar2)
When invoked, the argument 'procedure_name' will contain the name of the procedure that the request is trying to run.
For example, if all the PL/SQL application procedures that can be called from a browser are inside the package "mypkg", then a simple implementation of this function can be as shown in Example 2-1:
Example 2-1 Implementation of Request Validation Function to Block Procedures
boolean my_validation_check (procedure_name varchar2)
is
begin
if (upper(procedure_name) like upper('myschema.mypkg%')) then
return TRUE;
else
return FALSE;
end if;
end;
Tips:
It is highly recommended that you implement this function such that it only allows requests that belong to your application, and can be called from a browser.
As this function will be called for every request, ensure that this function is made performant. Some recommendations are:
Name your PL/SQL packages such that all procedures accessible from the Web are in a small set of known packages, and these packages do not define procedures that should not be accessible directly from the Web browser. With a good naming convention, the implementation of the validation function can be similar to Example 2-1.
If your implementation performs a table lookup to determine the packages/procedures that should be allowed to run, the performance can be further improved if you pin the cursor in the shared pool.
mod_plsql provides a DAD configuration parameter called PlsqlExclusionList, which can be used to disallow execution of procedures with specific patterns, directly from a browser URL. The specified pattern is case insensitive and allows a wildcard pattern of "*", which means "zero or more occurrences of any set of characters". The default patterns that are not accessible from a direct URL are sys.*, dbms_*, utl_*, owa_util.*, owa_*,ctxsys.*, mdsys.*, htp.*, and htf.*. Starting from Oracle Application Server 10g Release 2 (10.1.2), the default built-in exclusion list remains in effect even if the user has configured additional rules using PlsqlExclusionList. In previous versions, if the PlsqlExclusionList directive was overridden in the DAD configuration, the default settings no longer applied.
In addition to the patterns that are specified with this directive, mod_plsql also disallows any URLs that contain special characters such as tabs, new lines, carriage returns, single quotation marks ('), reverse slash (\), and so on.
The directive does not protect against database synonyms that might have been created to one or more of the excluded packages. If synonyms exist, and are vulnerable, then you have to explicitly add the synonym to the list of excluded patterns.
For details on the PlsqlExclusionList parameter, refer to the "mod_plsql" section in the Oracle Fusion Middleware Administrator's Guide for Oracle HTTP Server.
Caution:
Setting thePlsqlExclusionList directive to #NONE# disables all protection, and should not be used for an active Web site.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_INSTANCE/config/OHS/ohs1/mod_plsql
(Windows) ORACLE_INSTANCE\config\OHS\ohs1\mod_plsql
Where ORACLE_INSTANCE is the location of your Oracle HTTP Server installation.
To ensure the best security for user-defined PL/SQL procedures that are granted to PUBLIC, specify the user settings with the PlsqlExclusionList directive in the dads.conf file as shown in Example 2-2. For more information, see the Oracle Fusion Middleware Administrator's Guide for Oracle HTTP Server.
Applications using Custom Authentication can choose to deny access to restricted packages or procedures in their implementation of the AUTHORIZE procedure.
It can be useful to separate the schema containing your application objects (tables and procedures) from the schema against which you will run mod_plsql. This way, the user that mod_plsql connects as can be prevented from any direct access to your tables and other database objects, except through APIs that are granted to that user. Synonyms can be used to make the procedures executable without schema prefixes.
A SQL Injection attack occurs when a malicious user is able to modify a SQL command being run against a back-end database. The modification may be to read additional data, cause a validation check to succeed when it should fail, write records, or otherwise affect the smooth running of an application.
Many applications are built on databases and often user inputs are used as part of database queries. If the application has not been coded carefully, it is sometimes possible to change the query submitted to the database by careful construction of input.
To prevent such SQL Injection attacks, wherever possible, do not use dynamic SQL created from any user supplied input.
If user supplied input has to be used for dynamic SQL, then the input must be adequately filtered by the application before using the input in any dynamic SQL. For example, if the user supplies a table name as an input parameter, then the underlying application code should ensure that the only acceptable value for this parameter is a valid table name.
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 2-1.
Table 2-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 OracleAS Single Sign-On Server. |
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.
Oracle HTTP 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.
mod_plsql supports 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.
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 is done using OWA_CUSTOM and cannot be combined with dynamic username/password authentication. Custom authentication needs 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 different locations:
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.
For example, when using Custom OWA, an authorization 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 2-2 summarizes the parameter values.
Table 2-2 Custom Authentication Modes and Callback Functions
| Mode | Access Control Scope | Callback Function |
|---|---|---|
|
Global OWA |
All packages |
|
|
Custom OWA |
All packages |
|
|
Per package |
Specified package |
|
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