Grant Privileges to Users with the GRANT Statement in MySQL

As a database administrator or maintainer, you need more precise privileges control for database security. You can give different privileges to different users.

After you create a new user, the new user can log in to the MySQL database server, but he may not have any privileges. After he was granted privileges on databases and tables, he can he perform operations such as selecting databases and queries.

If a statement fails with ERROR 1044, the connected account may lack the specific privilege that operation requires. Check the active account and grant only the needed scope; see MySQL Error 1044 troubleshooting.

If the error says a command is denied for a named table, see MySQL Error 1142 troubleshooting.

In MySQL, GRANT statements are used to grant privileges to users.

MySQL `GRANT` syntax

The following is the syntax of MySQL GRANT:

GRANT privilege_type [,privilege_type],..
ON privilege_object
TO user_account;

In this syntax:

privilege_type: Privilege type. Privileges to be granted to the user. Such as: ALL, SELECT, UPDATE, DELETE, ALTER, DROP and INSERT etc. For more details, please refer to: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_all
privilege_object: Privilege object. It can be global objects, or objects in a certain database. Such as: *, *.*, db_name.*, db_name.table_name, table_name etc.
user_account: User account. Specify the user name and host explicitly as 'username'@'host'; omitting the host is equivalent to the broad wildcard %, which is deprecated in current MySQL.

Here are a few common cases:

Grant global privileges

ALL on *.* grants privileges server-wide. Reserve it for trusted administrator accounts; application users usually need narrower database- or table-level grants.
```
GRANT ALL ON *.* TO 'db_admin'@'localhost';
```
This grants server-wide privileges to a trusted administrator account. Do not use this scope for an application account.

ALL does not include GRANT OPTION or PROXY. Add WITH GRANT OPTION only if this administrator must delegate privileges; doing so lets the account grant onward the privileges it holds. See the MySQL GRANT reference.
Grant specific privileges on all objects in the database
```
GRANT SELECT, INSERT ON sqliz.* TO 'sqliz'@'localhost';
```
This grants only SELECT and INSERT on objects in sqliz to the sqliz account. Add other privileges only when the application needs them.
Grant query and insert privileges on a table
```
GRANT SELECT, INSERT ON sqliz.test_table TO 'sqliz'@'localhost';
```
Here, the SELECT and INSERT privileges on test_table in the sqliz database are granted to the user sqliz@localhost
Grant a privilege on a single column
```
GRANT SELECT (`email`) ON `sqliz`.`users` TO 'report_reader'@'localhost';
```
This grants access only to the email column. MySQL supports column-level SELECT, INSERT, REFERENCES, and UPDATE; see the GRANT column privilege syntax. If a statement is denied for a particular column, see MySQL Error 1143 troubleshooting.

MySQL `GRANT` Examples

Follow the steps below to execute some MySQL GRANT Examples:

Connect to the MySQL server using the mysql client tool and log in as root user :
```
mysql -u root -p
```
Enter the password for the root account and press Enter:
```
Enter password: ********
```

Display users of the current MySQL server :

SELECT user FROM mysql.user;

+------------------+
| user             |
+------------------+
| root             |
| test_role1       |
| test_role2       |
| testuser         |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+

Create a new local user named sqliz:
```
CREATE USER 'sqliz'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
```
MySQL returns the generated password in its result; save it securely. This syntax requires MySQL 8.0.18 or later.

Show all users again:

SELECT user FROM mysql.user;

+------------------+
| user             |
+------------------+
| root             |
| test_role1       |
| test_role2       |
| testuser         |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
| sqliz            |
+------------------+

The user sqliz was successfully created.

Open a new session and log in to MySQL as sqliz user :
```
mysql --user=sqliz --password
```
Enter the generated password returned by CREATE USER; it is not necessarily the same as the account name:
```
Enter password: ********
```

Show the list of accessible databases of the user sqliz :

SHOW DATABASES;

The following is a list of databases that sqliz can be accessed:

+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

Go to user root’s session and create a new database called sqlizdb:
```
CREATE DATABASE sqlizdb;
```
Grant only the privileges used by the remaining example. The user must create a table, insert a row, and read it:
```
GRANT CREATE, SELECT, INSERT ON sqlizdb.* TO 'sqliz'@'localhost';
```

Switch to the session of sqliz and display databases:

SHOW DATABASES;

Now, sqliz you can see sqlizdb:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| sqlizdb            |
+--------------------+

Select the sqlizdb database as default database :
```
USE sqlizdb;
```
Henceforth, the default database is: sqlizdb. All subsequent operations are performed in this database by default.

Create a new table named test_table:

CREATE TABLE test_table(
    id int AUTO_INCREMENT PRIMARY KEY,
    txt varchar(100) NOT NULL
);

Display all tables from the sqlizdb database :

SHOW TABLES;

Users sqliz can see the test_table table:

+-------------------+
| Tables_in_sqlizdb |
+-------------------+
| test_table        |
+-------------------+

Insert a new row into the test_table table :

INSERT INTO test_table(txt)
VALUES('Hello World.');

Query rows from the test_table table :)
```
SELECT * FROM test_table;
```
Here is the output:
```
+----+--------------+
| id | txt          |
+----+--------------+
|  1 | Hello World. |
+----+--------------+
```
The sqliz account can create tables in sqlizdb, insert rows, and read them. Other operations remain denied unless separately granted.

Conclusion

In this article, you learned how to grant different privileges to users using MySQL GRANT statements.

MySQL GRANT syntax

MySQL GRANT Examples

Conclusion

On this page

MySQL `GRANT` syntax

MySQL `GRANT` Examples