MySQL - Grant Privileges

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.