权限管理

GRANT 权限列表 ON 作用范围 TO '用户'@'主机' [WITH GRANT OPTION];

-- 创建测试数据库
CREATE DATABASE IF NOT EXISTS app_db CHARACTER SET utf8mb4;
USE app_db;

-- 创建员工表
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5, 2)
);

-- 插入测试数据
INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

CREATE USER 'global_reader'@'localhost'
IDENTIFIED BY 'GlobalReader123!';

USE app_db;
SELECT * FROM employees;

GRANT SELECT ON *.* TO 'global_reader'@'localhost';

SHOW GRANTS FOR 'global_reader'@'localhost';

USE app_db;
SELECT * FROM employees;

-- 尝试 INSERT（应该失败，未授予）
INSERT INTO employees (emp_name) VALUES ('测试');

REVOKE SELECT ON *.* FROM 'global_reader'@'localhost';

SHOW GRANTS FOR 'global_reader'@'localhost';

DROP USER 'global_reader'@'localhost';

CREATE USER 'db_admin'@'localhost'
IDENTIFIED BY 'DbAdmin123!';

USE app_db;
SHOW TABLES;

GRANT ALL PRIVILEGES ON app_db.* TO 'db_admin'@'localhost';

SHOW GRANTS FOR 'db_admin'@'localhost';

USE app_db;

-- 查询（成功）
SELECT * FROM scores;

-- 插入（成功）
INSERT INTO scores (emp_id, score) VALUES (1, 88);

-- 更新（成功）
UPDATE scores SET score = 88 WHERE score_id = 2;

-- 删除（成功）
DELETE FROM scores WHERE score_id = 2;

-- 创建新表（成功）
CREATE TABLE logs (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT,
    action VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 尝试访问其他数据库（失败）
USE mysql;
SELECT * FROM mysql.user;

SHOW TABLES;

-- 回收 DELETE 权限
REVOKE DELETE ON app_db.* FROM 'db_admin'@'localhost';

SHOW GRANTS FOR 'db_admin'@'localhost';

USE app_db;

-- 尝试 DELETE（失败）
DELETE FROM scores WHERE score_id = 1;

-- SELECT 仍然可以执行（成功）
SELECT * FROM scores;

REVOKE ALL PRIVILEGES ON app_db.* FROM 'db_admin'@'localhost';
DROP USER 'db_admin'@'localhost';

CREATE USER 'table_user'@'localhost'
IDENTIFIED BY 'TableUser123!';

-- 只能读 employees 表
GRANT SELECT ON app_db.employees TO 'table_user'@'localhost';

-- 可以读写 scores 表，但不能删表
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.scores TO 'table_user'@'localhost';

SHOW GRANTS FOR 'table_user'@'localhost';

USE app_db;

-- employees 表：只能 SELECT（成功）
SELECT * FROM employees;

-- employees 表：尝试 INSERT（失败）
INSERT INTO employees (emp_name) VALUES ('测试');

-- scores 表：可以 INSERT（成功）
INSERT INTO scores (emp_id, score) VALUES (1, 88);

-- scores 表：可以 DELETE（成功）
DELETE FROM scores WHERE score_id = 2;

-- 尝试访问 logs 表（如果存在，会失败）
SELECT * FROM logs;

REVOKE ALL PRIVILEGES ON app_db.* FROM 'table_user'@'localhost';
DROP USER 'table_user'@'localhost';

CREATE USER 'column_user'@'localhost'
IDENTIFIED BY 'ColumnUser123!';

-- 只能更新 employees 表的 dept 列
GRANT UPDATE(dept) ON app_db.employees TO 'column_user'@'localhost';

-- 可以查询 employees 表所有列
GRANT SELECT ON app_db.employees TO 'column_user'@'localhost';

SHOW GRANTS FOR 'column_user'@'localhost';

USE app_db;

-- SELECT 所有列（成功）
SELECT * FROM employees;

-- 更新 dept 列（成功）
UPDATE employees SET dept = '产品部' WHERE emp_id = 1;

-- 更新 score 列（失败，未授权）
UPDATE employees SET score = 95 WHERE emp_id = 1;

-- 更新 emp_name 列（失败，未授权）
UPDATE employees SET emp_name = '大翔翔' WHERE emp_id = 1;

REVOKE ALL PRIVILEGES ON app_db.* FROM 'column_user'@'localhost';
DROP USER 'column_user'@'localhost';

CREATE USER 'senior_user'@'localhost'
IDENTIFIED BY 'SeniorUser123!';

CREATE USER 'junior_user'@'localhost'
IDENTIFIED BY 'JuniorUser123!';

GRANT SELECT, INSERT ON app_db.* TO 'senior_user'@'localhost'
WITH GRANT OPTION;

SHOW GRANTS FOR 'senior_user'@'localhost';

GRANT SELECT ON app_db.* TO 'junior_user'@'localhost';

SHOW GRANTS FOR 'junior_user'@'localhost';

USE app_db;
SELECT * FROM employees;

-- 尝试 INSERT（失败）
INSERT INTO employees (emp_name) VALUES ('测试');

REVOKE ALL PRIVILEGES ON app_db.* FROM 'senior_user'@'localhost';
REVOKE ALL PRIVILEGES ON app_db.* FROM 'junior_user'@'localhost';
DROP USER 'senior_user'@'localhost';
DROP USER 'junior_user'@'localhost';

DELIMITER //

CREATE PROCEDURE app_db.GetEmployeeCount()
BEGIN
    SELECT COUNT(*) AS total_employees FROM app_db.employees;
END //

CREATE PROCEDURE app_db.GetHighScore(IN p_dept VARCHAR(20))
BEGIN
    SELECT emp_name, dept, e.score
    FROM app_db.employees e
    JOIN app_db.scores s ON e.emp_id = s.emp_id
    WHERE e.dept = p_dept
    ORDER BY s.score DESC
    LIMIT 1;
END //

DELIMITER ;

CREATE USER 'proc_user'@'localhost'
IDENTIFIED BY 'ProcUser123!';

GRANT EXECUTE ON PROCEDURE app_db.GetEmployeeCount TO 'proc_user'@'localhost';
GRANT EXECUTE ON PROCEDURE app_db.GetHighScore TO 'proc_user'@'localhost';

SHOW GRANTS FOR 'proc_user'@'localhost';

-- 执行授权的存储过程（成功）
CALL app_db.GetEmployeeCount();

-- 执行另一个授权的存储过程（成功）
CALL app_db.GetHighScore('技术部');

-- 尝试直接查询底层表（失败）
SELECT * FROM app_db.employees;

REVOKE EXECUTE ON PROCEDURE app_db.GetEmployeeCount FROM 'proc_user'@'localhost';
REVOKE EXECUTE ON PROCEDURE app_db.GetHighScore FROM 'proc_user'@'localhost';
DROP USER 'proc_user'@'localhost';
DROP PROCEDURE app_db.GetEmployeeCount;
DROP PROCEDURE app_db.GetHighScore;

CREATE USER 'readonly'@'localhost'
IDENTIFIED BY 'ReadOnly123!';

GRANT SELECT ON app_db.* TO 'readonly'@'localhost';

USE app_db;

-- SELECT 成功
SELECT * FROM employees;

-- INSERT 失败
INSERT INTO employees (emp_name) VALUES ('测试');

-- UPDATE 失败
UPDATE employees SET dept = '测试部' WHERE emp_id = 1;

-- DELETE 失败
DELETE FROM employees WHERE emp_id = 1;

REVOKE ALL PRIVILEGES ON app_db.* FROM 'readonly'@'localhost';
DROP USER 'readonly'@'localhost';

GRANT ...          -- 修改权限表
FLUSH PRIVILEGES;  -- 手动刷新（通常不需要，GRANT 自动刷新）

-- 创建新用户
CREATE USER 'test_timing'@'localhost' IDENTIFIED BY 'Test123!';

-- 立即授权
GRANT SELECT ON app_db.* TO 'test_timing'@'localhost';

-- 立即验证（无需 FLUSH PRIVILEGES）
-- 以 test_timing 连接，执行 SELECT，立即生效