Create new MySQL user
Posted by hneelu on February 18, 2009
There are more ways how you can do this.
* Using CREATE USER and/or GRANT commands
* Inserting a new record into the mysql.user table
CREATE USER command
Note: This command is available only in MySQL 5 (5.0.2) and newer releases.
Syntax is :
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass1';
CHECK : mysql.user table has a new record in it.
NOTE: All privileges are set to No so this user can do nothing in the DB.
To add some privileges we can use the GRANT command as follows:
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'user1'@'localhost';
To add all priviliges to the user you don’t have to list all of them but you can use the ALL shortcut as follows:
GRANT ALL ON *.* TO 'user1'@'localhost';
You can create a new MySQL user in one step as well using again the GRANT command with a small extension as here:
GRANT ALL ON *.* TO 'user2'@'localhost' IDENTIFIED BY 'pass1';
Inserting a new record into the mysql.user table
In this case you just inserts a new record into the table with a normal INSERT command:
INSERT INTO user (Host,User,Password)
Or you can add some priviliges as well in a form like this:
INSERT INTO user (Host,User,Password,Select_priv,Insert_priv)