Object Level Privileges

The GRANT statement can also be used to grant access to objects. The syntax for the statement is:

GRANT privilege list ON object TO { user list I PUBLIC } [WITH GRANT OPTION] [AS grantor];

The options are as follows:

privilege list

This is a comma-separated list of access privileges:

ALTER	Can alter the table or view
DELETE	Can delete records from the table or view
EXECUTE	Allows the execution of the stored procedure
INDEX	Can create indexes on the table
INSERT	Can insert records into the table or view
REFERENCES	Can create foreign keys that reference the table
SELECT	Can query any object
UPDATE	Can update records in the table or view
ALL	All of the foregoing privileges on the table or view

If REFERENCES, SELECT and UPDATE privileges are only to be applied to some of the columns in a table (or view), the following syntax is used in the privilege list:

privilege (column_list) ...

ON object

The object on which the privileges are granted. It can be one of the following:

Table
Synonym
View
Stored Procedure

TO user_list

This is a comma-separated list of operating system users.

TO PUBLIC

This refers to all operating system users.

WITH GRANT OPTION

This allows the recipients of the specified privileges to pass those privileges on to other users.

AS grantor

This records the name of the grantor as the person who granted the privileges, rather than the user who issued the command. The grantor must also be able to grant those privileges.

Note: When a table is created, the privileges SELECT, INSERT, UPDATE, DELETE and INDEX are granted to PUBLIC automatically.

The REVOKE statement is used to remove privileges from users. The syntax for the statement is:

REVOKE privilege list ON object FROM { user list | PUBLIC };