Create user in mysql

The mysql user information is kept inside USER table under mysql database. So creation of new user will affect the USER table of mysql database. In order to know a list of mysql users according with user table's description issue,

Here test is the root account password.
mysql -u root -ptest
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.30-community MySQL Community Server (GPL)

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

mysql> use mysql;
Database changed

mysql> desc user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       |
| User                  | char(16)                          | NO   | PRI |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       |
| File_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       |
| References_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher            | blob                              | NO   |     | NULL    |       |
| x509_issuer           | blob                              | NO   |     | NULL    |       |
| x509_subject          | blob                              | NO   |     | NULL    |       |
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+
39 rows in set (0.14 sec)

mysql> select host, user from user;
+-----------+---------+
| host      | user    |
+-----------+---------+
| %         | magento |
| 127.0.0.1 | root    |
| localhost |         |
| localhost | magento |
| localhost | pma     |
| localhost | root    |
+-----------+---------+
6 rows in set (0.00 sec)

By query the Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv etc you can check whether user has certain types of privilege or not.

There are several ways to create user in mysql which is described below.

Way 01: With the CREATE USER statement
1)Connect to mysql database as a user who must have the global CREATE USER privilege or the INSERT privilege for the mysql database. You can use root because by default root has privilege to create user.

2)In fact CREATE USER statement creates a new record in the mysql.user table that has no privileges assigned by default.

mysql> create user arju;
Query OK, 0 rows affected (0.13 sec)

mysql> select  user,password, host, select_priv, insert_priv from mysql.user where user='arju';
+------+----------+------+-------------+-------------+
| user | password | host | select_priv | insert_priv |
+------+----------+------+-------------+-------------+
| arju |          | %    | N           | N           |
+------+----------+------+-------------+-------------+
1 row in set (0.00 sec)

Notice that if you specify only the user name part of the account name while creating user, a host name part of '%' is used.

User arju is not assigned any password in this way as you see password is null. To assign password to user arju issue,

mysql> set password for 'arju'=password('test');
Query OK, 0 rows affected (0.03 sec)

mysql> select  user,password, host, select_priv, insert_priv from mysql.user where user='arju';
+------+-------------------------------------------+------+-------------+-------------+
| user | password                                  | host | select_priv | insert_priv |
+------+-------------------------------------------+------+-------------+-------------+
| arju | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | %    | N           | N           |
+------+-------------------------------------------+------+-------------+-------------+
1 row in set (0.00 sec)

In one command you can set password while creating user. The following command will crate a user arju2 and password is test in the host localhost. Both password, username and hostname should be within single quote.

mysql> create user 'arju2'@'localhost' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> select  user,password, host, select_priv, insert_priv from mysql.user where user='arju2';
+-------+-------------------------------------------+-----------+-------------+-------------+
| user  | password                                  | host      | select_priv | insert_priv |
+-------+-------------------------------------------+-----------+-------------+-------------+
| arju2 | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | localhost | N           | N           |
+-------+-------------------------------------------+-----------+-------------+-------------+
1 row in set (0.00 sec)

If you don't put username, host name and password within single quote syntax error will be resulted.

mysql> create user 'arju2'@'localhost' identified by test;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test' at line 1

An equivalent statement of setting password is issuing update statement.

mysql> UPDATE mysql.user SET Password=PASSWORD('test')
-> WHERE User='arju' AND Host='%';
Query OK, 0 rows affected (0.05 sec)
Rows matched: 1 Changed: 0 Warnings: 0

Changing via update and insert of the user table, it is necessary to use FLUSH PRIVILEGES to tell the server to reload the grant tables. Otherwise, the changes go unnoticed until you restart the server.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.08 sec)

mysql> select  user,password, host, select_priv, insert_priv from mysql.user where user='arju';
+------+-------------------------------------------+------+-------------+-------------+
| user | password                                  | host | select_priv | insert_priv |
+------+-------------------------------------------+------+-------------+-------------+
| arju | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | %    | N           | N           |
+------+-------------------------------------------+------+-------------+-------------+
1 row in set (0.00 sec)

Now, have a look that connecting to mysql database as user arju will fail but arju2 will be successful.
D:\xampp\mysql\bin>mysql -u arju -ptest
ERROR 1045 (28000): Access denied for user 'arju'@'localhost' (using password: YES)

D:\xampp\mysql\bin>mysql -u arju2 -ptest
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.1.30-community MySQL Community Server (GPL)

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

mysql> select user();
+-----------------+
| user()          |
+-----------------+
| arju2@localhost |
+-----------------+
1 row in set (0.00 sec)

Note that the 'arju'@'localhost' account can be used only when connecting from the local host. The 'arju'@'%' account uses the '%' wildcard for the host part, so it can be used to connect from any host.

You can check the privilege assigned for a user by issuing,

mysql> show grants for arju;
+-----------------------------------------------------------------------------------------------------+
| Grants for arju@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'arju'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for arju2;
ERROR 1141 (42000): There is no such grant defined for user 'arju2' on host '%'
mysql> show grants for arju2@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for arju2@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'arju2'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

By default it is search for host '%'.

Way 02: With the grant option
To create a user named momin with password test2 and all privileges for a particular database called ecommerce issue,
mysql> grant ALL PRIVILEGES on ecommerce.* to 'momin'@'localhost' identified by 'test2';
Query OK, 0 rows affected (0.00 sec)

mysql> use mysql;
Database changed

mysql> select host, user from user where user='momin';
+-----------+-------+
| host      | user  |
+-----------+-------+
| localhost | momin |
+-----------+-------+
1 row in set (0.00 sec)

D:\xampp\mysql\bin>mysql -u momin -ptest2
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.1.30-community MySQL Community Server (GPL)

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ecommerce          |
| test               |
+--------------------+
3 rows in set (0.00 sec)

Here ecommerce database is shown as we have given permission.

Way 03: Using INSERT statement into Table mysql.user
You can also create user in mysql by simply INSERT a new row into mysql.user table. Note that creating user through INSERT statement needs to use FLUSH PRIVILEGES to tell the server to reload the grant tables. If we don't do that the changes go unnoticed until you restart the server. This restriction is not applicable in CREATE USER statement.
Insert statement can be issued in two ways. One way to create user robert,

mysql> use mysql;
Database changed

mysql> INSERT INTO user SET Host='localhost',User='robert', Password=password('test');
Query OK, 1 row affected, 3 warnings (0.00 sec)

Alternative way of creating user richard is,

mysql> INSERT INTO user (Host,User,Password)
-> VALUES('localhost','richard',password('test'));
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Check by,

mysql> select user, host, password from user where user in('robert','richard');
+---------+-----------+-------------------------------------------+
| user    | host      | password                                  |
+---------+-----------+-------------------------------------------+
| robert  | localhost | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
| richard | localhost | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+---------+-----------+-------------------------------------------+
2 rows in set (0.01 sec)

See that while setting password we used the PASSWORD() function with INSERT in order to encrypt the password. But with the CREATE USER statement it was unnecessary as create user statement automatically encrypts the password.