MySQL Create User

The MySQL user is a record in the USER table of the MySQL server that contains the login information, account privileges, and the host information for MySQL account. It is essential to create a user in MySQL for accessing and managing the databases.

The MySQL Create User statement allows us to create a new user account in the database server. It provides authentication, SSL/TLS, resource-limit, role, and password management properties for the new accounts. It also enables us to control the accounts that should be initially locked or unlocked.

If you want to use the Create User, it is required to have a global privilege of Create User statement or the INSERT privilege for the MySQL system schema. When you create a user that already exists, it gives an error. But if you use, IF NOT EXISTS clause, the statement gives a warning for each named user that already exists instead of an error message.

Why Did Users require in MySQL server?

When the MySQL server installation completes, it has a ROOT user account only to access and manage the databases. But, sometimes, you want to give the database access to others without granting them full control. In that case, you will create a non-root user and grant them specific privileges to access and modify the database.

Syntax

The following syntax is used to create a user in the database server.

snippet
CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY 'password';

In the above syntax, the account_name has two parts one is the username, and another is the hostname, which is separated by @ symbol. Here, the username is the name of the user, and the hostname is the name of the host from which the user can connect with the database server.

snippet
username@hostname

The hostname is optional. If you have not given the hostname, the user can connect from any host on the server. The user account name without hostname can be written as:

snippet
username@%
Note
Note: The Create User creates a new user with full access. So, if you want to give privileges to the user, it is required to use the GRANT statement.

MySQL CREATE USER Example

The following are the step required to create a new user in the MySQL server database.

Step 1: Open the MySQL server by using the mysql client tool.

Step 2: Enter the password for the account and press Enter.

snippet
Enter Password: ********

Step 3: Execute the following command to show all users in the current MySQL server.

snippet
mysql> select user from mysql.user;

We will get the output as below:

MySQL Create User

Step 4: Create a new user with the following command.

snippet
mysql> create user peter@localhost identified by 'jtp12345';

Now, run the command to show all users again.

MySQL Create User

In the above output, we can see that the user peter has been created successfully.

Step 5: Now, we will use the IF NOT EXISTS clause with the CREATE USER statement.

snippet
mysql> CREATE USER IF NOT EXISTS adam@localhost IDENTIFIED BY 'jtp123456';

Grant Privileges to the MySQL New User

MySQL server provides multiple types of privileges to a new user account. Some of the most commonly used privileges are given below:

  1. ALL PRIVILEGES: It permits all privileges to a new user account.
  2. CREATE: It enables the user account to create databases and tables.
  3. DROP: It enables the user account to drop databases and tables.
  4. DELETE: It enables the user account to delete rows from a specific table.
  5. INSERT: It enables the user account to insert rows into a specific table.
  6. SELECT: It enables the user account to read a database.
  7. UPDATE: It enables the user account to update table rows.

If you want to give all privileges to a newly created user, execute the following command.

snippet
mysql> GRANT ALL PRIVILEGES ON * . * TO peter@localhost;

If you want to give specific privileges to a newly created user, execute the following command.

snippet
mysql> GRANT CREATE, SELECT, INSERT ON * . * TO peter@localhost;

Sometimes, you want to flush all the privileges of a user account for changes occurs immediately, type the following command.

snippet
FLUSH PRIVILEGES;

If you want to see the existing privileges for the user, execute the following command.

snippet
mysql> SHOW GRANTS for username;
Related Tutorial
Follow Us
https://www.facebook.com/Rookie-Nerd-638990322793530 https://twitter.com/RookieNerdTutor https://plus.google.com/b/117136517396468545840 #
Contents