MySQL create user account and grant privileges
MySQL can be said to be one of the leading and widely used open source database management systems. It is the first choice of relational database management system in most web hosting companies and other cloud providers.
In this tutorial, we will study how to create a MySQL user account and grant permissions to allow them to access and manage the created database.
prerequisites
Before starting, this tutorial assumes that you have installed a MySQL database or MariaDB instance, which is a fork of MySQL on your system. We already know how to install MySQL 8 on Ubuntu, so take a look.
Access MySQL Shell
Once MySQL or MariaDB is installed and secured, you can access the shell by running the following command
$ mysql -u root -p
After that, you will be prompted for the root password, and after providing the password, press 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 below
CREATE USER 'linoxide_user'@'localhost' IDENTIFIED BY '[email protected]';
MySQL users include 2 parts: username with hostname. In the above command, the username is linoxide_user And the hostname is localhost
The host name part indicates the user linoxide_user You can only connect to MySQL locally (ie from the server hosting MySQL).
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 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 ‘%’ as a wildcard.
CREATE USER 'linoxide_user'@'%' IDENTIFIED BY '[email protected]';
Granting permissions to MySQL users
When creating a user, you may want to assign certain privileges to the database engine. These privileges grant users access to the database and enable them to make some modifications to the database.
In MySQL, users can be assigned many privileges. However, in this guide, we clarify the most commonly used privileges:
select: This allows users to read the entire database
Update: This allows users to update rows in a table
insert: Allow users to insert rows in a given table
delete: Grant usage rights to delete rows in a given table
create: Allow users to create databases and tables
decline: Grant users permission to delete entire databases and tables
To grant users some privileges to access the database, use the following syntax:
GRANT privilege1, privilege2 ON database_name.* TO 'database_user'@'localhost';
E.g:
GRANT SELECT, INSERT, ON database_name.* TO [email protected]'localhost';
To grant all privileges to a specific user on a specific table in the database, issue the following command:
GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';
To grant the user all privileges for all databases, run the following command:
GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost
Display user account permissions
After a user is created and assigned privileges in the database, you can easily check and confirm the privileges granted to the user using the following command: SHOW GRANTS The command is shown below:
SHOW GRANTS FOR 'database_user'@'localhost';
Revoke privileges for MySQL users
In addition, you can choose to revoke one or more privileges of the MySQL user. The syntax is almost similar to that when granting user privileges.
REVOKE ALL PRIVILEGES ON database_name.* FROM 'database_user'@'localhost';
Delete existing MySQL user
To delete or delete a MySQL user, use DROP The command looks like this:
DROP USER 'database_user'@'localhost';
E.g:
in conclusion
This tutorial introduces the basic concepts of creating and managing MySQL users by assigning and revoking privileges. For starters, this should be a good starting point. We hope you can easily create MySQL user accounts and manage privileges and users.
