MySQL - Lock User Account

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

In many cases, the MySQL user accounts require to be locked for various reasons. For instance, to wait while completing the authorization of an account, or if the account has been inactive for a very long time, etc. In such cases, locking accounts will improve the efficiency of the MySQL server.

MySQL Lock User Account

To check whether an account is locked or not, MySQL provides the 'account_locked' attribute in the 'mysql.user' table that will hold either 'Y' or 'N' values respectively. A value of 'Y' indicates that the account is locked, while 'N' indicates that it is not locked.

Locking New Accounts

MySQL provides ACCOUNT LOCK clause to lock the accounts. Using this clause with CREATE USER and ALTER USER statements will either create a new already locked user or lock the existing user respectively.

Syntax

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

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

Example

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

CREATE USER test@localhost IDENTIFIED BY 'asdfgh' ACCOUNT LOCK;

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 'test' user is locked or not using the following SELECT statement −

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

Output of the above code is as shown below −

Since the account is locked, you cannot access it unless it is unlocked again. Look at the example below −

C:\Windows\System32> mysql -u test -p
Enter password: ******

The result produced is as follows −

ERROR 3118 (HY000): Access denied for user 'test'@'localhost'. Account is locked.

Locking Existing Accounts

We can use the ALTER USER... ACCOUNT LOCK statement to lock existing accounts in MySQL. But you must make sure that the user is in the unlock state before executing the query.

Syntax

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

ALTER USER username@hostname ACCOUNT LOCK;

Example

In here, we are locking an existing user account in MySQL using the ALTER USER statement −

ALTER USER sample@localhost ACCOUNT LOCK;

Output

Output of the above code is as follows −

Query OK, 0 rows affected (0.00 sec)

Verification

We can verify whether the account of the 'sample' user is locked or not using the following SELECT statement −

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

The result obtained is as shown below −

To verify that the account is locked, let us access it as shown in the query below −

C:\Windows\System32> mysql -u sample -p
Enter password: ******

We get the output as follows −

ERROR 3118 (HY000): Access denied for user 'sample'@'localhost'. Account is locked.

Locking User Account Using a Client Program

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

Syntax

Following are the syntaxes −

$sql = "CREATE USER user_name IDENTIFIED BY 'password' ACCOUNT LOCK";
Or,
$sql = "ALTER USER user_name@localhost IDENTIFIED BY 'password' ACCOUNT LOCK";
$mysqli->query($sql);

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

String sql = "ALTER USER USER_NAME@LOCALHOST IDENTIFIED BY 'password' ACCOUNT LOCK";
Or,
String sql = "CREATE USER USER_NAME IDENTIFIED BY 'password' ACCOUNT LOCK";
statement.execute(sql);

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

Example

Following are the programs to lock 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 = "CREATE USER Sarika IDENTIFIED BY 'password' ACCOUNT LOCK;";
if($mysqli->query($sql)){
   printf("User has been locked successfully..!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

User has been locked 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 test@localhost IDENTIFIED BY 'asdfgh' ACCOUNT LOCK;"
  con.query(sql);

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

Connected!
--------------------------
[ { User: 'test', Host: 'localhost', account_locked: 'Y' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class LockUserAccount {
	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 IDENTIFIED BY 'password' ACCOUNT LOCK";
            st.execute(sql);
            System.out.println("User 'Vivek' account locked successfully...!");    
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

User 'Vivek' account locked successfully...!

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

User 'newUser' account is locked successfully.