GRANT statement

Use the GRANT statement to assign access privileges and roles to users and to other roles. Users who hold the DBSECADM role can use this statement to assign user security labels and exemptions from label-based access control (LBAC) security rules.

Syntax

TO Options

Notes:

¹ HCL OneDB™ extension
² See Database-Level Privileges
³ See Role Name
⁴ See Security Administration Options
⁵ See Surrogate user properties (UNIX, Linux)
⁶ See Table-Level Privileges
⁷ See Routine-Level Privileges
⁸ See Language-Level Privileges
⁹ See Type-Level Privileges
¹⁰ See Sequence-Level Privileges


Element	Description	Restrictions	Syntax
grantor	Authorization identifier of a user who can use REVOKE to cancel the effects of this GRANT statement. If AS clause is omitted, default is login name of user issuing this statement	Must be valid user name (not a role name). On Windows™, the user name cannot exceed 20 bytes. On other platforms, the limit is 32 bytes.	Owner name
role	Name of an existing role to which you grant one or more access privileges, or to which you assign another role	Must exist in the database	Owner name
user	Authorization identifier of a user to whom you grant one or more access privileges, or to whom you assign a role	Same as for grantor	Owner name

Usage

The GRANT statement extends to other users specific discretionary access privileges or LBAC labels and exceptions that would normally accrue only to the DBA or to the creator of an object. Subsequent GRANT statements do not affect privileges that have already been granted to a user.

You can use the GRANT statement for operations like the following:

Authorize others to use or administer a database that you create
Allow others to view, alter, or drop a table, synonym, view or a sequence object that you create
Allow others to use a data type or the SPL language, or to execute a user-defined routine (UDR) that you create
Assign a role and its privileges to users, or to PUBLIC, or to another role
Assign a default role to one or more users or to PUBLIC
If you hold the DBSECADM role, assign LBAC security labels or exemptions from rules of LBAC security policies to users,

You can grant privileges to a previously created role or to a built-in role. You can grant a role to PUBLIC, to individual users, or to another role.

If you enclose grantor, role, or user in quotation marks, the name is case sensitive and is stored exactly as you typed it. In an ANSI-compliant database, if you do not use quotation marks as delimiters, the name is stored in uppercase letters.

On Windows only, the database server does not support user name that consists of more than 20 characters.

Privileges that you grant remain in effect until you cancel them with a REVOKE statement. Only the grantor of a privilege can revoke that privilege. The grantor is the person who issues the GRANT statement, unless the AS grantor clause transfers the right to revoke those privileges to another user.

Only the owner of an object or a user to whom privileges were explicitly granted with the WITH GRANT OPTION keywords can grant privileges on an object. Having DBA privileges is not sufficient. As DBA, however, you can grant a privilege on behalf of another user by using the AS grantor clause. For privileges on database objects whose owner is not a user recognized by the operating system (for example, user informix), the AS grantor clause is useful.

The keyword PUBLIC extends the specified privilege or role to the PUBLIC group of all users who connect to the database. If you intend to restrict privileges that PUBLIC already holds to only a subset of users, you must first revoke those privileges from PUBLIC.

To grant privileges on one or more fragments of a table that has been fragmented by expression, see GRANT FRAGMENT statement.