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
      Lock 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.

      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
      User
      Host
      account_locked
      test
      localhost
      Y
      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
      User
      Host
      account_locked
      sample
      localhost
      Y
      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
      PHPNodeJSJavaPython
      Following is the syntax to lock the MySQL user account using PHP 
      $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);

      Example

      Following are the programs to lock 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 = "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();

      Output

      The output obtained is as follows 
      User has been locked successfully..!