Share your love
How To Create a New User on MySQL
In this guide, we want to teach you How To Create a new User and Grant Permissions in MySQL.
MySQL is the world’s most popular open-source database. According to DB-Engines, MySQL ranks as the second-most-popular database, behind Oracle Database.
Since MySQL is open source, it includes numerous features developed in close cooperation with users over more than 25 years. So it’s very likely that your favorite application or programming language is supported by MySQL Database.
what you read in this post?
Steps To Create a New User and Grant Permissions in MySQL
To complete this guide, you must have access to MySQL Database. In this guide, we have installed MySQL on Rocky Linux 8. To do this, you can follow our guide on How To Install MySQL on Rocky Linux 8.
You can use the following instructions in any Linux system that you have MySQL installed on it.
For example, you can follow these guides to install MySQL:
How To Install MySQL 8.0 on Debian 11
How To Install MySQL on Centos 7
Now follow the steps below to complete this guide.
Create a New User in MySQL
At this point, you need to log in to your MySQL shell. To do this, you can use the command below:
sudo mysqlNote: If you have configured a password for your MySQL, you need to use the command below instead:
sudo mysql -u root -pOutput
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.30 Source distribution
Copyright (c) 2000, 2022, 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 that you have logged in to your MySQL shell, you can use the following syntax to create a user:
mysql> CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';If you only plan to access this user locally from your server, you can specify localhost.
As an alternative, you can leave out the WITH authentication_plugin portion of the syntax entirely to have the user authenticate with MySQL’s default plugin, caching_sha2_password.
For example, we create a user named orcauser with a strong password:
mysql> CREATE USER 'orcauser'@'localhost' IDENTIFIED BY 'strongpassword';Output
Query OK, 0 rows affected (0.04 sec)
Note: There is a known issue with some versions of PHP that causes problems with caching_sha2_password. If you plan to use this database with a PHP application — phpMyAdmin, for example — you may want to create a user that will authenticate with the older, though still secure, mysql_native_password plugin instead:
mysql> CREATE USER 'orcauser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'strongpassword';If you aren’t sure, you can always create a user that authenticates with caching_sha2_plugin and then ALTER it later on with this command:
mysql> ALTER USER 'orcauser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'strongpassword';Grant User Permissions
At this point, you have created your user in MySQL, now you can grant them the appropriate privileges. The general syntax to give the privileges is shown below:
mysql> GRANT PRIVILEGE ON database.table TO 'username'@'host';The PRIVILEGE value in this example syntax defines what actions the user is allowed to perform on the specified database and table. You can grant multiple privileges to the same user in one command by separating each with a comma. You can also grant a user privileges globally by entering asterisks (*) in place of the database and table names. In SQL, asterisks are special characters used to represent “all” databases or tables.
For example, to grant privileges to orcauser, you can use the command below:
mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'orcauser'@'localhost' WITH GRANT OPTION;Also, you can use the command below to grant all privileges to the user:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'orcauser'@'localhost' WITH GRANT OPTION;To apply the changes, you need to Flush the Privileges by using the command below:
mysql> FLUSH PRIVILEGES;Revoke User Permissions
If you plan to revoke permission from a user, you can use the following syntax:
mysql> REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';To see the current permissions of a user, you can use the command below:
mysql> SHOW GRANTS FOR 'username'@'host';Delete a User in MySQL
Just as you can delete databases with DROP, you can use DROP to delete a user:
mysql> DROP USER 'username'@'localhost';After you have created your new user and granted the privileges, you can use the command below to exit from the MySQL shell:
mysql> exitLog in to your Shell with a New MySQL User
At this point, you can access your MySQL shell with your new user by using the command below:
sudo mysql -u orcauser -pThe -p flag will cause the MySQL client to prompt you for your MySQL user’s password in order to authenticate.
Conclusion
At this point, you have learned to create a New user in MySQL and grant the permissions for it. Also, you have learned to revoke permission and delete a user in MySQL.
Hope you enjoy it.
You may be like these articles:
