Create a new user and grant permission in MySQL

In previous article I have explained How to Install Apache, MySQL, PHP on CentOs 6. Let us proceed one step further and add a MySQL user and grant privileges. So to add user you need to login on the terminal with root privileges.

Login to MySQL with following command

    mysql -u root -pmypwd@123

mypwd@123 is root password. Change it with your password.
Note: There is no space between -p and password. In following commands I have used john as user and john@123 as password. You are advised to change this with a secure password.

You are now logged in to MySQL. You can see MySQL command prompt as well.
Create user with this command

    CREATE USER 'john'@'localhost' IDENTIFIED BY 'john@123';

The User john is created with password john@123. But the user can not do anything until we grant privileges to him.

Now grant him all privileges by

    GRANT ALL PRIVILEGES ON *.* TO 'john'@'localhost' WITH GRANT OPTION;

So we have now given ALL privileges to user john on all database and all tables. The first asterisk refers the database and the second one refers tables.

Now to make the changes to take effect we need to reload all the privileges with following command

    FLUSH PRIVILEGES;

Here is the list of different user permission

ALL PRIVILEGES – To grant all privileges
CREATE – To allow user to create databases and tables
DROP - To allow user to drop databases and tables
DELETE - To allow user to delete rows
INSERT - To allow user to insert rows
SELECT – To allow user to read the database
UPDATE - To allow user to update rows

And thats it. A new user has been created in MySQL. You can see all this action in following screen



This user can now login to the MySQL on the same host i.e. localhost. What if user wants to access it from outside say from some MySQL query browsers. Let us create a user to access it from outside. We need to follow the same three commands what we used above just changing the localhost to some IP address (to access from this IP) or with wildcard to allow from everywhere. See

    CREATE USER 'john'@'%' IDENTIFIED BY 'john@123';

    GRANT ALL PRIVILEGES ON *.* TO 'john'@'%' WITH GRANT OPTION;

    FLUSH PRIVILEGES;

Above three commands will create a user who can access this database from anywhere with help of any query browser.