Using GRANT and REVOKE Effectively

1. Introduction

In this tutorial, our goal is to understand how to effectively use the GRANT and REVOKE commands in SQL, which are crucial for managing database access.

By the end of this tutorial, you will be able to:
- Understand the purpose of the GRANT and REVOKE commands.
- Use these commands to control permissions on a database.

The only prerequisite for this tutorial is basic knowledge of SQL.

2. Step-by-Step Guide

The GRANT command is used to allow users to perform specific tasks on a database, like SELECT, INSERT, UPDATE, DELETE, and others. Conversely, the REVOKE command is used to take back the permissions granted to users.

GRANT Command

The basic syntax of the GRANT command is:

GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];

• privilege_name is the access right or privilege granted to the user.
• object_name is the name of the database object like TABLE, VIEW, etc.
• WITH GRANT OPTION allows a user to grant or revoke privileges from other users too.

REVOKE Command

The basic syntax of the REVOKE command is:

REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name}

• privilege_name is the access right or privilege to be taken back.
• object_name is the name of the database object like TABLE, VIEW, etc.

3. Code Examples

Example 1: Granting privileges

GRANT SELECT, INSERT, DELETE ON my_table TO user1;

This command grants user1 SELECT, INSERT, and DELETE rights on my_table.

Example 2: Revoking privileges

REVOKE INSERT ON my_table FROM user1;

This command revokes user1’s INSERT rights on my_table.

4. Summary

In this tutorial, you have learned how to use the GRANT and REVOKE commands to manage database access. These commands are critical for maintaining database security by controlling the actions that users can perform.

For further learning, you should look into the different types of privileges that can be granted or revoked and how to manage groups of users using roles.

5. Practice Exercises

1. Create a database and a table within it. Grant SELECT, INSERT, and UPDATE privileges to a new user on this table.

2. After completing the first exercise, revoke the UPDATE privilege from this new user.

3. Grant all privileges to a new user on a new table. After that, revoke all privileges from this user.

Solutions:

CREATE DATABASE my_database;
USE my_database;
CREATE TABLE my_table (column1 INT, column2 VARCHAR(20));
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE ON my_table TO 'new_user'@'localhost';

REVOKE UPDATE ON my_table FROM 'new_user'@'localhost';

CREATE TABLE another_table (column1 INT, column2 VARCHAR(20));
CREATE USER 'another_user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON another_table TO 'another_user'@'localhost';
REVOKE ALL ON another_table FROM 'another_user'@'localhost';