Under Privilege

You own any named ROW data type that you create. If you want other users to be able to create subtypes under this named ROW type, you must grant these users the Under privilege on your named ROW type. If you later want to remove the ability of these users to create subtypes under the named ROW type, you must revoke the Under privilege from these users. A REVOKE statement with the UNDER ON TYPE keywords removes the Under privilege that you granted earlier to these users.

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:

If you later want to remove the ability of user kathy to create subtypes under the rtype1 ROW type, enter the following statement:

Routine-Level Privileges

If you revoke the Execute privilege on a UDR from a user, that user can no longer execute that UDR in any way. For details of how a user can execute a UDR, see Routine-Level Privileges.

Routine-Level Privileges:
 
|--EXECUTE 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

In an ANSI-compliant database, the owner name must qualify the routine name, unless the user who issues the REVOKE statement is the owner of the routine.

In Dynamic Server, any negator function for which you grant the Execute privilege requires a separate, explicit, REVOKE statement.

When you create a UDR under any of the following circumstances, PUBLIC will not be granted Execute privilege by default. Therefore you must explicitly grant the Execute privilege before you can revoke it:

You create the UDR in an ANSI-compliant database.
You have DBA privilege and specify DBA after the CREATE keyword to restrict the Execute privilege to users with the DBA database-level privilege.
The NODEFDAC environment variable is set to yes to prevent PUBLIC from receiving any privileges that are not explicitly granted.

But if you create a UDR with none of those conditions in effect, PUBLIC can execute your UDR without the GRANT EXECUTE statement. To limit who can executes your UDR, revoke Execute privilege FROM PUBLIC, and grant it to users (see User List) or roles (see Role Name).

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 no longer execute.

Keyword: UDR for Which Execution by the User is Prevented
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)

Language-Level Privileges (IDS)

A user must have the Usage privilege on SPL to register a UDR written in SPL.

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

When a user registers a UDR that is written in SPL, Dynamic Server verifies that the user has the Usage privilege on the SPL language. If the user does not, the CREATE FUNCTION or CREATE PROCEDURE statement fails with an error. (The C language and the Java language do not require the Usage privilege.)

To revoke the Usage privilege on the SPL language from a user or role, issue a REVOKE statement that includes the USAGE ON LANGUAGE SPL keywords. If this statement succeeds, any user or role that you specify in the FROM clause can no longer register UDRs that are written in the specified language. For example, if you revoke the default Usage privilege on SPL from PUBLIC, the ability to create SPL routines is taken away from all users:

REVOKE USAGE ON LANGUAGE SPL FROM PUBLIC

You can issue a GRANT USAGE ON LANGUAGE statement to restore Usage privilege on SPL to a restricted group, such as to the role named developers:

GRANT USAGE ON LANGUAGE SPL TO developers

Sequence-Level Privileges (IDS)

Although Dynamic Server implements sequence objects as tables, only the following subset of the table privileges (as described in Table-Level Privileges) can be granted or revoked on a sequence:

Select privilege
Alter privilege

Use the following syntax to specify privileges to revoke on a sequence object:

Sequence-Level Privileges:
 
|--+-ALL------------+--ON--+-----------+--+-sequence-+----------|
   | .-,----------. |      '- 'owner.'-'  '-synonym--'
   | V            | |
   '---+-ALTER--+-+-'
       '-SELECT-'

Element	Description	Restrictions	Syntax
owner	Owner of the sequence or of its synonym	Must be the owner	Owner Name, p. Owner Name
sequence	Sequence on which to revoke privileges	Must exist	Identifier, p. Identifier
synonym	Synonym for a sequence object	Must point to a sequence	Identifier, p. Identifier

The sequence must reside in the current database. (You can qualify the sequence or synonym identifier with a valid owner name, but the name of a remote database (or database@server) is not valid as a qualifier.) Syntax to revoke sequence-level privileges is an extension to the ANSI/ISO standard for SQL.