As you know MySQL is the most popular open-source relational database management system. It allows users to store, organize, and retrieve data from the database. It has a variety of working options to grant privileges to specific users within the tables and database.
I am assuming you have installed MySQL software on your system already, if not, then you can read my another post how to install MySQL in Ubuntu 19.04.
I will cover in this article how to create a MySQL user account and grant permissions, and last how to delete MySQL user.
One, Important thing you should know, all commands will be executed inside a MySQL shell. So these commands will remain the same for Linux user as well as Windows user.
To access the MySQL shell type the following command in Linux terminal and enter the password for root user password when asked.
[email protected]:~$mysql -h localhost -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.16 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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>
Article Content
- How to create a MySQL user with password.
- How to grant Privileges user to access database
- Display MySQL user account privileges
- MySQL Delete user account
- Conclusion
How to create a MySQL user with Password
When you create a user account in MySQL, You should remember three things as described following:
Hostname: Do you know hostname? if No read article on how to change hostname in Linux. You will be a little bit familiar with hostname or computer name detailed information about hostname is here.
At the time of creating a new user in MySQL hostname is used to define permission for the user “From where a new user can log in and work”
- localhost: New user can log in only on localhost:
- If you set remote IP address then the user will be able to access MySQL from specific IP address only.
- Any host will grant permission to use MySQL from any computer or device worldwide.
User Name: Username is assigned name for new user.
Password: Password is the secret key of identification and provide password will work for same username only.
Example how to create a MySQL user on local host
mysql> CREATE USER 'ram'@'localhost' IDENTIFIED BY 'ExAdmin#123'
-> ;
Query OK, 0 rows affected (0.02 sec)
Example how to create a MySQL user on specific host
mysql> CREATE USER 'raj'@'192.168.0.10' IDENTIFIED BY 'ExTra#123'; Query OK, 0 rows affected (0.01 sec)
Example how to create a MySQL user on any host
mysql> CREATE USER 'shila'@'%' IDENTIFIED BY '[email protected]#123' -> ; Query OK, 0 rows affected (0.09 sec)
How to grant Privileges user to access database
MySQL has multiple types of privileges that can be assigned to a user account. To see the full list of privileges supported by MySQL Visit official website.
The most commonly used privileges for an user are:
- ALL PRIVILEGES – grants all privileges to a user account.
- CREATE – user account is allowed to create databases and tables.
- DROP – user account is allowed to drop databases and tables.
- DELETE – user account is allowed to delete rows from a specific table.
- INSERT – user account is allowed to insert rows into a specific table.
- SELECT – user account is allowed to read a database.
- UPDATE – user account is allowed to update table rows.
If you want to grant multiple specific privileges to a user account, you can use the following syntax:
mysql> GRANT first_permission, second_permission ON database_name.table_name TO ‘database_user’@’localhost’;
Grand All privileges on particular database for a user
mysql> GRANT ALL PRIVILEGES ON database_name.* TO ‘database_user’@’localhost’;
Grant All privileges to a user for all database on MySQL server.
mysql> GRANT ALL PRIVILEGES ON . TO ‘database_user’@’localhost’;
Grand all privileges to a user account over a specific table from a database:
mysql> GRANT ALL PRIVILEGES ON database_name.table_name TO ‘database_user’@’localhost’;
Grant multiple privileges to a user account over a specific database:
mysql> GRANT SELECT, INSERT, DELETE ON database_name.* TO database_user@’localhost’;
Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.
mysql> FLUSH PRIVILEGES;
Display MySQL user account privileges
If you are using MySQL 8.0 then you can use following command to display privileges of an user in MySQL database and other. May be it will not work on old version.
mysql> SHOW GRANTS FOR shila
-> ;
+-----------------------------------+
| Grants for shila@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO `shila`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)
mysql>
In above example, I found only one permission to usage of any database, but not other. So I am going to create a database ‘mydata’ by using following command.
mysql> CREATE DATABASE mydata
-> ;
Query OK, 1 row affected (0.10 sec)
mysql>
In this example I am going to set all privileges on mydata database to shila@% user.
mysql> GRANT ALL PRIVILEGES ON mydata.* TO 'shila'@'%'; Query OK, 0 rows affected (0.01 sec) mysql>
Again I used command to show the permission of shila user I found following the result
mysql> SHOW GRANTS FOR shila; +---------------------------------------------------+ | Grants for shila@% | +---------------------------------------------------+ | GRANT USAGE ON *.* TO `shila`@`%` | | GRANT ALL PRIVILEGES ON `mydata`.* TO `shila`@`%` | +---------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
MySQL Delete user account
Drop the username with the following the command. you must give the full address of username for example shila user was created for any host if you remember, then the complete name of this user will be ‘shila’@’%’
Example:
mysql> DROP USER 'shila'@'%'; Query OK, 0 rows affected (0.08 sec) mysql>
I used above command and shila user has been deleted, but the same command I used to delete ram user, but it didn’t work. See in Example
mysql> DROP USER 'ram'@'%'; ERROR 1396 (HY000): Operation DROP USER failed for 'ram'@'%' mysql>
What is wrong with this command?
There is nothing wrong with the command, It is wrong with a given username. username should be ‘ram’@’localhost’ instead of ‘ram’@’%’
mysql> DROP USER 'ram'@'localhost'; Query OK, 0 rows affected (0.02 sec) mysql>
Conclusion
I am really very happy that you are reading this post. Today I have covered about create a mysql username, mysql delete user, mysql grant permissions to a user, and more. But everything related user and its permission can’t cover in one article.
May things are left, if you have any idea or suggestion or any question please leave in comment box.
I give my time to read your comments.
Thanks and Cheers!