[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.
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
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
Related posts:
- [Wordpress] Optimize and Repair your MySQL Database Using phpMyAdmin
- [MySQL] How To Rename a User in MySQL
- [How-To] Delete Wordpress Spam Comments Using phpMyAdmin
- MySQL Tips for Java Developers With Mark Matthews
- [MySQL] Performance Tuning Best Practices
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.
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
Roy Eliasson wrote does any one have any insite on the best firewall to use? roy eliassson
[...] / 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 [...]
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
Need help with my exchange server it is constantly losing connection
[...] If you are looking for ways to create and delete a user in MySQL, read [How-To] Create and Delete Users in MySQL. [...]
[...] Also Read: [How-To] Create and Delete Users in MySQL [...]