MySQL Create User

You can create MySQL accounts in two ways:

1. By using statements intended for creating accounts, such as CREATE USER or GRANT. These statements cause the server to make appropriate modifications to the grant tables.
2. By manipulating the MySQL grant tables directly with statements such as INSERT, UPDATE, or DELETE.

The preferred method is to use account-creation statements because they are more concise and less error-prone than manipulating the grant tables directly. i.e CREATE USER or GRANT

Procedure
First, use the mysql program to connect to the server as the MySQL root user:

[root@burnzdb]# mysql --user=root mysql

OR If you have assigned a password to the root account, you will also need to supply a --password or -p option or simply run

[root@burnzdb]# mysql -p

After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts:

mysql> CREATE USER 'burnz'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'burnz'@'localhost' WITH GRANT OPTION;
mysql> flush privileges;
 
mysql> CREATE USER 'burnz'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'burnz'@'%' WITH GRANT OPTION;
mysql> flush privileges;
 
mysql> CREATE USER 'admin'@'localhost';
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
# RELOAD - Privilege enables use of FLUSH Statements
# PROCESS - Privilege pertains to display of information about the threads executing within the server (that is, information about the statements #being executed by sessions). i.e. show processlist;
mysql> flush privileges;
 
mysql> CREATE USER 'dummy'@'localhost';

The accounts created by these statements have the following properties:

* Two of the accounts have a user name of burnz and a password of some_pass. Both accounts are superuser accounts with full privileges to do anything.
– The ‘burnz’@’localhost’ account can be used only when connecting from the local host.
– The ‘burnz’@’%’ account uses the ‘%’ wildcard for the host part, so it can be used to connect from any host.
It is necessary to have both accounts for burnz to be able to connect from anywhere as burnz.

* The ‘admin’@’localhost’ account has no password. This account can be used only by admin to connect from the local host. It is granted the RELOAD and PROCESS administrative privileges. These privileges enable the admin user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-xxx commands, as well as mysqladmin processlist . No privileges are granted for accessing any databases. You could add such privileges later by issuing other GRANT statements.

* The ‘dummy’@’localhost’ account has no password. This account can be used only to connect from the local host. No privileges are granted. It is assumed that you will grant specific privileges to the account later.

The statements that create accounts with no password will fail if the NO_AUTO_CREATE_USER SQL mode is enabled. To deal with this, use an IDENTIFIED BY clause that specifies a none empty password.