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
      Show Privileges

      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.

      Show Privileges

      The users in MySQL must have enough privileges to interact with the server. This is possible by assigning authentication details, like passwords to the users. In addition to this, operational or administrative privileges are granted separately if a user wants to interact with and operate on the data.

      The MySQL SHOW Privileges

      The MySQL SHOW PRIVILEGES Statement displays the list of privileges that are supported by the MYSQL server. The displayed list includes all static and currently registered dynamic privileges.
      The information (returned list) contains three columns
      • Privilege − Name of the privilege
      • Context − Name of the MySQL object for which the privilege is applicable.
      • Comment − A string value describing the purpose of the privilege.

      Syntax

      Following is the syntax to list out all privileges in a MySQL Server 
      SHOW PRIVILEGES;

      Example

      Following query lists out all the privileges supported by the MySQL server 
      SHOW PRIVILEGES

      Output

      After executing the above code, we get the following output
      Privilege
      Context
      Comment
      Alter
      Tables
      To alter the table
      Alter routine
      Functions, Procedures
      To alter or drop stored functions/procedures
      Create
      Databases, Tables, Indexes
      To create new databases and tables
      Create routine
      Databases
      To use CREATE FUNCTION/PROCEDURE
      Create role
      Server Admin
      To create new roles
      Create temporary tables
      Databases
      To use CREATE TEMPORARY TABLE
      Create view
      Tables
      To create new views
      Create user
      Server Admin
      To create new users
      Delete
      Tables
      To delete existing rows
      Drop
      Databases, Tables
      To drop databases, tables, and views
      Drop role
      Server Admin
      To drop roles
      Event
      Server Admin
      To create, alter, drop and execute events
      Execute
      Functions, Procedures
      To execute stored routines
      File
      File access on server
      To read and write files on the server
      Grant option
      Databases, Tables, Funcs, Procedures
      To give to other users those privileges you possess
      Index
      Tables
      To create or drop indexes
      Insert
      Tables
      To insert data into tables
      Lock tables
      Databases
      To use LOCK TABLES (together with SELECT privilege)
      Process
      Server Admin
      To view the plain text of currently executing queries
      Proxy
      Server Admin
      To make proxy user possible
      References
      Databases,Tables
      To have references on tables
      Reload
      Server Admin
      To reload or refresh tables, logs and privileges
      Replication client
      Server Admin
      To ask where the slave or master servers are
      Replication slave
      Server Admin
      To read binary log events from the master
      Select
      Tables
      To retrieve rows from table
      Show databases
      Server Admin
      To see all databases with SHOW DATABASES
      Show view
      Tables
      To see views with SHOW CREATE VIEW
      Shutdown
      Server Admin
      To shut down the server
      Super
      Server Admin
      To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
      Trigger
      Tables
      To use triggers
      Create tablespace
      Server Admin
      To create/alter/drop tablespaces
      Update
      Tables
      To update existing rows
      Usage
      Server Admin
      No privileges - allow connect only
      BINLOG_ENCRYPTION_ADMIN
      Server Admin
      
      AUDIT_ADMIN
      Server Admin
      
      ENCRYPTION_KEY_ADMIN
      Server Admin
      
      INNODB_REDO_LOG_ARCHIVE
      Server Admin
      
      APPLICATION_PASSWORD_ADMIN
      Server Admin
      
      SHOW_ROUTINE
      Server Admin
      
      BACKUP_ADMIN
      Server Admin
      
      BINLOG_ADMIN
      Server Admin
      
      CLONE_ADMIN
      Server Admin
      
      CONNECTION_ADMIN
      Server Admin
      
      SET_USER_ID
      Server Admin
      
      SERVICE_CONNECTION_ADMIN
      Server Admin
      
      GROUP_REPLICATION_ADMIN
      Server Admin
      
      REPLICATION_APPLIER
      Server Admin
      
      INNODB_REDO_LOG_ENABLE
      Server Admin
      
      PERSIST_RO_VARIABLES_ADMIN
      Server Admin
      
      TABLE_ENCRYPTION_ADMIN
      Server Admin
      
      ROLE_ADMIN
      Server Admin
      
      REPLICATION_SLAVE_ADMIN
      Server Admin
      
      SESSION_VARIABLES_ADMIN
      Server Admin
      
      RESOURCE_GROUP_ADMIN
      Server Admin
      
      RESOURCE_GROUP_USER
      Server Admin
      
      SYSTEM_USER
      Server Admin
      
      SYSTEM_VARIABLES_ADMIN
      Server Admin
      
      XA_RECOVER_ADMIN
      Server Admin
      

      Listing Privileges Using a Client Program

      Now, let us see how to retrieve/list all the privileges granted to the current MySQL user using a client program in programming languages like Java, PHP, Python, JavaScript, C++ etc.

      Syntax

      Following are the syntaxes
      PHPNodeJSJavaPython
      To show all the privileges granted to an user, we need to pass the SHOW PRIVILEGES statement as a parameter to the query() function of the PHP mysqli library as 
      $sql = "SHOW PRIVILEGES";
      $mysqli->query($sql);

      Example

      Following are the programs
      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 = "SHOW PRIVILEGES";
      if($result = $mysqli->query($sql)){
         printf("PRIVILEGES found successfully...!");
         printf("Lists are: ");
         while($row = mysqli_fetch_array($result)){
             print_r($row);
         }
      }
      if($mysqli->error){
         printf("Failed..!" , $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as follows 
      PRIVILEGES found successfully...!Lists are: Array
      (
          [0] => Alter
          [Privilege] => Alter
          [1] => Tables
          [Context] => Tables
          [2] => To alter the table
          [Comment] => To alter the table
      )
      Array
      (
          [0] => Alter routine
          [Privilege] => Alter routine
          [1] => Functions,Procedures
          [Context] => Functions,Procedures
          [2] => To alter or drop stored functions/procedures
          [Comment] => To alter or drop stored functions/procedures
      )
      Array
      (
          [0] => Create
          [Privilege] => Create
          [1] => Databases,Tables,Indexes
          [Context] => Databases,Tables,Indexes
          [2] => To create new databases and tables
          [Comment] => To create new databases and tables
      )
      Array
      (
          [0] => Create routine
          [Privilege] => Create routine
          [1] => Databases
          [Context] => Databases
          [2] => To use CREATE FUNCTION/PROCEDURE
          [Comment] => To use CREATE FUNCTION/PROCEDURE
      )
      Array
      (
          [0] => Create role
          [Privilege] => Create role
          [1] => Server Admin
          [Context] => Server Admin
          [2] => To create new roles
          [Comment] => To create new roles
      )
      ..........
      
       (
          [0] => REPLICATION_SLAVE_ADMIN
          [Privilege] => REPLICATION_SLAVE_ADMIN
          [1] => Server Admin
          [Context] => Server Admin
          [2] =>
          [Comment] =>
      )
      Array
      (
          [0] => SENSITIVE_VARIABLES_OBSERVER
          [Privilege] => SENSITIVE_VARIABLES_OBSERVER
          [1] => Server Admin
          [Context] => Server Admin
          [2] =>
          [Comment] =>
      )