In this post, we will discuss how to Create a New User in MySQL and grant permissions to it using the MySQL Shell or MySQL Command-Line. Most important we will also discuss why it is important to create a user in MySQL?
Why to Create a New User?
Default ‘root’ user in MySQL has full access to all databases and tables.
However, in certain situations, we may require to put restrictions on some databases for security purposes. In that case, we create a new user in MySQL and grant specific permission to it, so that only the permissible databases and tables is accessible by him/her.
This provides access control power to the database administrator.
How to Create a New User?
The general syntax to create a new user in MySQL is
CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password';
Note: Make sure to replace the user_name and password with your new user name and password.
‘localhost’ is the host of your MySQL database. This can be some server’s IP address where the MySQL database is located but if the MySQL is installed in your local machine or is relatively located in the same server (or machine) then we use ‘localhost’ instead.
Executing the above statement will create the new user but the new user has no permissions to do anything with the databases. In fact, the ‘new_user’ will not be able to login to MySQL using the password.
So to complete the process, we need to grant appropriate MySQL permissions to the new user.
Grant Permissions to User
The general syntax to grant permissions to any user in MySQL is
GRANT type_of_permission ON database_name.table_name TO ‘user_name’@'localhost’;
Example: If you want to grant all MySQL permissions to the user for all available databases
GRANT ALL PRIVILEGES ON *.* TO ‘user1’@'localhost’;
The asterisk in this statement refers to all databases and tables respectively.
Executing the above statement will grant full root-level access to ‘user1’. It means that the ‘user1’ can perform any MySQL operations on any table of any database.
Different User Permissions
These are the different user permission you can grant to a user in MySQL.
- ALL PRIVILEGES – gives full access to the designated database and tables (or if no database is specified, global access across the system).
- CREATE – allows creating new databases and tables.
- DELETE – allows deleting of records from tables.
- INSERT – allows inserting record into table.
- DROP – allows deleting of databases or tables.
- SELECT – allows select or read tables records.
- UPDATE – allows updating of databases and tables.
- GRANT OPTIOM – allows to grant or remove other users’ privileges.
Here is an example to demonstrate the use of these privileges while granting permission to a user
GRANT CREATE, INSERT ON sales_db.* TO ‘user1’@'localhost’;
The ‘user1’ can only create and insert records to the tables of the ‘sales_db’ database.
Revoke Permissions from User
If you need to revoke permissions from the user then the syntax for that is almost similar to ‘grant MySQL syntax’ i.e
REVOKE type_of_permission ON database_name.table_name FROM ‘user_name’@‘localhost’;
Whenever you change the permission for a user always use the flush privileges command to reload all the privileges.
Display MySQL User Privileges
To see what permissions or privileges does a MySQL user have, use the
SHOW GRANTS statement.
SHOW GRANTS FOR 'user_name'@'localhost';
Note: Make sure to replace the user_name with the name of the user.
List all MySQL Users
After successfully creating a user in MySQL, if you wish to see the list of all MySQL users then use the following
SELECT USER FROM mysql.user;
Delete an Existing User
To delete an existing user in MySQL use the following MySQL
DROP USER 'user_name'@'localhost';
Note: Replace the user_name with the name of an existing MySQL user.
I hope now you will be able to create a user in MySQL as well as grant permissions to it. If you have any doubts regarding any explanation or have any suggestions then please comment below.