Granting Privileges to Other Users (GRANT)

Granting Privileges to Other Users (GRANT)
The GRANT statement is used to grant privileges on database objects to specific users.
Normally the GRANT statement is used by the owner of a table to give other users access
to the data. The format of the GRANT statement is:
GRANT {PrivilegeList | ALL PRIVILEGES}
ON ObjectName
TO {AuthorizationIdList | PUBLIC}
[WITH GRANT OPTION]
PrivilegeList consists of one or more of the following privileges separated by commas:
SELECT
DELETE
INSERT [(columnName [, . . . ])]
UPDATE [(columnName [, . . . ])]
REFERENCES [(columnName [, . . . ])]
USAGE
For convenience, the GRANT statement allows the keyword ALL PRIVILEGES to be
used to grant all privileges to a user instead of having to specify the six privileges
individually. It also provides the keyword PUBLIC to allow access to be granted to all
present and future authorized users, not just to the users currently known to the DBMS.
ObjectName can be the name of a base table, view, domain, character set, collation, or
translation.
The WITH GRANT OPTION clause allows the user(s) in AuthorizationIdList to pass the
privileges they have been given for the named object on to other users. If these users pass
a privilege on specifying WITH GRANT OPTION, the users receiving the privilege may
in turn grant it to still other users. If this keyword is not specified, the receiving user(s) will
not be able to pass the privileges on to other users. In this way, the owner of the object
maintains very tight control over who has permission to use the object and what forms of
access are allowed.
Example 6.7 GRANT all privileges
Give the user with authorization identifier Manager full privileges to the Staff table.
GRANT ALL PRIVILEGES
ON Staff
TO Manager WITH GRANT OPTION;
The user identified as Manager can now retrieve rows from the Staff table, and also insert,
update, and delete data from this table. Manager can also reference the Staff table, and all the
Staff columns in any table that he or she creates subsequently. We also specified the keyword
WITH GRANT OPTION, so that Manager can pass these privileges on to other users.
Example 6.8 GRANT specific privileges
Give users Personnel and Director the privileges SELECT and UPDATE on column salary
of the Staff table.
GRANT SELECT, UPDATE (salary)
ON Staff
TO Personnel, Director;
We have omitted the keyword WITH GRANT OPTION, so that users Personnel and
Director cannot pass either of these privileges on to other users.
Example 6.9 GRANT specific privileges to PUBLIC
Give all users the privilege SELECT on the Branch table.
GRANT SELECT
ON Branch
TO PUBLIC;
The use of the keyword PUBLIC means that all users (now and in the future) are able to
retrieve all the data in the Branch table. Note that it does not make sense to use WITH
GRANT OPTION in this case: as every user has access to the table, there is no need to
pass the privilege on to other users.