[How-To] Create and Delete Users in MySQL

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.

MYSQL Logo

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.

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

Vaibhav

Advertisements

6 Responses

  1. ROY ELIASSON For the single-table syntax, the DELETE statement deletes rows from tbl_name. The number of rows deleted is returned by the ROW_COUNT() function Roy A Eliasson (see Section 11.10.3, “Information Functions”). The WHERE clause, if given, specifies the conditions that identify which rows to delete. With no WHERE clause, all rows are deleted. If the ORDER BY clause is specified, the rows are deleted in the order that is specified. The LIMIT clause places a limit on the number of rows that can be deleted.

    For the multiple-table syntax, DELETE deletes from each tbl_name the rows that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.

  2. Roy Eliasson wrote MySQL 5.0 Community Server – Generally Available (GA) Release
    In contrast to the MySQL Enterprise Server, which receives both monthly rapid updates and quarterly service pack releases, there is no specific schedule for when a new version of the MySQL Community Server is released. While every bug fix that has been applied to the Enterprise Server will also be available in the subsequent Community Server release, there will be source-only releases in between full (source and binary) Community builds. So while the latest published community sources will always be available from the Source Downloads Section, the binaries listed on this page may be from a previous release. In any case, full binaries for all our supported operating systems are and will remain conveniently available from this page. roy eliasson

  3. Roy Eliasson wrote does any one have any insite on the best firewall to use? roy eliassson

  4. […] / delete users from a command line. For the information of them and for all others, this is the poshttp://technofriends.in/2008/04/17/how-to-create-and-delete-users-in-mysql/Default database of sa user – SQL Server PerformanceAug 1, 2006 … I am not able to edit/create sql […]

  5. Roy Elasson wrote The command in turn removes the user record from the mysql.user table.roy eliasson
    Roy Eliasson
    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

  6. Need help with my exchange server it is constantly losing connection

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: