GRANT assigns the following privileges:
Table and column privileges for base tables and views
Special privileges for databases and storage groups
EXECUTE privileges for routines
If the GRANT statement is included in a CREATE SCHEMA statement, you cannot grant special privileges with GRANT.
The current authorization identifier must be authorized to grant the specified privileges:
It is the authorization identifier of the universal user.
It is owner of the table, database, storage group or routine.
It has GRANT authorization for granting the privileges to other users.
Information on which authorization identifiers are owners is stored in the SCHEMATA, SPACES and STOGROUPS views. The TABLE_PRIVILEGES, COLUMN_PRIVILEGES, USAGE_PRIVILEGES, CATALOG_PRIVILEGES and ROUTINE_PRIVILEGES views provide you with information on whether the authorization identifier has GRANT authorization for a certain privilege (see chapter "Information schemas").
Only the authorization identifier that granted a privilege (the “grantor”) can revoke that privilege.
The GRANT statement has several formats. Examples are provided under the format concerned.
See also
REVOKE, CREATE SCHEMA
GRANT format for table and column privileges
GRANT { ALL PRIVILEGES | table_and_column_privileg ,...}
ON [TABLE] table
TO { PUBLIC | authorization_identifier },...
[WITH GRANT OPTION]
table_and_column_privilege ::=
{
SELECT |
DELETE |
INSERT |
UPDATE [( column ,... )] |
REFERENCES [( column ,... )]
}
ALL PRIVILEGES
All the table and column privileges that the current authorization identifier can grant are granted. ALL PRIVILEGES comprises the privileges SELECT, DELETE, INSERT, UPDATE and REFERENCES.
table_and_column_privilege
The table and column privileges are granted individually. You can specify more than one privilege.
ON [TABLE] table
Name of the table for which you want to grant privileges.
If you use the GRANT statement in a CREATE SCHEMA statement, you can only qualify the table name with the database and schema name from the CREATE SCHEMA statement.
The table can be a base table or a view. You can only grant the SELECT privilege for a table that cannot be updated.
TO PUBLIC
The privileges are granted to all authorization identifiers. In addition to its own privileges, each authorization identifier also has those which have been granted to PUBLIC. Authorization identifiers added later also have these privileges.
TO authorization_identifier
The privileges are granted to authorization_identifier. You may specify more than one authorization identifier.
WITH GRANT OPTION
The specified authorization identifiers are granted not only the specified privileges but also GRANT authorization. This means that the authorization identifier(s) is authorized to grant the privileges it has been extended to other authorization identifiers. You cannot specify WITH GRANT OPTION together with PUBLIC.
WITH GRANT OPTION omitted:
The specified authorization identifier(s) cannot grant the privileges it has been extended to other authorization identifiers.
table_and_column_privilege
Specification of the individual table and column privileges.
SELECT
Privilege that allows rows in the table to be read.
DELETE
Privilege that allows rows to be deleted from the table.
INSERT
Privilege that allows rows to be inserted into the table.
UPDATE [(column,...)]
Privilege that allows rows in the table to be updated.
The update operation can be limited to the specified columns. column must be the name of a column in the specified table. You can specify more than one column.
(column,...) omitted:
All columns in the table may be updated. Columns added later may also be updated.
REFERENCES [(column,...)]
Privilege that allows the definition of referential constraints that reference the table. The reference can be limited to the specified columns. column must be the name of a column in the specified table. You can specify more than one column.
(column,...) omitted:
All columns in the table may be referenced. Columns added later may also be referenced.
Example
Grant all table privileges for IMAGES to the authorization identifier UTIUSR1, and the table privileges SELECT, DELETE, INSERT, and UPDATE for DESCRIPTIONS to the authorization identifier UTIUSR2. The two authorization identifiers must be created beforehand.
GRANT ALL PRIVILEGES ON images TO utiusr1
GRANT SELECT, DELETE, INSERT, UPDATE ON descriptions TO utiusr2
GRANT format for special privileges
GRANT { ALL SPECIAL PRIVILEGES | special_privilege ,...}
ON CATALOG { catalog | STOGROUP stogroup }
TO { PUBLIC | authorization_identifier },...
[WITH GRANT OPTION]
special_privilege ::=
{
CREATE USER |
CREATE SCHEMA |
CREATE STOGROUP |
UTILITY |
USAGE
}
ALL SPECIAL PRIVILEGES
All the special privileges that the current authorization identifier may grant are granted. ALL SPECIAL PRIVILEGES comprises the special privileges.
special_privilege
The special privileges are granted individually. You can specify more than one special privilege.
ON CATALOG catalog
Name of the database for which you are granting special privileges.
ON STOGROUP stogroup
Name of the storage group for which you want to grant the USAGE privilege. You can qualify the name of the storage group with a database name.
TO
See "GRANT - Grant privileges".
WITH GRANT OPTION
See "GRANT - Grant privileges".
special_privilege
Specification of the individual special privileges.
CREATE USER
Special privilege that allows you to define and delete authorization identifiers. You can only grant the CREATE USER privilege for a database.
CREATE SCHEMA
Special privilege that allows you to define database schemas. You can only grant the CREATE SCHEMA privilege for a database.
CREATE STOGROUP
Special privilege that allows you to define storage groups. You can only grant the CREATE STOGROUP privilege for a database.
UTILITY
Special privilege that allows you to use utility statements. You can only grant the UTILITY privilege for a database.
USAGE
Special privilege that allows you to use a storage group. You can only grant the USAGE privilege for a storage group.
Examples
Grant the special privilege CREATE SCHEMA to the existing authorization identifier UTIUSR.
GRANT CREATE SCHEMA ON CATALOG ordercust TO utiusr
Grant all special privileges for the database ORDERCUST to the authorization identifier UTIADM.
In addition, grant UTIADM the special privilege which authorizes use of the storage group STOGROUP1.
GRANT ALL SPECIAL PRIVILEGES ON CATALOG ordercust TO utiadm GRANT USAGE ON STOGROUP stogroup1 TO utiadm
GRANT format for EXECUTE privileges (procedure)
GRANT EXECUTE ON SPECIFIC PROCEDURE procedure
TO { PUBLIC | authorization_identifier },...
[WITH GRANT OPTION]
procedure ::= routine
EXECUTE ON SPECIFIC PROCEDURE procedure
Name of the procedure for which the privilege is to be passed on. You can qualify the procedure name with a database and schema name. If you use the GRANT statement in a CREATE SCHEMA statement, you can only qualify the procedure name with the database and schema name from the CREATE SCHEMA statement.
TO
See "GRANT - Grant privileges".
WITH GRANT OPTION
See "GRANT - Grant privileges".
Example
The privilege of being entitled to execute the myproc procedure is granted to all authorization identifiers.
GRANT EXECUTE ON SPECIFIC PROCEDURE myproc TO PUBLIC
GRANT format for EXECUTE privileges (UDF)
GRANT EXECUTE ON SPECIFIC FUNCTION udf
TO { PUBLIC | authorization_identifier },...
[WITH GRANT OPTION]
udf ::= routine
EXECUTE ON SPECIFIC FUNCTION udf
Name of the UDF for which the privilege is to be passed on. You can qualify the unqualified UDF name with a database and schema name. If you use the GRANT statement in a CREATE SCHEMA statement, you may qualify the UDF name only with the database and schema names from the CREATE SCHEMA statement.
TO
See "GRANT - Grant privileges".
WITH GRANT OPTION
See "GRANT - Grant privileges".
Example
The privilege of being entitled to execute the myproc UDF is granted to all authorization identifiers.
GRANT EXECUTE ON SPECIFIC FUNCTION myudf TO PUBLIC