MySQL: create user and database, assign privileges and user to database

Update: There is a small update to this article, as if lets say some one does not have any root password yet, then how to create it 1st and then use the following commands. So that’s simple, open terminal and type the following:

[cc lang=”php” escaped=”true” nowrap=”false” noborder=”true” line_numbers=”false” lines=”1″ tab_size=”4″]

mysqladmin -u root password YOURPASSWORD

[/cc]

YOURPASSWORD = Your new root password

——————————————————————————-

Hi all,

Do you want to create a separate user for a database which only some users/user can access. Want to create user who can only have access to databases you assign them. Then this tutorial is for you.

This will be a very simple tutorial and howto about how to create a database 1st, a user and then assign some privileges to the user to allow him/her to perform some specific actions like insert,create,update,select etc etc.

I assume you are on Linux and command shell (terminal) is opened.

To enter to MySQL mode in terminal type this:

mian@liveserver:~$ mysql -u root -p

Just enter the red color text from above, or copy from here will work and upon asking for the password enter your root password (not distro one, MySQL root password). -u means username and -p is password.

You will see output like this:

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35614
Server version: 5.0.51a-3ubuntu5.7 (Ubuntu)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

Lets create a database (if you don’t have yet) with any name you desire (use text in red):

mysql> create database store;

You will see this output:

Query OK, 1 row affected (0.00 sec)

Now the fun part, lets create a user with password and assign some basic privileges on a database we created above:

mysql> grant CREATE,INSERT,DELETE,UPDATE,SELECT on store.* to asim@localhost identified by ‘storeadminasim’;

In the above command you need to change the following according to your need.

store represents the database we created above.

asim is the user we want to create for the database store.

storeadminasim is the password assigned to asim.

Text in dark blue above shows the privileges we want to assign to user asim, in this case he will just perform that actions on a database.  We will not let him do whatever he want, this is good for security. This user will only access this database assigned to him and nothing else, and he can create tables in it, can update it, insert data into tables etc etc. If the above command runs successfully you will see this output in terminal:

Query OK, 0 rows affected (0.00 sec)

Now the last and important thing is to flush the privileges, type this (text in red):

mysql> flush privileges;

You will see this output:

Query OK, 0 rows affected (0.00 sec)

That’s it, you can now quit/exit from MySQL session:

mysql> exit
Bye

The user and database is created and ready for use. Got any questions?, use the comment form to ask.