Informix Guide to SQL: Tutorial Creating and Using SPL 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.

To register a routine in the database, an authorized 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:

• Any user with the DBA privilege can register a routine with or without the DBA keyword in the CREATE statement.

For an explanation of the DBA keyword, see DBA Privileges for Executing a Routine.

• A user who does not have the DBA privilege needs the Resource privilege to register an SPL routine. The creator is the owner of the routine.

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

A DBA must give other users the Resource privilege needed to create routines. The DBA can also revoke the Resource privilege, preventing the revokee from creating further routines.

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

The Execute privilege enables users to invoke a routine. The routine might be invoked 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:

• By default, any user with the DBA privilege can execute any routine in the database.
• If the routine is registered with the qualified CREATE DBA FUNCTION or CREATE DBA PROCEDURE statements, only users with the DBA privilege have a default Execution privilege for that routine.
• If the database is not ANSI compliant, user public (any user with Connect database privilege) automatically has the Execute privilege to a routine that is not registered with the DBA keyword.
• In an ANSI-compliant database, the procedure owner and any user with the DBA privilege can execute the routine without receiving additional privileges.

Routines have the following GRANT and REVOKE requirements:

• The DBA can grant or revoke the Execute privilege to any routine in the database.
• The creator of a routine can grant or revoke the Execute privilege on that particular routine. The creator forfeits the ability to grant or revoke by including the AS grantor clause with the GRANT EXECUTE ON statement.
• Another user can grant the Execute privilege if the owner applied the WITH GRANT keywords in the GRANT EXECUTE ON statement.

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

• A routine in an ANSI-compliant database
• A database with the NODEFDAC environment variable set to yes
• A routine that was created with the DBA keyword

An owner can restrict the Execute 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 can still execute the routine and can grant the Execute privilege to specific users, if applicable.

Important: If you must explicitly grant the Execute privilege on an SPL function that you create with a COMMUTATOR or NEGATOR modifier, you must also explicitly grant the Execute privilege on the commutator or negator modifier before the grantee can use either. You cannot specify COMMUTATOR or NEGATOR modifiers with SPL procedures.

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:

CREATE FUNCTION greater(y PERCENT, z PERCENT)
RETURNS BOOLEAN
NEGATOR= less(y PERCENT, z PERCENT)
...
CREATE FUNCTION less(y PERCENT, z PERCENT)
RETURNS BOOLEAN
NEGATOR= greater(y PERCENT, z PERCENT)
...

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

REVOKE EXECUTE ON greater FROM PUBLIC
REVOKE EXECUTE ON less FROM PUBLIC
GRANT ROLE accounting TO mary, jim, ted
GRANT EXECUTE ON greater TO accounting
GRANT EXECUTE ON less TO accounting

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.

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:

• Tables and columns
• User-defined data types
• Other routines executed by the routine

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

CREATE PROCEDURE promo()

	.

	.

	.

	CREATE TABLE newcatalog 
	(
	catlog_num INTEGER
	cat_advert VARCHAR(255, 65)
	cat_picture BLOB
	) ;

	CREATE TABLE dawn.mailers
	(
	cust_num INTEGER

	interested_in SET(catlog_num INTEGER)
	);
END PROCEDURE;

User julia runs the routine, which creates the table newcatalog. Because no owner name qualifies table name newcatalog, the routine owner (howie) owns newcatalog. By contrast, the qualified name dawn.maillist identifies dawn as the owner of maillist.

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 EXECUTION 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 EXECUTION ON clust_catalog(CHAR) to marty;

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