23.2.1 Execute Authority on Stored Objects

The secret to achieving this level of transaction integrity is the concept of execute authority (also known as run authority). Instead of granting to a role or user the authority to update a table, you grant privileges to that role/user only to execute a procedure. This procedure controls and provides access to the underlying data structures (see Figure 23.2). The procedure is owned by a separate Oracle RDBMS account, which in turn is granted the actual update privileges on those tables needed to perform the transaction. The procedure therefore becomes the gatekeeper for the transfer transaction. The only way a program (whether an Oracle Forms application or a Pro*C executable) can execute the transfer is through the procedure, so the transaction integrity can be guaranteed.

Figure 23.2: Transaction integrity with a PL/SQL code layer

In order for a stored procedure or package to compile (which occurs at the time of creation or replacement), the owner of that program must explicitly be granted all the necessary privileges to any objects referenced by the program. These privileges may not be granted simply to a role. If, for example, procedure disp_customer issues a SELECT statement against the customer table, then the owner of disp_customer must be granted a minimum of SELECT privileges on that table with an explicit command:

GRANT privilege ON customer TO procedure_owner;

Requiring direct grants to individual Oracle users sometimes causes difficulty in environments where grants are controlled carefully and efficiently through the use of roles. After all, the whole idea of the role is to allow DBAs to move away from the onerous task of directly granting privileges to a myriad of individual users. Yet every one of those users must execute the stored procedures underlying an application. What's a DBA to do?

In some Oracle shops, a single account (user), which I'll call STOROBJ, is created in the production environment. This user owns all stored objects and has update privileges on all tables, as is appropriate. Other people who use the applications might have SELECT privileges on a number of tables, and perhaps even update privileges on certain tables that are maintained through Oracle Forms applications. But all complex transactions are bundled into stored procedures and stored functions, and users are granted EXECUTE authority only to those stored programs. If an Oracle Forms screen needs to perform a funds transfer, it calls the stored procedure and displays confirmation information on the screen. The logic and authority would, however, reside in the database and be controlled tightly by the STOROBJ account.

To grant execute authority on a stored object, you issue the following command (in SQL*Plus, SQL*DBA, or another product that supports the issuing of DDL calls):

SQL> GRANT EXECUTE ON object_name TO user_or_role;

NOTE: You can only grant EXECUTE authority to an entire package, not to individual programs within a package. Suppose that you build a single package to encapsulate all SQL-based access to a table; this package contains procedures which perform inserts, updates, and deletes, as well as cursors and functions to query data. There is no way to grant "read-only" EXECUTE authority. A schema can either run all programs in a package or none. As a result, you might want to build a single package encapsulating all SQL against a table or view, but then build additional packages on top of that "base" package which act as filters for certain classes of activity.

23.2.2 Creating Synonyms for Stored Objects

You should create public synonyms for each of the stored objects so that individual developers do not have to reference the STOROBJ account name in order to access the programs. Suppose, for example, that we have a funds transfer procedure, as follows:

PROCEDURE xfer_funds
   (from_account_in IN NUMBER, to_account_in IN NUMBER,
    transfer_amount_in IN NUMBER, transfer_status_out OUT NUMBER);

Without a synonym, you would have to execute this procedure in this way:

storobj.xfer_funds (:old_acct, :new_acct, :xfer_amt, :xfer_stat);

You should always avoid hardcoding the name of the owner of an object, be it a procedure or a table. What if you need to change the account name? What if you decide to move the procedure to an entirely different server and then need to execute this procedure as an RPC? Whenever you are working with stored objects, you should create synonyms, as follows:

SQL> CREATE PUBLIC SYNONYM xfer_funds FOR storobj.xfer_funds;

Now I can call the transfer procedure without making reference to its owner:

xfer_funds (:old_acct, :new_acct, :xfer_amt, :xfer_stat);

While it is possible to create a synonym for a standalone procedure, it is not possible to create a synonym for a packaged procedure or function. You can, however, create a synonym for the package itself. In effect, synonyms can be used to avoid having to provide the schema name of an object; you cannot use it to avoid specifying the package name in which a program is defined.