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 −
User | Host | account_locked |
---|---|---|
testuser | localhost | N |
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 −
User | Host | account_locked |
---|---|---|
demo | localhost | N |
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 −
User | Host | account_locked |
---|---|---|
test | localhost | Y |
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 −
User | Host | account_locked |
---|---|---|
sample | localhost | N |
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 −
Following is the syntax to unlock a MySQL user account using PHP −
$sql = "ALTER USER user_name ACCOUNT UNLOCK"; $mysqli->query($sql);
Following is the syntax to unlock a MySQL user account using JavaScript −
sql= "CREATE USER username@hostname IDENTIFIED BY ''new_password'' ACCOUNT UNLOCK"; con.query(sql, function (err, result) { if (err) throw err; console.log(result); });
Following is the syntax to unlock a MySQL user account using Java −
String sql = "ALTER USER USER_NAME@LOCALHOST ACCOUNT UNLOCK"; statement.execute(sql);
Following is the syntax to unlock a MySQL user account using Python −
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();
Output
The output obtained is as follows −
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); }); });
Output
The output produced is as follows −
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(); } } }
Output
The output obtained is as shown below −
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()
Output
Following is the output of the above code −
User ''newUser'' account is unlocked successfully.