GRANT Statement

statement_GRANT ::= statement_GRANT_for_tables | statement_GRANT_for_routines

statement_GRANT_for_tables ::= GRANT privilege_list ON [scheme.]table_name TO subject_list 
    [ WITH GRANT OPTION ] [ WHERE where_condition ]
privilege_list ::= ALL [ PRIVILEGES ] | single_privilege {, single_privilege }…
single_privilege ::=  DELETE | INSERT | SELECT | UPDATE [( column_list )] 
    | REFERENCES [( column_list ) | GRANT ]
column_list ::= column { , column }…
subject_list ::= PUBLIC | subject {, subject }…
subject ::= [ USER ] user_name | [ GROUP ] role_name | ROLE [scheme. ] role_name

statement_GRANT_for_routines ::= GRANT privilege_list ON [ PROCEDURE | FUNCTION ] [schema.]routine_name 
    TO subject_list [ WITH GRANT OPTION ]
privilege_list ::= ALL [ PRIVILEGES ] | single_privilege {, single_privilege }…
single_privilege ::= EXECUTE | UPDATE

Description

The GRANT statement sets privileges for users, groups and roles specified in the subject_list. You can grant privileges for these objects:

Table table_name in the schema application
Specified table records of the table_name table
SQL routine routine_name (either a procedure or function)

It is acceptable to distinguish routines with the PROCEDURE or FUNCTION keyword in order to prevent possible conflicts between table name and routine name.

If you wish to set privileges on individual records, you must specify the WHERE clause with the constraint condition on the table_name table records (make sure this table has record privileges enabled).

Privilege_list defines what privileges should be set. If you use the ALL keyword, all possible privileges will be set. If you only want to set some privileges, you must specify these privileges separated with commas. The following privileges can be used for tables:

DELETE: Privilege to delete records.
INSERT: Privilege to insert records.
SELECT: Privilege to read all columns.
REFERENCES: Privilege to read only those columns that are specified in the column_list. If the column_list is not set, the privilege to read is valid for all records.
UPDATE: Privilege to update only those columns that are specified in the column_list. If the column_list is not set, the privilege to update is valid for all records.
GRANT: Privilege to grant privileges.

INSERT privileges cannot be used for record privileges.

The list of privileges for routines is much easier:

UPDATE: Privilege to update the object definition.
EXECUTE: Privilege to execute the routine.

The subject_list specifies the subjects that are assigned this privilege. If you use the PUBLIC keyword, privileges are set for the EVERYBODY group. Otherwise, the privileges are set for those users, groups or roles, that are specified individually. If you use a group name, the privileges will be granted to this group. Users assigned to this group will inherit these privileges automatically. If the keywords USER, GROUP or ROLE are not specified, the subject name is searched for among users, then groups.

If the specified subject already has certain privileges, then the privileges set in the GRANT statement will be appended to these privileges.

If you specify the WITH GRANT OPTION clause, the users and groups will gain the the ability to grant these privileges to other users.

You cannot set privileges for objects (except procedures) with the GRANT statement in the current version.

Example:

Grant the user PETER the privileges to delete records, edit the employee number and salary. Grant all privileges for one particular record in the DocTab table and the privilege to execute the PriceChange procedure.

GRANT DELETE, UPDATE (Salary, Number)
ON Employed
TO Peter

GRANT ALL ON DocTab
TO USER Peter
WHERE id=25

GRANT EXECUTE
ON PROCEDURE PriceChange
TO Peter

See

RENAME Statement

SQL Statements

REVOKE Statement