Pages

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 server
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 privilages/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 permisson 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’;

No comments:

Post a Comment