Extending Informix Dynamic Server 2000 Managing a User-Defined Routine

The Execute privilege enables users to invoke a user-defined routine. You might invoke the UDR from the EXECUTE or CALL statements or from a function in an expression. By default, the following users have Execute privilege, which enables them to invoke a UDR:

• 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 the Execution privilege for that routine by default.
• 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.

To control Execute privilege on a user-defined routine, use the EXECUTE ON clause of the GRANT and REVOKE statements. The database server stores privileges for user-defined routines in the sysprocauth system catalog table.

User-defined routines have the following GRANT and REVOKE requirements for Execute privilege:

• The DBA can grant the Execute privilege to or revoke it from 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 Execution 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 registered 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 so, issue the REVOKE EXECUTE ON...PUBLIC statement. The DBA and owner can still execute the routine and can grant the Execute privilege to specific users, if applicable.

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 to whom that user granted the Execute privilege.

The following example shows an equal() function defined for a user-defined data type and the GRANT statement to enable user mary to execute this variation of the equal() function:

CREATE FUNCTION equal (arg1 udtype1, arg2 udtype1)

RETURNING BOOLEAN

EXTERNAL NAME 
"/usr/lib/udtype1/lib/libbtype1.so(udtype1_equal)"

LANGUAGE C

END FUNCTION;


GRANT EXECUTE ON equal(udtype1, udtype1) to mary

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 the Informix Guide to SQL: Syntax.

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

A routine can reference the following objects:

• 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.

In the course of routine execution, the owner of the routine, not the user who runs the routine, owns any unqualified objects that the routine creates.

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

CREATE PROCEDURE promo()



	CREATE TABLE hotcatalog
	(
		catlog_num INTEGER
		cat_advert VARCHAR(255, 65)
		cat_picture BLOB
	) PUT cat_picturein sb1;

	CREATE TABLE libby.maillist
	(
		cust_num INTEGER

		interested_in SET(catlog_num INTEGER)
	);
END PROCEDURE;

Suppose the user tony executes the CREATE PROCEDURE statement 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.

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. However, a DBA can 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 the database server exits from a DBA routine, it 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 from on page 12-6 with the DBA keyword, as follows:

CREATE DBA PROCEDURE promo()

EXTERNAL NAME create_mo_catalog



END PROCEDURE;

Although tony owns the routine, if marty runs it, 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:

strcopy(statement, "CREATE INDEX stmt");
EXEC SQL 
create  cluster  index  c_clust_ix  on  catalog  (catalog_num);

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

EXECUTE PROCEDURE cluster_catalog(hotcatalog) 

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

CREATE DBA PROCEDURE dbspace_cleanup(loc CHAR)
	EXTERNAL NAME ... 
	LANGUAGE C
END PROCEDURE 
CREATE PROCEDURE cluster_catalog(catalog CHAR)
	EXTERNAL NAME ...
LANGUAGE C
END PROCEDURE
GRANT EXECUTION ON dbspace_cleanup(CHAR) to marty;

User marty runs dbpace_cleanup(). Index c_clust_ix is created by a non-DBA routine. Therefore 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:

CREATE PROCEDURE dbspace_cleanup(loc CHAR)
	EXTERNAL NAME ... 
	LANGUAGE C
END PROCEDURE 
CREATE DBA PROCEDURE cluster_catalog(catalog CHAR)
	EXTERNAL NAME ...
LANGUAGE C
END PROCEDURE
GRANT EXECUTION ON cluster_catalog(CHAR) to marty;

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