Manage users in MySQL server

This is the advanced user tutorial for MySQL server,  also known as MySQL Administration tutorial. In this tutorial I will explain you how to manage user in MySQL.

Managing user means creating, deleting, assigning permission, revoking permission/privilege. Before going to detail I recommend you, to first see the structure of user table under mysql database.

Just open your mysql client and type

use mysql;

desc user;

here we showing some field of user data base

Now we have to understand the two terms define below, more of us already know these terms.

Authentication : Means system know about you (user). system have some kind of information about you.

MySQL uses username, password and hostname or host-ip for authentication of user

Autherization : Permission to do any operation using your authentication detail.

Add a new user in MySQL server

Use the following SQL command for adding new user to your MySQL DB

CREATE USER 'ganga'@'localhost' IDENTIFIED BY 'password';

you need administrative privileges for this.

This command will create a user ganga and password is password.

This command simply create a user without any permission. you can do nothing.

if you login to MySQL using this credentials(ganga, password) you only see information_schema database, all other databases hidden to you.

Now we provide privileges/permission to newly created user ganga.

Some privileges are database level and some are table level.

below is the table contains some MySQL privileges

List of privileges

Assigning privileges to a User

GRANT privilege1, privilege2, privilege ON database_name.*  TO ‘username’@’host’ IDENTIFIED BY ‘password’;

GRANT select ON college.*  to 'ganga'@'localhost' identified by 'password';

The following command provide select permission to user ganga in college database.

Deleting a User  

To delete a user permanently use the following command 

mysql>DROP USER ‘ganga’@’localhost’;

Removing all user privilege

To remove all user permission use the following command-

mysql>REVOKE ALL PRIVILEGES FROM ‘ganga’@’localhost’;

Changing user password

To change the password use the following command --

mysql>SET PASSWORD FOR ‘ganga’@’localhost’ = PASSWORD(‘newpassword’);

Renaming user

To rename the user use the following command --

mysql>RENAME USER ‘ganga’@’192.168.1.145’ TO ‘jamuna’@’localhost’;

Revoking privileges 

mysql>REVOKE DELETE, UPDATE FROM ‘ganga’@’localhost’;