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
      Drop Users

      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.

      Drop User

      Dropping users in MySQL will remove a user's access and permissions on a specific database. This is performed by database administrators to maintain security and control over who can interact with the database system, ensuring that only authorized users can access and manipulate the data.

      The MySQL Drop User Statement

      You can drop/delete one or more existing users in MySQL using the DROP USER Statement. Once you delete an account, all privileges of it are deleted. To execute this statement, you need to have CREATE USER privilege.

      Syntax

      Following is the syntax of the DROP USER statement 
      DROP USER [IF EXISTS] 'username'@'hostname';
      Where, user_name is the name of the MySQL user you need to delete.

      Example

      Suppose, we have created a MySQL user account named 'TestUser' as shown below 
      CREATE USER TestUser@localhost IDENTIFIED BY 'password1';
      Following is the output obtained 
      Query OK, 0 rows affected (0.04 sec)
      You can verify the list of users using the following query 
      SELECT user FROM MySQl.user;
      The table will be displayed as shown below
      user
      TestUser
      mysql.infoschema
      mysql.session
      mysql.sys
      newUser
      root
      sample
      Now, let us delete the 'TestUser' account created above using the DROP USER statement as shown below 
      DROP USER TestUser@localhost;
      After executing the above code, we can see the output as shown below 
      Query OK, 0 rows affected (0.02 sec)

      Verification

      Once a table is dropped, if you verify the list of the users as shown below using the SELECT statement, you will find that its name is missing from the list 
      SELECT user FROM MySQl.user;
      The table obtained is as follows
      user
      mysql.infoschema
      mysql.session
      mysql.sys
      newUser
      root
      sample

      Removing Multiple Users

      You can also delete multiple users at once using the DROP ROLE statement. Roles are used to manage permissions and access control in a database system. By dropping a role, you revoke all privileges associated with that role.

      Example

      Let us start by creating two roles 'MyAdmin' and 'MyDeveloper' 
      CREATE ROLE 'MyAdmin', 'MyDeveloper';
      The output obtained is as follows 
      Query OK, 0 rows affected (0.01 sec)
      Now, let us remove these roles using the DROP ROLE statement 
      DROP ROLE 'MyAdmin', 'MyDeveloper';
      This query will effectively delete both roles from the database 
      Query OK, 0 rows affected (0.01 sec)

      The IF EXISTS clause

      If you try to drop a MySQL user that doesn't exist, an error will be generated. To address this issue, MySQL provides the IF EXISTS clause, which can be used with the DROP USER statement.
      Hence, the IF EXISTS clause allows you to drop a user if they exist, and it handles situations where the specified user is not found in the database.

      Example

      In the below query, we are attempting to drop the 'demo' user. However, it results in an error because the user doesn't exist in the database 
      DROP USER demo@localhost;
      The output produced is as shown below 
      ERROR 1396 (HY000): Operation DROP USER failed for 'demo'@'localhost'
      If you use the IF EXISTS clause along with the DROP USER statement as shown below, the specified user will be dropped and if a user with the given name doesn't exist, the query will be ignored 
      DROP USER IF EXISTS demo;
      The output obtained is as follows 
      Query OK, 0 rows affected, 1 warning (0.01 sec)

      Dropping User Using a Client Program

      In this section we are going to see various client programs to drop an existing user from MySQL.

      Syntax

      Following are the syntaxes to drop a MySQL user in various programming languages
      PHPNodeJSJavaPython
      The MySQL PHP connector mysqli provides a function named query() to execute an SQL query in the MySQL database. To drop a user from a MySQL database, we need to execute the DROP USER statement using this function as 
      $sql = "DROP USER 'username'@'localhost'";
      $mysqli->query($sql);

      Example

      Following are the client programs to drop an user in MySQL
      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 = "DROP USER 'Revathi'@'localhost'";
      if($mysqli->query($sql)){
         printf("User dropped successfully...!");
      }
      if($mysqli->error){
         printf("Failed..!" , $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as follows 
      User dropped successfully...!