MySQL - Grant Privileges

MySQL - Granting Privileges and Roles

In any production database system, managing user access is a critical security function. The GRANT statement is MySQL’s primary tool for assigning specific permissions (privileges) and collections of permissions (roles) to user accounts, ensuring that users and applications can only access the data and perform the actions they are explicitly authorized for.

The Principle of Least Privilege

The cornerstone of database security is the Principle of Least Privilege. This means a user account should only have the bare minimum privileges required to perform its intended function. For example, a web application that only reads product data should only have SELECT privileges on the products table, not UPDATE, DELETE, or any administrative privileges. This minimizes the potential damage from application bugs, SQL injection attacks, or compromised credentials.

The Modern GRANT Statement Workflow

Modern MySQL security practice dictates a two-step process for setting up a new user:

1. 1. Create the User: Use the CREATE USER statement to define the user account and its authentication method.
2. 2. Grant Privileges: Use the GRANT statement to assign the necessary permissions to the newly created user.

Important: The old syntax GRANT ... TO 'user'@'host' IDENTIFIED BY 'password' is deprecated and has been removed in modern MySQL versions (8.0+). Always create users explicitly with CREATE USER.

Syntax

-- Step 1: Create the user
CREATE USER 'user_name'@'host_name' IDENTIFIED BY 'strong_password';

-- Step 2: Grant privileges
GRANT privilege1, privilege2, ...
ON privilege_level
TO 'user_name'@'host_name';

-- Step 3 (Optional but recommended): Apply the changes
FLUSH PRIVILEGES;

Example: Creating a Read-Only Application User

Let’s create a user app_reader that can only SELECT from the Products table in the ecommerce_db database.

-- Prerequisite: a database and table exist
CREATE DATABASE IF NOT EXISTS ecommerce_db;
USE ecommerce_db;
CREATE TABLE IF NOT EXISTS Products (id INT, name VARCHAR(100));

-- Step 1: Create the user. 'localhost' means the user can only connect from the same server.
CREATE USER 'app_reader'@'localhost' IDENTIFIED BY 'a_very_secure_password_123!';

-- Step 2: Grant the specific privilege
GRANT SELECT ON ecommerce_db.Products TO 'app_reader'@'localhost';

-- Step 3: Flush privileges to ensure the changes are active immediately
FLUSH PRIVILEGES;

Verification with SHOW GRANTS

You can verify the privileges assigned to any user with the SHOW GRANTS statement.

SHOW GRANTS FOR 'app_reader'@'localhost';

The output will show the exact permissions the user has:

Grants for app_reader@localhost
GRANT USAGE ON . TO app_reader@localhost
GRANT SELECT ON ecommerce_db.products TO app_reader@localhost

The USAGE privilege is a default, minimal privilege that allows a user to connect to the database but nothing else.

Privilege Levels: Global, Database, Table, and Column

Privileges can be granted at different scopes:

Level	Syntax (ON clause)	Description
Global	ON *.*	Applies to all databases on the server. Use with extreme caution. Typically for administrative accounts only.
Database	ON database_name.*	Applies to all objects (tables, views, etc.) within a specific database.
Table	ON database_name.table_name	Applies to a single table. This is the most common level for application users.
Column	ON database_name.table_name with (col1, col2)	Applies only to specific columns within a table. Useful for highly sensitive data.

Example: Database and Column-Level Privileges

-- Grant a user all permissions on a specific database
GRANT ALL PRIVILEGES ON ecommerce_db.* TO 'db_admin'@'localhost';

-- Grant a user SELECT on one column and UPDATE on another
GRANT SELECT(product_name, price), UPDATE(stock_level)
ON ecommerce_db.Products
TO 'inventory_clerk'@'localhost';

Managing Permissions with Roles

Roles are named collections of privileges. They are the modern, recommended way to manage permissions for groups of users. Instead of granting the same 10 privileges to 20 different developer accounts, you create one developer role, grant the privileges to the role, and then grant the role to each developer.

Role Management Workflow

-- 1. Create a role
CREATE ROLE 'read_only_analyst';

-- 2. Grant privileges to the role
GRANT SELECT ON ecommerce_db.* TO 'read_only_analyst';

-- 3. Create a user
CREATE USER 'susan'@'localhost' IDENTIFIED BY 'password123';

-- 4. Grant the role to the user
GRANT 'read_only_analyst' TO 'susan'@'localhost';

-- 5. Set the role as a default active role for the user upon login
SET DEFAULT ROLE 'read_only_analyst' TO 'susan'@'localhost';

Special Privileges: GRANT OPTION and PROXY

• WITH GRANT OPTION: Appending this to a GRANT statement allows the recipient user to grant those same privileges to other users. This is a powerful and potentially dangerous option that should be given out sparingly.
• PROXY: GRANT PROXY ON 'real_user' TO 'proxy_user'; allows proxy_user to impersonate real_user. When proxy_user connects as real_user, they will have the privileges of real_user. This is used in advanced authentication scenarios.

Granting Privileges Using Modern Client Applications

Executing GRANT statements programmatically is common in automated setup scripts. Always ensure the code is run by a user with sufficient permissions (like root or an administrative user).

Node.js (mysql2)
Python (mysql-connector-python)
Java (JDBC)
PHP (mysqli)


This Node.js script demonstrates the full workflow of creating a user and granting privileges.

```javascript
// Requires: npm install mysql2
const mysql = require('mysql2/promise');

async function setupAppUser() {
  let connection;
  try {
    // Connect with a user that has permission to create users and grant privileges
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'your_root_password'
    });

    const newUser = 'webapp_user';
    const host = 'localhost';
    const password = 'super_secret_app_password';
    const db = 'ecommerce_db';
    const table = 'Orders';

    // Use parameterized queries where possible, but user/db names often cannot be parameters.
    // Sanitize these inputs if they come from an external source.
    await connection.execute(`CREATE USER IF NOT EXISTS '${newUser}'@'${host}' IDENTIFIED BY '${password}'`);
    console.log(`User '${newUser}'@'${host}' created or already exists.`);

    await connection.execute(`GRANT SELECT, INSERT ON ${db}.${table} TO '${newUser}'@'${host}'`);
    console.log(`Privileges granted to user.`);

    await connection.execute(`FLUSH PRIVILEGES`);
    console.log("Privileges flushed.");

  } catch (error) {
    console.error(`An error occurred: ${error.message}`);
  } finally {
    if (connection) await connection.end();
  }
}

setupAppUser();

This Python script performs the same user setup task.

# Requires: pip install mysql-connector-python
import mysql.connector
from mysql.connector import errorcode

def setup_app_user():
    try:
        # Connect with an administrative user
        with mysql.connector.connect(user='root', password='your_root_password', host='localhost') as connection:
            with connection.cursor() as cursor:
                new_user = 'webapp_user'
                host = 'localhost'
                password = 'super_secret_app_password'
                db = 'ecommerce_db'
                table = 'Orders'

                try:
                    cursor.execute(f"CREATE USER '{new_user}'@'{host}' IDENTIFIED BY '{password}'")
                    print(f"User '{new_user}'@'{host}' created.")
                except mysql.connector.Error as err:
                    if err.errno == 1396: # Operation CREATE USER failed for...
                        print(f"User '{new_user}'@'{host}' already exists.")
                    else:
                        raise

                cursor.execute(f"GRANT SELECT, INSERT ON {db}.{table} TO '{new_user}'@'{host}'")
                print(f"Privileges granted.")

                cursor.execute("FLUSH PRIVILEGES")
                print("Privileges flushed.")

    except mysql.connector.Error as err:
        print(f"Database error: {err}")

setup_app_user()

Using Java’s JDBC to execute multiple DDL/DCL statements.

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

public class GrantPrivilegesExample {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/";
    private static final String ADMIN_USER = "root";
    private static final String ADMIN_PASS = "your_root_password";

    public static void main(String[] args) {
        String newUser = "webapp_user";
        String host = "localhost";
        String password = "super_secret_app_password";
        String db = "ecommerce_db";
        String table = "Orders";

        try (Connection conn = DriverManager.getConnection(DB_URL, ADMIN_USER, ADMIN_PASS);
             Statement stmt = conn.createStatement()) {

            System.out.println("Connection successful. Setting up user...");
            // Note: In production, handle user existence gracefully.
            stmt.executeUpdate("CREATE USER '" + newUser + "'@'" + host + "' IDENTIFIED BY '" + password + "'");
            System.out.println("User created.");

            stmt.executeUpdate("GRANT SELECT, INSERT ON " + db + "." + table + " TO '" + newUser + "'@'" + host + "'");
            System.out.println("Privileges granted.");

            stmt.executeUpdate("FLUSH PRIVILEGES");
            System.out.println("Privileges flushed.");

        } catch (SQLException e) {
            // Error 1396: User already exists
            if (e.getErrorCode() != 1396) {
                 e.printStackTrace();
            } else {
                 System.out.println("User already exists, skipping creation.");
            }
        }
    }
}

PHP script using mysqli to create a user and grant permissions.

<?php
// Connect with an administrative user
$mysqli = new mysqli('localhost', 'root', 'your_root_password');
if ($mysqli->connect_error) {
    die("Admin connection failed: " . $mysqli->connect_error);
}

$newUser = 'webapp_user';
$host = 'localhost';
$password = 'super_secret_app_password';
$db = 'ecommerce_db';
$table = 'Orders';

// Use multi_query for a sequence of operations
$sql = "CREATE USER IF NOT EXISTS '$newUser'@'$host' IDENTIFIED BY '$password';";
$sql .= "GRANT SELECT, INSERT ON `$db`.`$table` TO '$newUser'@'$host';";
$sql .= "FLUSH PRIVILEGES;";

if ($mysqli->multi_query($sql)) {
    do {
        // Store first result set
        if ($result = $mysqli->store_result()) {
            $result->free();
        }
    } while ($mysqli->more_results() && $mysqli->next_result());
    echo "User setup script executed successfully.";
} else {
    echo "Error during user setup: " . $mysqli->error;
}

$mysqli->close();
?>