Mysql Permissions – Create, Update, Delete Database Users

In this gude, we are going to see how to manage user and database permissions in mysql, i.e. Creating mysql database, creating mysql user, granting user access to mysql databases, updating user permissions and deleting user and database when no longer needed.

MySQL is the most popular open-source relational database management system.

Mysql is commonly installed as part of the popular LAMP or LEMP (Linux, Apache/Nginx, MySQL/MariaDB, PHP/Python/Perl) stack. It implements the relational model and Structured Query Language (SQL) to manage and query data.

MySQL server allows us to create numerous user accounts and grant appropriate privileges so that the users can access and manage databases.

# Prerequisites

Admin access to mysql server

Note: All commands in this guide should be executed inside the MySQL shell as root or as a user with administrative privileges.

To login to mysql shell, use the following command. Subtitute 127.0.0.1 with your database hostname and root with the admin username:

1
mysql -h 127.0.0.1 -u root -p

You should see an output similar to this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
$ mysql -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52
Server version: 8.0.21 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

# Creating mysql database

In the mysql shell, execute this command to create a database

1
create database db_name;

Example output:

1
2
mysql> create database citizix_test;
Query OK, 1 row affected (0.00 sec)

# Create a new MySQL User Account

To connect to mysql, we will need a user and a password. Create a user with a password using this command:

1
create user 'db_user'@'%' identified by 'db_pass';

Example output:

1
2
mysql> create user 'citizix_user'@'%' identified by 'S0mStrongPa$$word';
Query OK, 0 rows affected (0.01 sec)

# Grant Privileges to a MySQL User Account

A number of mysql provileges can be granted. Check them here. Common privileges:

ALL PRIVILEGES - Grants all privileges
CREATE - Create databases and tables
DROP - Drop databases and tables
DELETE - Delete rows from a specific table
INSERT - Insert rows into a specific table
SELECT - Read database
UPDATE - Update table rows

When creating a user, you can either grant them a single permission, multiple permissions as a comma separated list or all permissions using the ALL PRIVILEGES keyword.

This is the command format:

1
2
GRANT permission1, permission2 ON database_name.table_name TO 'database_user'@'localhost';
grant all privileges on db_name.* to 'db_user'@'%';

Example Output:

1
2
mysql> grant all privileges on citizix_test.* to 'citizix_user'@'%';
Query OK, 0 rows affected (0.01 sec)

# Flush privileges

To reload the changes done when creating the user and applying them permissions, use this command:

1
flush privileges;

Output:

1
2
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

# Checking users in the system

Once the user is created, you can connect using that user using this command:

1
mysql -h 127.0.0.1 -u db_user -p

Example Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
$ mysql -h 127.0.0.1 -u citizix_user -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10669
Server version: 8.0.21 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

To get a list of users in the system, use this command:

1
SELECT user FROM mysql.user;

Sample Output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
mysql> SELECT user FROM mysql.user;
+------------------+
| user             |
+------------------+
| citizix_user     |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
7 rows in set (0.00 sec)

# Checking user permissions

To check users permissions for mysql users, use the following commannds: For the currentlly logged in user:

1
show grants

For another user:

1
show grants for db_user;

Sample output:

1
2
3
4
5
6
7
8
mysql> show grants for citizix_user;
+----------------------------------------------------------------+
| Grants for citizix_user@%                                      |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `citizix_user`@`%`                       |
| GRANT ALL PRIVILEGES ON `citizix_test`.* TO `citizix_user`@`%` |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)

# Revoke Privileges from a MySQL User Account

To revoke all privileges from a user account over a specific database, run the following command:

1
REVOKE ALL PRIVILEGES ON database_name.* FROM 'database_user'@'localhost';

Example output:

1
2
mysql> revoke all privileges on citizix_test.* from 'citizix_user'@'%';
Query OK, 0 rows affected (0.00 sec)

# Remove an Existing MySQL User Account

To delete a MySQL user account run this command:

1
drop user 'user'@'host';

Example output:

1
2
mysql> drop user 'citizix_user'@'%';
Query OK, 0 rows affected (0.01 sec)

# Removing database

To delete a database, use this command:

1
drop database db_name;

Example output:

1
2
mysql> drop database citizix_test;
Query OK, 0 rows affected (0.01 sec)

# Conclusion

This guide described how we would go about managing users and databases in mysql.

To summarize, the following are the commands used to create database, user and grant permissions:

1
2
3
create database citizix_test;
create user 'citizix_user'@'%' identified by 'S0mStrongPa$$word';
grant all privileges on citizix_test.* to 'citizix_user'@'%';

These are the commands used to revoke user access, delete user and delete database:

1
2
3
revoke all privileges on citizix_test.* from 'citizix_user'@'%';
drop user 'citizix_user'@'%';
drop database citizix_test;</code></pre>