Syntax
┌◄────── , ──────┐
── GRANT ──┬─┴─┬── INSERT ──┬─┴──────┬──── ON ──────┬─────────┬───────────────────►
│ ├── UPDATE ──┤ │ └─ TABLE ─┘
│ ├── SELECT ──┤ │
│ └── DELETE ──┘ │
│ │
└─ ALL ─┬──────────────┬──┘
└─ PRIVILEGES ─┘
►───────────┬────────────────┬───────────┬──<table name>──┬────────────────────────►
└─<auth ID>── . ─┘ ├──<view name>───┤
└────── * ───────┘
┌◄────── , ──────┐
►─── TO ──┬─┴────<usercode> ─┴─┬─────────┬─────────────────────┬───────────────────►
└───── PUBLIC ───────┘ └─ WITH GRANT OPTION ─┘Explanation
The GRANT command enables a user (known as the grantor) to specify one or more privileges that one or more users can perform on a given structure.
WITH GRANT OPTION
Specifies that the recipient of the permission can grant the same permission to other users.
Restrictions
The GRANT command has the following restrictions:
-
The GRANT command cannot exceed 65535 characters.
-
Only one table (or all tables if specified using the * (asterisk) option) can be specified in a GRANT statement.
-
The PUBLIC usercode and usercodes named using the <usercode> element cannot be used in the same statement.
-
If PUBLIC is specified as a system usercode, it will conflict with the use of PUBLIC as a keyword to indicate all users for the purposes of MCPSQL permissions.
-
Users must have permissions for both parent and embedded structures to give permissions to an embedded structure.
Examples
Example 1
The following example permission grants the SELECT privilege to USER1 for the table CUSTOMERS:
GRANT SELECT ON CUSTOMERS TO USER1
Example 2
The following example permission grants all privileges to the user ADMIN for the table ORDERS. It also gives the ADMIN user the ability to GRANT the same permission to other users:
GRANT ALL ON ORDERS TO ADMIN WITH GRANT OPTION
Example 3
The following example permission grants the INSERT and UPDATE privileges to users OP1 and OP2 for the table PRODUCTS:
GRANT INSERT, UPDATE ON PRODUCTS TO OP1, OP2
Example 4
The following example permission grants the SELECT privilege to all users of the system for the table SALES:
GRANT SELECT ON SALES TO PUBLIC
After you perform a GRANT command, you receive a response detailing the status of the command. The following examples are responses that a grantor might receive after issuing a GRANT command.
Example 5
The following response is returned when a GRANT is successfully performed for one user:
1 row affected.
Example 6
The following response is returned when a GRANT is successfully performed for three users in the same command:
3 rows affected.
Example 7
The following response is returned when the user attempting to perform the GRANT command does not have the ability to grant this permission to others:
The Requestor is not permitted to perform the action.
| Note: | A GRANT submitted successively by the same user reports the same status as the first submitted GRANT. Additional records are not created for granting repeated permissions. |
