Create a new MySQL user – Howto?

There are few ways you can create a new user for a MySQL DB.

1. Using grant command to create a new user and assign privileges to access a DB.
2. Use the create user mysql command followed by grant command to set access privileges.
3. Directly editing the mysql DB using insert, update etc (which is a bit complex)

Create USER

For creating user following command is used

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']

The above command creates a new user, but who has no access to any DB. In order to grant prermissions to access any DB, we need to use the grant command.

Grant Command

GRANT ALL ON *.* TO 'someuser'@'somehost';

The above will grant ALL the permission (select, insert, updat, delet etc) to someuser at somehost. If you would like to set a user from a particular IP to access the DB, you can use like

GRANT ALL ON *.* TO 'someuser'@'IPADDRESS';

The following command will give only select and insert privileges to someuser at somehost

GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';

3 Responses to “Create a new MySQL user – Howto?”

1. Kiran says:

   May 22, 2008 at 9:46 am

   Why to struggle so much.. we can use SQLYog and use the inerface.
   Thats a good client tool to access DB.

   Reply
2. Ramkumar says:

   May 22, 2008 at 10:02 am

   hmm 😉 All are spoon fed to a level where in you need not even write queries 🙂

   Reply
3. vinod says:

   July 27, 2010 at 5:31 am

   in mysql5, i believe we can specify the resource that each mysql user can consume , like number of questions ( queries ) he can run , number of connections he can make at a time etc which I do not think is able to manipulate vis the web interface 🙂

   Reply