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`

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.

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.

MySQL provides an account to each user which is authenticated with a username and a password. The default account in MySQL is a root with no password (One can however set a password to the root using a statement). Similarly, other user-defined accounts can have passwords set explicitly using an SQL statement or can have it system generated by MySQL.

MySQL Change User Password

Just like any other authenticated accounts, MySQL has a provision to change the user password. But one must make sure that there is currently no application being used by the user. If the password is reset without disconnecting the application, the application cannot connect to the server through this user again.

We can change the password for a MySQL user account using the following three SQL statements −

• UPDATE statement

• SET PASSWORD statement

• ALTER USER statement

The UPDATE Statement

The most basic way to change a user”s password in MySQL is by using the UPDATE statement. This statement is used to update account details, including the account password, from the ”root” account. But, once the modifications are done using this statement, you must use the FLUSH PRIVILEGES statement to reload privileges from the grant table of the MySQL database.

Syntax

Following is the syntax to change password using the UPDATE statement −

UPDATE mysql.user
SET authentication_string = PASSWORD(password_string)
WHERE User = user_name AND
      Host = host_name
FLUSH PRIVILEGES;

Example

Following example demonstrates how to change the password of a user account using the UPDATE statement. Firstly, we are creating a user account “sample” with a password ”123456” −

CREATE USER ''sample''@''localhost'' IDENTIFIED BY ''123456

Following is the output obtained −

Query OK, 0 rows affected (0.02 sec)

Now, you can verify the list of users using the following query −

SELECT User FROM mysql.user;

The table will be displayed as shown below −

If you have the MySQL version 5.7.6 and later, you can directly modify the mysql.user table with the following query −

UPDATE user
SET authentication_string = PASSWORD(''xxxxxx'')
WHERE User = ''sample'' AND Host = ''localhost

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

Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

After making changes to user accounts, you need to use the FLUSH PRIVILEGES statement to apply these changes immediately −

FLUSH PRIVILEGES;

The output obtained is as shown below −

Query OK, 0 rows affected (0.01 sec)

The SET PASSWORD statement

The SET PASSWORD statement is used to set a password for a MySQL account. It contains a “password-verification” clause which lets the system know that the current user password needs to be replaced by another.

Syntax

Following is the syntax for the SET PASSWORD statement −

SET PASSWORD FOR username@localhost = password_string;

You can also change the password using SET PASSWORD without using the FOR clause. To use this syntax however, you must already be logged in on the user account you wish to change the password of −

SET PASSWORD = password_string;

Example

Now, using the SET PASSWORD statement, we are changing the password to ”hello” −

SET PASSWORD = ''hello

Output

Following is the output of the above code −

Query OK, 0 rows affected (0.01 sec)

The ALTER USER Statement

To alter anything regarding a user account in MySQL, including changing passwords, ALTER USER statement is more preferable than SET PASSWORD statement. This statement is not used alone, instead is followed by the IDENTIFIED BY clause to authenticate the new password.

Note that the user must be connected to the MySQL server for this statement to work.

Syntax

Following is the syntax to change the password using the ALTER USER statement −

ALTER USER username IDENTIFIED BY ''password

Example

Here, we are changing the password of the sample@localhost account to ”000000” using the ALTER USER query given below −

ALTER USER sample@localhost IDENTIFIED BY ''000000

Output

Output of the above code is shown below −

Query OK, 0 rows affected (0.01 sec)

The password is now changed. To verify, log in to the sample account again using the new password −

C:WindowsSystem32> mysql -u sample -p
Enter password: ******

mysql>

Changing User password Using a Client Program

Besides using MySQL queries to change the user password in MySQL, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes −

$sql = "ALTER USER ''root''@''localhost'' IDENTIFIED BY ''new_password''";
$mysqli->query($sql);

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

String sql = "ALTER USER ''USER_NAME''@''LOCALHOST'' IDENTIFIED BY ''NEW_PASSWORD''";
statement.execute(sql);

sql = f"ALTER USER ''{username_to_change}''@''localhost'' IDENTIFIED BY ''{new_password}''";
cursorObj.execute(sql);

Example

Following are the client programs to change the user password in MySQL −

$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 ''root''@''localhost'' IDENTIFIED BY ''password1''";
if($mysqli->query($sql)){
   printf("User password has been changed successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Your password has been changed 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("--------------------------");

  //Listing the users
  sql = "SELECT USER FROM mysql.user;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**List of Users:**")
    console.log(result)
    console.log("--------------------------");
  });

  sql = "ALTER USER ''sample''@''localhost'' IDENTIFIED BY ''tutorials";
  con.query(sql, function(err){
    if (err) throw err;
    console.log("Password changed Successfully...");
  });
  sql = "FLUSH PRIVILEGES;"
  con.query(sql);
});

Connected!
--------------------------
**List of Users:**
[
  { USER: ''mysql.infoschema'' },
  { USER: ''mysql.session'' },
  { USER: ''mysql.sys'' },
  { USER: ''root'' },
  { USER: ''sample'' }
]
--------------------------
Password changed Successfully...

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class ChangePassword {
	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 ''root''@''localhost'' IDENTIFIED BY ''password1''";
            st.execute(sql);
            System.out.println("User ''root'' password changed successfully...!");
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

User ''root'' password changed successfully...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password''
)
username_to_change = ''newUser''
new_password = ''passwordSet!''
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute(f"ALTER USER ''{username_to_change}''@''localhost'' IDENTIFIED BY ''{new_password}''")
print(f"Password for user ''{username_to_change}'' changed successfully.")
cursorObj.close()
connection.close()

Password for user ''newUser'' changed successfully.

As you might have already known, MySQL is a multi-user database that allows multiple users to work on it simultaneously. But have you ever wondered who these users might be?

MySQL provides an account to each user that is authenticated with a username and a password. And details of these accounts are stored in the “user” table in the database. This table contains details like username, the host this user is connected from, and other privileges the said user has etc.

The MySQL SHOW Users

MySQL does not provide any direct command to show (list out) all the users. However, the details of these user accounts is stored in the “user” table within the database. Hence, we can use the SELECT statement to list out the contents of this table.

There is no limit for how many users can connect to a MySQL database but the default user is always “root”. And it does not have any password, unless it is set manually.

Syntax

Following is the syntax to show users in a MySQL database −

SELECT * FROM mysql.user;

Example

To see the structure of this “user” table, use the following query with the DESC command −

DESC mysql.user;

Now, in this example, we are listing out all the users in the MySQL database local to a system −

SELECT Host, User, User_attributes, account_locked
FROM mysql.user;

Output

The output obtained is as shown below −

The actual user table contains a lot more columns/fields than what is displayed in this chapter. Here, however, only some information is displayed for simplicity.

Note that list of these users are local to a system. Hence, not all systems would give the same output (apart from the default users).

Show Current User

Not only the list of all users, MySQL also has a provision to see the current user. This is done with the help of user() or current_user() functions.

Syntax

Following is the syntax to show the current user −

SELECT user();
or
SELECT current_user();

Example

Using the following query, let us display the username of the currently logged in user in MySQL database using the user() function −

SELECT user();

Output

Following is the output obtained −

Example

In here, we are using the current_user() function to show the current user −

SELECT current_user();

Output

The output obtained is as follows −

Show Currently Logged in Users

The difference between current users and currently logged in users is that, current user is the user that is executing the queries; whereas, currently logged in user list includes all the active users that are connected to the MySQL server at the moment.

This information can be extracted from the “information_schema.processlist” table using the SELECT statement.

Example

In the following query, we are retrieving the information of all the currently logged in users −

DESC information_schema.processlist;

Output

Following is the output of the above code −

Example

In here, we are retrieving information of current users, host, database, and command from the information_schema −

SELECT user, host, db, command
FROM information_schema.processlist;

Output

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

Show Users Using a Client Program

We can also display information about the MySQL users using a client program.

Syntax

Following are the syntaxes to display information regarding MySQL users in various programming languages −

$sql = "SELECT USER FROM MYSQL.user";
$mysqli->query($sql);

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

String sql = "SELECT USER FROM MYSQL.USER";
statement.executeQuery(sql);

sql = "SELECT user, host FROM mysql.user";
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 = "SELECT USER FROM MYSQL.user";
if($result = $mysqli->query($sql)){
   printf("User found successfully...!");
   printf("Users list are: ");
   while($row = mysqli_fetch_array($result)){
       print_r($row);
   }
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

User found successfully...!Users list are: Array
(
    [0] => Vivek Verma
    [USER] => Vivek Verma
)
Array
(
    [0] => Revathi
    [USER] => Revathi
)
Array
(
    [0] => Sarika
    [USER] => Sarika
)
Array
(
    [0] => mysql.infoschema
    [USER] => mysql.infoschema
)
Array
(
    [0] => mysql.session
    [USER] => mysql.session
)
Array
(
    [0] => mysql.sys
    [USER] => mysql.sys
)
Array
(
    [0] => root
    [USER] => root
)

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

  //Creating Users
  sql = "CREATE USER TestUser1@localhost IDENTIFIED BY ''password1"
  con.query(sql);
  sql = "CREATE USER TestUser2@localhost IDENTIFIED BY ''password2"
  con.query(sql);
  sql = "CREATE USER TestUser3@localhost IDENTIFIED BY ''password3"
  con.query(sql);
  sql = "CREATE USER TestUser4@localhost IDENTIFIED BY ''password4"
  con.query(sql);

  //Listing the users
  sql = "SELECT USER FROM mysql.user;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**List of Users:**")
    console.log(result)
  });
});

Connected!
--------------------------
**List of Users:**
[
  { USER: ''TestUser1'' },
  { USER: ''TestUser2'' },
  { USER: ''TestUser3'' },
  { USER: ''TestUser4'' },
  { USER: ''mysql.infoschema'' },
  { USER: ''mysql.session'' },
  { USER: ''mysql.sys'' },
  { USER: ''root'' },
  { USER: ''sample'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ShowUsers {
	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 = "CREATE USER ''Vivek''@''localhost'' IDENTIFIED WITH mysql_native_password BY ''password''";
            st.execute(sql);
            System.out.println("User ''Vivek'' created successfully...!");
            String sql1 = "SELECT USER FROM MYSQL.user";
            rs = st.executeQuery(sql1);
            System.out.println("Users: ");
            while(rs.next()) {
            	String users = rs.getNString(1);
            	System.out.println(users);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

User ''Vivek'' created successfully...!
Users:
Sarika
Vivek Verma
Revathi
Sarika
Vivek
mysql.infoschema
mysql.session
mysql.sys
root

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
)
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute("SELECT user, host FROM mysql.user")
users = cursorObj.fetchall()
print("Existing users are:")
for user, host in users:
    print(f"User: {user}, Host: {host}")
cursorObj.close()
connection.close()

Existing users are:
User: UserNew, Host: localhost
User: mysql.infoschema, Host: localhost
User: mysql.session, Host: localhost
User: mysql.sys, Host: localhost
User: newUser, Host: localhost
User: root, Host: localhost

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

The MySQL Drop User Statement

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

Syntax

Following is the syntax of the DROP USER statement −

DROP USER [IF EXISTS] ''username''@''hostname

Where, user_name is the name of the MySQL user you need to delete.

Example

Suppose, we have created a MySQL user account named ”TestUser” as shown below −

CREATE USER TestUser@localhost IDENTIFIED BY ''password1

Following is the output obtained −

Query OK, 0 rows affected (0.04 sec)

You can verify the list of users using the following query −

SELECT user FROM MySQl.user;

The table will be displayed as shown below −

Now, let us delete the ”TestUser” account created above using the DROP USER statement as shown below −

DROP USER TestUser@localhost;

After executing the above code, we can see the output as shown below −

Query OK, 0 rows affected (0.02 sec)

Verification

Once a table is dropped, if you verify the list of the users as shown below using the SELECT statement, you will find that its name is missing from the list −

SELECT user FROM MySQl.user;

The table obtained is as follows −

Removing Multiple Users

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

Example

Let us start by creating two roles ”MyAdmin” and ”MyDeveloper” −

CREATE ROLE ''MyAdmin'', ''MyDeveloper

The output obtained is as follows −

Query OK, 0 rows affected (0.01 sec)

Now, let us remove these roles using the DROP ROLE statement −

DROP ROLE ''MyAdmin'', ''MyDeveloper

This query will effectively delete both roles from the database −

Query OK, 0 rows affected (0.01 sec)

The IF EXISTS clause

If you try to drop a MySQL user that doesn”t exist, an error will be generated. To address this issue, MySQL provides the IF EXISTS clause, which can be used with the DROP USER statement.

Hence, the IF EXISTS clause allows you to drop a user if they exist, and it handles situations where the specified user is not found in the database.

Example

In the below query, we are attempting to drop the ”demo” user. However, it results in an error because the user doesn”t exist in the database −

DROP USER demo@localhost;

The output produced is as shown below −

ERROR 1396 (HY000): Operation DROP USER failed for ''demo''@''localhost''

If you use the IF EXISTS clause along with the DROP USER statement as shown below, the specified user will be dropped and if a user with the given name doesn”t exist, the query will be ignored −

DROP USER IF EXISTS demo;

The output obtained is as follows −

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

Dropping User Using a Client Program

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

Syntax

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

$sql = "DROP USER ''username''@''localhost''";
$mysqli->query($sql);

sql= "DROP USER [IF EXISTS] user_name ...";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

String sql = "DROP USER "USER_NAME''@''LOCALHOST''";
statement.execute(sql);

sql = "DROP USER ''UserName''@''localhost''";
cursorObj.execute(sql);

Example

Following are the client programs to drop an user in MySQL −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf(''Connected successfully.
'');
$sql = "DROP USER ''Revathi''@''localhost''";
if($mysqli->query($sql)){
   printf("User dropped successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

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

  //Deleting Users
  sql = "DROP USER TestUser1@localhost;"
  con.query(sql);
  sql = "DROP USER TestUser2@localhost;"
  con.query(sql);

  //Listing the users after deleting
  sql = "select user from MySQl.user;"
  con.query(sql, function(err, result){
    console.log("**List of Users after deleting:**")
    if (err) throw err
    console.log(result)
  })
});

Connected!
--------------------------
*List of Users after deleting:**
[
  { user: ''TestUser3'' },
  { user: ''TestUser4'' },
  { user: ''mysql.infoschema'' },
  { user: ''mysql.session'' },
  { user: ''mysql.sys'' },
  { user: ''root'' },
  { user: ''sample'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class DropUsers {
	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 = "DROP USER ''Vivek''@''localhost''";
            st.execute(sql);
            System.out.println("User ''Vivek'' dropped successfully...!");
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

User ''Vivek'' created successfully...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password''
)
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute("DROP USER ''UserNew''@''localhost''")
print("User ''UserNew'' is dropped successfully.")
cursorObj.close()
connection.close()

User ''UserNew'' is dropped successfully.

In MySQL, you can create multiple user accounts to access the database, each with specific authentication detail such as password. These users can be granted specific privileges using SQL statements like CREATE USER for authentication when creating a new user, and GRANT and REVOKE for assigning and removing administrative privileges, respectively.

The MySQL CREATE USERS Statement

We can create a new user account using the CREATE USER Statement in MySQL. To execute this statement, the current account must have the CREATE USER privilege or the INSERT privilege for the MySQL system schema.

Syntax

Following is the syntax of the MySQL CREATE USER statement −

CREATE USER ''user_name''@''host_name'' IDENTIFIED BY ''password

Where,

• user_name is the name of the user you need to create.

• hostname specifies the host from which the user can connect.

• password is the user”s password.

Example

In the following query, we are creating a user named ”sample” who can only connect from the ”localhost” host and sets their password as ”123456”. Make sure that you have logged in with a user with admin privileges (root) −

CREATE USER ''sample''@''localhost'' IDENTIFIED BY ''123456

Output

The output will be displayed as −

Query OK, 0 rows affected (0.12 sec)

Verification

You can verify the list of users using the following query −

SELECT USER FROM MySQL.USER;

The table will be displayed as shown below −

Granting Privileges in MySQL

You can grant all privileges to the created user using the GRANT ALL statement. This allows you to give specific permissions to users for actions like accessing databases, tables, and performing operations, such as SELECT, INSERT, or DELETE, on them.

Syntax

Following is the syntax to grant all privileges in MySQL −

GRANT ALL PRIVILEGES ON database_name.* TO ''username''@''host

Example

The following query grants the user ”sample” full privileges to perform any action on any database or table when connecting from the ”localhost” host, giving complete control over the MySQL server locally −

GRANT ALL PRIVILEGES ON * . * TO ''sample''@''localhost

Output

The output will be displayed as −

Query OK, 0 rows affected (0.02 sec)

Logging as a Different User

To log in as a different user in MySQL, you should first exit the current MySQL session if you are already logged in and then execute the command -u user_name -p in your system”s command prompt or terminal, not within the MySQL shell itself.

Example

Here, we are executing the -u sample -p command. After running the command, you will be prompted to enter the password for the specified user. Enter the correct password to log in as shown below −

mysql -u sample -p
Enter password: ******

Output

This will log you in as the sample user with the appropriate privileges and permissions as shown below −

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 12
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ''help;'' or ''h'' for help. Type ''c'' to clear the current input statement.

The Expire Clause

If you use the expire clause, the old password (current password) will expire immediately and the user need to choose new password at first connection.

Example

Here, we are first removing the existing user ”sample”@”localhost” −

DROP user sample@localhost;

We are now creating a new user ”sample”@”localhost” with the password ”MyPassword” while immediately expiring the password, forcing the user to set a new password upon the first login −

CREATE USER ''sample''@''localhost''
IDENTIFIED BY ''MyPassword'' PASSWORD EXPIRE;

Now, if you log in as a newly created user, an error will be generated. So, to login as newly created user, open command prompt browse through bin folder of the MySQL directory and execute the following command −

C:Program FilesMySQLMySQL Server 8.0bin> mysql -u sample@localhost -p
Enter password: **********

Any MySQL command execution at this point will trigger an error message as shown below −

select now();

The output obtained is as shown below −

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Since the password is expired, the above error message is generated. To make this right we need to change (reset) the password using the following command −

SET PASSWORD=''passwordtest

Following is the output produced −

Query OK, 0 rows affected (0.34 sec)

You can also set an interval for the EXPIRE clause to implement periodic password changes as shown below −

DROP user sample@localhost;
CREATE USER ''sample''@''localhost''
   IDENTIFIED BY ''MyPassword''
   PASSWORD EXPIRE INTERVAL 25 DAY
   FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

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

Query OK, 0 rows affected (0.20 sec)

User Comment

You can add comments to the user while creating a user in MySQL using the COMMENT clause. This provides additional information or context about the user.

Example

In the following example, we are first removing the existing ”sample”@”localhost” user. Then, we are creating a new ”sample”@”localhost” user while adding a comment to describe the user −

drop user sample@localhost;
CREATE USER ''sample''@''localhost'' COMMENT ''Sample information

Output

The result obtained is as shown below −

Query OK, 0 rows affected (0.10 sec)

Verification

You can verify the attributes and comments info using the SELECT query given below −

SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
WHERE USER=''sample'' AND HOST=''localhost

The result produced is as shown below −

User Attribute

You can add attributes to a user in MySQL using the ATTRIBUTE clause when creating a user account. These attributes can store additional information about the user.

Example

In here, we are first removing the existing ”sample@localhost” user. Then, we are creating a new ”sample”@”localhost” user with attributes ”attr1” and ”attr2” set to ”val1” and ”val2,” respectively, associated with the user account −

DROP user sample@localhost;
CREATE USER ''sample''@''localhost''
ATTRIBUTE ''{"attr1": "val1", "attr2": "val2"}

The result obtained is as shown below −

Output

Query OK, 0 rows affected (0.09 sec)

Verification

You can verify the attributes and comments info using the SELECT query given below −

SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
WHERE USER=''sample'' AND HOST=''localhost

The result obtained is as shown below −

The IF NOT EXISTS Clause

If you try to create a user with an existing name, an error will be generated. To prevent this error and ensure the user is created only if it does not already exist, you can use the “IF NOT EXISTS” clause.

Example

In the example below we are creating a user ”sample@localhost” without the “IF NOT EXISTS” clause −

CREATE USER ''sample@localhost

We can see in the below output that an error is generated −

ERROR 1396 (HY000): Operation CREATE USER failed for ''sample@localhost''@''%''

However, if we use the “IF NOT EXISTS” clause along with the CREATE statement, a new user will be created, and if a user with the given name already exists, the query will be ignored −

CREATE USER IF NOT EXISTS ''sample@localhost

Following is the output obtained −

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

Creating User Using a Client Program

In addition to creating a user into MySQL Database using the MySQL query, we can also create using a client program.

Syntax

Following are the syntaxes to create a MySQL user in various programming languages −

$sql = "CREATE USER ''user_name''@''localhost'' IDENTIFIED WITH mysql_native_password BY ''password''";
$mysqli->query($sql);

sql= " CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY ''password
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

sql = "CREATE USER ''USER_NAME''@LOCALHOST INDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY ''PASSWORD''";
statement.execute(sql);

sql = "CREATE USER ''UserNew''@''localhost'' IDENTIFIED BY ''newPassword''";
cursorObj.execute(sql)

Example

Following are the client programs to create an user in MySQL −

$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 ''Revathi''@''localhost'' IDENTIFIED WITH mysql_native_password BY ''password''";
if($mysqli->query($sql)){
   printf("User created successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

User created 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("--------------------------");

   //Creating User
   sql = "CREATE USER ''sample''@''localhost'' IDENTIFIED BY ''123456"
   con.query(sql);

   //List of users
   sql = "select user from MySQl.user;"
   con.query(sql, function(err, result){
      if (err) throw err
      console.log(result)
   });
});

Connected!
--------------------------
[
  { user: ''mysql.infoschema'' },
  { user: ''mysql.session'' },
  { user: ''mysql.sys'' },
  { user: ''root'' },
  { user: ''sample'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class CreateUsers {
   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 = "CREATE USER ''Vivek''@''localhost'' IDENTIFIED WITH mysql_native_password BY ''password''";
         st.execute(sql);
         System.out.println("User ''Vivek'' created successfully...!");
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

User ''Vivek'' created successfully...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
   host=''localhost'',
   user=''root'',
   password=''password'',
   database=''textx''
)
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute("CREATE USER ''UserNew''@''localhost'' IDENTIFIED BY ''newPassword''")
print("User ''newUser'' is created successfully.")
cursorObj.close()
connection.close()

User ''newUser'' is created successfully.

MySQL usually stores two types of data in it: actual data stored in the form of tables and views, and information about the structure of a database and its objects. Such information is known as Metadata.

For instance, whenever a user forgets certain information of a database or its objects, MySQL provides specific commands to retrieve the said information. There are actually three types of information, which you can retrieve from a MySQL database. They are as follows −

• Information about the result of queries − This includes the number of records affected by any SELECT, UPDATE or DELETE statement.

• Information about the tables and databases − This includes information related to the structure of the tables and the databases.

• Information about the MySQL server − This includes the status of the database server, version number, etc.

It is very easy to get all this information at the MySQL prompt, but while using PERL or PHP APIs, we need to call various APIs explicitly to obtain all this information.

Obtaining Database Info from MySQL Prompt

While accessing a MySQL server from MySQL prompt, which is a Command Prompt in Windows and a Terminal in Linux etc., any information regarding a database using following commands.

• SHOW DATABASES: This command is used to retrieve the list of all databases present in MySQL.

• SHOW TABLES: This command is used to display the list of tables present in a database.

• mysql -V: This command is used to provide the current version of MySQL installed in your system.

• DESC or DESCRIBE: This command is used to retrieve the structure or definition of a database table.

mysql -V Command

If you want to check the version of MySQL Server installed in your system, use the following mysql -V on Command Prompt or Terminal.

Note: You must remember that the command prompt must be run as an administrator in Windows.

Example

In here, we are checking the current version of MySQL server in command prompt −

C:WindowsSystem32> mysql -V

Output

As we can see the output below, the current MySQL server version is ”8.0.33” −

mysql  Ver 8.0.33 for Win64 on x86_64 (MySQL Community Server - GPL)

SHOW DATABASES Command

To list or retrieve the names of all the databases in MySQL, you can use the following SHOW DATABASES command after logging into MySQL server −

Note − This command will list both system databases and user-defined databases together. The user must identify their specific user-defined database all the data is stored in.

Example

In the following query, we are fetching list of databases that exists in the current MySQL server −

SHOW DATABASES;

Here, the first three rows are system databases and the last two rows are user-defined databases −

SHOW TABLES Command

To list all the tables in a MySQL database, you can use the SHOW TABLES command after selecting a database with USE command −

Example

In the below query, we are selecting a database named ”Tutorials” −

USE Tutorials;

Now, let us use the SHOW TABLES to fetch all the names of tables present in the above database −

Show Tables;

Output

Following is the list of tables exist in the ”Tutorials” database −

DESC Command

If we want to check the structure of a MySQL table, we need to use the DESC or DESCRIBE query. DESC is a shortcut for DESCRIBE query, but retrieves the same result.

Example

Here, we are fetching the structure of MySQL CUSTOMERS table −

DESC CUSTOMERS;

Following is the structure −

Obtaining the Number of Rows Affected by a Query

Let is now see how to obtain this information.

PERL Example

In DBI scripts, the affected row count is returned by the do( ) or by the execute( ) command, depending on how you execute the query.

# Method 1
# execute $query using do( )
my $count = $dbh->do ($query);
# report 0 rows if an error occurred
printf "%d rows were affectedn", (defined ($count) ? $count : 0);

# Method 2
# execute query using prepare( ) plus execute( )
my $sth = $dbh->prepare ($query);
my $count = $sth->execute ( );
printf "%d rows were affectedn", (defined ($count) ? $count : 0);

PHP Example

In PHP, invoke the mysql_affected_rows( ) function to find out how many rows a query changed.

$result_id = mysql_query ($query, $conn_id);
# report 0 rows if the query failed
$count = ($result_id ? mysql_affected_rows ($conn_id) : 0);
print ("$count rows were affectedn");

Listing Tables and Databases

It is very easy to list down all the databases and the tables available with a database server. Your result may be null if you don”t have the sufficient privileges.

Apart from the method which is shown in the following code block, you can use SHOW TABLES or SHOW DATABASES queries to get the list of tables or databases either in PHP or in PERL.

PERL Example

# Get all the tables available in current database.
my @tables = $dbh->tables ( );

foreach $table (@tables ){
   print "Table Name $tablen";
}

PHP Example

Try the following example to get database info −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Getting MySQL Database Info</title>
   </head>
   <body>
      <?php
         $dbhost = ''localhost
         $dbuser = ''root
         $dbpass = ''root@123
         $dbname = ''TUTORIALS
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         $tutorial_count = null;

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

         if ($result = mysqli_query($mysqli, "SELECT DATABASE()")) {
            $row = mysqli_fetch_row($result);
            printf("Default database is %s<br />", $row[0]);
            mysqli_free_result($result);
         }
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.
Default database is tutorials

Getting Server Metadata

There are a few important commands in MySQL which can be executed either at the MySQL prompt or by using any script like PHP to get various important information about the database server.

In modern times, companies rely on databases to store crucial information like financial transactions, customer profiles, and employee records. It is very important to maintain regular copies of databases there can always be a chance of data loss from power surges and disk crashes. Therefore, regular backups of databases are crucial for effective data management.

Copy Database in MySQL

In MySQL, copying a database involves creating an exact duplicate of an existing database, including its schema and data. This is almost similar to having a backup of a database. It is important to ensure that any changes made to the original database after the copy is made are also reflected in the copied database, if necessary.

To create a copy of a database SQL Server provides the Copy Database statement. But, this is not available in MySQL. Therefore, to create copy of a database we need to dump the contents of one database to other manually.

The following are three steps that involve in copying a database −

• First of all, we need to create a new database.

• Then, we need to export the original database using mysqldump.

• Finally, importing the exported data into the new database.

Example

First of all, let us create a database in the MySQL server using the following query −

CREATE DATABASE testdb;

We can verify whether the database testdb is created or not using the SHOW DATABASES statement.

SHOW DATABASES;

As we can see the output below, the testdb database has been created successfully.

Once the database is created successfully, we need to change the current database to ”testdb”, using the USE statement so that any operations we perform such as creating a table will be stored in this database.

USE testdb;

Now, let us create a table named CUSTOMERS using the CREATE query as follows −

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

The following query inserts 7 records into the above-created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Using the following query, we can verify whether the table CUSTOMERS is created in ”testdb” database or not −

SHOW TABLES;

The table is successfully created in the testdb database.

Creating Copy database (Manually)

As discussed earlier in MySQL to create a copy of an existing database we need to create a separate database and dump the contents of it to the newly created database manually.

Following statement creates a database named testdb_copy (to which we need to copy the contents of the testdb database created above).

CREATE DATABASE testdb_copy;

Once both our source (testdb) and destination (testdb_copy) databases are ready, we need to follow the steps given below to manually copy the data from one to another.

Step1 − Open the command prompt, browse through the bin folder of the MySQL server. For instance, we have installed MySQL in the C Program Files directory so, the following command will take us to the bin folder −

C:> CD C:Program FilesMySQLMySQL Server 8.0bin

Step 2 − Using the mysqldump tool, we can copy the database objects and data into a .sql file. Here, we are exporting the contents of the testdb database to a file named “testdb.sql” located at “D:Database_backup”.

Note − The (>) operator is used for exporting the database from one location to another.

mysqldump -u root -p testdb > D:database_backuptestdb.sql

Step 3 − Import the contents of the “testdb.sql” file into the destination database (in our case “testdb_copy”).

Note − The (<) operator is used for importing the database from one location to another.

mysql -u root -p testdb_copy
Verification
To verify whether the data and database object is imported into the testdb_copy database, first, we need to use the current database using the following query in the MySQL Command Line Client −
USE testdb_copy;





If the contents of the testdb are copied in to the testdb_copy successfully, we should be able to find the customers table in the list of tables (which is created earlier).




Therefore, let us verify whether the data from the "testdb" database have been copied to the "testdb_copy" database or not using the following query −




SHOW TABLES;





As we can see in the list below, all the database objects and data have been successfully copied.











Tables_in_testdb










customers










Copy Database Without MySQLdump




If we want to copy a database without using the mysqldump tool, we must manually create each table in the destination database and copy all the data from the tables present in the current database. This is a repitetive process that should be done for each table that needs to be copied.




Example




Let us create a new database in the MySQL server using the following query −




CREATE DATABASE Tutorials;





We can verify whether the database Tutorials is created or not using the following query −




SHOW DATABASES;





As we can see the output below, the ”Tutorials” database has been created successfully.










Database










information_schema










mysql










performance_schema










tutorials










Now, we are switching the current database to Tutorials, so that any operations we perform such as creating a table will be stored in this database.




USE Tutorials;





Once we have switched, create a table named CUSTOMERS using the following query −




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





Here, we are inserting some records into the table using the INSERT INTO statement below −




INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );





Using the below query, let us create another database Tutorials_copy, where we want to copy all the data objects and data of the Tutorials database.




CREATE DATABASE Tutorials_copy;





We can verify whether the database Tutorials_copy is created or not, using the following query −




SHOW DATABASES;





The database has been created.










Database










information_schema










mysql










performance_schema










tutorials










tutorials_copy










Now, switch the current database to ”tutorials_copy” using the following query −




USE Tutorials_copy;





Here, we are creating an empty table named “CUSTOMERS” in the ”Tutorials_copy” database with the same schema as the original ”CUSTOMERS” table in the ”Tutorials” −




CREATE TABLE Tutorials_copy.customers LIKE Tutorials.customers;





This query inserts all the data from the original ”customers” table in the ”Tutorials” database into the new ”customers” table in the ”tutorials_copy” database.




INSERT Tutorials_copy.customers SELECT * FROM Tutorials.customers;





We can verify whether the database objects and data from the ”Tutorials” database have been copied to the ”Tutorials_copy” database or not.




SHOW TABLES;





Output




As we can see in the list below, the table has been successfully copied −










Tables_in_tutorials_copy










customers










Let us also retrieve the records of CUSTOMERS table in ”Tutorials_copy” database to verify whether the records have been copied or not −




Select * from CUSTOMERS;





As we can see the CUSTOMERS table in ”Tutorials_copy” database below, the records have been successfully copied −










ID




NAME




AGE




ADDRESS




SALARY










1




Ramesh




32




Ahmedabad




2000.00










2




Khilan




25




Delhi




1500.00










3




Kaushik




23




Kota




2000.00










4




Chaitali




25




Mumbai




6500.00










5




Hardik




27




Bhopal




8500.00










6




Komal




22




Hyderabad




4500.00










7




Muffy




24




Indore




10000.00










		


	


 

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương:   Khóa học lập trình nhận lương tại trung tâm Toidayhoc




			
August 17, 2024
		

MySQL is one of the most popular relational database systems used to store and manage data. It structures data in the form of tables and views so that data handling becomes easier making organizations prefer using MySQL to manage their company”s confidential data. Since their data is highly confidential, it becomes necessary to back up the database and restore it whenever necessary. Hence we perform database export.

Exporting a database in MySQL is commonly used for backup purposes or transferring data between servers. You can export entire database or just a portion of it. The simplest way of exporting a database is by using the mysqldump command-line tool.

Exporting Database using mysqldump

The mysqldump command-line tool is used in MySQL to create backups of databases. It can be used to back up an entire database, specific tables, or even specific rows based of a table.

Following is the syntax of mysqldump command to export a database −

$ mysqldump -u username -p database_name > output_file_path

Where,

• username: It is the MySQL username to use when connecting to the database.

• database_name: It is the name of the database to be exported.

• output_file_path: It is the path of the backup file. This is where the backup data will be stored.

• >: This symbol exports the output of the mysqldump command into a file named output_file_path.

Example

First of all, create a database named TUTORIALS using the following query −

Create database TUTORIALS;

Execute the below query to select the current database as TUTORIALS −

USE TUTORIALS;

Let us also create a table named CUSTOMERS in the above-created database −

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

The following query inserts 7 records into the CUSTOMERS table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Here, we are using the mysqldump command-line statement to export the TUTORIALS database to the ”datadump.sql” file. Once we execute the below statement, we need to enter our MySQL server password.

$ mysqldump -u root -p TUTORIALS > data-dump.sql

The above command will not produce any visual output. Instead, the ”data-dump.sql” file will be saved in the current working directory of the command prompt or terminal where you executed the command.

Exporting only Specific Tables in Database

We can also export only specific tables in a database using the mysqldump command-line tool. To do so, we use the following syntax −

mysqldump -u username -p database_name table1 table2 ... > output_file.sql

Example

Before exporting, let us create two new tables (STUDENTS and EMPLOYEES) in the above-created TUTORIALS database using the following query −

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

Here, we are creating the EMPLOYEES table −

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

Let us fetch the list of tables in the TUTORIALS database using the below query −

Show Tables;

As we can in the output below, we have three tables present in TUTORIALS database −

Now, let us export two tables named EMPLOYEES and STUDENTS into the ”datadump2.sql” as follows −

$ mysqldump -u root -p TUTORIALS employees students > output_file.sql

Once we executed the above query, it wont display any ouptut. Instead, it exports the data of both the tables into the specified file.

Exporting all Databases in a Host

For instance, you have multiple databases in your host and you want to export all of them in a single query. In such scenario, we can use the “–all-databases” option of mysqldump command.

Example

Following is the query to export all the databases in a host using the –all-databases option −

$ mysqldump -u root -p --all-databases > database_dump.sql

The above command won”t show any visible output on the screen. Instead, the ”database_dump.sql” file will be saved in the current working directory of the command prompt or terminal where you ran the command.”