r> END FUNCTION;
User mary does not have permission to execute any other user-defined routine named equal().

For more information, see the GRANT and REVOKE statements in Chapter 1 of the Informix Guide to SQL: Syntax.

Privileges on Objects Associated with a Routine

The database server checks the existence of any referenced objects and verifies that the user invoking the routine has the necessary privileges to access the referenced objects. For example, if a user executes a routine that updates data in a table, the user must have the Update privilege for the table or columns referenced in the routine.

Objects referenced by a routine include:

When the owner of a routine grants the Execute privilege, some privileges on objects automatically accompany the Execute privilege. A GRANT EXECUTE ON statement confers to the grantee any table-level privileges that the grantor received from a GRANT statement that contained the WITH GRANT keywords.

The owner of the routine, and not the user who runs the routine, owns the unqualified objects created in the course of executing the routine.

Figure 3-1 shows an SPL procedure called promo() that creates two tables, hotcatalog and libby.mailers.

Figure 3-1
CREATE PROCEDURE Example

Suppose the user tony executes the CREATE PROCEDURE statement in Figure 3-1 to register the SPL promo() procedure. The user marty executes the promo() routine with an EXECUTE PROCEDURE statement, which creates the table hotcatalog. Because no owner name qualifies table name hotcatalog, the routine owner (tony) owns hotcatalog. By contrast, the qualified name libby.maillist identifies libby as the owner of maillist.

Executing a Routine as DBA

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

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.

Effect of DBA Privileges on Objects and Nested Routines

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 from Figure 3-1 with the DBA keyword, as follows:

Although tony owns the routine, if marty runs it, then marty owns table hotcatalog. User libby owns libby.maillist 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. Procedure dbspace_cleanup() executes procedure cluster_catalog(). Procedure cluster_catalog() creates an index. The C-language source for cluster_catalog() includes the following statements:

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

Assume tony registered dbspace_cleanup() as a DBA procedure, and cluster_catalog() is registered without the DBA keyword, as follows:

User marty runs dbpace_cleanup(). Index c_clust_ix is created by a non-DBA routine, so tony, who owns both routines, also owns c_clust_ix. By contrast, marty owns index c_clust_ix if cluster_catalog() is a DBA procedure, as in the follows registering and grant statements:

The dbspace_cleanup() procedure need not be a DBA procedure to call a DBA procedure.



Extending INFORMIX-Universal Server: User-Defined Routines, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.