Written by Sai Sandeep Thota on January 19, 2013 in Programming > SQL

Create, Grant and Revoke Permissions Commands in MySQL

GET ALERTS:

Get our Latest updates delivered to your mailbox!

This article refers to SQL Queries on how to Create, Grant and Revoke permissions to an user on a Database. If you have missed our Basic SQL Commands Article, take a look.
mysql-big

How to Create a user on MySQL

The below query creates an User without any password.

mysql> CREATE USER 'sandeep'@'localhost';
Query OK, 0 rows affected (0.29 sec)

If you want to create an user with an authentication, then you need to use IDENTIFIED BY clause.

mysql> CREATE USER 'sandeep'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

If you need to change password of an user then:

mysql> UPDATE USER SET PASSWORD='newpassword' WHERE user='sandeep';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

After updating your password, you need to Flush Privileges.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.11 sec)

After creating an user, We need to give permissions to the user to access a Database.

Grant Permissions to an User in MySQL

If you want to give access to all the Databases then:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'sandeep'@'localhost';
Query OK, 0 rows affected (0.01 sec)

To give permissions on a specific Database then:

mysql> GRANT ALL PRIVILEGES on databasename.* TO 'sandeep'@'localhost';
Query OK, 0 rows affected (0.01 sec)

If you want an user to be a superuser with all the privileges that an Administrator will have then:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'sandeep'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

Revoke Permissions from an User in MySQL

The below query is used to Revoke permissions from an user in MySQL. If you want to remove all the privileges then:

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'sandeep'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Revoke specific Privileges:

mysql> REVOKE INSERT ON *.* FROM 'sandeep'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Remove a User

:

mysql> DROP USER 'sandeep'@'localhost';
Query OK, 0 rows affected (0.00 sec)

The above query removes an user from localhost.

Hope this article is useful for you for creating an user, granting and revoking permissions from an user in MySQL.

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: