- GRANT is a required keyword that indicates you are granting
access to tables
to other users.
- Privilege refers to the type of privilege or privileges you are
granting. One or more of the following privileges can be granted:
ALTER. Alternatively, ALL can be
specified if all of the above actions are to be granted to the user.
- ON indicates the table(s)
to which these privileges
are being assigned.
- PUBLIC is used if the privileges are to granted to all users.
If you want only certain users to have privileges assigned to this
table, you must list the user identifications ("
all those who will be allowed to share the table.
- If the clause WITH GRANT OPTION is specified, the recipient of
the privileges specified can grant these privileges to other users.
The Systems Administrator in the Information Systems Management
Department created the EMPLOYEE table, and is therefore its owner. As
owner of the EMPLOYEE table, he grants the
SELECT privilege to the
firm's CPA in Accounting. As owner of the table he issues the
TO CPA ;
- When the
SELECT privilege is granted, it is done so with
read-only access. Therefore the person granted the
can read the data in the table, but cannot write to it, or in other
words, cannot change it with
UPDATE or other such privileges.
- ON refers to the table these privileges are being granted on; in
this case, the EMPLOYEE table.
- What follows TO is the user ID (
userid) of the person to
whom the privilege is granted. In this case the
SELECT privilege is
granted to the person in accounting whose user ID is "CPA".
The owner of the EMPLOYEE table allows the clerks in Personnel to add
and modify employee data with this command:
GRANT UPDATE, INSERT
TO CLERK1, CLERK2 ;
In this case there are two clerks with two separate user ID's,
"CLERK1" and "CLERK2". Both are granted privileges to
INSERT new information into the EMPLOYEE table.
The owner of the EMPLOYEE table, the System Administrator, gives the
Director of Personnel complete access (
ALTER) to the EMPLOYEE table, along with permission to assign these
privileges to others. This statement is used:
WITH GRANT OPTION ;
ALL following GRANT includes all 5 of the privileges. "PERS" is the
user ID of the Director of Personnel. WITH GRANT OPTION allows the
Director of Personnel to grant these privileges to other users.
A systems analyst in the Strategic Planning and Intelligence
Department has created and is owner of the NEWS table in which they
are daily archiving online news articles of interest. It is decided
to give all employees read-only access to this database. Owner of the
table can do so with this command:
TO PUBLIC ;
Anyone with access to the server on which the news table is stored
will have permission to read the articles in the NEWS table, since the
SELECT privilege has been granted to PUBLIC.
Copyright © Thunderstone Software Last updated: Sun Mar 17 21:14:49 EDT 2013