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.

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. It uses the form username@host.

Here are a few common cases:

Grant global privileges
```
GRANT ALL ON *.* TO sqliz@localhost;
```
Here, all privileges of all objects in all databases are granted to the user sqliz@localhost.
Grant privileges on all objects in the database
```
GRANT ALL ON sqliz.* TO sqliz@localhost;
```
Here, all privileges on of the sqliz database are granted to the user sqliz@localhost
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

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 user named sqliz:

CREATE user 'sqliz'@'%' IDENTIFIED by 'SqLiZ9879123!';

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 -u sqliz -p
```
Enter your password sqliz and press Enter:
```
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 all privileges on the sqlizdb database to the user sqliz using the GRANT statement :
```
GRANT ALL PRIVILEGES ON sqlizdb.* TO sqliz@'%';
```

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. |
+----+--------------+

Now, sqliz can do anything in the sqlizdb database.

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