Create New User in MySQL Database

There is three ways to create new user in MySQL

1. Using grant statement

2. By manipulating the MySQL grant tables directly using SQL statement

3. Using create user command

Before creating user we should know below details

1. username
2. hostname i.e. host/ip from which user is going to connect it should be  either ‘localhost’ or hostname/ip or ‘%’ ( wild card-will be able to connect from any source)

Using grant statement

Privileges need to be set up by root user, Login to instance using root user

Consider we are creating user test@localhost with access on all databases and its associated objects.

mysql> select database(), user();
+------------+----------------+
| database() | user()         |
+------------+----------------+
| mysql      | root@localhost |
+------------+----------------+

Below is the command to create user using GRANT statement. We can see this method is depreciated in 5.7 and will be removed in future release.flush privileges is used to update grants table in order to update grants

mysql>  grant all privileges on *.* to test@localhost identified by 'MysqlDB!23';
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> show warnings;
| Level   | Code | Message                                          -------------------------------------------------------------------
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement   |
+---------+------+-------------------------------------------------
1 row in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

Verification:

mysql> select user,host,account_locked from user where user='test';
+------+-----------+----------------+
| user | host      | account_locked |
+------+-----------+----------------+
| test | localhost | N              |
+------+-----------+----------------+
1 row in set (0.00 sec)
   
Connecting to database using created account

bash$ mysql -u test -p mysql
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
.....

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select database(),user();
+------------+----------------+
| database() | user()         |
+------------+----------------+
| mysql      | test@localhost |
+------------+----------------+

1 row in set (0.00 sec)

By manipulating the MySQL grant tables directly using SQL statement

Below are commands used to create user using INSERT statement. This is kind of inserting user information in mysq.user table with privileges. Following example creates user test2 with different privileges.

mysql> insert into user (host,user,authentication_string,select_priv,insert_priv,delete_priv,create_priv,drop_priv,reload_priv,alter_priv,ssl_cipher,x509_issuer,x509_subject)
    ->  values ('localhost','test1',password('MysqlDB!12'),"y","y","y","y","y","y","y","","","");
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> show warnings;
+----------------------------------------------------------------
| Level   | Code | Message                                       
+---------+------+--------------------------------------------------
| Warning | 1681 | 'PASSWORD' is deprecated and will be removed in a future release. |
+---------+------+--------------------------------------------------
1 row in set (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

Verification:

mysql> select user, host,account_locked from mysql.user where user like 'test1';
+-------+-----------+----------------+
| user  | host      | account_locked |
+-------+-----------+----------------+
| test1 | localhost | N              |
+-------+-----------+----------------+

1 row in set (0.00 sec)

By Create user statement

This is most preferred method for user creation.

mysql> create user 'test4'@'%' identified by 'MysqlDB!23';

Query OK, 0 rows affected (0.02 sec)

mysql> grant select on *.* to 'test4'@'%';

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

Verification:

mysql> select user, host,account_locked from mysql.user where user like 'test4';

+-------+------+----------------+

| user  | host | account_locked |

+-------+------+----------------+

| test4 | %    | N              |

+-------+------+----------------+