MySQL - Unlock User Account

Account Locking and Unlocking in MySQL is introduced to increase security of the database by preventing unauthorized transactions or suspicious activities.

MySQL Unlock User Account

To check whether an account is unlocked or not, MySQL provides the 'account_locked' attribute in the 'mysql.user' table that will hold either 'Y' or 'N' values respectively. If the attribute holds the 'N' value, then the account is said to be in the unlock mode.

By default, all the new user accounts created in MySQL are unlocked.

Unlocking New Accounts

You can use the CREATE USER... ACCOUNT UNLOCK statement to unlock new accounts created in MySQL. By default, the newly created accounts are always unlocked unless specified otherwise. However, the ACCOUNT UNLOCK clause is mostly used when an account is in the locked state.

Syntax

Following is the syntax of CREATE USER... ACCOUNT UNLOCK statement −

CREATE USER username@hostname 
IDENTIFIED BY 'new_password' ACCOUNT UNLOCK;

Example

In the following query, we are creating a new user account in MySQL using the CREATE USER statement −

CREATE USER testuser@localhost 
IDENTIFIED BY 'qwerty' ACCOUNT UNLOCK;

Output

Following is the output of the above code −

Query OK, 0 rows affected (0.02 sec)

Verification

We can verify whether the account of the 'testuser' is unlocked or not using the following SELECT statement −

SELECT User, Host, account_locked 
FROM mysql.user WHERE User = 'testuser';

Output of the above code is as shown below −

Example

As we have learned above, the newly created user accounts are unlocked by default. Look at the example below −

CREATE USER demo@localhost IDENTIFIED BY '000000';

Output

The result produced is as follows −

Query OK, 0 rows affected (0.02 sec)

Verification

We can verify whether the newly created account is unlocked by default using the following SELECT statement −

SELECT User, Host, account_locked 
FROM mysql.user WHERE User = 'demo';

The output obtained is as follows −

Unlocking Existing Accounts

We can use the ALTER USER... ACCOUNT UNLOCK statement unlock existing accounts in MySQL that are locked beforehand.

Syntax

Following is the syntax of ALTER USER... ACCOUNT UNLOCK statement −

ALTER USER username@hostname ACCOUNT UNLOCK;

Example

We are first retrieving the information of the existing account 'sample', including its username, host, and the status of its account lock −

SELECT User, Host, account_locked 
FROM mysql.user WHERE User = 'sample';

We can see in the output below that the user account is locked −

Now, we will unlock the existing account 'sample' using the ALTER USER statement −

ALTER USER sample@localhost ACCOUNT UNLOCK;

Output

Following is the output of the above query −

Query OK, 0 rows affected (0.00 sec)

Verification

We can verify whether the account is unlocked or not using the following SELECT query −

SELECT User, Host, account_locked 
FROM mysql.user WHERE User = 'sample';

As we can see in the below output, the 'sample@localhost' account is now unlocked and can be accessed according to its privileges −

Unlock User Account Using a Client Program

Now, in this section let us discuss how to unlock a MySQL user using various client programs.

Syntax

Following are the syntaxes −

$sql = "ALTER USER user_name ACCOUNT UNLOCK";
$mysqli->query($sql);

sql= "CREATE USER username@hostname IDENTIFIED BY
  'new_password' ACCOUNT UNLOCK";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

String sql = "ALTER USER USER_NAME@LOCALHOST ACCOUNT UNLOCK";
statement.execute(sql);

sql = f"ALTER USER '{username_to_unlock}'@'localhost' ACCOUNT UNLOCK";
cursorObj.execute(sql);

Example

Following are the programs to unlock users in various programming languages −

$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 = "ALTER USER Sarika ACCOUNT UNLOCK";
if($mysqli->query($sql)){
   printf("User has been unlocked successfully..!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

User has been unlocked 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("--------------------------");

  sql = "CREATE USER testuser@localhost IDENTIFIED BY 'qwerty' ACCOUNT UNLOCK;"
  con.query(sql);

  sql = "SELECT User, Host, account_locked FROM mysql.user WHERE User = 'testuser';";
  con.query(sql, function(err, result){
    if (err) throw err;
    console.log(result);
  });
});

Connected!
--------------------------
[ { User: 'testuser', Host: 'localhost', account_locked: 'N' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class UnlockUserAccount {
	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 = "ALTER USER Vivek@localhost ACCOUNT UNLOCK";
            st.execute(sql);
            System.out.println("User 'Vivek' account unlocked successfully...!");    
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

User 'Vivek' account unlocked successfully...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password'
)
username_to_unlock = 'newUser'
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute(f"ALTER USER '{username_to_unlock}'@'localhost' ACCOUNT UNLOCK")
print(f"User '{username_to_unlock}' account is unlocked successfully.")
cursorObj.close()
connection.close()

User 'newUser' account is unlocked successfully.