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.

MySQL ALTER Command

The MySQL ALTER command is used to modify the structure of an existing table. It allows you to make various changes, such as adding, deleting, or modify columns within the table.

Additionally, the ALTER command is also used to add and drop different constraints associated with an existing table.

Since this command modifies the structure of a table, it is a part of Data Definition Language in SQL. This is also where the ALTER command differs from UPDATE command; while ALTER interacts with the structure of a table to modify it, UPDATE only interacts with the data present in the table without disturbing its structure.

Syntax

Following is the syntax of ALTER command in MySQL −

ALTER TABLE table_name [alter_option ...];

Example

Let us begin with the creation of a table named CUSTOMERS.

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

Now, execute the following query to display information about the columns in CUSTOMERS table.

SHOW COLUMNS FROM CUSTOMERS;

Output

Following are the details of the columns of the CUSTOMERS table −

Dropping a Column

To drop a column in an existing table, we use the ALTER TABLE command with DROP clause.

Example

In the following example, we are dropping an existing column named ID from the above-created CUSTOMERS table −

ALTER TABLE CUSTOMERS DROP ID;

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

To verify whether the ID column has been dropped from the CUSTOMERS table, execute the following query −

SHOW COLUMNS FROM CUSTOMERS;

As we can see in the output below, there is no ID column present. Hence it is dropped.

Note: A DROP clause will not work if the column is the only one left in the table.

Adding a Column

To add a new column into an existing table, we use ADD keyword with the ALTER TABLE command.

Example

In the following query, we are adding a column named ID into an existing table CUSTOMERS.

ALTER TABLE CUSTOMERS ADD ID INT;

Output

Executing the query above will produce the following output −

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

Now, the CUSTOMERS table will contain the same two columns that it had when you first created the table. But the newly added ID column will be added at the end of the table by default. In this case, it will add after the NAME column.

Verification

Let us verify using the following query −

SHOW COLUMNS FROM CUSTOMERS;

As we can see in the output below, the newly added ID column is inserted at the end of the table.

Repositioning a Column

If we want a column to be placed at a specific position within the table, we can use FIRST to make it the first column or AFTER col_name to indicate that the new column should be positioned after the col_name.

Example

Consider the previously modified CUSTOMERS table, where the NAME is the first column and ID is the last column.

In the following query, we are removing the ID column from the table and then adding it back, positioning it as the first column in the table using FIRST keyword −

ALTER TABLE CUSTOMERS DROP ID;
ALTER TABLE CUSTOMERS ADD ID INT FIRST;

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

Now, let us verify the positions of the column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can see in the output below, the ID column is positioned first.

Example

Here, we are removing the ID column from the table and then adding it back, positioning it after the NAME column using the AFTER col_name keyword.

ALTER TABLE CUSTOMERS DROP ID;
ALTER TABLE CUSTOMERS ADD ID INT AFTER NAME;

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

Now, let us verify the positions of the column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can see in the output below, the ID column is positioned first.

Note: The FIRST and AFTER specifiers work only with the ADD clause. This means that if you want to reposition an existing column within a table, you first must DROP it and then ADD it at the new position.

Altering a Column Definition or a Name

In MySQL, to change a column”s definition, we use MODIFY or CHANGE clause in conjunction with the ALTER command.

Example

In the query below, we are changing the definition of column NAME from varchar(20) to INT using the MODIFY clause −

ALTER TABLE CUSTOMERS MODIFY NAME INT;

Output

Executing the query above will produce the following output −

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

Verification

Now, let us verify the definition of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can observe, the definition for NAME column has been changed to INT.

Example

We can also change the column definition using CHANGE, but the syntax is a bit different from MODIFY. After the CHANGE keyword, we specify the name of the column (twice) that we want to change, then specify the new definition.

Here, we are changing the definition of column ID from INT to varchar(20) using the CHANGE clause −

ALTER TABLE CUSTOMERS MODIFY ID VARCHAR(20);

Output

Executing the query above will produce the following output −

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

Verification

Now, let us verify the definition of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can observe, the definition for NAME column has been changed to INT.

Altering a Column”s Default Value

In MySQL, we can change a default value for any column by using the DEFAULT constraint with ALTER command.

Example

In the following example, we are changing the default value of NAME column.

ALTER TABLE CUSTOMERS ALTER NAME SET DEFAULT 1000;

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

Now, let us verify the default value of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can observe, the default value for NAME column has been changed to 1000.

Example

We can remove the default constraint from any column by using DROP clause along with the ALTER command.

Here, we are removing the default constraint of NAME column.

ALTER TABLE CUSTOMERS ALTER NAME DROP DEFAULT;

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

Now, let us verify the default value of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM CUSTOMERS;

As we can observe, the default value for NAME column has been changed to NULL.

Altering (Renaming) a Table

To rename a table, use the RENAME option of the ALTER TABLE statement.

Example

The following query renames the table named CUSTOMERS to BUYERS.

ALTER TABLE CUSTOMERS RENAME TO BUYERS;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.02 sec)

Verification

Now, let us verify the default value of the NAME column in the CUSTOMERS table −

SHOW COLUMNS FROM BUYERS;

The table has been renamed to BUYERS, as we can see from the columns within it.

Altering Table Using a Client Program

Besides altering an existing table in a MySQL database with a MySQL query, we can also use a client program to perform the ALTER TABLE operation.

Syntax

Following are the syntaxes of Altering a table from MySQL Database in various programming languages −

$sql = "ALTER TABLE table_name";
$mysqli->query($sql);

sql = "ALTER TABLE table_name";
con.query(sql);

String sql = "ALTER TABLE table_name";
statement.execute(sql);

sql = "ALTER TABLE table_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.
'');

$sql = "ALTER TABLE testalter_tbl  DROP i";
if ($mysqli->query($sql)) {
    printf("table altered successfully.
");
}
if ($mysqli->errno) {
    printf("table could not alter: %s
", $mysqli->error);
}
$mysqli->close();

table altered 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);

  //Altering a table
  sql = "ALTER TABLE testalter_tbl  DROP i";
    con.query(sql, function(err){
    if (err) throw err
    console.log("Altered table successfully...");
  });
});

Connected!
--------------------------
Altered table successfully...

import java.sql.*;
public class AlterTable {
    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...!");

            //Alter table statement...!
            String sql = "ALTER TABLE testalter_tbl  DROP i";
            statement.executeUpdate(sql);
            System.out.println("Table altered successfully...!");
            connection.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}

Connected successfully...!
Table altered successfully...!

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

Table ''testalter_tbl'' is altered successfully.