MySql - Granting and revoking privileges

Granting and Revoking Privileges (GRANT, REVOKE) in MySQL

In MySQL, managing user privileges is essential for maintaining database security, stability, and effective access control. By using the GRANT and REVOKE statements, database administrators can control which users can perform specific operations such as reading, writing, altering, or administering databases. These statements form the core of MySQL’s access control system. In this guide, we will explore the syntax, usage, and best practices related to GRANT and REVOKE in MySQL.

Understanding MySQL Privileges

Privileges in MySQL determine what actions a user can perform on the database server. These actions may include SELECT, INSERT, UPDATE, DELETE, EXECUTE, or administrative actions such as CREATE USER, GRANT OPTION, and more.

Types of Privileges

• Global Privileges: Apply to all databases on the server.
• Database Privileges: Apply to all tables in a specific database.
• Table Privileges: Apply to individual tables within a database.
• Column Privileges: Apply to specific columns in a table.
• Routine Privileges: Apply to stored procedures and functions.

Common Privileges in MySQL

• SELECT – Allows reading rows from tables.
• INSERT – Allows inserting rows into tables.
• UPDATE – Allows modifying existing rows.
• DELETE – Allows deleting rows.
• CREATE – Allows creating new databases and tables.
• DROP – Allows deleting databases and tables.
• GRANT OPTION – Allows granting privileges to other users.
• ALL PRIVILEGES – Grants all privileges on the specified level.

Creating Users in MySQL

Before granting privileges, you must first create a user account.

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

Example:

CREATE USER 'john'@'localhost' IDENTIFIED BY 'johnpassword';

Granting Privileges

The GRANT statement is used to assign one or more privileges to a user at various levels (global, database, table, column, etc.).

Basic Syntax

GRANT privileges ON database.table TO 'username'@'host';

Granting Global Privileges

This grants privileges across all databases and tables on the server.

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';

This command grants full access to all databases for user 'admin' on localhost.

Granting Database-Level Privileges

GRANT SELECT, INSERT, UPDATE ON companydb.* TO 'employee'@'localhost';

This allows the user to read, insert, and update data in all tables of the companydb database.

Granting Table-Level Privileges

GRANT SELECT, DELETE ON companydb.employees TO 'auditor'@'localhost';

This grants SELECT and DELETE access to only the employees table in the companydb database.

Granting Column-Level Privileges

GRANT SELECT (first_name, last_name), UPDATE (email) ON companydb.employees TO 'clerk'@'localhost';

The above command allows the user to read the first_name and last_name columns and update the email column.

Granting Routine Privileges

GRANT EXECUTE ON PROCEDURE companydb.calc_bonus TO 'manager'@'localhost';

Gives the user permission to run the calc_bonus stored procedure.

Granting WITH GRANT OPTION

GRANT SELECT ON companydb.* TO 'john'@'localhost' WITH GRANT OPTION;

This allows the user to grant the same privileges to other users. Use this option carefully.

Viewing Granted Privileges

SHOW GRANTS Statement

SHOW GRANTS FOR 'john'@'localhost';

This command displays the privileges that have been granted to the user.

Example Output

GRANT SELECT ON `companydb`.* TO 'john'@'localhost'

Revoking Privileges

To remove privileges from a user, use the REVOKE statement.

Basic Syntax

REVOKE privileges ON database.table FROM 'username'@'host';

Revoking Database-Level Privileges

REVOKE INSERT, UPDATE ON companydb.* FROM 'employee'@'localhost';

Revoking Table-Level Privileges

REVOKE DELETE ON companydb.employees FROM 'auditor'@'localhost';

Revoking Column-Level Privileges

REVOKE SELECT (first_name), UPDATE (email) ON companydb.employees FROM 'clerk'@'localhost';

Deleting User Accounts

DROP USER 'john'@'localhost';

This removes the user and all associated privileges.

Flushing Privileges

Normally, GRANT and REVOKE take effect immediately. However, if you manually modify the MySQL privilege tables, you must run:

FLUSH PRIVILEGES;

Understanding the mysql.user Table

MySQL stores all user accounts and privileges in the mysql system database. The user table contains global privileges, while other tables like db, tables_priv, columns_priv, and procs_priv store more specific ones.

Example Query

SELECT User, Host, Select_priv, Insert_priv FROM mysql.user;

Securing Privileges - Best Practices

1. Principle of Least Privilege

Only grant the minimum permissions necessary for the user to perform their tasks.

2. Avoid Using Root for Application Access

Create specific users with limited permissions for applications instead of using the root user.

3. Avoid WITH GRANT OPTION Unless Necessary

This option can be dangerous if misused. Limit it to admin-level users.

4. Periodically Audit Privileges

Use SHOW GRANTS and system table queries to review access levels.

5. Separate Read and Write Access

Use different users for read-only and write operations, especially in applications.

Using GRANT for Remote Users

GRANT ALL PRIVILEGES ON reports.* TO 'report_user'@'192.168.1.100' IDENTIFIED BY 'password';

This allows a user to connect from a remote IP address with specific privileges.

Backing Up and Restoring Privileges

Exporting Privileges

Run the following command to dump user privileges along with databases:

mysqldump --routines --triggers --events --all-databases --add-drop-database --flush-privileges > full_backup.sql

Restoring Privileges

mysql -u root -p < full_backup.sql

Using Proxy Users (MySQL Enterprise)

MySQL Enterprise Edition supports proxy users to control authentication and privilege mapping across systems.

GRANT PROXY ON 'original_user' TO 'proxy_user'@'localhost';

Revoking ALL Privileges

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user1'@'localhost';

This revokes everything, including the ability to grant privileges to others.

Privilege Changes in MySQL 8.0

MySQL 8 introduced the concept of roles and improved privilege granularity.

Example: Creating and Granting a Role

CREATE ROLE 'app_readonly';
GRANT SELECT ON appdb.* TO 'app_readonly';
GRANT 'app_readonly' TO 'appuser'@'localhost';
SET DEFAULT ROLE 'app_readonly' TO 'appuser'@'localhost';

Revoking a Role

REVOKE 'app_readonly' FROM 'appuser'@'localhost';

Managing user access using the GRANT and REVOKE statements is foundational for secure and effective MySQL administration. With a clear understanding of privilege levels—global, database, table, column, and routine—administrators can precisely control access. Always follow best practices such as least privilege, regular auditing, and privilege separation for roles. With MySQL 8.0's role-based privilege management and advanced GRANT capabilities, managing users is more powerful and flexible than ever before.