MySQL GRANT Statement

The GRANT statement in MySQL is used to assign privileges to MySQL user accounts. It defines what operations users can perform on database objects, like tables and views, ensuring controlled access to the database.

Usage

The GRANT statement is employed when you need to give specific users permissions to execute certain types of SQL operations. It helps manage user rights efficiently and securely.

GRANT privileges
ON database_name.table_name
TO 'user'@'host'
[IDENTIFIED BY 'password'];

In this syntax, privileges are the permissions granted, database_name.table_name specifies the scope, user is the MySQL username, and host is the location from which the user connects. Note that the IDENTIFIED BY 'password' clause is only applicable when creating a new user. In MySQL 8.0 and later, it is typically recommended to use CREATE USER followed by GRANT for user creation and privilege assignment.

Examples

1. Granting Basic Privileges

GRANT SELECT, INSERT
ON mydb.mytable
TO 'user1'@'localhost';

This example grants the SELECT and INSERT privileges on mydb.mytable to user1 connecting from localhost.

2. Granting All Privileges on a Database

GRANT ALL PRIVILEGES
ON mydb.*
TO 'user2'@'192.168.1.1';

Here, user2 is granted all privileges on the entire mydb database from the host 192.168.1.1.

3. Granting Privileges with Password

GRANT SELECT, UPDATE
ON mydb.mytable
TO 'user3'@'%' IDENTIFIED BY 'securepassword';

This statement grants SELECT and UPDATE privileges on mydb.mytable to user3 from any host (%), setting a specified password. Using % allows connections from any host, which could have security implications.

Additional Information

Global Privileges

To grant global privileges that apply to all databases, use the ON *.* syntax. This grants the specified privileges globally.

Revoking Privileges

To revoke privileges, use the REVOKE statement:

REVOKE privileges
ON database_name.table_name
FROM 'user'@'host';

This removes the specified privileges from the user.

Tips and Best Practices

• Limit privileges. Only grant the necessary privileges to users to minimize security risks.
• Use specific hostnames. Where possible, specify hostnames or IP addresses to restrict access, rather than using % for all hosts.
• Manage passwords securely. Always use strong, unique passwords when setting up user accounts.
• Regularly review permissions. Periodically audit and revoke unnecessary privileges to maintain optimal security. Utilize the mysql.user table or relevant information schema views to review existing user privileges.
• Document changes. Keep a record of all GRANT statements issued for easy tracking of permissions.