Revoking the EXTEND Role

The REVOKE EXTEND FROM user-list statement cancels the EXTEND role of the specified users. In databases where the IFX_EXTEND_ROLE configuration parameter is enabled, revoking this role prevents the specified users from creating or dropping external UDRs. Whether or not a user holds the EXTEND role has no effect on creating or dropping UDRs written in the SPL language.

Only the Database Server Administrator (DBSA), by default user informix, can grant the built-in EXTEND role to one or more users or to PUBLIC by issuing the GRANT EXTEND TO user-list statement. (Because EXTEND is a built-in role, a user who holds it does not need to activate it with the SET ROLE statement, and the DROP ROLE statement cannot destroy the EXTEND role.)

If the IFX_EXTEND_ROLE configuration parameter is set to ON or to 1, users who do not hold the EXTEND role cannot create or drop UDRs that are written in the C or Java™ languages, both of which support shared libraries. The following example revokes the EXTEND role from user max: 
REVOKE EXTEND FROM 'max';

This prevents user max from creating or dropping external UDRs, even if max is the owner of a UDR that he subsequently attempts to drop.

In databases for which this security feature is not needed, the DBSA can disable this restriction on who can create or drop external UDRs by setting the IFX_EXTEND_ROLE parameter to OFF or to 0 in the ONCONFIG file. But whether IFX_EXTEND_ROLE is enabled or disabled, users who create or drop external UDRs must also hold the following access privileges:
  • Either the Resource privilege or the DBA privilege on the database in which the UDR is registered.
  • The Usage privilege on the external programming language in which the UDR is written,

See Database-level privileges for information about the Resource privilege. See Language-Level Privileges for the syntax of the GRANT USAGE ON LANGUAGE C and the GRANT USAGE ON LANGUAGE JAVA statements of SQL.