Privileges for registering a routine

To register a routine in the database, an authorized user wraps the SPL commands in a CREATE FUNCTION or CREATE PROCEDURE statement. The database server stores a registered SPL routine internally. 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 statement.

    For an explanation of the DBA keyword, see DBA privileges for executing a routine.

  • A user who does not have the DBA privilege needs the Resource privilege to register an SPL routine. The creator is the owner of the routine.

    A user who does not have the DBA privilege cannot use the DBA keyword to register the routine.

    A DBA must give other users the Resource privilege needed to create routines. The DBA can also revoke the Resource privilege, preventing the user from creating further routines.

  • Besides holding the DBA privilege or the Resource privilege on the database in which the UDR is registered, the user who creates a UDR must also hold the Usage privilege on the programming language in which the UDR is written. These SQL statements can grant language-level Usage privileges for specific programming languages:
    • GRANT USAGE ON LANGUAGE C
    • GRANT USAGE ON LANGUAGE JAVA
    • GRANT USAGE ON LANGUAGE SPL

    Besides an individual user, the grantee of these privileges can also be a user-defined role, or the PUBLIC group. After language-level Usage privileges are granted to a role, any user who holds that role can enable all the access privileges of the role by using the SET ROLE statement of SQL to specify that role as the current role.

For external routines written in the C language or the Java™ language, if the IFX_EXTEND_ROLE configuration parameter is enabled, only users to whom the DBSA has granted EXTERNAL role has been granted can register, drop, or alter external UDRs or modules. This parameter is enabled by default. By setting the IFX_EXTEND_ROLE configuration parameter to OFF or to 0, the DBSA can disable the requirement of holding the EXTEND role for DDL operations on modules and external UDRs. This security feature has no effect, however, on SPL routines.

In summary, a user who holds the database-level and language-level discretionary access privileges that are identified above (and who also holds the EXTEND role, if IFX_EXTEND_ROLE is enabled and the UDR is an external routine) can reference UDRs in the following SQL statements:
  • The DBA or a user can register a new UDR with the CREATE FUNCTION, CREATE FUNCTION FROM, CREATE PROCEDURE, CREATE PROCEDURE FROM, CREATE ROUTINE, or CREATE ROUTINE FROM statement.
  • The DBA or the owner of an existing UDR can cancel the registration of that UDR with the DROP FUNCTION, DROP PROCEDURE, or DROP ROUTINE statement.
  • The DBA or the owner of an existing UDR can modify the definition of that UDR with the ALTER FUNCTION, ALTER PROCEDURE, or ALTER ROUTINE statement.