Create and Delete Users in MySQL

December 29, 2011 by: Youzhny

MYSQL which forms the M of the LAMP series is a widely used open source relational database management system (RDBMS).
While working on a project, i learnt that some of the developers who were too much addicted with tools like PhpMyAdmin didn’t actually know how to create / delete users from a command line. For the information of them and for all others, this is the post to read.

Creating Users
First and the foremost thing to know, if you wish to create users on your MySQL engine, you must have sufficient administrator rights. Specifically speaking, you must have the privilege either for CREATE USER or INSERT privilege on the mysql database.
CREATE USER user@host IDENTIFIED BY ‘password’;
To create a user that can connect to a MySQL database running on the local machine, use localhost.
CREATE USER ‘technofriends’@'localhost’ IDENTIFIED BY ‘matrix’;
Typically in a web application scenario, one allows access to database from a script using a single MySQL username and password, even if the web application performs additional user authentication.
It must be noted that CREATE USER command was added in the MySQL version 5.0.2. In earlier versions, users could be created automatically when assigning permissions using the GRANT command or by manually inserting records in the mysql database.
The mysql database contains three tables – user, host and db. These tables contains the database permissions.
The user table contains the usernames and password combination of anyone who has access to any part of the MYSQL database. The password part is the encrypted string, which can be generated using the PASSWORD() function.
As an administrator, you can even directly insert the values into the user table of mysql database and get the desired results.
INSERT INTO user(Host,User,Password) VALUES(‘localhost’, ‘technofriends’, PASSWORD(‘matrix’));
FLUSH PRIVILEGES;
The FLUSH PRIVILEGES command is required to inform MySQL to reload the privilege data after the change is made.
Deleting Users
To delete users from the MySQL database use the DROP command.
DROP USER user@host;
The command in turn removes the user record from the mysql.user table.
As the CREATE USER command, even the DROP USER command has been added since MySQL 5.0.2. In previous versions of MySQL you must revoke the user’s privileges first, delete the records from user manually and then issue the FLUSH PRIVILEGES command.
DELETE FROM user WHERE User= ‘technofriends’ AND Host= ‘localhost’;
FLUSH PRIVILEGES;

This brings me to the end of this post. I would suggest visiting this link from MySQL Reference manual to all those interested in knowing more.
If you already have an existing user in your MYSQL database and would like to rename the user, refer [MySQL] How To Rename a User in MySQL.
Also read:
How not to get Phished,Learn from Phil the Fish
Bluetooth hacking: Essential tools.
Spoofing Explained : Another attempt to cover Hacking fundas
Learn to Hack
Do stay tuned to Technofriends for more, one of the best ways of doing so is by subscribing to our feeds. You can subscribe to Technofriends feed by clicking here.
Cheers

Share and Enjoy:
  • Print
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • Blogplay
Have you found this script useful? Please support author by PayPal donation.
Filed under: Database,Mysql
Tags: ,

Leave a Reply