Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Unlock User Account

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      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
      PHPNodeJSJavaPython
      Following is the syntax to unlock a MySQL user account using PHP 
      $sql = "ALTER USER user_name ACCOUNT UNLOCK";
      $mysqli->query($sql);

      Example

      Following are the programs to unlock users in various programming languages
      PHPNodeJSJavaPython
      $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..!