DBA privileges for executing a routine

If a DBA creates a routine using the DBA keyword, the database server automatically grants the Execute privilege only to other users with the DBA privilege. A DBA can, however, explicitly grant the Execute privilege on a DBA routine to a user who does not have the DBA privilege.

When a user executes a routine that was registered with the DBA keyword, that user assumes the privileges of a DBA for the duration of the routine. If a user who does not have the DBA privilege runs a DBA routine, the database server implicitly grants a temporary DBA privilege to the invoker. Before exiting a DBA routine, the database server implicitly revokes the temporary DBA privilege.

Objects created in the course of running a DBA routine are owned by the user who executes the routine, unless a statement in the routine explicitly names someone else as the owner. For example, suppose that tony registers the promo() routine with the DBA keyword, as follows:

CREATE DBA PROCEDURE promo()
   . . .
   CREATE TABLE catalog 
   . . .
   CREATE TABLE libby.mailers
   . . .
END PROCEDURE;

Although tony owns the routine, if marty runs it, then marty owns the catalog table, but user libby owns libby.mailers because her name qualifies the table name, making her the table owner.

A called routine does not inherit the DBA privilege. If a DBA routine executes a routine that was created without the DBA keyword, the DBA privileges do not affect the called routine.

If a routine that is registered without the DBA keyword calls a DBA routine, the caller must have Execute privileges on the called DBA routine. Statements within the DBA routine execute as they would within any DBA routine.

The following example demonstrates what occurs when a DBA and non-DBA routine interact. Suppose procedure dbspc_cleanup() executes another procedure clust_catalog(). Suppose also that the procedure clust_catalog() creates an index and that the SPL source code for clust_catalog() includes the following statements:

CREATE CLUSTER INDEX c_clust_ix ON catalog (catalog_num);

The DBA procedure dbspc_cleanup() invokes the other routine with the following statement:

EXECUTE PROCEDURE clust_catalog(catalog);

Assume tony registered dbspc_cleanup() as a DBA procedure and clust_catalog() is registered without the DBA keyword, as the following statements show:

CREATE DBA PROCEDURE dbspc_cleanup(loc CHAR)
CREATE PROCEDURE clust_catalog(catalog CHAR)
GRANT EXECUTE ON dbspc_cleanup(CHAR) to marty;

Suppose user marty runs dbspc_cleanup(). Because index c_clust_ix is created by a non-DBA routine, tony, who owns both routines, also owns c_clust_ix. By contrast, marty would own index c_clust_ix if clust_catalog() is a DBA procedure, as the following registering and grant statements show:

CREATE PROCEDURE dbspc_cleanup(loc CHAR);
CREATE DBA PROCEDURE clust_catalog(catalog CHAR);
GRANT EXECUTE ON clust_catalog(CHAR) to marty;

Notice that dbspc_cleanup() need not be a DBA procedure to call a DBA procedure.