MySQL Create User Grant Rights

User creation in MySQL is a simple process. when a “user” is created in MySQL a record gets entered in the DB “mysql” under table “user”. To create a user you need to have the user creation rights or the super user rights. When MySQL is initially installed, root account is created in the database by default with no password to that account. As the root account has all the privileges in the MySQL server, it is advisable to create separate user account(s) for each database.

To create a new DATABASE with new USER in mysql database:

Log into mysql

$ mysql -uroot -p’password’;

Note: If you feel unsecured entering the password through command line, then you can issue the command ‘mysql -uroot -p;’ and in the next line the system will ask for the password.

Create the database

$ create database [DATABASE];

Create the user with password (available for Mysql ver 5)

$ create user [USERNAME] identified by ‘PASSWORD’;

To grant privileges to the new user

$ grant all on *.* to ‘USERNAME’@’localhost’;

You can combine the above two steps by creating the user and granting him the privileges in one line as

$ grant all on *.* to ‘USERNAME’@’localhost’ identified by ‘PASSWORD’;

If you want to give access to only the tables in a single database “MYDB” then you could issue the command as

$ grant all on MYDB.* to ‘USERNAME’@’localhost’ identified by ‘PASSWORD’;

There is another way to create a new user and give him the necessary permission settings when the “GRANT” option is not feasible to be executed on the shared server. It is through the usage of INSERT statement as

INSERT INTO user (host,user,password) values(‘localhost’,’username’,PASSWORD(‘pwd’));

And to add privileges to the user, issue the command

INSERT INTO user (host,user,password,select_priv, insert_priv) values(‘localhost’,’username’,PASSWORD(‘pwd’), ‘Y’, ‘Y’);