SQL GRANT

In most instances, many users access the same database environment. It is often not a good idea to allow everyone full freedom to perform any operation on all the database objects. For example, if I have an important table, I would not want anyone to be able to drop the table or alter the content of the table. Most relational databases provide the ability for database administrators and users to manage such privileges. There are two main commands that allows this:

• Grant: Gives user privileges.
• Revoke: Removes user privileges.

We will focus on GRANT here. REVOKE is covered in the next section.

It is worth noting that some lightweight databases, such as SQLite, do not implement GRANT and REVOKE commands. This is because such databases are intended to be an embedded database engine and hence user-level privileges are meaningless.

Syntax

The syntax for GRANT is as follows:

GRANT "Privilege_Name" ON "Database_Object" TO "User" [WITH GRANT OPTION];

The available "Privilege_Name" will vary depending on the relational database. Typical privileges include the following:

• SELECT
• INSERT
• DELETE
• UPDATE

In many databases there is a privilege called "ALL." However, granting the "ALL" privilege doesn't necessarily provide all the available privileges to a user. Exactly which privileges are included as part of "ALL" is database-dependent.

In some databases such as DB2, it is necessary to add the word USER before the user name. This is because in DB2 it is possible to grant privileges at three different levels (user, role, group), so it is necessary to be explicit on which level the grant would take effect to avoid any ambiguity.

It is also possible to grant privileges to all users. On many databases granting a privilege to PUBLIC means you are granting to all users. There are exceptions. For example, on MySQL you would use ''@'localhost'.

The links below show the types of privileges available for each of the databases:

• Oracle
• SQL Server
• MySQL
• PostgreSQL
• DB2

The optional "[With Grant Option]" means "User" can grant the same "Privilege_Name" to other users.

Examples

Example 1: Grant one privilege to a user

To grant SELECT privilege on the "Sales" table to user tanner1, we type in the following:

GRANT SELECT ON Sales TO tanner1;

Example 2: Use WITH GRANT OPTION

To enable user tanner1 to have the SELECT privilege on the "Sales" table and be able to grant the SELECT privilege on this table to other users, we will type the following:

GRANT SELECT ON Sales TO tanner1 With Grant Option;

Please note that a user can only grant a privilege to others if that user has access to that privilege. For example, if user tanner1 only has SELECT privilege on a table, tanner1 cannot grant a different type of privilege (such as DELETE) on this table to other users.

Example 3: Grant one privilege to all users

To grant SELECT privilege on the "Sales" table to all users, we type in the following:

GRANT SELECT ON Sales TO public;