MySQL - 授予权限

MySQL - 授予权限和角色

在任何生产数据库系统中，管理用户访问都是一项关键的安全功能。GRANT 语句是 MySQL 的主要工具，用于向用户账户分配特定权限（privileges）和权限集合（roles），确保用户和应用程序只能访问其明确授权的数据并执行操作。

最小权限原则

数据库安全的基石是最小权限原则。这意味着用户账户应仅拥有执行其预期功能所需的最低权限。例如，一个只读取产品数据的 Web 应用程序应只对 products 表拥有 SELECT 权限，而不是 UPDATE、DELETE 或任何管理权限。这最大限度地减少了应用程序错误、SQL 注入攻击或凭据泄露可能造成的损害。

现代 GRANT 语句工作流程

现代 MySQL 安全实践规定了设置新用户的两步流程：

1. 1. 创建用户： 使用 CREATE USER 语句定义用户账户及其身份验证方法。
2. 2. 授予权限： 使用 GRANT 语句为新创建的用户分配必要的权限。

重要提示： 旧语法 GRANT ... TO 'user'@'host' IDENTIFIED BY 'password' 已弃用，并在现代 MySQL 版本（8.0+）中已移除。请始终使用 CREATE USER 明确创建用户。

语法

-- 步骤 1：创建用户
CREATE USER 'user_name'@'host_name' IDENTIFIED BY 'strong_password';

-- 步骤 2：授予权限
GRANT privilege1, privilege2, ...
ON privilege_level
TO 'user_name'@'host_name';

-- 步骤 3（可选但推荐）：应用更改
FLUSH PRIVILEGES;

示例：创建只读应用程序用户

让我们创建一个用户 app_reader，它只能从 ecommerce_db 数据库的 Products 表中 SELECT（查询）数据。

-- 前提条件：数据库和表已存在
CREATE DATABASE IF NOT EXISTS ecommerce_db;
USE ecommerce_db;
CREATE TABLE IF NOT EXISTS Products (id INT, name VARCHAR(100));

-- 步骤 1：创建用户。'localhost' 意味着用户只能从同一服务器连接。
CREATE USER 'app_reader'@'localhost' IDENTIFIED BY 'a_very_secure_password_123!';

-- 步骤 2：授予特定权限
GRANT SELECT ON ecommerce_db.Products TO 'app_reader'@'localhost';

-- 步骤 3：刷新权限以确保更改立即生效
FLUSH PRIVILEGES;

使用 SHOW GRANTS 验证

您可以使用 SHOW GRANTS 语句验证分配给任何用户的权限。

SHOW GRANTS FOR 'app_reader'@'localhost';

输出将显示用户拥有的确切权限：

app_reader@localhost 的权限
GRANT USAGE ON . TO app_reader@localhost
GRANT SELECT ON ecommerce_db.products TO app_reader@localhost

USAGE 权限是默认的最小权限，它允许用户连接到数据库，但不能执行其他任何操作。

权限级别：全局、数据库、表和列

权限可以在不同范围内授予：

级别	语法（ON 子句）	描述
全局	ON *.*	适用于服务器上的所有数据库。使用时务必极其谨慎。通常仅用于管理账户。
数据库	ON database_name.*	适用于特定数据库中的所有对象（表、视图等）。
表	ON database_name.table_name	适用于单个表。这是应用程序用户最常见的级别。
列	ON database_name.table_name 带 (col1, col2)	仅适用于表中的特定列。对于高度敏感的数据很有用。

示例：数据库和列级权限

-- 授予用户对特定数据库的所有权限
GRANT ALL PRIVILEGES ON ecommerce_db.* TO 'db_admin'@'localhost';

-- 授予用户对一列的 SELECT 权限和对另一列的 UPDATE 权限
GRANT SELECT(product_name, price), UPDATE(stock_level)
ON ecommerce_db.Products
TO 'inventory_clerk'@'localhost';

使用角色管理权限

角色是权限的命名集合。它们是管理用户组权限的现代推荐方法。与其将相同的 10 项权限授予 20 个不同的开发人员账户，不如创建一个 developer 角色，将权限授予该角色，然后将该角色授予每个开发人员。

角色管理工作流程

-- 1. 创建角色
CREATE ROLE 'read_only_analyst';

-- 2. 授予角色权限
GRANT SELECT ON ecommerce_db.* TO 'read_only_analyst';

-- 3. 创建用户
CREATE USER 'susan'@'localhost' IDENTIFIED BY 'password123';

-- 4. 授予用户角色
GRANT 'read_only_analyst' TO 'susan'@'localhost';

-- 5. 将该角色设置为用户登录时的默认活动角色
SET DEFAULT ROLE 'read_only_analyst' TO 'susan'@'localhost';

特殊权限：GRANT OPTION 和 PROXY

• WITH GRANT OPTION：将此选项附加到 GRANT 语句允许接收用户将这些相同权限授予其他用户。这是一个强大且具有潜在危险的选项，应谨慎授予。
• PROXY：GRANT PROXY ON 'real_user' TO 'proxy_user'; 允许 proxy_user 模拟 real_user。当 proxy_user 以 real_user 身份连接时，他们将拥有 real_user 的权限。这用于高级身份验证场景。

使用现代客户端应用程序授予权限

在自动化安装脚本中，以编程方式执行 GRANT 语句很常见。务必确保代码由具有足够权限的用户（例如 root 或管理用户）运行。

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


此 Node.js 脚本演示了创建用户和授予权限的完整工作流程。

```javascript
// 必需：npm install mysql2
const mysql = require('mysql2/promise');

async function setupAppUser() {
  let connection;
  try {
    // 使用具有创建用户和授予权限的用户连接
    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';

    // 尽可能使用参数化查询，但用户/数据库名称通常不能作为参数。
    // 如果这些输入来自外部源，请对其进行清理。
    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();

此 Python 脚本执行相同的用户设置任务。

// 必需：pip install mysql-connector-python
import mysql.connector
from mysql.connector import errorcode

def setup_app_user():
    try:
        // 使用管理用户连接
        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()

使用 Java 的 JDBC 执行多个 DDL/DCL 语句。

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...");
            // 注意：在生产环境中，请优雅地处理用户存在的情况。
            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.");
            }
        }
    }
}

使用 mysqli 创建用户并授予权限的 PHP 脚本。

<?php
// 使用管理用户连接
$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';

// 使用 multi_query 执行一系列操作
$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 {
        // 存储第一个结果集
        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();
?>