INFORMIX
Informix Guide to SQL: Tutorial
Chapter 14: Creating and Using SPL Routines
Home Contents Index Master Index New Book

Privileges on Routines

 Privileges differentiate users who can create a routine from users who can execute a routine. Some privileges accrue as part of other privileges. For example, the DBA privilege includes permissions to create routines, execute routines, and grant these privileges to other users.

Privileges for Registering a Routine

To register a routine in the database, a qualified user wraps the SPL commands in a CREATE FUNCTION or CREATE PROCEDURE statement. The database server stores a registered SPL routine internally. The following users qualify to register a new routine in the database:

    A user who does not have the DBA privilege cannot use the DBA keyword to register the routine.

A DBA and the routine owner can cancel the registration with the DROP FUNCTION or DROP PROCEDURE statement.

Privileges for Executing a Routine

The Execute privilege enables users to invoke a routine. The routine might be invokded by the EXECUTE or CALL statements, or by using a function in an expression.The following users have a default Execute privilege, which enables them to invoke a routine:

Granting and Revoking the Execute Privilege

 Routines have the following GRANT and REVOKE requirements:

A DBA or the routine owner must explicitly grant the Execution privilege to non-DBA users for the following conditions:

An owner can restrict the Execution privilege on a routine even though the database server grants that privilege to public by default. To do this, issue the REVOKE EXECUTION ON.... PUBLIC statement. The DBA and owner still can Execute the routine and can grant the Execution privilege to specific users, if applicable.

The following example demonstrates both limiting privileges for a function and its negator to one group of users. Suppose you create the following pair of negator functions:

By default, any user can execute both the function and negator. The following statements allow only accounting to execute these functions:

A user might receive the Execute privilege accompanied by the WITH GRANT option authority to grant the Execute privilege to other users. If a user loses the Execute privilege on a routine, the Execute privilege is also revoked from all users who were granted the Execute privilege by that user.

For more information, see the GRANT and REVOKE statements in 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:

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. For example, assume tony registers an SPL routine that creates two tables, using the following statements:

User marty runs the routine, 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 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.

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 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 effect the called 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 SPL 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(). 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 cluster_catalog() is a DBA procedure, as the following registering and grant statements show:

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



Informix Guide to SQL: Tutorial, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.