602SQL Documentation Index  

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:

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:

INSERT privileges cannot be used for record privileges.

The list of privileges for routines is much easier:

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