Privileges for Registering a Routine
To register a routine in the database, a qualified user issues a CREATE FUNCTION or CREATE PROCEDURE statement. 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 must grant the Resource privilege required for any other user to create a routine. The DBA can revoke the Resource privilege, preventing the revoke from creating further routines.
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 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:
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 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 who were granted the Execute privilege by that user.
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:
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.
|
