GRANT
Function
GRANT gives users privileges to access tables or views.
Syntax
GRANT privilege_specification ON [TABLE] table_specification TO grantee_specification [WITH GRANT OPTION ]
where
table_specification | The table or view for which the grant is to be performed. The table or view name should only be specified once. See
Table Specification. |
privilege_specification | |
grantee_specification | A user, a list of users or PUBLIC for which the grant is to be executed. A user should only be specified once. See
Grantee Specification. |
Description
For the specified tables or views, GRANT gives the specified privileges to a user, a list of users, or to PUBLIC. Do not specify the user identifier, the table, or the view identifier, multiple times.
By default, owners of a table have all privileges for that table. If you are the table owner, do not grant yourself rights on that table.
A privilege given with the WITH GRANT OPTION permits this user to grant other users privileges on the specified tables or views. The WITH GRANT OPTION can be specified for ALL PRIVILEGES, and so enables the grantee to grant all privileges to another user; or it can be specified for a particular set of privileges (see examples below).
Unsuccessful execution of the GRANT ALL PRIVILEGES statement results in response code 0, even though the ANSI Standard prescribes a Warning.
Limitations
General rules:
Each user can have a privilege granted once. If Peter received the privilege SELECT on CRUISE from Tim, no one else can grant Peter the same SELECT on CRUISE privilege.
Privileges on views are not automatically granted, just because privileges have been granted for the underlying base tabless. For example, Peter has created an updatable view based on the base table CRUISE. He has SELECT and UPDATE privilege on this view. If Peter is granted INSERT on the base table CRUISE, this will not result in INSERT privilege on the view.
Granting UPDATE privileges on a table always means an implicit UPDATE on all columns of the table on which the grantor also has the GRANT option. In addition, a table privilege means that, when a column is added, all grantees that have the table privilege also receive the column privilege for the new column.
Authority to grant privileges:
The table or view owner can grant privileges.
Anyone the table or view owner has granted privileges to (with the WITH GRANT OPTION) can grant those privileges to others.
Granting privileges on Views:
The view creator must have at least the SELECT privilege on all the base tables.
For a read-only view, the SELECT privilege is the only privilege the owner has and may grant.
For a read-only view, the SELECT privilege is the only privilege the owner has and may grant.
The grantee must have at least a SELECT privilege, as above.
If the above is not true, then the owner of the view must have at least the SELECT privilege plus the "WITH GRANT OPTION" to be able to grant privileges to other users for all base tables.
The execution of the GRANT statement is closed by an implicit COMMIT and is, therefore, not capable of ROLLBACK.
Warning:
This statement is not subject to transaction logic. An implicit COMMIT will be performed after successful execution of this statement. If an error is detected during execution of this statement, an implicit ROLLBACK will be performed. Therefore, before executing this statement, it is strongly recommended to complete any open transaction containing INSERT, UPDATE and/or DELETE statements by issuing an explicit COMMIT or ROLLBACK statement.
ANSI Specifics
The optional keyword TABLE in ON TABLE table specification is not supported.
CONNX Specifics
The keyword TABLE in ON TABLE table specification is optional.
EXAMPLES
Tim decided to GRANT ALL privileges to Peter on his table CRUISE:
GRANT ALL ON CRUISE TO 'PETER';
GRANT ALL PRIVILEGES ON CRUISE TO 'PETER'; [ ANSI-specific grant ]
Tim decided to GRANT the privilege SELECT to Anne on his table CRUISE:
GRANT SELECT ON CRUISE TO 'ANNE';
Tim decided to GRANT the privileges SELECT, INSERT, and DELETE to Martin with the "WITH GRANT OPTION" on his table CRUISE. Martin then decides to GRANT the select privilege to Chris.
Tim :
GRANT SELECT, INSERT, DELETE ON CRUISE TO 'MARTIN' WITH GRANT OPTION;
Martin :
GRANT SELECT ON CRUISE TO 'CHRIS';
Peter decided to GRANT Roland the SELECT privilege on table CRUISE for which he has no "WITH GRANT OPTION". Roland himself has no privileges for the table CRUISE. This statement fails as Peter has no privileges to perform this operation:
GRANT SELECT ON CRUISE TO 'ROLAND';
Peter decided to GRANT Roland the SELECT privilege on his view CRUISE_YACHTS:
GRANT SELECT ON TABLE CRUISE_YACHTS TO 'ROLAND';