Command Discussion

• 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: SELECT, INSERT, UPDATE, DELETE, and 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 ("userid's") of 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.

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:

• When the 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.

• 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".

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.