UNDER Privilege

You own any named ROW type that you create. If you want other users to be able to create subtypes under this named ROW type, you must grant to these users the Under privilege on your named ROW type.

If you want another user named kathy to be able to create a subtype under this named ROW type, you must grant the Under privilege on this named ROW type to user kathy:

Now user kathy can create another ROW type under the rtype1 ROW type, even though kathy is not the owner of the rtype1 ROW type:

For more about named ROW types, see CREATE ROW TYPE , and the discussion of data types in the IBM Informix Guide to SQL: Reference and the IBM Informix Database Design and Implementation Guide.

Routine-Level Privileges

When you create a user-defined routine (UDR), you become owner of the UDR and you automatically receive the Execute privilege on that UDR.

The Execute privilege allows you to invoke the UDR with an EXECUTE FUNCTION or EXECUTE PROCEDURE statement, whichever is appropriate, or with a CALL statement in an SPL routine. The Execute privilege also allows you to use a user-defined function in an expression, as in this example:

SELECT * FROM table WHERE in_stock(partnum) < 20

Routine-Level Privileges:
 
|--GRANT ON----------------------------------------------------->
 
>--+-SPL_routine--------------------------------------------------------------+--|
   |  (1)                                                                     |
   +--------+-PROCEDURE-+--routine--(--+---------------------------------+--)-+
   |        +-FUNCTION--+              |                            (2)  |    |
   |        '-ROUTINE---'              '-| Routine Parameter List |------'    |
   |                                            (3)                           |
   '-SPECIFIC--+-ROUTINE---+--| Specific Name |-------------------------------'
               +-FUNCTION--+
               '-PROCEDURE-'

Notes:

Dynamic Server only
See page Routine Parameter List
See page Specific Name

Element	Description	Restrictions	Syntax
routine	A user-defined routine	Must exist	Database Object Name, p. Database Object Name
SPL_routine	An SPL routine	Must be unique in the database	Database Object Name, p. Database Object Name

Whether you must grant the Execute privilege explicitly depends on the following conditions:

If you have DBA-level privileges, you can use the DBA keyword of CREATE FUNCTION or CREATE PROCEDURE to restrict the default Execute privilege to users with the DBA privilege. You must explicitly grant the Execute privilege on that UDR to users who do not have the DBA privilege.
If you have the Resource database-level privilege but not the DBA privilege, you cannot use the DBA keyword when you create a UDR:
- When you create a UDR in a database that is not ANSI compliant, PUBLIC can execute that UDR. You do not need to issue a GRANT statement for other users to receive the Execute privilege.
- Setting the NODEFDAC environment variable to yes prevents PUBLIC from executing the UDR until you explicitly grant the Execute privilege.
In an ANSI-compliant database, the creator of a UDR must explicitly grant the Execute privilege on the UDR for other users to be able to execute it.

In Dynamic Server, if two or more UDRs have the same name, use a keyword from this list to specify which of those UDRs a user list can execute.

Keyword: UDR that the User Can Execute
SPECIFIC: The UDR identified by specific name
FUNCTION: Any function with the specified routine name (and parameter types that match routine parameter list, if specified)
PROCEDURE: Any procedure with the specified routine name (and parameter types that match routine parameter list, if specified)
ROUTINE: Functions or procedures with the specified routine name (and parameter types that match routine parameter list, if specified)

If both a user-defined function and a user-defined procedure of Dynamic Server have the same name and the same list of parameter data types, you can grant the Execute privilege to both with the keyword ROUTINE.

To limit the Execute privilege to one routine among several that have the same identifier, use the FUNCTION, PROCEDURE, or SPECIFIC keyword.

To limit the Execute privilege to a UDR that accepts certain data types as arguments, include the routine parameter list or use the SPECIFIC keyword to introduce the specific name of a UDR.

In Dynamic Server, if an external function has a negator function, you must grant the Execute privilege on both the external function and its negator function before users can execute the external function.

A user must have the Usage privilege on a language to register a user-defined routine of Dynamic Server that is written in that language.

Langage-Level Privileges (IDS)

Dynamic Server also supports language-level privileges, which specify the programming languages of UDRs that users who have been granted Usage privileges for a given language can register in the database. This is the syntax of the USAGE ON LANGUAGE clause for specifying language-level privileges:

Language-Level Privileges:
 
|--USAGE ON LANGUAGE--SPL---------------------------------------|

In this release of Dynamic Server, only the SPL keyword is supported in the USAGE ON LANGUAGE clause.

When a user executes the CREATE FUNCTION or CREATE PROCEDURE statement to register a UDR that is written in SPL, the database server verifies that the user has the Usage privilege on the language in which the UDR is written. (In this release of Dynamic Server, the C language and the Java language do not require Usage privilege.)

For information on other privileges that these statements require, see CREATE FUNCTION and CREATE PROCEDURE.