8. SQL Statements : GRANT (privilege) : Syntax
 
Share this page                  
Syntax
The GRANT (privilege) statement has the following format:
[EXEC SQL] GRANT ALL [PRIVILEGES] | privilege {, privilege}
              [ON [object_type] [schema.]object_name {, [schema.]object_name}]
              TO PUBLIC | [auth_type] auth_id {, auth_id} [WITH GRANT OPTION];
privilege
Specifies the privilege, which must be valid for the object_type.
Valid privileges are described under the section Object Privileges (see Object Privileges).
object_type
Specifies the type of object on which you are granting privileges. Object_type must be one of the following:
TABLE
(Default) Controls access to individual tables or views.
DATABASE
Controls access to database resources.
PROCEDURE
Controls who can execute individual database procedures.
DBEVENT
Controls who can register for and raise specific database events.
SEQUENCE
Controls who can retrieve values from individual database sequences.
CURRENT INSTALLATION
Grants the specified privilege on the current installation.
The object_type must agree with the privilege being granted (for example, EXECUTE privilege cannot be granted on a table).
Privileges cannot be defined for more than one type of object in a single GRANT statement. If object_type is CURRENT INSTALLATION, object_name must be omitted.
object_name
Specifies the name of the table, view, procedure, database event, sequence or database for which the privilege is being defined. The object must correspond to the object_type. For example, if object_type is TABLE, object_name must be the name of an existing table or view.
auth_type
Specifies the type of authorization to which you are granting privileges. A GRANT statement cannot contain more than one auth_type. Valid auth_types are:
USER
GROUP
ROLE
The auth_ids specified in the statement must agree with the specified auth_type. For example, if you specify auth_type as GROUP, all auth_ids listed in the statement must be group identifiers.
Default: USER
PUBLIC
Grants a privilege to all users. The auth_type parameter can be omitted.
auth_id
Specifies the name of the users, groups, or roles to which you are granting privileges, or PUBLIC. Both PUBLIC and a list of auth_ids can be specified in the same GRANT statement. If the privilege is subsequently revoked from PUBLIC, the individual privileges still exist.