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.