MySQL is arguably one of the leading and widely used opensource 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.
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. We have seen how to install MySQL 8 on Ubuntu before, so have a look.
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
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';
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';
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.