Registering a User-Defined Routine

A database server administrator (DBSA), the user informix by default, can implement security measures that establish which users can register external routines. This prevents unauthorized users from registering the external routines.

To grant a user privileges to create or drop a UDR that has the EXTERNAL clause, the DBSA must set the IFX_EXTEND_ROLE configuration parameter to On. When this parameter is set to On, the EXTEND role is operational and the DBSA can grant a user privileges to create or drop a external routine that has the EXTERNAL clause.

After setting the IFX_EXTEND_ROLE configuration parameter to On, the DBSA uses the following syntax to grant and revoke privileges to and from specific users.

• GRANT extend To username
• REVOKE extend From username

If the IFX_EXTEND_ROLE configuration parameter is set to Off, the EXTEND role is not operational and any user can register external routines.

For more information on the EXTEND role, see the IBM Informix: Dynamic Server Administrator's Guide and the IBM Informix: Guide to SQL Syntax.

If you have privileges to register a UDR or if the IFX_EXTEND_ROLE configuration parameter is set to Off and you are responsible for registering UDRs, you must register UDRs in all databases in which they will be used, unless the database is on the secondary database server of an HDR pair.

The database server recognizes the following SQL statements for the registration of UDRs in the database:

• The CREATE FUNCTION statement registers UDRs that return a value.
• The CREATE PROCEDURE statement registers UDRs that do not return a value.

The following example shows the syntax of a CREATE FUNCTION statement:

CREATE FUNCTION func_name(parameter_list) RETURNS ret_type
   WITH (NOT VARIANT)
   EXTERNAL NAME 'pathname'
   LANGUAGE C

This SQL statement provides the following information to the database:

• The name, func_name, and owner of the support function
• An optional specific name for the support function (not shown)
• The data types of the parameters, parameter_list, and return value, ret_type, of the support function
• The location, pathname, of the source code for the support function
• The language of the support function: LANGUAGE C.
• The routine modifier NOT VARIANT that indicates that the function does not return different results with different arguments.