Oracle Web Applications: PL/SQL Developer's Intro

Creating Dynamic Resources

Now that we have a basic understanding of how the WRB uses cartridges to execute different kinds of resources, let’s look at the PL/SQL cartridge in more detail.

The PL/SQL cartridge allows us to use PL/SQL procedures to create dynamic resources. As with the CGI interface, these resources are called with a URL. Unlike CGI, a PL/SQL cartridge maintains a persistent connection to a database, so it executes almost instantaneously. In addition to producing lightning-fast performance, the cartridge resolves the two thorny problems with the CGI interface: connection management and parameter passing.

Following our discussion of the advantage of this cartridge, we’ll bring up a few security caveats to keep in mind when using cartridges.

Connection Management

WRBX processes connect to one particular account within a database upon initialization. The configuration for the connection is divided into two parts: the Database Access Descriptor and the PL/SQL agent.

Database Access Descriptor

A Database Access Descriptor (DAD) creates a unique alias for a database that is to be accessed over the Web. The DAD contains all the information needed to connect to the database, including the database name, its ORACLE_HOME directory, and its SQL*Net V2 service name. Figure 4.5 shows OAS’s DAD configuration page.

Figure 4-5. Configuration screen for a DAD

PL/SQL agent

The PL/SQL agent is a unique alias for a database account owned by a particular DAD that makes the account’s procedures and packages accessible over the Web. This includes procedures and packages owned directly by the account, as well as those owned by other accounts that have granted EXECUTE permission to the schema.

The agent consists of:

• A unique name

• Account login information, including:

  • The DAD name for the database that owns the account

  • The account name and password

• Web-specific configuration information, including:

  • The URL for the error page that is displayed if the agent cannot execute a request

  • Authorized ports on which the agent accepts requests

Figure 4.6 shows OAS’s agent configuration page.

Figure 4-6. Configuration screen for a PL/SQL agent

Figure 4.7 illustrates the relationship between a database, a DAD, and a PL/SQL agent.

Figure 4-7. The relationship between the DAD and PL/SQL agent

Parameter Passing

In addition to simplifying connection management, the PL/SQL agent also simplifies parameter passing by automatically associating each parameter with one of the procedure’s formal parameters. As noted in the previous chapter, you can pass values in a query string of a hyperlink or as a named field in a form. In either case, the PL/SQL agent makes sure that these values are used to execute the PL/SQL procedure specified in the resource name section.

Security Issues with the PL/SQL Cartridge

For all its benefits, the PL/SQL cartridge can open a major security hole. When you create an agent for an account, every procedure in the account is fair game for web execution. For example, if you create a DAD and agent on your HR schema so that you can write a phone list, you are also exposing procedures like give_raise or fire_employee. Savvy (or downright evil!) users who understand how to execute these procedures can do so with impunity.

You can use database privileges to prevent this sort of abuse. The scheme is very similar to using database roles to limit access to privileged tables. Basically, you assign the DAD and PL/SQL agent to a minimally privileged account (maybe it only has CONNECT privilege). You then use a combination of the GRANT EXECUTE and CREATE SYNONYM commands to allow the schema to execute procedures owned by privileged accounts. Figure 4.8 illustrates how this works.

Figure 4-8. A minimally privileged PL/SQL agent

There are several advantages to this approach. First, it guarantees that the only code you have explicitly made available is accessible from a web browser. Second, the use of grants increases security because, as any DBA will tell you, the fewer publicly available privileges the better, especially if the schema contains sensitive information or is highly privileged. Finally, it reduces a lot of administrative overhead necessary to get an application up and running because you don’t have dozens of agents to maintain.

To use the grant method, follow these steps:

1. Log into the account that owns the application code you want to execute.

2. Use the GRANT EXECUTE command to make the procedure or package available to the agent account.

3. Log into the agent account.

4. Create a synonym that points to the procedure in the other schema; the synonym does not have to have the same name as the original procedure or package.

5. Use the synonym name in the URL to execute the procedure.