MySQL CREATE USER: How To Create New User In MySQL

This tutorial explains MySQL Create User command with different authorization mechanisms, password management, resource limit options, etc:

MySQL CREATE USER command is used to create new users and grant granular access to databases/tables, etc.

Multiple people use a MySQL server instance, having different access levels. For example, some users are having read access to a specific database, similarly, some can have read-write access to a particular database, etc.

=> Click here for the complete MySQL tutorial series

MySQL CREATE USER

CREATE USER command is more frequently used by MySQL Admins to create users for the MySQL Server instances and grant different permissions using the GRANT query.

My SQL CREATE USER Command

CREATE USER command is used to create or add new accounts to MySQL server instances.

This command is generally accessible/is used by MySQL admins to manage access to different users of the MySQL Server instance.

While creating a user using the CREATE USER command, you can specify

  • Authentication, which should be used while connecting to MySQL.
  • Resource Limit
  • Password Management properties: Password expiration, Password reuse settings.
  • Account locking: Newly created accounts would be either locked or unlocked.

The accounts are created in MySQL system table named ‘mysql.user’

Note: To use the CREATE USER command, the user should have CREATE_USER privilege or insert grant for MySQL system schema.

In the simplest form, the syntax for CREATE USER command is as below:

CREATE USER [IF NOT EXISTS] '{username}'@'{hostname}' IDENTIFIED BY '{passwordString}';

Notice the optional IF NOT EXISTS. This ensures that if the user is already existing, the SQL query result will just throw a warning and no error. Here, ‘username’ refers to the actual username that the user would connect against and ‘hostname’ refers to the host from which the user would connect from.

Please note, if the ‘hostname’ field is left blank then the value for the host is assumed to be ‘%’ which would allow any host to connect with the specified username.

Create a user with this syntax:

CREATE USER 'userx'@'localhost' IDENTIFIED BY 'password';

With the above statement, we have created a user with the username ‘userx’ and password as ‘password’. The hostname is localhost as we are creating the users on our local MySQL instance.

Let’s try querying the mysql.users table to see the entry for the user we created.

SELECT * from mysql.user

You’ll see the output as below:

mysql.user

The first 4 users are pre-created during MySQL installation itself while the last entry is the user we have created – i.e. ‘userx’.

It’s important to note that at this point we have just created a user without giving any rights to the user in terms of creating / updating / querying a database etc.

Let’s try logging in with this user with the MySQL client.

You can use the terminal to connect with the command below:

$ /usr/local/mysql/bin/mysql -u userx -p

On being prompted for a password, enter the password as ‘password’.

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>

Now, we will create a new database using the CREATE DATABASE keyword.

mysql> create database student;
ERROR 1044 (42000): Access denied for user 'userx'@'localhost' to database 'student'

As you can see above, there’s an error message generated which tells that the user ‘userx’ does not have access to create DATABASE Let’s see other options that could be used with CREATE USER Command.

CREATE USER With Auth Plugin

‘Auth plugin’ specifies an authentication option & is stored in the plugin column of the mysql.user table. MySQL supports a handful of authentication plugins.

Some plugins that are supported are as below:

  • MySQL native password hashing: Native password hashing is nothing but using SHA1 to store password values in the mysql.users table. This mechanism is considered being less secure than SHA1 password hashing.
  • SHA2 256 password hashing: This is the default authentication plugin used by MySQL. i.e. even if no authentication plugin is specified with CREATE_USER command, by default this plugin would be applied.
  • External authentication using LDAP: LDAP is generally used for linking MySQL authentication with the organization’s active directory. For example, authenticating using Google SSO, OAuth, or Microsoft Outlook LDAP. This requires LDAP plugin installation on MySQL side as well. For more details, you can refer here.

>>Refer here for a complete list of supported plugins.

Let’s try creating a user with a default auth plugin and SHA2 auth plugin and their corresponding hashed values in the mysql.users table. First, we will create a user with default authentication, which is (SHA2), and let us see what’s stored in the mysql.user table.

CREATE USER IF NOT EXISTS 'user-default'@'localhost' IDENTIFIED BY 'P@ssw0rd'

Let’s see the authentication plugin for this user ‘user-default’ in mysql.users table:

SELECT host,user,plugin,authentication_string from mysql.user where user='user-default'

authentication_string

Here in authentication_string, you can see its SHA-256 value for the password string – ‘P@ssw0rd’.

Let’s now create a user with an authentication plugin. ‘MySQL native password’ and see what’s the value of the plugin that gets stored.

CREATE USER IF NOT EXISTS 'user-sha1'@'localhost' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd'
SELECT host,user,plugin,authentication_string from mysql.user where user='user-sha1'

authentication_string 1

CREATE USER With Role

MySQL also provides assigning pre-defined roles to new users. These roles already have a configured access to some or all databases (which could be customized as well), for example, a role named ‘developer’ could be created which could be assigned all privileges to a database and later we can use the same role to assign to new users.

Let’s see this with an example:

  • Create a role named developer
CREATE ROLE 'developer'
  • Create a database named ‘test’
CREATE DATABASE test
  • Assign privileges to test database for ‘developer’ role
GRANT ALL ON test.* TO 'developer'
  • Create user and assign developer role
CREATE USER IF NOT EXISTS 'user-with-role'@'localhost' DEFAULT ROLE developer;
  • Validate the user can create a table in the test database

Let’s try logging in with the user named ‘user-with-role’ and we will try to create a new table in the test database.

$ /usr/local/mysql/bin/mysql -u user-with-role
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 44
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use test;
Database changed
mysql> create table test_table(name varchar(20));
Query OK, 0 rows affected (0.07 sec)

CREATE USER With SSL/TLS

Creating a user with SSL/TLS options would require both client and server to have SSL certificates installed. By default, SSL is not configured for MySQL.

Follow these steps to configure SSL for MySQL server instance.

In order to create a user with SSL enabled, you can use the REQUIRE SSL option while creating the user.

CREATE USER 'user-with-ssl'@'localhost' REQUIRE SSL;

Similarly, creating using X509 would expect the client/user to connect to have a valid X509 certificate.

>> Refer here to understand more about X509.

To create a user with an X509 certificate, use the below query:

CREATE USER 'user-with-x509'@'localhost' REQUIRE X509;

CREATE USER With Password Management Options

Password options are used to set policy on the Password while creating a user using the CREATE USER command.

#1) Expire the password on login. The password is expired after first use and prompts users to change the password.

CREATE USER IF NOT EXISTS 'test'@'localhost' IDENTIFIED BY 'Password' PASSWORD EXPIRE;

#2) Expire the password after a fixed interval. Password expiry can be configured with the configured interval, for example, 90 days.

CREATE USER IF NOT EXISTS 'test'@'localhost' IDENTIFIED BY 

'Password' PASSWORD EXPIRE INTERVAL 90 DAY;

#3) Password gets locked after configured retry attempts. A lot of times, it’s desired that the user account should get locked (for a configured period) after ‘n’ incorrect retry attempts. In the below query, the user account would get locked for 2 days after 5 incorrect attempts.

CREATE USER 'test'@'localhost'
IDENTIFIED WITH caching_sha2_password BY 'Password'
FAILED_LOGIN_ATTEMPTS 5 
PASSWORD_LOCK_TIME 2;

#4) New password should not be the same as configured previous passwords.

In the below query, the new password set by the users should not be the same as the past 2 passwords.

CREATE USER IF NOT EXISTS 'test'@'localhost' IDENTIFIED BY 'Password' 
PASSWORD EXPIRE
PASSWORD HISTORY 2;

CREATE USER With Resource Limit Options

Resource limits are required especially for production MySQL instances so as to avoid getting the database overwhelmed by queries/requests from a single user and which could impact the MySQL server performance.

We can set the Resource limits by using any 3 of these options below:

#1) MAX_QUERIES_PER_HOUR – Number of get queries allowed for a given user per hour.

CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_QUERIES_PER_HOUR 5

Suppose if the user tries to exceed more than 5 queries per hour, the MySQL server would throw an error like below:

ERROR 1226 (42000): User 'user-with-resource-limits' has exceeded the 'max_questions' resource (current value: 5)

#2) MAX_UPDATES_PER_HOUR – Number of update queries allowed for a given user per hour.

CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_UPDATES_PER_HOUR 50

#3) MAX_CONNECTIONS_PER_HOUR – The number of times an account can connect to the server per hour.

CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 50

#4) MAX_USER_CONNECTIONS – The number of simultaneous connections to the MySQL server instance from the given user.

CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_USER_CONNECTIONS 50

Please note that all the above options could also be combined while creating a user. Suppose if we want to specify MAX_QUERIES as 10 and MAX_UPDATES as 100, then we can have a single CREATE_USER query as:

CREATE USER 'user-with-resource-limits'@'localhost' WITH 
MAX_UPDATES_PER_HOUR 100
MAX_QUERIES_PER_HOUR 10

MySQL Update And Delete Users

MySQL provides 2 important commands – ALTER USER and DROP USER to modify and delete existing users, respectively. Let’s understand both of these using examples.

ALTER USER

MySQL ALTER USER is used to update/modify existing MySQL user accounts. It can be used to,

  • Update resource limits
  • Set password options
  • Lock and unlock accounts, etc.

Let’s see an example of using ALTER USER to unlock a locked account.

Create a user in a locked state and then unlock it with ALTER USER command.

CREATE USER 'test'@'localhost'I DENTIFIED BY 'Password' ACCOUNT LOCK;

Now, on logging in with this user, we will get an account locked message (as the user was created in a locked account state).

$ /usr/local/mysql/bin/mysql -u test -p
Enter password: 
ERROR 3118 (HY000): Access denied for user 'test'@'localhost'. Account is locked.

Use the below query for unlocking the user with the ALTER command.

ALTER USER 'test'@'localhost' ACCOUNT UNLOCK

Log in with the user with MySQL client:

$ /usr/local/mysql/bin/mysql -u test -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

DROP USER

MySQL DROP USER command removes one or more existing users and all their associated privileges and grants.

Please note that it will not remove/delete any data inserted/tables created or updated by the user, which is being deleted.

If someone tries to delete a non-existing user, the DROP USER command would throw an error.

Let’s create a user and delete it using the DROP command:

CREATE USER 'test'@'localhost';
DROP USER 'test'@'localhost';

Frequently Asked Questions

Q #1) What is a user in MySQL?

Answer: User in MySQL is an account/entity that can log in to MySQL server instance and perform different operations. A user can be assigned role-based access or granular access to one or more databases and/or tables.

Q #2) How to check different users connected to MySQL?

Answer: In order to see the active user sessions on a MySQL server instance, you can run processlist command as below.

SHOW processlist;

The output of this command would display different attributes of the active user’s sessions connected at the time of command execution.

Sample output below:

Sample output

Q #3) How do I switch users in MySQL?

Answer: MySQL works on the concept of user sessions. When logged in with a given user, you can either plan to terminate the session or open a session against a new user in a new window or as a new connection in MySQL GUI clients like a workbench.

To log in with a particular user from mysql client you can use the below syntax:

mysql -u {userName} -p

After writing the above command, once you press Enter, you will be prompted to enter the password for the user specified in {userName} field. Once the password is validated, you will be taken to MySQL shell/command prompt.

To log in with a separate user, you can terminate the current session by entering the exit command and re-login with another user.

mysql> exit

It’s also possible to have multiple connections to the MySQL server instances for different users. You can simply open a new tab/window for the command prompt and use the same command to log in with another user.

mysql -u {userName2} -p

Q #4) How to make a MySQL user read-only?

Answer: MySQL provides an exhaustive mechanism to grant granular access to databases or tables. You can provide different kinds of access like SELECT, UPDATE, INSERT, etc to one or many databases or tables.

In order to grant just read-only access to a user, you can grant SELECT access to a database (using * for all tables or explicitly mentioning table name as per requirement)
Let’s understand this with the help of an example below:

We are creating a user named ‘readaccess’ having password as ‘Password’

create user 'readaccess'@'localhost' IDENTIFIED BY 'Password'

Now, grant read access to all the tables of a database named ‘test’ by using the GRANT keyword in MySQL

grant select on test.* to 'readaccess'@'localhost'

Now, to validate the access, you can login with this user named ‘readaccess’ and try querying the tables inside the database ‘test’.

However, if you try inserting data to any of the tables inside the test database, then you would get an access denied error as we have explicitly just granted read access to this user.

Q #5) How do I see users in MySQL?

Answer: MySQL has a system-level table named ‘mysql.user’ which contains a list of all the users that are created for the MySQL server instance. The privileges to this table are generally restricted to root or admin users or someone who manages the authentication and authorization for the MySQL server instance.

Q #6) How to find MySQL user’s name and password?

Answer: The MySQL user and password mapping is stored in ‘mysql.user’ system table, which is access restricted table. The password is stored in encrypted format depending on the encryption mode that was chosen during the user creation.

However, if someone’s account is locked, MySQL provides another command called ALTER USER, which can be used to unlock a given user account.

Further reading =>> How to use MySQL SHOW USER Command

Conclusion

In this tutorial, we learned about the MySQL CREATE USER command, which is used for authentication and access management generally by Database admins.

MySQL provides a lot of powerful authentication mechanisms and role-based assignments that enable new users to be created with pre-defined access grants. We also saw examples for creating users with different authorization mechanisms, different password settings, expiry options, etc.

Also read =>> MySQL LIMIT Clause with examples

Recommended Reading