MySQL - Show Privileges

MySQL: Managing User Privileges

In MySQL, a robust security model controls what users can do. Access is managed through a system of privileges. A user must be granted the appropriate privileges to perform actions like selecting data, creating tables, or administering the server.

Listing All Available Privileges with SHOW PRIVILEGES

The SHOW PRIVILEGES statement displays a comprehensive list of all system privileges that the MySQL server supports. This is useful for understanding the full range of permissions you can grant to users.

The output contains three columns:

• Privilege: The name of the privilege (e.g., SELECT, CREATE USER).
• Context: The level at which the privilege applies (e.g., Tables, Server Admin, Functions).
• Comment: A brief description of the privilege.

Syntax

SHOW PRIVILEGES;

Example

Executing this query will list all privileges supported by your MySQL server version.

SHOW PRIVILEGES;

Output (Abbreviated)

The output will be a long list of system privileges. Here is a small sample from a modern MySQL 8.x server:

Privilege	Context	Comment
Alter	Tables	To alter the table
Create user	Server Admin	To create new users
Delete	Tables	To delete existing rows
Drop	Databases, Tables, Views	To drop databases, tables, and views
Execute	Functions, Procedures	To execute stored routines
Insert	Tables	To insert data into tables
Select	Tables	To retrieve rows from table
Update	Tables	To update existing rows
REPLICATION_SLAVE_ADMIN	Server Admin	To perform replication administration

Checking a Specific User’s Grants with SHOW GRANTS

More commonly, you will want to see the specific privileges that have been granted to a particular user. For this, the SHOW GRANTS command is the correct tool.

Syntax

SHOW GRANTS FOR 'username'@'hostname';

Example

Let’s check the privileges for a user named app_user connecting from any host (%).

SHOW GRANTS FOR 'app_user'@'%';

Example Output

+-------------------------------------------------------------------------+
| Grants for app_user@%                                                   |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`%`                                     |
| GRANT SELECT, INSERT, UPDATE ON `my_app_db`.* TO `app_user`@`%`           |
+-------------------------------------------------------------------------+

This output clearly shows that app_user has basic connection rights (USAGE) and can perform SELECT, INSERT, and UPDATE operations on all tables within the my_app_db database.

Checking Privileges Programmatically

You can also execute SHOW GRANTS from your application code to verify permissions. Here are modern, secure examples in several languages.

Best Practice: Environment Variables

Never hard-code database credentials in your source code. Use environment variables (e.g., loaded from a .env file) to store sensitive information like usernames and passwords.

Python
Node.js
PHP
Java



This example uses the `mysql-connector-python` library and a `with` statement for automatic resource management.

**Setup:** `pip install mysql-connector-python python-dotenv`

```python
import mysql.connector
import os
from dotenv import load_dotenv

load_dotenv() # Load variables from .env file

# --- Configuration from environment variables ---
db_config = {
    'host': os.getenv('DB_HOST'),
    'user': os.getenv('DB_USER'),
    'password': os.getenv('DB_PASSWORD'),
}

user_to_check = 'app_user'
host_to_check = '%'

try:
    # Use a 'with' statement for automatic connection closing
    with mysql.connector.connect(**db_config) as connection:
        with connection.cursor() as cursor:
            query = "SHOW GRANTS FOR %s@%s"
            cursor.execute(query, (user_to_check, host_to_check))

            print(f"Privileges for {user_to_check}@{host_to_check}:")
            for grant in cursor:
                print(grant[0])

except mysql.connector.Error as e:
    print(f"Error connecting to MySQL: {e}")

This example uses the mysql2/promise library with async/await for modern asynchronous code.

Setup: npm install mysql2 dotenv

const mysql = require('mysql2/promise');
require('dotenv').config(); // Load variables from .env file

// --- Configuration from environment variables ---
const dbConfig = {
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
};

const userToCheck = 'app_user';
const hostToCheck = '%';

async function checkGrants() {
    let connection;
    try {
        connection = await mysql.createConnection(dbConfig);
        const query = 'SHOW GRANTS FOR ?@?';
        const [rows] = await connection.execute(query, [userToCheck, hostToCheck]);

        console.log(`Privileges for ${userToCheck}@${hostToCheck}:`);
        rows.forEach(row => {
            console.log(Object.values(row)[0]);
        });

    } catch (error) {
        console.error(`Error connecting to or querying MySQL: ${error.message}`);
    } finally {
        if (connection) {
            await connection.end();
        }
    }
}

checkGrants();

This example uses the modern mysqli extension with an object-oriented style and error handling.

Setup: Use Composer to manage dependencies.

<?php
// Use Composer's autoloader for libraries like a .env loader
// require 'vendor/autoload.php';
// $dotenv = Dotenv\Dotenv::createImmutable(__DIR__);
// $dotenv->load();

// --- Configuration (ideally from environment variables) ---
$dbHost = getenv('DB_HOST');
$dbUser = getenv('DB_USER');
$dbPassword = getenv('DB_PASSWORD');

$userToCheck = 'app_user';
$hostToCheck = '%';

// Enable exception reporting for mysqli
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $mysqli = new mysqli($dbHost, $dbUser, $dbPassword);

    // Use prepared statements even for SHOW GRANTS to be safe
    $stmt = $mysqli->prepare("SHOW GRANTS FOR ?@?");
    $stmt->bind_param("ss", $userToCheck, $hostToCheck);
    $stmt->execute();

    $result = $stmt->get_result();

    echo "Privileges for {$userToCheck}@{$hostToCheck}:\n";
    while ($row = $result->fetch_array()) {
        echo $row[0] . "\n";
    }

    $stmt->close();
    $mysqli->close();

} catch (mysqli_sql_exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}

?>

This example uses modern JDBC with a try-with-resources statement for automatic resource management and a PreparedStatement.

Setup: Use a build tool like Maven or Gradle to include the MySQL JDBC driver dependency.

import java.sql.*;

public class ShowGrantsExample {
    public static void main(String[] args) {
        // --- Configuration (ideally from a properties file or environment variables) ---
        String url = "jdbc:mysql://" + System.getenv("DB_HOST") + ":3306/";
        String user = System.getenv("DB_USER");
        String password = System.getenv("DB_PASSWORD");

        String userToCheck = "app_user";
        String hostToCheck = "%";
        String sql = "SHOW GRANTS FOR ?@?";

        // Use try-with-resources for automatic closing of Connection, PreparedStatement, and ResultSet
        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, userToCheck);
            pstmt.setString(2, hostToCheck);

            try (ResultSet rs = pstmt.executeQuery()) {
                System.out.printf("Privileges for %s@%s:%n", userToCheck, hostToCheck);
                while (rs.next()) {
                    System.out.println(rs.getString(1));
                }
            }
        } catch (SQLException e) {
            System.err.println("Database Error: " + e.getMessage());
        }
    }
}