MySQL - Grant Privileges

Table of content

The MySQL Grant Privileges
The MySQL GRANT Statement
Granting Privileges on Stored Routines
Privileges to Multiple Users
Global Privileges
Database Level Privileges
Column Level Privileges
Proxy User Privileges
Granting Roles
Granting Privileges Using a Client Program

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 −

Grants for test_user@localhost
GRANT USAGE ON . TO `test_user`@`localhost`
GRANT SELECT ON `test_database`.`mytable` TO `test_user`@`localhost`

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 −

Privileges	Description
ALTER	Allows users to modify table structures using the ALTER TABLE statement.
CREATE	Grants the ability to create new objects such as tables and databases.
DELETE	Enables users to delete rows from tables.
INSERT	Allows users to insert new records into tables.
SELECT	Provides read access to tables, allowing users to retrieve data.
UPDATE	Allows users to modify existing data in tables.
SHOW DATABASES	Grants the ability to see a list of available databases.
CREATE USER	Allows users to create new MySQL user accounts.
GRANT OPTION	Provides users with the authority to grant privileges to other users.
SUPER	Grants high-level administrative privileges.
SHUTDOWN	Allows users to shut down the MySQL server.
REPLICATION CLIENT	Provides access to replication-related information.
REPLICATION SLAVE	Enables users to act as a replication slave server.
FILE	Grants permission to read and write files on the server's file system.
CREATE VIEW	Allows users to create new database views.
CREATE TEMPORARY TABLES	Allows the creation of temporary tables.
EXECUTE	Enables users to execute stored procedures and functions.
TRIGGER	Provides the ability to create and manage triggers.
EVENT	Grants the ability to create and manage events.
SHOW VIEW	Allows users to see the definition of views.
INDEX	Enables users to create and drop indexes on tables.
PROXY	Provides the capability to proxy or impersonate other users.

Example

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

Output

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 −

PHP NodeJS Java Python

To grant all the privileges to an user in MySQL database using the PHP program, we need to execute the GRANT ALL statement as shown below −

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

Following is the syntax to grant a particular privilege to the desired user using a JavaScript program −

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

To grant the privilege in MySQL database, we need to execute the GRANT ALL PRIVILEGES statement using the JDBC execute() function as −

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

Following is the syntax to grant a particular privilege to the desired user using a Python program −

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

Example

Following are the programs −

PHP NodeJS Java Python

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

Output

The output obtained is as follows −

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

Output

The output produced is as follows −

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

Output

The output obtained is as shown below −

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

Output

Following is the output of the above code −

Privileges granted to user 'newUser' successfully.