SELECT
, INSERT
, UPDATE
, DELETE
, and ALTER
. Alternatively, ALL can be
specified if all of the above actions are to be granted to the user.userid's
") of
all those who will be allowed to share the table.
Example:
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
following command:
GRANT SELECT
ON EMPLOYEE
TO CPA ;
Syntax Notes:
SELECT
privilege is granted, it is done so with
read-only access. Therefore the person granted the SELECT
privilege
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.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".
Example: The owner of the EMPLOYEE table allows the clerks in Personnel to add and modify employee data with this command:
GRANT UPDATE, INSERT
ON EMPLOYEE
TO CLERK1, CLERK2 ;
In this case there are two clerks with two separate user ID's,
"CLERK1" and "CLERK2". Both are granted privileges to UPDATE
and
INSERT
new information into the EMPLOYEE table.
Example:
The owner of the EMPLOYEE table, the System Administrator, gives the
Director of Personnel complete access (SELECT
, INSERT
, UPDATE
, DELETE
,
ALTER
) to the EMPLOYEE table, along with permission to assign these
privileges to others. This statement is used:
GRANT ALL
ON EMPLOYEE
TO PERS
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.
Example: 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:
GRANT SELECT
ON NEWS
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.