MySQL is one of the most popular database management systems around the world. This open-source database management system helps you to store, organize, and retrieve data. MySQL comes with a lot of options that allow you to grant specific permissions to databases based on user needs.

This post will show you how to create a user and grant permissions in MySQL 8.

Step 1 – Update Server

Once you are logged in to your CentOS 8 server, run the following command to update your base system with the latest available packages.

dnf update -y

Step 2 – Install MySQL 8

First, you will need to install MySQL 8 on your server. You can install it with the following command:

dnf install mysql-server -y

Once the installation is completed, start the MySQL service and enable it to start at system reboot:

systemctl start mysqld
systemctl enable mysqld

Step 3 – Create a Database and User in MySQL 8

In this section, we will create a database and user in MySQL.

First, connect to the MySQL shell using the following command:

mysql

Once you are connected, create a database named testdb and testdb1 using the following command:

CREATE DATABASE testdb;
CREATE DATABASE testdb1;

Next, display all the databases using the following command:

show databases;

You should get all databases in the following output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
| testdb1            |
+--------------------+
6 rows in set (0.01 sec)

Next, create a user named testuser for localhost and set a password using the following command:

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'password';

We have created a testuser for localhost, which means testuser will be able to connect to MySQL only from the localhost.

If you want to create a MySQL user and grant access from the remote machine with IP 192.168.10.100, run the following command:

CREATE USER 'testuser'@'192.168.10.100' IDENTIFIED BY 'password';

If you want to create a MySQL user and grant access from all remote hosts, run the following command:

CREATE USER 'testuser'@'%' IDENTIFIED BY 'password';

Step 4 – Grant Privileges to a MySQL User Account

MySQL provides several types of user privileges that you can grant to a user. Some of them are listed below:

  • ALL PRIVILEGES: Used to grant all privileges to the user account.
  • INSERT: This allows the user to insert rows into a table.
  • SELECT: Allows users to read a database.
  • UPDATE: Allows users to update table rows.
  • CREATE: Allows users to create a database and table.
  • DELETE: This allows the user to delete rows from a table.
  • DROP: Allows users to delete a database and table.

Let’s see some examples:

To grant all the privileges to testuser on testdb database, run the following command:

GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost';

To grant all the privileges to testuser on all databases, run the following command:

GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost';

To grant only SELECT, INSERT and DELETE privileges to testuser on testdb1 database, run the following command:

GRANT SELECT, INSERT, DELETE ON testdb1.* TO testuser@'localhost';

Step 5 – Show Granted Privileges

You can use the SHOW GRANTS command to display the privileges that you have granted to MySQL users.

Run the following command to display all granted privileges to testuser:

SHOW GRANTS FOR 'testuser'@'localhost';

You should get the following output:

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------a+
| Grants for testuser@localhost                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `testuser`@`localhost`                                                                                                                                               |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `testuser`@`localhost` |
| GRANT ALL PRIVILEGES ON `testdb`.* TO `testuser`@`localhost`                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| GRANT SELECT, INSERT, DELETE ON `testdb1`.* TO `testuser`@`localhost`                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Step 6 – Revoke Privileges from a MySQL User Account

If you want to revoke or restore all privileges from a MySQL user over a database, use the revoke command as shown below:

REVOKE ALL PRIVILEGES ON testdb.* FROM 'testuser'@'localhost';

Step 7 – Delete MySQL Database and User Account

To delete a MySQL database, use the following command:

DROP DATABSE testdb;

To delete a MySQL user account, use the following command:

DROP USER 'testuser'@'localhost';

Conclusion

This guide explained how to create a MySQL database, user, and grant privileges in MySQL 8. You can now experiment with different permissions settings for your database and apply them in the development environment. Try it today on your dedicated server from Atlantic.Net!