MySQL Show Columns Statement

To retrieve entire information of a table, we use DESCRIBE, DESC or SHOW COLUMNS statements.

All of these statements of MySQL can be used to retrieve/display the description of all the columns of a table, as they all retrieve the same result-sets.

Obtaining column information can be useful in several situations like inserting values into a table (based on the column datatype), updating or dropping a column, or to just simply know a table”s structure.

In this chapter, let us understand how to use SHOW COLUMNS statement in detail.

Syntax

Following is the syntax of the MySQL SHOW COLUMNS Statement −

SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}
   {FROM | IN} tbl_name
   [{FROM | IN} db_name]
   [LIKE ''pattern'' | WHERE expr]

Example

Let us start with creating a database named TUTORIALS using the below query −

CREATE DATABASE TUTORIALS;

Execute the following statement to change into TUTORIALS database −

USE TUTORIALS;

In the following query, we are creating a table named CUSTOMERS using the following CREATE TABLE statement −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, we are using the SHOW COLUMNS statement to retrieve the information about columns of the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

Output

Following is the information of columns in CUSTOMERS table −

Example

We can also use the IN clause instead of FROM as shown in the query below −

SHOW COLUMNS IN CUSTOMERS;

Output

As we can obeserve the output, it is exactly the same as the previous output.

Example

We can specify the name of the database along with the table name as shown in the query below −

SHOW COLUMNS IN CUSTOMERS FROM TUTORIALS;

Output

Following is the information of columns in CUSTOMERS table that is present in TUTORIALS database.

Example

We can replace the COLUMNS clause with FIELDS and get the same results −

SHOW FIELDS IN CUSTOMERS;

Output

As we see the output, we got the same results as COLUMNS clause.

The LIKE clause

In MySQL, using the LIKE clause, you can specify a pattern to retrieve info about specific columns.

Example

Following query retrieves the column names starting with the letter “P” from CUSTOMERS table.

SHOW COLUMNS FROM CUSTOMERS LIKE ''N%

Output

Executing the query above will produce the following output −

The WHERE clause

We can use the MySQL WHERE clause of the SHOW COLUMNS statements to retrieve information about the columns which match the specified condition.

Example

In the following example, we are using the WHERE clause to retrieve the columns where there type is int.

SHOW COLUMNS FROM CUSTOMERS WHERE Type= ''int

Output

Executing the query above will produce the following output −

The FULL clause

Usually, the information provided by the SHOW COLUMNS statements contains field type, can be null or not, key, default values and some extra details. If you use the full clause details like collation, privileges and comments will be added.

Example

In the following example, we are using the FULL clause with SHOW COLUMNS to retrieve extra details of the CUSTOMERS table −

SHOW FULL COLUMNS IN CUSTOMERS FROM tutorials;

Executing the query above will produce the following output −

Showing Columns of a table Using a Client Program

Besides showing the columns of a table in a MySQL database with a MySQL query, we can also use a client program to perform the SHOW COLUMNS operation.

Syntax

Following are the syntaxes to show columns of a MySQL table in various programming languages −

$sql="Show Table_name";
$mysqli->query($sql);

sql="SHOW COLUMNS FROM table_name";
con.query(sql);

String sql="SHOW COLUMNS FROM table_name FROM database";
statement.executeQuery(sql);

sql="SHOW COLUMNS FROM table_name FROM database";
cursorObj.execute(sql);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf(''Connected successfully.
'');

//column can be shown by the following queries
// $sql = "SHOW COLUMNS FROM tut_tbl";
$sql = "SHOW COLUMNS FROM sales FROM tutorials";
if ($show_clmn = $mysqli->query($sql)) {
    printf("show column executed successfully!.
");
    while ($col = mysqli_fetch_array($show_clmn)) {
        echo "n{$col[''Field'']}";
    }
}
if ($mysqli->errno) {
    printf("Columns could be shown by the above query!.
", $mysqli->error);
}
$mysqli->close();

show column executed successfully!.
ID
ProductName
CustomerName
DispatchDate
DeliveryTime
Price
Location

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 DATABASE demo"
  con.query(sql);

  sql = "USE demo"
  con.query(sql);

  sql = "CREATE TABLE sales(ID INT, ProductName VARCHAR(255), CustomerName VARCHAR(255), DispatchDate date, DeliveryTime time, Price INT, Location VARCHAR(255));"
  con.query(sql);

  //Displaying all the columns from the Sales table
  sql = "SHOW COLUMNS FROM sales;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Connected!
--------------------------
[
  {
    Field: ''ID'',
    Type: ''int'',
    Null: ''YES'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''ProductName'',
    Type: ''varchar(255)'',
    Null: ''YES'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''CustomerName'',
    Type: ''varchar(255)'',
    Null: ''YES'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''DispatchDate'',
    Type: ''date'',
    Null: ''YES'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''DeliveryTime'',
    Type: ''time'',
    Null: ''YES'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''Price'',
    Type: ''int'',
    Null: ''YES'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''Location'',
    Type: ''varchar(255)'',
    Null: ''YES'',
    Key: '''',
    Default: null,
    Extra: ''''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class ShowColumn {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //show column
            String sql = "SHOW COLUMNS FROM tutorials_tbl FROM TUTORIALS";
            ResultSet resultSet = statement.executeQuery(sql);
            System.out.println("Column has been shown successfully...!");
            while (resultSet.next()) {
                System.out.print(resultSet.getString(1));
                System.out.println();
            }
            connection.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

Connected successfully...!
Column has been shown successfully...!
ID
tutorial_title
tutorial_author

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''NOVELS''
#Creating a cursor object
cursorObj = connection.cursor()
show_columns_query = f"SHOW COLUMNS FROM {table_name}"
cursorObj.execute(show_columns_query)
columns_info = cursorObj.fetchall()
print(f"Columns of table ''{table_name}'':")
for column in columns_info:
    print(f"Column Name: {column[0]}, Type: {column[1]}, Null: {column[2]}, Key: {column[3]}, Default: {column[4]}")
cursorObj.close()
connection.close()

Columns of table ''tutorials_tbl'':
Column Name: tutorial_id, Type: b''int'', Null: NO, Key: PRI, Default: None
Column Name: tutorial_title, Type: b''varchar(100)'', Null: NO, Key: , Default: None
Column Name: tutorial_author, Type: b''varchar(40)'', Null: NO, Key: , Default: None
Column Name: submission_date, Type: b''date'', Null: YES, Key:, Default: None
Column Name: tutorial_name, Type: b''varchar(20)'', Null: YES, Key: , Default: None

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 −

Since the account is locked, you cannot access it unless it is unlocked again. Look at the example below −

C:WindowsSystem32> 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 −

To verify that the account is locked, let us access it as shown in the query below −

C:WindowsSystem32> 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 −

$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);

sql= "CREATE USER username@hostname IDENTIFIED BY ''new_password'' ACCOUNT LOCK";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

String sql = "ALTER USER USER_NAME@LOCALHOST IDENTIFIED BY ''password'' ACCOUNT LOCK";
Or,
String sql = "CREATE USER USER_NAME IDENTIFIED BY ''password'' ACCOUNT LOCK";
statement.execute(sql);

sql = f"ALTER USER ''{username_to_lock}''@''localhost'' ACCOUNT LOCK";
cursorObj.execute(sql);

Example

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

User has been locked 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 test@localhost IDENTIFIED BY ''asdfgh'' ACCOUNT LOCK;"
  con.query(sql);

  sql = "SELECT User, Host, account_locked FROM mysql.user WHERE User = ''test";
  con.query(sql, function(err, result){
    if (err) throw err;
    console.log(result);
  });
});

Connected!
--------------------------
[ { User: ''test'', Host: ''localhost'', account_locked: ''Y'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class LockUserAccount {
	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 IDENTIFIED BY ''password'' ACCOUNT LOCK";
            st.execute(sql);
            System.out.println("User ''Vivek'' account locked successfully...!");
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

User ''Vivek'' account locked successfully...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password''
)
username_to_lock = ''newUser''
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute(f"ALTER USER ''{username_to_lock}''@''localhost'' ACCOUNT LOCK")
print(f"User ''{username_to_lock}'' account is locked successfully.")
cursorObj.close()
connection.close()

User ''newUser'' account is locked successfully.

The ALTER TABLE statement in MySQL can be used to change the structure of a table. For instance, we can add, delete, or rename the columns, etc. using this statement.

Following are the two options that can be used with the ALTER TABLE statement to rename a column(s) of a table:

• RENAME COLUMN
• CHANGE COLUMN

Note: Renaming a column(s) of table requires ALTER and DROP privilages.

Using the RENAME COLUMN

In MySQL, we can change the name of one or multiple columns of a specified table using the ALTER TABLE RENAME COLUMN command.

Syntax

Following is the syntax to rename a column in MySQL table −

ALTER TABLE table_name
RENAME COLUMN old_column1_name TO new_column1_name,
RENAME COLUMN old_column2_name TO new_column2_name,
...;

Example

First of all, let us create a table named CUSTOMERS using the query below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL
);

Here, we are using the DESCRIBE command to display the information about the above created table structure −

DESCRIBE CUSTOMERS;

As we can see in the table below, we have three columns present in CUSTOMERS table −

Now, we are renaming the column named ID to cust_id using following query −

ALTER TABLE CUSTOMERS
	RENAME COLUMN ID TO cust_id;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Let us retrive the CUSTOMERS table description to verify whether the column ID is renamed to stud_id or not −

DESCRIBE CUSTOMERS;

As we observe in the output table, the ID column is renamed to stud_id successfully.

Example

Now, we are renaming the other two columns in CUSTOMERS table named NAME and AGE to cust_name and cust_age −

ALTER TABLE CUSTOMERS
	RENAME COLUMN NAME TO cust_name,
	RENAME COLUMN AGE TO cust_age;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

To verify whether column names have been renamed or not, execute the following query −

DESCRIBE CUSTOMERS;

As we observe in the output table, the above mentioned columns are successfully renamed.

Using CHANGE COLUMN

In MySQL, we can change the name of one or more columns along with their datatypes using the ALTER TABLE … CHANGE COLUMN command.

Syntax

Following is the syntax of the ALTER TABLE … CHANGE commnad in MySQL −

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name Data Type;

Example

Consider the previously updated CUSTOMERS table and, let us change the name and the datatype of cust_id column −

ALTER TABLE CUSTOMERS
	CHANGE COLUMN cust_id ID varchar(10);

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Using the following query, we can verify whether the column cust_id has changed its name and datatype or not −

DESCRIBE CUSTOMERS;

The name of the column and datatype has been changed successfully.

Example

Here, we are changing the names and datatypes of multiple columns (cust_name and cust_age) in the CUSTOMERS table −

ALTER TABLE CUSTOMERS
	CHANGE COLUMN cust_name NAME DECIMAL(18,2),
	CHANGE COLUMN cust_age AGE VARCHAR(20);

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Let us retrive the CUSTOMERS table description to verify whether the columns name and datatype are changed or not −

DESCRIBE STUDENTS;

As we observe in the output table, the names and datatypes of above mentioned columns are successfully changed.

Renaming a Column of a Table Using a Client Program

In addition to rename a column of a table in MySQL Database using MySQL query, we can also perform the ALTER TABLE operation on a table using a client program.

Syntax

Following are the syntaxes to rename a column of a MySQL table in various programming languages −

$sql = "ALTER TABLE table_name
RENAME COLUMN old_column1_name TO new_column1_name";
$mysqli->query($sql);

sql= "ALTER TABLE table_name
RENAME COLUMN old_column1_name TO new_column1_name";
con.query(sql);

String sql = "ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name";
statement.executeUpdate(sql);

sql = "ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name"
cursorObj.execute(sql);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf(''Connected successfully.
'');

// rename column;
$sql = "ALTER TABLE tut_tbl
        RENAME COLUMN tutorial_id TO tutorial_IDs";
if ($mysqli->query($sql)) {
    printf("Column renamed successfully!.
");
}
if ($mysqli->errno) {
    printf("Columns could be renamed!.
", $mysqli->error);
}
$mysqli->close();

Column renamed 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 = "USE TUTORIALS"
  con.query(sql);

  sql = "CREATE TABLE STUDENTS (ID INT NOT NULL, NAME VARCHAR(40) NOT NULL, AGE INT);"
  con.query(sql);

  //Records before renaming
  sql = "DESCRIBE STUDENTS;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
    console.log("--------------------------");
  });

  //Renaming column ID to "stud_id"
  sql = "ALTER TABLE STUDENTS RENAME COLUMN ID TO stud_id;"
  con.query(sql);

  //Records after renaming
  sql = "DESCRIBE STUDENTS;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Connected!
--------------------------
[
  {
    Field: ''ID'',
    Type: ''int'',
    Null: ''NO'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''NAME'',
    Type: ''varchar(40)'',
    Null: ''NO'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''AGE'',
    Type: ''int'',
    Null: ''YES'',
    Key: '''',
    Default: null,
    Extra: ''''
  }
]
--------------------------
[
  {
    Field: ''stud_id'',
    Type: ''int'',
    Null: ''NO'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''NAME'',
    Type: ''varchar(40)'',
    Null: ''NO'',
    Key: '''',
    Default: null,
    Extra: ''''
  },
  {
    Field: ''AGE'',
    Type: ''int'',
    Null: ''YES'',
    Key: '''',
    Default: null,
    Extra: ''''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class RenameColumn {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //Rename column name
            String sql = "ALTER TABLE tutorials_tbl RENAME COLUMN ID TO tutorial_Id";
            statement.executeUpdate(sql);
            System.out.println("Name renamed successfully...!");

            ResultSet resultSet = statement.executeQuery("DESCRIBE tutorials_tbl");
            while (resultSet.next()) {
                System.out.print(resultSet.getString(1)+ " " +resultSet.getString(2));
                System.out.println();
            }
            connection.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

Connected successfully...!
Name renamed successfully...!
tutorial_Id int
tutorial_title varchar(50)
tutorial_author varchar(30)

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''NOVELS''
old_column_name = ''PAGES''
new_column_name = ''PAGES_COUNT''
#Creating a cursor object
cursorObj = connection.cursor()
rename_column_query = f"ALTER TABLE {table_name} CHANGE {old_column_name} {new_column_name} INT"
cursorObj.execute(rename_column_query)
print(f"Column ''{old_column_name}'' is renamed to ''{new_column_name}'' in table ''{table_name}'' successfully.")
cursorObj.close()
connection.close()

Column ''tutorial_price'' is renamed to ''tutorial_amount'' in table ''tutorials_tbl_cloned'' successfully.

As we learnt earlier, a root user is connected to the server (using a password) immediately after installing MySQL. The privileges available to this user are default. The user accessing MySQL using root account has enough privileges to perform basic operations on the data. However, in exceptional cases, the user must manually request the host to grant privileges.

The MySQL Grant Privileges

MySQL provides several SQL statements to allow or restrict administrative privileges for users to interact with the data stored in the database. They are listed below −

• GRANT statement

• REVOKE statement

In this tutorial, let us learn about the GRANT statement in detail.

The MySQL GRANT Statement

The MySQL GRANT statement is used to assign various privileges or roles to MySQL user accounts. However, it”s important to note that you cannot assign both privileges and roles in a single GRANT statement. To grant privileges to users using this statement, you need to have the GRANT OPTION privilege.

Syntax

Following is the syntax of the MySQL GRANT Statement −

GRANT
privilege1, privilege2, privilege3...
ON object_type
TO user_or_role1, user_or_role2, user_or_role3...
[WITH GRANT OPTION]
[AS user
  [WITH ROLE
    DEFAULT
    | NONE
    | ALL
    | ALL EXCEPT role [, role ] ...
    | role [, role ] ...
   ]
]

Example

Assume we have created a user named ”test_user”@”localhost” in MySQL using the CREATE USER statement −

CREATE USER ''test_user''@''localhost'' IDENTIFIED BY ''testpassword

Following is the output of the above code −

Query OK, 0 rows affected (0.23 sec)

Now, let us create a database −

CREATE DATABASE test_database;

The output produced is as follows −

Query OK, 0 rows affected (0.56 sec)

Next, we will use the created database −

USE test_database;

We get the output as shown below −

Database changed

Now, let us create a table in the database −

CREATE TABLE MyTable(data VARCHAR(255));

The output obtained is as follows −

Query OK, 0 rows affected (0.67 sec)

Following query grants SELECT privileges on the table created above to the user ”test_user”@”localhost” −

GRANT SELECT ON test_database.MyTable TO ''test_user''@''localhost

After executing the above code, we get the following output −

Query OK, 0 rows affected (0.31 sec)

Verification

You can verify the granted privileges using the SHOW GRANTS statements −

SHOW GRANTS FOR ''test_user''@''localhost

The output we get is as shown below −

Granting Various Privileges

We know that the MySQL GRANT statement allows a wide range of privileges to user accounts. Here is a list of some commonly used privileges that can be granted using the GRANT statement −

To GRANT all the available privileges to a user, you need to use the ”ALL” keyword in the GRANT statement −

GRANT ALL ON test_database.MyTable TO ''test_user''@''localhost

After executing the above code, we get the following output −

Query OK, 0 rows affected (0.13 sec)

Granting Privileges on Stored Routines

To grant privileges on stored routines, such as tables, procedures or functions, in MySQL, you need to specify the object type (PROCEDURE or FUNCTION) after the ON clause followed by the name of the routine.

You can grant ALTER ROUTINE, CREATE ROUTINE, EXECUTE, and GRANT OPTION privileges on these stored routines.

Example

Assume we have created a stored procedure and a stored function with the name ”sample” in the current database as follows −

//Creating a procedure
DELIMITER //
CREATE PROCEDURE sample ()
   BEGIN
      SELECT ''This is a sample procedure
   END//
Query OK, 0 rows affected (0.29 sec)

//Creating a function
CREATE FUNCTION sample()
   RETURNS VARCHAR(120)
   DETERMINISTIC
   BEGIN
      DECLARE val VARCHAR(120);
      SET val = ''This is a sample function
      return val;
   END//
DELIMITER ;

Following is the output obtained −

Query OK, 0 rows affected (0.34 sec)

After creating these stored routines, you can grant ALTER ROUTINE, EXECUTE privileges on the above created procedure to the user named ”test_user”@”localhost” as follows −

GRANT ALTER ROUTINE, EXECUTE ON
PROCEDURE test_database.sample TO ''test_user''@''localhost

The output produced is as shown below −

Query OK, 0 rows affected (0.24 sec)

Now, the query below grants ALTER ROUTINE, EXECUTE privileges on the above created function to the user named ”test_user”@”localhost”.

GRANT ALTER ROUTINE, EXECUTE ON
FUNCTION test_database.sample TO ''test_user''@''localhost

Following is the output of the above query −

Query OK, 0 rows affected (0.15 sec)

Privileges to Multiple Users

You can grant privileges to multiple users. To do so, you need to provide the names of the objects or users separated by commas.

Example

Assume we have created a table named ”sample” and three user accounts using the CREATE statement as shown below.

Creating a table −

CREATE TABLE sample (data VARCHAR(255));

We will get the output as shown below −

Query OK, 0 rows affected (3.55 sec)

Now, let us create the user accounts.

Creating User ”test_user1” −

CREATE USER test_user1 IDENTIFIED BY ''testpassword

The output obtained is as follows −

Query OK, 0 rows affected (0.77 sec)

Creating User ”test_user2” −

CREATE USER test_user2 IDENTIFIED BY ''testpassword

Following is the output produced −

Query OK, 0 rows affected (0.28 sec)

Creating the 3rd user −

Creating User ”test_user3” −

CREATE USER test_user3 IDENTIFIED BY ''testpassword

We get the output as follows −

Query OK, 0 rows affected (0.82 sec)

Following query grant SELECT, INSERT and UPDATE privileges on the tables ”sample1”, ”sample2” and ”sample3” to to all three users (”test_user1”, ”test_user2”, and ”test_user3”) using a single GRANT statement.

GRANT SELECT, INSERT, UPDATE ON
TABLE sample TO test_user1, test_user2, test_user3;

Output

After executing the above code, we get the following output −

Query OK, 0 rows affected (0.82 sec)

Global Privileges

Instead of specifying the table, procedure or a function you can grant global privileges: privileges that apply to all databases to a user. To do so, you need to use *.* after the ON clause.

Example

Following query grants SELECT, INSERT and UPDATE privileges on all databases to the user named ”test_user”@”localhost” −

GRANT SELECT, INSERT, UPDATE ON *.* TO ''test_user''@''localhost

Output

Following is the output obtained −

Query OK, 0 rows affected (0.43 sec)

Example

Similarly, following query grants all privileges on all the databases to the ”test_user”@”localhost −

GRANT ALL ON *.* TO ''test_user''@''localhost

Output

The output produced is as shown below −

Query OK, 0 rows affected (0.41 sec)

Database Level Privileges

You can grant privileges to all the objects in a database by specifying the database name followed by “.*” after the ON clause.

Example

Following query grants SELECT, INSERT and UPDATE privileges on all objects in the database named test to the user ”test_user”@”localhost” −

GRANT SELECT, INSERT, UPDATE
ON test.* TO ''test_user''@''localhost

Output

Following is the output of the above code −

Query OK, 0 rows affected (0.34 sec)

Example

Similarly, following query grants all privileges on all the databases to the ”test_user”@”localhost −

GRANT ALL ON test.* TO ''test_user''@''localhost

Output

Output of the above code is as follows −

Query OK, 0 rows affected (0.54 sec)

Column Level Privileges

You can grant privileges on a specific column of a table to a user. To do so, you need to specify the column names after the privileges.

Example

Assume we have created a table named Employee using the CREATE query as −

CREATE TABLE Employee (
ID INT, Name VARCHAR(15), Phone INT, SAL INT);

The output produced is as shown below −

Query OK, 0 rows affected (6.47 sec)

Following query grants SELECT privilege to the user named ”test_user”@”localhost” on the ID column and INSERT and UPDATE privileges on the columns Name and Phone of the Employee table −

GRANT SELECT (ID), INSERT (Name, Phone)
ON Employee TO ''test_user''@''localhost

The output obtained is as follows −

Query OK, 0 rows affected (0.54 sec)

Proxy User Privileges

You can make one user as a proxy of another by granting the PROXY privilege to it. If you do so, both users have the same privileges.

Example

Assume we have created a users named sample_user, proxy_user in MySQL using the CREATE statement as shown below −

CREATE USER sample_user, proxy_user IDENTIFIED BY ''testpassword

Following is the output obtained −

Query OK, 0 rows affected (0.52 sec)

The following query grants SELECT and INSERT privileges on the Employee table created above to the user sample_user −

GRANT SELECT, INSERT ON Emp TO sample_user;

We get the output as shown below −

Query OK, 0 rows affected (0.28 sec)

Now, we can assign proxy privileges to the user proxy_user using the GRANT statement as shown below −

GRANT PROXY ON sample_user TO proxy_user;

The output is as follows −

Query OK, 0 rows affected (1.61 sec)

Granting Roles

Role in MySQL is a set of privileges with name. You can create one or more roles in MySQL using the CREATE ROLE statement. If you use the GRANT statement without the ON clause, you can grant a role instead of privileges.

Example

Let us start by creating a role named TestRole_ReadOnly.

CREATE ROLE ''TestRole_ReadOnly

Following is the output obtained −

Query OK, 0 rows affected (0.13 sec)

Now, let us grant read only privilege to the created role using the GRANT statement for accessing all objects within the database −

GRANT SELECT ON * . * TO ''TestRole_ReadOnly

The output of this GRANT statement should be −

Query OK, 0 rows affected (0.14 sec)

Then, you can GRANT the created role to a specific user. First, you will need to create the user as shown below −

CREATE USER ''newuser''@''localhost'' IDENTIFIED BY ''password

Following is the output produced −

Query OK, 0 rows affected (0.14 sec)

Now, you can grant the ”TestRole_ReadOnly” role to ”newuser”@”localhost” −

GRANT ''TestRole_ReadOnly'' TO ''newuser''@''localhost

The output obtained is as shown below −

Query OK, 0 rows affected (0.13 sec)

Granting Privileges Using a Client Program

Now, let us see how to grant privileges to a MySQL user using the client program.

Syntax

Following are the syntaxes −

$sql = "GRANT ALL PRIVILEGES ON database_name.* TO ''username''@''localhost''";
$mysqli->query($sql);

sql= "GRANT privilege_name(s) ON object TO user_account_name";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

String sql = "GRANT ALL PRIVILEGES ON DATABASE_NAME.* TO ''USER_NAME''@''localhost''";
statement.execute(sql);

sql = f"GRANT {privileges} ON your_database.* TO ''{username_to_grant}''@''localhost''";
cursorObj.execute(sql);

Example

Following are the programs −

$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 = "GRANT ALL PRIVILEGES ON tutorials.* TO ''Revathi''@''localhost''";
if($result = $mysqli->query($sql)){
   printf("Grant privileges executed successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Grant privileges executed 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 ''test_user''@''localhost'' IDENTIFIED BY ''testpassword"
  con.query(sql);

  sql = "CREATE DATABASE test_database;"
  con.query(sql);
  sql = "USE test_database;"
  con.query(sql);
  sql = "CREATE TABLE MyTable(data VARCHAR(255));"
  con.query(sql);

  sql = "GRANT SELECT ON test_database.MyTable TO ''test_user''@''localhost"
  con.query(sql);
  sql = "SHOW GRANTS FOR ''test_user''@''localhost";
  con.query(sql, function(err, result){
    if (err) throw err;
    console.log(result);
  });
});

Connected!
--------------------------
[
  {
    ''Grants for test_user@localhost'': ''GRANT USAGE ON *.* TO `test_user`@`localhost`''
  },
  {
    ''Grants for test_user@localhost'': ''GRANT SELECT ON `test_database`.`mytable` TO `test_user`@`localhost`''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class GranPriv {
	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 = "GRANT ALL PRIVILEGES ON tutorials.* TO ''Vivek''@''localhost''";
            st.execute(sql);
            System.out.println("You grant all privileges to user ''Vivek''...!");
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

You grant all privileges to user ''Vivek''...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password''
)
username_to_grant = ''newUser''
# privileges we want to grant
privileges = ''SELECT, INSERT, UPDATE''
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute(f"GRANT {privileges} ON your_database.* TO ''{username_to_grant}''@''localhost''")
print(f"Privileges granted to user ''{username_to_grant}'' successfully.")
cursorObj.close()
connection.close()

Privileges granted to user ''newUser'' successfully.

The MySQL DROP TABLE statement

The MySQL DROP TABLE statement is a Data Definition Language (DDL) command that is used to remove a table”s definition, and its data, indexes, triggers, constraints and permission specifications (if any). In simple terms, this statement will delete the entire table from the database.

However, while using DROP TABLE command, we need make note of the following −

• You should be very careful while using this command because once a table is deleted then all the information available in that table will also be lost forever.

• To drop a table in a database, one must require ALTER permission on the specified table and CONTROL permissions on the table schema.

• Even though it is a data definition language command, it is different from TRUNCATE TABLE statement as the DROP statement completely removes the table from the database.

Syntax

Following is the syntax of MySQL DROP TABLE statement −

DROP TABLE table_name ;

Dropping Tables from a database

To drop tables from a database, we need to use the MySQL DROP TABLE command. But we must make sure that the table exists in the database, before dropping it. If we try to drop a table that does not exist in the database, an error is raised.

Example

Let us start by creating a database TUTORIALS by executing below statement −

CREATE DATABASE TUTORIALS;

Using the following query, change the database to TUTORIALS −

USE TUTORIALS;

Create a a table CUSTOMERS using the following CREATE TABLE statement −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

To verify whether the above created is created in the TUTORIALS database or not, execute the following SHOW TABLES command −

SHOW TABLES IN TUTORIALS;

As we can see in the output below, the CUSTOMERS table is created in the TUTORIALS database.

Now, let us use the MySQL DROP TABLE statement to delete the above created CUSTOMERS table −

DROP TABLE CUSTOMERS;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.02 sec)

Verification

Since we have removed the table CUSTOMERS, if we try to verify the list of tables again, using the “SHOW TABLES” query it will display an Empty Set as shown below −

Empty set (0.00 sec)

The IF EXISTS clause

Instead of constantly checking whether a table exists or not in a database before deleting it, we can use the IF EXISTS clause along with the DROP TABLE statement.

When we specify this clause in the DROP TABLE query, it will automatically verify if the table exists in the current database. If it exists, it will then delete the table. If the table doesn”t exist, the query will be ignored.

Syntax

Following is the basic syntax of DROP TABLE IF EXISTS statement −

DROP TABLE [IF EXISTS] table_name;

Example

Here, we are using just DROP TABLE statement to drop the CUSTOMERS table which has been deleted already in the previous example.

DROP TABLE CUSTOMERS;

Output

Since, we are not using IF EXISTS with the DROP TABLE command, it will display an error as follows −

ERROR 1051 (42S02): Unknown table ''tutorials.customers''

Example

If we try to drop CUSTOMERS that does not exist in the database, using the IF EXISTS clause, the query will be ignored without issuing any error −

DROP TABLE IF EXISTS CUSTOMERS;

Executing the query above will produce the following output −

Query OK, 0 rows affected, 1 warning (0.01 sec)

Dropping Table Using a Client Program

In addition to dropping a table from MySQL Database using the MySQL query, we can also perform the DROP TABLE operation on a table using a client program.

Syntax

Following are the syntaxes to drop a table from MySQL in various programming languages −

$sql="DROP TABLE Table_name";
$mysqli->query($sql);

sql = "DROP TABLE Table_name";
con.query(sql);

String sql="DROP TABLE Table_name";
statement.execute(sql);

sql="DROP TABLE Table_name";
cursorObj.execute(sql);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''root@123
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if($mysqli->connect_errno ) {
   printf("Connect failed: %s<br />", $mysqli->connect_error);
   exit();
}
printf(''Connected successfully.<br />'');

if ($mysqli->query("Drop Table tutorials_tbl")) {
   printf("Table tutorials_tbl dropped successfully.<br />");
}
if ($mysqli->errno) {
   printf("Could not drop table: %s<br />", $mysqli->error);
}

$mysqli->close();

Connected successfully.
Table tutorials_tbl dropped 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("--------------------------");

  //Selecting a Database
  sql = "USE TUTORIALS"
  con.query(sql);

  sql = "DROP TABLE SalesSummary;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Connected!
--------------------------
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  info: '''',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DropTable {
    public static void main(String[] args) {
       String url = "jdbc:mysql://localhost:3306/TUTORIALS";
       String username = "root";
       String password = "password";
       try {
          Class.forName("com.mysql.cj.jdbc.Driver");
          Connection connection = DriverManager.getConnection(url, username, password);
          Statement statement = connection.createStatement();
          System.out.println("Connected successfully...!");

          //Drop a table....
          String sql = "DROP TABLE customer";
          statement.execute(sql);
          System.out.println("Table Dropped successfully...!");

          connection.close();
       } catch (Exception e) {
          System.out.println(e);
       }
    }
}

Connected successfully...!
Table Dropped successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''tutorials_tbl_cloned''
#Creating a cursor object
cursorObj = connection.cursor()
drop_table_query = f"DROP TABLE {table_name}"
cursorObj.execute(drop_table_query)
print(f"Table ''{table_name}'' is dropped successfully.")
cursorObj.close()
connection.close()

Table ''tutorials_tbl_cloned'' is dropped successfully.

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 −

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 −

$sql = "SHOW PRIVILEGES";
$mysqli->query($sql);

sql= "SHOW PRIVILEGES;"
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

String sql = "SHOW PRIVILEGES";
statement.executeQuery(sql);

sql = f"SHOW GRANTS FOR ''{username_to_show}''@''localhost''";
cursorObj.execute(sql);

Example

Following are the programs −

$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();

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] =>
)

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 = "SHOW PRIVILEGES";
  con.query(sql, function(err, result){
    if (err) throw err;
    console.log(result);
  });
});

Connected!
--------------------------
[
  {
    Privilege: ''Alter'',
    Context: ''Tables'',
    Comment: ''To alter the table''
  },
.
.
.
  {
    Privilege: ''TELEMETRY_LOG_ADMIN'',
    Context: ''Server Admin'',
    Comment: ''''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ShowPriv {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		ResultSet rs;
		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 = "SHOW PRIVILEGES";
            rs = st.executeQuery(sql);
            System.out.println("All privileges: ");
            while(rs.next()) {
            	String priv = rs.getNString(1);
            	System.out.println(priv);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

All privileges:
Alter
Alter routine
Create
Create routine
Create role
Create temporary tables
Create view
Create user
Delete
Drop
Drop role
Event
Execute
File
Grant option
Index
Insert
Lock tables
Process
Proxy
References
Reload
Replication client
Replication slave
Select
Show databases
Show view
Shutdown
Super

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password''
)
username_to_show = ''newUser''
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute(f"SHOW GRANTS FOR ''{username_to_show}''@''localhost''")
privileges = cursorObj.fetchall()
print(f"Privileges for user ''{username_to_show}'' are:")
for grant in privileges:
    print(grant[0])
cursorObj.close()
connection.close()

Privileges for user ''newUser'' are:
GRANT USAGE ON *.* TO `newUser`@`localhost`
GRANT SELECT, INSERT, UPDATE ON `your_database`.* TO `newUser`@`localhost`

MySQL database provides a multi-user environment, that allows multiple clients to access the database at the same time. To run this environment smoothly, MySQL introduced the concept of locks.

A client in a session can lock a certain table they are working on, in order to prevent other clients from using the same table. This process will avoid any data losses that might occur when multiple users work on the same table simultaneously.

A client can lock a table and unlock it whenever needed. However, if a table is already locked by a client session, it cannot be accessed by other client sessions until it is released.

Locking Tables in MySQL

You can restrict the access to records of the tables in MYSQL by locking them. These locks are used to keep other sessions away from modifying the tables in the current session.

MySQL sessions can acquire or release locks on the table only for itself. To lock a table using the MySQL LOCK TABLES Statement you need have the TABLE LOCK and SELECT privileges.

These locks are used to solve the concurrency problems. There are two kinds of MYSQL table locks −

• READ LOCK − If you apply this lock on a table the write operations on it are restricted. i.e., only the sessions that holds the lock can write into this table.

• WRITE LOCK − This lock allows restricts the sessions (that does not possess the lock) from performing the read and write operations on a table.

Syntax

Following is the syntax of the MySQL LOCK TABLES Statement −

LOCK TABLES table_name [READ | WRITE];

Unlocking Tables in MySQL

Once the client session is done using/accessing a MySQL table, they must unlock the table for other client sessions to use it. To do so, you can use the MySQL UNLOCK TABLE statement. This will release the table until other sessions lock it again.

Syntax

Following is the syntax of the MySQL UNLOCK TABLES Statement −

UNLOCK TABLES;

Example

Let us start with creating a table named CUSTOMERS that contains the details as shown below −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, let”s insert 2 records into the above created table using the INSERT statement as −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
  (1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
  (2, ''Khilan'', 25, ''Delhi'', 1500.00 );

Create another table named BUYERS using the following query −

CREATE TABLE BUYERS (
   B_ID INT AUTO_INCREMENT,
   B_NAME VARCHAR(20) NOT NULL,
   B_AGE INT NOT NULL,
   B_ADDRESS CHAR (25),
   B_SALARY DECIMAL (18, 2),
   PRIMARY KEY (B_ID)
);

Following queries inserts records into the BUYERS table using the INSERT INTO SELECT statement. Here, we are trying to insert records from the CUSTOMERS table to BUYERS table.

Locking and Unlocking:

Here before the transfer, we are acquiring the write lock on the BUYERS table to which we are inserting records and acquiring read lock on the CUSTOMERS table from which we are inserting records. Finally, after the transfer we are releasing the records.

LOCK TABLES CUSTOMERS READ, BUYERS WRITE;

INSERT INTO BUYERS (B_ID, B_NAME, B_AGE, B_ADDRESS, B_SALARY)
   SELECT
      ID, NAME, AGE, ADDRESS, SALARY
   FROM
      CUSTOMERS
   WHERE
      ID = 1 AND NAME = ''Ramesh
INSERT INTO BUYERS (B_ID, B_NAME, B_AGE, B_ADDRESS, B_SALARY)
   SELECT
      ID, NAME, AGE, ADDRESS, SALARY
   FROM
      CUSTOMERS
   WHERE
      ID = 2 AND NAME = ''Khilan

UNLOCK TABLES;

Verification

We can verify the contents of the BUYERS table using the below query −

SELECT * FROM BUYERS;

As we can see in the BUYERS table, the records has been transferred.

Table Locking Using a Client Program

Besides locking a table in a MySQL database with a MySQL query, we can also use a client program to perform the LOCK TABLES operation.

Syntax

Following are the syntaxes to Lock a table in MySQL in various programming languages −

$sql="LOCK TABLES table_name [READ | WRITE]";
$mysqli->query($sql);

sql = "LOCK TABLES table_name [READ | WRITE]";
con.query(sql);

String sql="LOCK TABLES table_name [READ | WRITE]";
statement.executeUpdate(sql);

sql="LOCK TABLES table_name [READ | WRITE]";
cursorObj.execute(sql);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf(''Connected successfully.
'');

// Here we are locking two table;
$sql = "LOCK TABLES tut_tbl READ, clone_table WRITE";
if ($mysqli->query($sql)) {
    printf("Table locked successfully!.
");
}
if ($mysqli->errno) {
    printf("Table could not be locked!.
", $mysqli->error);
}
$mysqli->close();

Table locked 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 = "USE TUTORIALS"
  con.query(sql);

  sql = "CREATE TABLE SalesDetails (ID INT, ProductName VARCHAR(255), CustomerName VARCHAR(255), DispatchDate date, DeliveryTime time, Price INT, Location VARCHAR(255), CustomerAge INT, CustomrtPhone BIGINT, DispatchAddress VARCHAR(255), Email VARCHAR(50));"
  con.query(sql);

  sql = "insert into SalesDetails values(1, ''Key-Board'', ''Raja'', DATE(''2019-09-01''), TIME(''11:00:00''), 7000, ''Hyderabad'', 25, ''9000012345'', ''Hyderabad - Madhapur'', ''pujasharma@gmail.com'');"
  con.query(sql);
  sql = "insert into SalesDetails values(2, ''Mobile'', ''Vanaja'', DATE(''2019-03-01''), TIME(''10:10:52''), 9000, ''Chennai'', 30, ''90000123654'', ''Chennai- TNagar'', ''vanajarani@gmail.com'');"
  con.query(sql);

  sql = "CREATE TABLE CustContactDetails (ID INT,Name VARCHAR(255), Age INT,Phone BIGINT, Address VARCHAR(255), Email VARCHAR(50));"
  con.query(sql);

  sql = "LOCK TABLES SalesDetails READ, CustContactDetails WRITE;"
  con.query(sql);

  sql = "INSERT INTO CustContactDetails (ID, Name, Age, Phone, Address, Email) SELECT ID, CustomerName, CustomerAge, CustomrtPhone, DispatchAddress, Email FROM SalesDetails  WHERE  ID = 1 AND CustomerName = ''Raja"
  con.query(sql);

  sql = "INSERT INTO CustContactDetails (ID, Name, Age, Phone, Address, Email) SELECT ID, CustomerName, CustomerAge, CustomrtPhone, DispatchAddress, Email FROM  SalesDetails WHERE ID = 2 AND CustomerName = ''Vanaja"
  con.query(sql);

  sql = "UNLOCK TABLES;"
  con.query(sql);

  sql = "SELECT * FROM CustContactDetails;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Connected!
--------------------------
[
  {
    ID: 1,
    Name: ''Raja'',
    Age: 25,
    Phone: 9000012345,
    Address: ''Hyderabad - Madhapur'',
    Email: ''pujasharma@gmail.com''
  },
  {
    ID: 2,
    Name: ''Vanaja'',
    Age: 30,
    Phone: 90000123654,
    Address: ''Chennai- TNagar'',
    Email: ''vanajarani@gmail.com''
  }
]

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TableLock {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //Lock table....
            String sql = "LOCK TABLES tutorials_tbl READ, clone_tbl WRITE";
            statement.executeUpdate(sql);
            System.out.println("Table Locked successfully...!");

            connection.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

Connected successfully...!
Table Locked successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''tutorials_tbl''
#Creating a cursor object
cursorObj = connection.cursor()
lock_table_query = f"LOCK TABLES {table_name} WRITE"
cursorObj.execute(lock_table_query)
print(f"Table ''{table_name}'' is locked successfully.")
cursorObj.close()
connection.close()

Table ''tutorials_tbl'' is locked successfully.

Earlier, we discussed how a root user gets access to a MySQL server with default privileges after installation. These privileges are sufficient for performing basic operations on the data. However, in some special situations, users might need to request the server”s host to take away certain privileges. To do so, we use the MySQL REVOKE statement.

The MySQ REVOKE statement

The MySQL REVOKE statement is used to remove certain administrative privileges or roles from users. It revokes permissions that were previously granted.

Syntax

Following is the syntax of the MySQL REVOKE Statement −

REVOKE privileges
   ON database_name.table_name
   FROM ''user''@''host

Example

Assume we have created a user named ”test_user”@”localhost” in MySQL using the CREATE USER statement as shown below −

CREATE USER ''test_user''@''localhost'' IDENTIFIED BY ''testpassword

Following is the output produced −

Query OK, 0 rows affected (0.23 sec)

Now, let us create a database named ”test_database” −

CREATE DATABASE test_database;

The output produced is as follows −

Query OK, 1 row affected (0.56 sec)

Next, we will use the created database −

USE test_database;

We get the output as shown below −

Database changed

Now, let us create a table in the database −

CREATE TABLE MyTable(data VARCHAR(255));

The output obtained is as follows −

Query OK, 0 rows affected (0.67 sec)

Following query grants privileges on the table created above to the user ”test_user”@”localhost −

GRANT SELECT ON test_database.MyTable TO ''test_user''@''localhost

After executing the above code, we get the following output −

Query OK, 0 rows affected (0.31 sec)

You can verify the granted privileges using the SHOW GRANTS statements −

SHOW GRANTS FOR ''test_user''@''localhost

The output we get is as shown below −

Now, you can revoke the above granted privilege using the REVOKE statement as shown below −

REVOKE SELECT ON test_database.MyTable FROM ''test_user''@''localhost

We get the output as follows −

Query OK, 0 rows affected (0.25 sec)

Verification

We can verify whether the SELECT privilege has been revoked or not using the SHOW GRANTS statements as shown below −

SHOW GRANTS FOR ''test_user''@''localhost

We can see that the output no longer lists the SELECT privilege, indicating that it has been revoked −

Revoking All Privileges

If a user has multiple privileges with a user, you can revoke all those privileges at once using the REVOKE ALL statement in MySQL.

Syntax

Following is the syntax to revoke all privileges in MySQL −

REVOKE ALL PRIVILEGES ON *.* FROM ''user''@''host

Example

Assume we have created a user as follows −

CREATE USER ''sample_user''@''localhost

Following is the output produced −

Query OK, 0 rows affected (0.18 sec)

We also create a procedure as shown below −

DELIMITER //
CREATE PROCEDURE sample ()
   BEGIN
      SELECT ''This is a sample procedure
   END//
DELIMITER ;

The output obtained is as follows −

Query OK, 0 rows affected (0.29 sec)

Additionally, we create a table named ”sample” in a database −

CREATE TABLE sample(data INT);

We get the output as shown below −

Query OK, 0 rows affected (0.68 sec)

Now, the following queries grants ALTER ROUTINE, EXECUTE privileges on the above created procedure to the user named ”sample_user”@”localhost”.

GRANT ALTER ROUTINE, EXECUTE
ON PROCEDURE test_database.sample TO ''sample_user''@''localhost

Output of the above code is as shown below −

Query OK, 0 rows affected (0.20 sec)

Similarly, following query grants SELECT, INSERT and UPDATE privileges on the table ”sample” to the user ”sample_user”@”localhost −

GRANT SELECT, INSERT, UPDATE
ON test.sample TO ''sample_user''@''localhost

The result produced is −

Query OK, 0 rows affected (0.14 sec)

You can verify the list of all privileges granted for the user using the SHOW GRANTS statement −

SHOW GRANTS FOR ''sample_user''@''localhost

The result obtained is as follows −

Finally, to revoke all the privileges granted to ”sample_user”@”localhost”, you can use the following statement −

REVOKE ALL PRIVILEGES, GRANT OPTION FROM ''sample_user''@''localhost

The result produced is −

Query OK, 0 rows affected (0.30 sec)

Verification

After revoking privileges, you can check the user”s grants again −

SHOW GRANTS FOR ''sample_user''@''localhost

The output below confirms that all privileges have been revoked −

Revoking Proxy Privilege

You can make one user as a proxy of another by granting the PROXY privilege to it. If you do so, both users have the same privileges.

Example

Assume we have created users named sample_user, proxy_user in MySQL using the CREATE statement −

CREATE USER sample_user, proxy_user IDENTIFIED BY ''testpassword

Following is the output obtained −

Query OK, 0 rows affected (0.52 sec)

Now, we are creating a table ”Employee” −

CREATE TABLE Employee (
ID INT, Name VARCHAR(15), Phone INT, SAL INT);

We get the output as shown below −

Query OK, 0 rows affected (6.47 sec)

Following query grants SELECT and INSERT privileges on the table created above, to the user sample_user −

GRANT SELECT, INSERT ON Emp TO sample_user;

The output obtained is as follows −

Query OK, 0 rows affected (0.28 sec)

Now, we can assign proxy privileges to the user proxy_user using the GRANT statement as shown below −

GRANT PROXY ON sample_user TO proxy_user;

The result produced is −

Query OK, 0 rows affected (1.61 sec)

You can revoke a proxy privilege using the REVOKE PROXY statement as shown below −

REVOKE PROXY ON sample_user FROM proxy_user;

We get the following result −

Query OK, 0 rows affected (0.33 sec)

Revoking a Role

A role in MySQL is a set of privileges with name. You can create one or more roles in MySQL using the CREATE ROLE statement. If you use the GRANT statement without the ON clause, you can grant a role instead of privileges.

Example

Following query creates a role named TestRole_ReadOnly −

CREATE ROLE ''TestRole_ReadOnly

Following is the output of the above code −

Query OK, 0 rows affected (0.13 sec)

Now, let us grant read only privilege to the created role using the GRANT statement −

GRANT SELECT ON * . * TO ''TestRole_ReadOnly

The result obtained is −

Query OK, 0 rows affected (0.14 sec)

Then, you can GRANT the created role to a user as follows −

CREATE USER ''newuser''@''localhost'' IDENTIFIED BY ''password

Output of the above code is as follows −

Query OK, 0 rows affected (0.14 sec)

Next, you can grant the ”TestRole_ReadOnly” role to the ”newuser”@”localhost”−

GRANT ''TestRole_ReadOnly'' TO ''newuser''@''localhost

We get the following result −

Query OK, 0 rows affected (0.13 sec)

Following query revokes the role from the user −

REVOKE ''TestRole_ReadOnly'' FROM ''newuser''@''localhost

After executing the above code, we get the following output −

Query OK, 0 rows affected (1.23 sec)

Revoking Privileges Using a Client Program

We can also revoke privileges from a MySQL user using a client program.

Syntax

Following are the syntaxes to revoke MySQL Privileges in various programming languages −

$sql = "REVOKE ALL, GRANT OPTION FROM user_name";
$mysqli->query($sql);

sql= "REVOKE privilege_name(s) ON object FROM user_account_name";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

String sql = "REVOKE ALL PRIVILEGES, GRANT OPTION FROM USER_NAME";
statement.execute(sql);

sql = f"REVOKE {privileges} ON your_database.* FROM ''{username_to_revoke}''@''localhost''";
cursorObj.execute(sql);

Example

Following are the implementations of this operation 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 = "REVOKE ALL, GRANT OPTION FROM Sarika";
if($result = $mysqli->query($sql)){
   printf("Revoke privileges executed successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Revoke privileges executed 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 ''test_user''@''localhost'' IDENTIFIED BY ''testpassword"
  con.query(sql);

  sql = "CREATE DATABASE test_database;"
  con.query(sql);

  sql = "USE test_database;"
  con.query(sql);

  sql = "CREATE TABLE MyTable(data VARCHAR(255));"
  con.query(sql);

  sql = "GRANT SELECT ON test_database.MyTable TO ''test_user''@''localhost"
  con.query(sql);

  sql = "SHOW GRANTS FOR ''test_user''@''localhost";
  con.query(sql, function(err, result){
    if (err) throw err;
    console.log("**Granted privileges:**");
    console.log(result);
    console.log("--------------------------");
  });
  sql = "REVOKE SELECT ON test_database.MyTable FROM ''test_user''@''localhost"
  con.query(sql);

  sql = "SHOW GRANTS FOR ''test_user''@''localhost";
  con.query(sql, function(err, result){
    if (err) throw err;
    console.log("**Grants after revoking:**");
    console.log(result);
  });
});

Connected!
--------------------------
**Granted privileges:**
[
  {
    ''Grants for test_user@localhost'': ''GRANT USAGE ON *.* TO `test_user`@`localhost`''
  },
  {
    ''Grants for test_user@localhost'': ''GRANT SELECT ON `test_database`.`mytable` TO `test_user`@`localhost`''
  }
]
--------------------------
**Grants after revoking:**
[
  {
    ''Grants for test_user@localhost'': ''GRANT USAGE ON *.* TO `test_user`@`localhost`''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class RevokePriv {
	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 = "REVOKE ALL PRIVILEGES, GRANT OPTION FROM Vivek";
            st.execute(sql);
            System.out.println("You revoked all the privileges form user ''Vivek''");
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

You revoked all the privileges form user ''Vivek''

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password''
)
username_to_revoke = ''newUser''
# privileges we want to revoke
privileges = ''SELECT, INSERT, UPDATE''
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute(f"REVOKE {privileges} ON your_database.* FROM ''{username_to_revoke}''@''localhost''")
print(f"Privileges revoked from the user ''{username_to_revoke}'' successfully.")
cursorObj.close()
connection.close()

Privileges revoked from the user ''newUser'' successfully.

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 −

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 −

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 −

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 −

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 −

$sql = "ALTER USER user_name ACCOUNT UNLOCK";
$mysqli->query($sql);

sql= "CREATE USER username@hostname IDENTIFIED BY
  ''new_password'' ACCOUNT UNLOCK";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

String sql = "ALTER USER USER_NAME@LOCALHOST ACCOUNT UNLOCK";
statement.execute(sql);

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();

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);
  });
});

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();
		}
	}
}

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()

User ''newUser'' account is unlocked successfully.