Setting Privileges for a Routine

A user must have the following privileges to issue a CREATE FUNCTION or CREATE PROCEDURE statement that registers a UDR in the database:

• Database-level privilege
• Language-level privilege

After you register the UDR, you can assign routine-level privileges. For information about how to assign privileges, refer to the GRANT statement in the IBM Informix: Guide to SQL Syntax.

Database-Level Privilege

Database-level privileges control the ability to extend the database by registering or dropping a UDR. 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 FUNCTION or CREATE PROCEDURE statement.
• A non-DBA user needs the Resource privilege to register a routine.

  The creator has owner privileges on the routine. A user who does not have the DBA privilege cannot use the DBA keyword in the CREATE FUNCTION or CREATE PROCEDURE statement to register the routine.

A DBA must grant the Resource privilege required for any non-DBA user to create a routine. The DBA can revoke the Resource privilege, which prevents that user from creating additional routines.

A DBA or the routine owner can cancel the registration with the DROP ROUTINE, DROP FUNCTION, or DROP PROCEDURE statement. A DBA or routine owner can register a modification to the routine with the ALTER ROUTINE, ALTER FUNCTION, or ALTER PROCEDURE statement.

Language-Level Privilege

The language-level Usage privilege controls the ability to write a UDR in a particular UDR language. This privilege needs to be granted by user informix or by another user who has been granted the DBA privilege with WITH GRANT OPTION.

UDR languages have the following GRANT and REVOKE requirements for the Usage privilege:

• The DBA can grant or revoke the Usage privilege to any language that the database server supports.
• Another user can grant the Usage privilege if the DBA applied the WITH GRANT keywords in the GRANT EXECUTE ON statement.

The following GRANT statement grants Usage privilege on Java UDRs to the user named dorian:

GRANT USAGE ON LANGUAGE JAVA TO dorian

By default, the database server:

• Does not grant Usage privilege on external languages to PUBLIC
• Does grants Usage privilege on SPL to PUBLIC

For more information, see the description of privileges in the IBM Informix: Database Design and Implementation Guide and the description of the GRANT statement in the IBM Informix: Guide to SQL Syntax

Routine-Level Privilege

When you register a UDR, you automatically receive the Execute privilege on that routine. The Execute privilege allows you to invoke the UDR. For information about allowing other users to execute your routine, see Assigning the Execute Privilege to a Routine.