You can have better control of access to your database and protect sensitive information by creating separate user accounts and assigning specific privileges to them. You can also keep track of who has access to which parts of your database, making it easier to manage and maintain.
Granting only the necessary privileges to each user can improve the performance of your database, as it will only allow users to access the data and resources they need. This guide focuses on creating new MySQL user accounts and granting them privileges while covering the following points.
- Check MySQL Version
- Login as root User
- Create New User Account
- Grant & Display Permissions to a MySQL User
- Revoking the Permissions
- Removing an Existing Account
The above points will now be explained in detail, as follows.
Checking MySQL Version
First, you should know the version of MySQL installed on your system as there’s a slight difference between version 5.7 and above to the older versions; use this command to get info about the version.
$ mysql -V
We recommend that our followers always use the latest (stable) version because of the new features and bug fixes.
Login as root User
In the Linux world, the root is the ultimate user with the right to grant or revoke privileges to any other Linux account; in simple words, it is a user account with superpowers.
To create or grant permissions to users in MySQL, you must log into the MySQL Shell using this command in the terminal (for version 5.6 or earlier).
$ sudo mysql -u root -p
However, as seen below, our version is ‘8.0.31’, above 5.6, so we’d use this command to login into MySQL Shell.
$ sudo mysql
You may be prompted to enter the password, which won’t be visible while typing, so enter the correct password and hit the “Enter” key.
Create a New User Account
You must add these two things to create a new user in MySQL Shell.
- User Name is what the user would use to login into MySQL.
- Host Name is the identity of the user, which could be some IP (remote) or LocalHost (for the same network)
When you have met the above two requirements, use this command in MySQL Shell to create a new user.
> CREATE USER 'New-UserName'@'localhost or IP' IDENTIFIED BY 'User-Password';
Now, if you want to connect from any host, use the following way to create a new user.
> CREATE USER 'New-UserName'@'%' IDENTIFIED BY 'User-Password';
Grant & Display Privileges to a MySQL User Account
The Privileges make Database Management easy, and you can do that in MySQL. Here are some standard privileges.
- All Privileges
- Create Only
- Drop Only
- Delete Only
- Insert Only
- Select/Read Only
- Update Only
Here’s the format you can grant permissions to other users as root.
> GRANT permission1, permission2 ON Database-Name.Table-Name TO 'user-name'@'localhost or IP';
As seen in the above image, we’ve replaced the “Table-Name” with “*”, which means all, so we gave all privileges to the Test (User) on testDB (Database).
Here is an official guide using which you can grant the above-listed privileges to the users.
To display the granted privileges to any user created in MySQL, use this command in MySQL Shell.
> SHOW GRANTS FOR 'user-name'@'localhost or IP';
As seen in the above image, we’ve granted all privileges to Test (User) on testDB (Database).
Revoking the Permissions
Sometimes, the Database Administrators need to revoke some or all permissions from a MySQL user, which is done using this command.
> REVOKE 'privileges' ON database-name.* FROM 'User-Name'@'localhost or IP';
In the above image, we’ve revoked the insert permissions from the Test (user) from testDB (Database), and you can replace the INSERT with any privilege(s) you want to remove.
Removing an Existing Account
When an employee leaves the organization, the database administrator can remove the user account using this command in MySQL Shell.
> DROP USER 'user-name'@'localhost or IP';
As seen in the above image, we’ve removed the user “test” from MySQL, and you can do the same by replacing the “test” with the desired user name.
Creating new user accounts and granting and revoking permissions is one of the essential duties of a Database Administrator, so one should know what they’re doing. This guide teaches how to create new user accounts and grant or revoke their privileges.