MySQL is one of the most popular and open-source relational database management systems around the world. It provides a lot of management options like creating and managing a user with specific permissions to databases and tables.
When you hire a new developer to manage MySQL databases then you may need to grant specific permission to manage those databases such as deleting or modifying the information. In that case, it is essential for your to know how to grant specific privileges to the MySQL user account.
In this post, we will show you how to create a MySQL user and grant specific permissions
Prerequisites
- A Linux VPS with MySQL server installed.
- Access to the root user account (or access to an admin account with root privileges)
Log in to the Server & Update the Server OS Packages
First, log in to your Debian 10 server via SSH as the root user:
ssh root@IP_Address -p Port_number
You will need to replace ‘IP_Address’ and ‘Port_number’ with your server’s respective IP address and SSH port number. Additionally, replace ‘root’ with the username of the admin account if necessary.
Before starting, you have to make sure that all OS packages installed on the server are up to date. You can do this by running the following commands:
apt-get update -y apt-get upgrade -y
Create a New MySQL User
First, you will need to connect to the MySQL shell using the MySQL root user. You can connect it using the following command:
mysql -u root -p
You will be asked to provide your MySQL root password. Once you are connected to the MySQL shell, you should see the following output:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.25-0ubuntu0.20.04.1 (Ubuntu) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Now, create a new MySQL user with the following command:
mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'userpassword';
Where:
- username is the name of the MySQL user you want to create.
- userpassword is the password of the MySQL user.
- localhost is a host from where you want to connect to MySQL.
You will need to replace the localhost with the remote server IP address if you want to connect the MySQL from the remote server. In that case, you can create a new MySQL user with the following command:
mysql> CREATE USER 'username'@'192.168.0.100' IDENTIFIED BY 'userpassword';
- 192.168.0.100 is the IP address of the remote server.
Grant Privileges to a MySQL User Account
There are multiple types of permissions available in MySQL that you can provide to the MySQL user account. Some of the most commonly used permissions are shown below:
- ALL PRIVILEGES: – This will allow MySQL users to run any query on the specified database.
- CREATE: – This will allow MySQL users to create databases and tables.
- DELETE: – This will allow MySQL users to delete rows from the table.
- DROP: – This will allow MySQL users to drop databases and tables.
- INSERT: – This will allow MySQL users to insert rows to a specific table.
- SELECT: – This will allow MySQL users to read a database.
- UPDATE: – This will allow MySQL users to update table rows.
- GRANT OPTION: – This will allow MySQL users to grant or remove other users’ privileges.
To grant all privileges to the MySQL user account on a specific database, run the following command:
mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';
To grant all privileges to the MySQL user on a specific table from a database, run the following command:
mysql> GRANT ALL PRIVILEGES ON dbname.tablename TO 'username'@'localhost';
To grant multiple privileges like, SELECT, INSERT, DELETE to the MySQL user on a specific database, run the following command:
mysql> GRANT SELECT, INSERT, DELETE ON dbname.* TO username@'localhost';
You will need to run the flush privileges command for the changes to take effect.
mysql> FLUSH PRIVILEGES;
View MySQL User Account Privileges
If you want to view the privileges assigned to the MySQL user account, run the following command:
mysql> SHOW GRANTS FOR 'username'@'localhost';
You should see the following output:
+--------------------------------------------------------------+ | Grants for username@localhost | +--------------------------------------------------------------+ | GRANT USAGE ON *.* TO `username`@`localhost` | | GRANT ALL PRIVILEGES ON `dbname`.* TO `username`@`localhost` | +--------------------------------------------------------------+ 2 rows in set (0.00 sec)
Remove MySQL User Account Privileges
You can use the REVOKE command to remove the permission of the MySQL user from the specific database or table.
For example, to remove ALL PRIVILEGES from the database, run the following command:
mysql> REVOKE ALL ON *.* FROM 'username'@'localhost';
Next, you will need to run the flush privileges command for the changes to take effect.
mysql> FLUSH PRIVILEGES;
Of course, you don’t have to manage MySQL server if you use one of our Managed Hosting services, in which case you can simply ask our expert Linux admins to manage the MySQL server for you. They are available 24/7 and will take care of your request immediately.
If you liked this post on How to Create a New User and Grant Privileges in MySQL, please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.