Privileges for registering a routine
- 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.
- 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.