GRANT (User-Defined Type Privileges)

This form of the GRANT statement grants privileges on a user-defined type.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

• For each user-defined type identified in the statement:
  • Every privilege specified in the statement
  • The system authority of *OBJMGT on the user-defined type
  • The system authority *EXECUTE on the library containing the user-defined type
• Administrative authority

If WITH GRANT OPTION is specified, the privileges held by the authorization ID of the statement must include at least one of the following:

• Ownership of the user-defined type
• Administrative authority

Syntax

                   .-PRIVILEGES-.
>>-GRANT----+-ALL--+------------+--+---------------------------->
            |  .-,----------.      |
            |  V            |      |
            '----+-ALTER-+--+------'
                 '-USAGE-'
 
                                     .-,------------.
                                     V              |
>----ON--+---------------+---TYPE-------type-name---+----------->
         |          (1)  |
         '-DISTINCT------'
 
         .-,-----------------------.
         V                         |
>----TO----+-authorization-name-+--+--+-------------------+----><
           '-PUBLIC-------------'     '-WITH GRANT OPTION-'
 

Notes:

1. The keyword DATA can be used when granting to any user-defined-type.

Description

ALL  or  ALL PRIVILEGES

Grants one or more privileges. The privileges granted are all those grantable privileges that the authorization ID of the statement has on the specified user-defined types. Note that granting ALL PRIVILEGES on a user-defined type is not the same as granting the system authority of *ALL.

If you do not use ALL, you must use one or more of the keywords listed below. Each keyword grants the privilege described.

ALTER

Grants the privilege to use the COMMENT ON statement.

USAGE

Grants the privilege to use the user-defined type in tables, functions, procedures, or as the source type in a CREATE DISTINCT TYPE statement.

ON TYPE type-name

Identifies the user-defined types on which you are granting the privilege. The type-name must identify a user-defined type that exists at the current server.

TO

Indicates to whom the privileges are granted.

authorization-name,...

Lists one or more authorization IDs.

PUBLIC

Grants the privileges to a set of users (authorization IDs).

The set consists of those users who do not have privately granted privileges on the user-defined type. For example, if ALTER has been granted to PUBLIC, and USAGE is then granted to HERNANDEZ, this private grant prevents HERNANDEZ from having the ALTER privilege.

WITH GRANT OPTION

Allows the specified authorization-names to grant privileges on the user-defined types specified in the ON clause to other users.

If WITH GRANT OPTION is omitted, the specified authorization-names cannot grant privileges on the user-defined types specified in the ON clause to another user unless they have received that authority from some other source (for example, from a grant of the system authority *OBJMGT).

Note

GRANT and REVOKE statements assign and remove AS/400 system authorities for SQL objects. The following table describes the AS/400 system authorities that correspond to the SQL privileges:

Table 30. Privileges Granted to or Revoked from User-Defined Types

SQL Privilege	Corresponding AS/400 System Authorities when Granting to or Revoking from a User-Defined Type
ALL (Grant or revoke of ALL grants or revokes only those privileges the authorization ID of the statement has)	*OBJALTER *OBJOPR *EXECUTE *OBJMGT (Revoke only)
ALTER	*OBJALTER
USAGE	*EXECUTE *OBJOPR
WITH GRANT OPTION	*OBJMGT

Example

Grant the USAGE privilege on distinct type SHOE_SIZE to user JONES. This GRANT statement does not give JONES the privilege to execute the cast functions that are associated with the distinct type SHOE_SIZE.

GRANT USAGE
  ON DISTINCT TYPE SHOE_SIZE
  TO JONES