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 ‘[email protected]’;
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 ‘[email protected]’;
To grant access from any host system, use the ‘%’ which acts as a wildcard.CREATE USER ‘linoxide_user’@’%’ IDENTIFIED BY ‘[email protected]’;
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 [email protected]’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.mysql create user with grant option,mysql create database