Atlantic.Net Blog

How To Create a New User and Grant Permissions in MySQL8 on CentOS8

Hitesh Jethva
by Atlantic.Net (272 posts) under Dedicated Server Hosting, Tutorials
0 Comments

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 allows you to grant specific permissions to databases based on user needs.

In this post, we will show you how to create a user and grant permissions in MySQL 8.

Prerequisites

  • A fresh CentOS 8 server on the Atlantic.Net Cloud Platform
  • A root password configured on your server

Step 1 – Create Atlantic.Net Cloud Server

First, log in to your Atlantic.Net Cloud Server. Create a new server, choosing CentOS 8 as the operating system with at least 2GB RAM. Connect to your Cloud Server via SSH and log in using the credentials highlighted at the top of the page.

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 Server 8

First, you will need to install MySQL server 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 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 [email protected]'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 [email protected]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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

In this guide, we 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!

Get A Free To Use Cloud VPS

Free Tier Includes:
G3.2GB Cloud VPS Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


Looking for a Hosting Solution?

We Provide Cloud, Dedicated, & Colocation.

  • Seven Global Data Center Locations.
  • Flexible Private, Public, & Hybrid Hosting.
  • 24x7x365 Security, Support, & Monitoring.
Contact Us Now! Med Tech Award FTC
SOC Audit HIPAA Audit HITECH Audit

Recent Posts

Get started with 12 months of free cloud VPS hosting

Free Tier includes:
G3.2GB Cloud VPS Server Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


New York, NY

100 Delawanna Ave, Suite 1

Clifton, NJ 07014

United States

San Francisco, CA

2820 Northwestern Pkwy,

Santa Clara, CA 95051

United States

Dallas, TX

2323 Bryan Street,

Dallas, Texas 75201

United States

Ashburn, VA

1807 Michael Faraday Ct,

Reston, VA 20190

United States

Orlando, FL

440 W Kennedy Blvd, Suite 3

Orlando, FL 32810

United States

Toronto, Canada

20 Pullman Ct, Scarborough,

Ontario M1X 1E4

Canada

London, UK

14 Liverpool Road, Slough,

Berkshire SL1 4QZ

United Kingdom

Resources