MySQL Create User Accounts and Grant Privileges

MySQL is arguably one of the leading and widely used open-source database management systems. It's the preferred relational database management system of choice in most web hosting companies and other cloud providers.

In this tutorial, we will look at how you can create MySQL user accounts and grant privileges to allow them to access and manage the created databases.

Prerequisites

Before you get started, the tutorial assumes that you have already installed an instance of MySQL database or MariaDB which is a fork of MySQL on your system.

Create MySQL Database Using Command...

To view this video please enable JavaScript, and consider upgrading to a web browser that supports HTML5 video

Create MySQL Database Using Command Prompt XAMPP | 2020 Accessing MySQL shell

Once MySQL or MariaDB is installed and secured, you can access the shell by running the command

$ mysql -u root -p

Thereafter, you will be prompted for the root password, and when provided, hit ENTER to access the shell

How to create a user in MySQL

To add a user in MYSQL,  use the mysql create user command as shown

CREATE USER 'linoxide_user'@'localhost' IDENTIFIED BY 'P@ssword123';

The MySQL user comprises 2 sections: The username and the hostname. From the command above the username is linoxide_user and while the hostname is localhost

The hostname section indicates that the user linoxide_user can only connect to MySQL locally, i.e. from the server where MySQL is hosted.

To grant access from an external host system, replace localhost with the IP address of the remote host.

For example, to grant access from a host system with an IP '172.16.10.10' , run:

CREATE USER 'linoxide_user'@'172.16.10.10' IDENTIFIED BY 'P@ssword123';

To grant access from any host system, use the '%' which acts as a wildcard.

CREATE USER 'linoxide_user'@'%' IDENTIFIED BY 'P@ssword123';

Granting Privileges to a MySQL user

When a user is created, you may want to assign certain privileges to the database engine. These privileges grant user access to databases and enable them to perform some modifications to the databases.

In MySQL, there are myriad of privileges that can be assigned to a user. In this guide, however, we shed light on the most commonly used privileges:

SELECT: This allows a user to read the entire database

UPDATE: This allows a user to update the rows in a table

INSERT: Allows a user to insert rows in a given table

DELETE: Grants a use permission to delete rows in a given table

CREATE: Allows a user to create databases and tables

DROP: Grants the user permission to delete the entire database and the tables

To grant a user certain privileges to  access to a database, use the syntax shown below:

GRANT privilege1, privilege2 ON database_name.* TO 'database_user'@'localhost';

For example:

GRANT SELECT, INSERT, ON database_name.* TO database_user@'localhost';

To grant all privileges to a specific user on a specific table in a database issue the command:

GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';

To grant a user  all the privileges to all the databases run the command:

GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost

Displaying user account privileges

Once a user had been created and assigned privileges on the databases, you can easily check and confirm the privileges granted on the user using the SHOW GRANTS commands as illustrated below:

SHOW GRANTS FOR 'database_user'@'localhost';

Revoking privileges from a MySQL user

Additionally, one can choose to revoke one or multiple privileges from a MySQL user. The syntax is almost similar to when granting the user privileges.

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

Delete an existing MySQL user

To remove or delete a MySQL user, run use the DROP command as shown:

DROP USER 'database_user'@'localhost';

For example:

Conclusion

This tutorial covers the basic concepts of creating and managing MySQL users by assigning and revoking privileges. This should be a great starting point for beginners. We hope you can easily create MySQL user accounts and manage privileges and users.