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
| 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.
- 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,
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.