In this post, we will see about users and their privileges in MySQL. We will see how we can create ore remove user in MySQL, how to assign or revoke privileges from users.
Topics in this article
- Show all users in MySQL
- How to Check Privileges in MySQL
- How to create user in MySQL
- How to Grant Privileges to new User
- How to Remove/Revoke Privileges from User
- How to Remove User from MySQL
Show all users in MySQL
We can check existing users in MySQL by using select user from user; command. To run this command you have to first run use mysql; statement.
select user from user;
OR
Simply run this command
select user from mysql.user;
This way you can check all the users currently present in MySQL.
Currently we have 5 user. Let’s see what kind of privileges root user have. root user is our admin user account, so it will have all privileges.
How to Check Privileges in MySQL
show privileges command is used to know what privileges are currently in your MySQL and that are enabled.
show privileges;
You will see output something like below, it will contain all the privileges currently in your MySQL.
MySQL command to check User Privileges
show grants for '<username>'@'<host>' command is used to check the privileges any user has in MySQL. In our case currently only one user which is root and host is localhost.
show grants for 'root'@'localhost';
How to create user in MySQL
Create User command is used to create new user in MySQL. Syntax to create user in MySQL is:
CREATE USER '<username>'@'<host>' IDENTIFIED BY 'some_password';
MySQL example to create new user :
CREATE USER 'reader'@'localhost' IDENTIFIED BY 'readerP';
We created user with name reader in localhost domain.
let’s run select user from mysql.user statement to see whether reader user is created or not.
reader user has been created successfully.
How to Grant Privileges to new User
We created new user reader in above example. now we will assign or grant privileges to new user.
Grant all Privileges
Grant All command is used to grant all privileges to user. Following is the syntax to grant all privileges to user in MySQL.
GRANT ALL ON *. * TO 'username'@'hostname' WITH GRANT OPTION;
MySQL example to grant privileges to new user
GRANT ALL ON *. * TO 'reader'@'localhost' WITH GRANT OPTION;
All permissions are granted to user reader.
Grant Specific permission to user
If you don’t want to give all permissions then some permissions can also be granted.
Grant only Select permission to user
If you want to give only read permission then you can use below statement
grant select databases on employee.* to 'guest'@'localhost';
Here we grant the select permission on employee database to guest user. This way you can grant any permission to user in MySQL according to your need.
How to Remove/Revoke Privileges from User
We can remove or revoke few or all the privileges from user.
Revoke few Privileges from User
We can remove the Privileges from user by using Revoke command.
Revoke priv1,priv2,.on [objt_name].* from 'username'@'host';
MySQL example to revoke remove privileges from user
Revoke select on employee.* from 'guest'@'localhost';
This will remove or revoke the select privilege from guest user from database employee.
Revoke all Privileges from User
Revoke ALL command is used to remove all the privileges from User.
MySQL Syntax to revoke privileges from User
REVOKE ALL,GRANT OPTION from 'user'@'host';
MySQL example to revoke privileges from User
REVOKE ALL,GRANT OPTION from 'guest'@'localhost';
It will revoke all the privileges from guest user.
How to Remove User from MySQL
drop command is used to remove the user from MySQL.
MySQL syntax to remove user
drop user 'username'@'hostname';
Let’s delete or remove the guest user from MySQL.
drop user 'guest'@'localhost';