MySQL – Create Users

Table of content

In MySQL, you can create multiple user accounts to access the database, each with specific authentication detail such as password. These users can be granted specific privileges using SQL statements like CREATE USER for authentication when creating a new user, and GRANT and REVOKE for assigning and removing administrative privileges, respectively.

The MySQL CREATE USERS Statement

We can create a new user account using the CREATE USER Statement in MySQL. To execute this statement, the current account must have the CREATE USER privilege or the INSERT privilege for the MySQL system schema.

Syntax

Following is the syntax of the MySQL CREATE USER statement −

CREATE USER ''user_name''@''host_name'' IDENTIFIED BY ''password

Where,

user_name is the name of the user you need to create.
hostname specifies the host from which the user can connect.
password is the user”s password.

Example

In the following query, we are creating a user named ”sample” who can only connect from the ”localhost” host and sets their password as ”123456”. Make sure that you have logged in with a user with admin privileges (root) −

CREATE USER ''sample''@''localhost'' IDENTIFIED BY ''123456

Output

The output will be displayed as −

Query OK, 0 rows affected (0.12 sec)

Verification

You can verify the list of users using the following query −

SELECT USER FROM MySQL.USER;

The table will be displayed as shown below −

USER
mysql.infoschema
mysql.session
mysql.sys
myuser
openkm
root
sample

Granting Privileges in MySQL

You can grant all privileges to the created user using the GRANT ALL statement. This allows you to give specific permissions to users for actions like accessing databases, tables, and performing operations, such as SELECT, INSERT, or DELETE, on them.

Syntax

Following is the syntax to grant all privileges in MySQL −

GRANT ALL PRIVILEGES ON database_name.* TO ''username''@''host

Example

The following query grants the user ”sample” full privileges to perform any action on any database or table when connecting from the ”localhost” host, giving complete control over the MySQL server locally −

GRANT ALL PRIVILEGES ON * . * TO ''sample''@''localhost

Output

The output will be displayed as −

Query OK, 0 rows affected (0.02 sec)

Logging as a Different User

To log in as a different user in MySQL, you should first exit the current MySQL session if you are already logged in and then execute the command -u user_name -p in your system”s command prompt or terminal, not within the MySQL shell itself.

Example

Here, we are executing the -u sample -p command. After running the command, you will be prompted to enter the password for the specified user. Enter the correct password to log in as shown below −

mysql -u sample -p
Enter password: ******

Output

This will log you in as the sample user with the appropriate privileges and permissions as shown below −

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 12
Server version: 8.0.22 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.

The Expire Clause

If you use the expire clause, the old password (current password) will expire immediately and the user need to choose new password at first connection.

Example

Here, we are first removing the existing user ”sample”@”localhost” −

DROP user sample@localhost;

We are now creating a new user ”sample”@”localhost” with the password ”MyPassword” while immediately expiring the password, forcing the user to set a new password upon the first login −

CREATE USER ''sample''@''localhost'' 
IDENTIFIED BY ''MyPassword'' PASSWORD EXPIRE;

Now, if you log in as a newly created user, an error will be generated. So, to login as newly created user, open command prompt browse through bin folder of the MySQL directory and execute the following command −

C:Program FilesMySQLMySQL Server 8.0bin> mysql -u sample@localhost -p
Enter password: **********

Any MySQL command execution at this point will trigger an error message as shown below −

select now();

The output obtained is as shown below −

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Since the password is expired, the above error message is generated. To make this right we need to change (reset) the password using the following command −

SET PASSWORD=''passwordtest

Following is the output produced −

Query OK, 0 rows affected (0.34 sec)

You can also set an interval for the EXPIRE clause to implement periodic password changes as shown below −

DROP user sample@localhost;
CREATE USER ''sample''@''localhost''
   IDENTIFIED BY ''MyPassword''
   PASSWORD EXPIRE INTERVAL 25 DAY
   FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

After executing the above code, we get the following output −

Query OK, 0 rows affected (0.20 sec)

User Comment

You can add comments to the user while creating a user in MySQL using the COMMENT clause. This provides additional information or context about the user.

Example

In the following example, we are first removing the existing ”sample”@”localhost” user. Then, we are creating a new ”sample”@”localhost” user while adding a comment to describe the user −

drop user sample@localhost;
CREATE USER ''sample''@''localhost'' COMMENT ''Sample information

Output

The result obtained is as shown below −

Query OK, 0 rows affected (0.10 sec)

Verification

You can verify the attributes and comments info using the SELECT query given below −

SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES 
WHERE USER=''sample'' AND HOST=''localhost

The result produced is as shown below −

USER	HOST	ATTRIBUTE
sample	localhost	{“comment”: “Sample information”}

User Attribute

You can add attributes to a user in MySQL using the ATTRIBUTE clause when creating a user account. These attributes can store additional information about the user.

Example

In here, we are first removing the existing ”sample@localhost” user. Then, we are creating a new ”sample”@”localhost” user with attributes ”attr1” and ”attr2” set to ”val1” and ”val2,” respectively, associated with the user account −

DROP user sample@localhost;
CREATE USER ''sample''@''localhost'' 
ATTRIBUTE ''{"attr1": "val1", "attr2": "val2"}

The result obtained is as shown below −

Output

Query OK, 0 rows affected (0.09 sec)

Verification

You can verify the attributes and comments info using the SELECT query given below −

SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES 
WHERE USER=''sample'' AND HOST=''localhost

The result obtained is as shown below −

USER	HOST	ATTRIBUTE
sample	localhost	{“attr1”: “val1”, “attr2”: “val2”}

The IF NOT EXISTS Clause

If you try to create a user with an existing name, an error will be generated. To prevent this error and ensure the user is created only if it does not already exist, you can use the “IF NOT EXISTS” clause.

Example

In the example below we are creating a user ”sample@localhost” without the “IF NOT EXISTS” clause −

CREATE USER ''sample@localhost

We can see in the below output that an error is generated −

ERROR 1396 (HY000): Operation CREATE USER failed for ''sample@localhost''@''%''

However, if we use the “IF NOT EXISTS” clause along with the CREATE statement, a new user will be created, and if a user with the given name already exists, the query will be ignored −

CREATE USER IF NOT EXISTS ''sample@localhost

Following is the output obtained −

Query OK, 0 rows affected, 1 warning (0.01 sec)

Creating User Using a Client Program

In addition to creating a user into MySQL Database using the MySQL query, we can also create using a client program.

Syntax

Following are the syntaxes to create a MySQL user in various programming languages −

The MySQL PHP connector mysqli provides a function named query() to execute an SQL query in the MySQL database. To create a user in MySQL, we need to execute the CREATE USER statement using this function as −

$sql = "CREATE USER ''user_name''@''localhost'' IDENTIFIED WITH mysql_native_password BY ''password''";
$mysqli->query($sql);

To create a user using a NodeJS program, we need to execute the CREATE USER statement using the function named query() as −

sql= " CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY ''password 
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

To create a user in a MySQL database using Java program, we need to execute the CREATE USER statement using the JDBC function named execute() as −

sql = "CREATE USER ''USER_NAME''@LOCALHOST INDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY ''PASSWORD''";
statement.execute(sql);

The MySQL Connector/Python provides a function named execute() to execute an SQL query in the MySQL database. To create a user into a MySQL database, we need to execute the CREATE USER statement using this function as −

sql = "CREATE USER ''UserNew''@''localhost'' IDENTIFIED BY ''newPassword''";
cursorObj.execute(sql)

Example

Following are the client programs to create an user in MySQL −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf(''Connected successfully.
'');
$sql = "CREATE USER ''Revathi''@''localhost'' IDENTIFIED WITH mysql_native_password BY ''password''";
if($mysqli->query($sql)){
   printf("User created successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as follows −

User created successfully...!

var mysql = require(''mysql2'');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "Nr5a0204@123"
});

//Connecting to MySQL
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   console.log("--------------------------");

   //Creating User
   sql = "CREATE USER ''sample''@''localhost'' IDENTIFIED BY ''123456"
   con.query(sql);

   //List of users
   sql = "select user from MySQl.user;"
   con.query(sql, function(err, result){
      if (err) throw err
      console.log(result)
   });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  { user: ''mysql.infoschema'' },
  { user: ''mysql.session'' },
  { user: ''mysql.sys'' },
  { user: ''root'' },
  { user: ''sample'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class CreateUsers {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String user = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection con = DriverManager.getConnection(url, user, password);
         Statement st = con.createStatement();
         //System.out.println("Database connected successfully...!");
         String sql = "CREATE USER ''Vivek''@''localhost'' IDENTIFIED WITH mysql_native_password BY ''password''";
         st.execute(sql);
         System.out.println("User ''Vivek'' created successfully...!");
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

Output

The output obtained is as shown below −

User ''Vivek'' created successfully...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
   host=''localhost'',
   user=''root'',
   password=''password'',
   database=''textx''
)
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute("CREATE USER ''UserNew''@''localhost'' IDENTIFIED BY ''newPassword''")
print("User ''newUser'' is created successfully.")
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

User ''newUser'' is created successfully.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

Sign up for Newsletter

mysql

Khóa học miễn phí MySQL – Create Users nhận dự án làm có lương

MySQL – Create Users

The MySQL CREATE USERS Statement

Syntax

Example

Output

Verification

Granting Privileges in MySQL

Syntax

Example

Output

Logging as a Different User

Example

Output

The Expire Clause

Example

User Comment

Example

Output

Verification

User Attribute

Example

Output

Verification

The IF NOT EXISTS Clause

Example

Creating User Using a Client Program

Syntax

Example

Output

Output

Output

Output

user

Sign up for Newsletter

MySQL – Create Users

The MySQL CREATE USERS Statement

Syntax

Example

Output

Verification

Granting Privileges in MySQL

Syntax

Example

Output

Logging as a Different User

Example

Output

The Expire Clause

Example

User Comment

Example

Output

Verification

User Attribute

Example

Output

Verification

The IF NOT EXISTS Clause

Example

Creating User Using a Client Program

Syntax

Example

Output

Output

Output

Output

user

Login

Register