-- 查看當前用戶
SELECT USER();
-- 查看當前用戶的權限
SHOW GRANTS;
-- 以root用戶身份查看所有權限
SHOW GRANTS FOR 'root'@'localhost';

-- 切換到mysql系統(tǒng)數(shù)據(jù)庫
USE mysql;
-- 查看user表的結構
DESC user;
-- 查看user表的主要字段
SELECT Host, User, authentication_string FROM user;

-- 方法1：查看所有用戶
SELECT Host, User FROM mysql.user;
-- 方法2：查看當前數(shù)據(jù)庫的所有用戶
SELECT DISTINCT User FROM mysql.user;
-- 方法3：查看特定用戶的詳細信息
SELECT Host, User, authentication_string, 
       Select_priv, Insert_priv, Update_priv, Delete_priv
FROM mysql.user 
WHERE User = 'root';
-- 方法4：使用SHOW命令查看用戶權限
SHOW GRANTS FOR 'root'@'localhost';

-- 基本語法
CREATE USER '用戶名'@'主機名' IDENTIFIED BY '密碼';
-- 完整語法
CREATE USER [IF NOT EXISTS] '用戶名'@'主機名' 
IDENTIFIED BY '密碼' 
[PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]]
[ACCOUNT [LOCK | UNLOCK]];

-- 查看默認的密碼驗證插件
SHOW VARIABLES LIKE 'default_authentication_plugin';
-- 創(chuàng)建用戶時指定密碼驗證插件
CREATE USER 'test_user'@'localhost' 
IDENTIFIED WITH mysql_native_password BY 'password123';

-- 示例1：創(chuàng)建只能本機登錄的用戶
CREATE USER 'local_user'@'localhost' IDENTIFIED BY 'local_pass123';
-- 示例2：創(chuàng)建可從任意主機登錄的用戶
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'remote_pass123';
-- 示例3：創(chuàng)建只能從特定IP登錄的用戶
CREATE USER 'office_user'@'192.168.1.100' IDENTIFIED BY 'office_pass123';
-- 示例4：創(chuàng)建只能從特定網(wǎng)段登錄的用戶
CREATE USER 'dept_user'@'192.168.1.%' IDENTIFIED BY 'dept_pass123';
-- 示例5：創(chuàng)建用戶并設置密碼永不過期
CREATE USER 'app_user'@'%' 
IDENTIFIED BY 'app_pass123' 
PASSWORD EXPIRE NEVER;
-- 驗證用戶創(chuàng)建成功
SELECT Host, User FROM mysql.user WHERE User LIKE '%user%';

-- 錯誤1：ERROR 1396 (HY000): Operation CREATE USER failed
-- 原因：用戶已經(jīng)存在
-- 解決方法：使用IF NOT EXISTS或先刪除用戶
CREATE USER IF NOT EXISTS 'existing_user'@'localhost' IDENTIFIED BY 'password';
-- 錯誤2：ERROR 1045 (28000): Access denied
-- 原因：當前用戶沒有CREATE USER權限
-- 解決方法：使用有足夠權限的用戶（如root）登錄
-- 錯誤3：密碼策略不符合要求
-- 查看密碼策略
SHOW VARIABLES LIKE 'validate_password%';
-- 創(chuàng)建符合密碼策略的用戶
CREATE USER 'strong_user'@'localhost' IDENTIFIED BY 'StrongPass123!';

-- 基本語法
DROP USER '用戶名'@'主機名';
-- 刪除多個用戶
DROP USER '用戶1'@'主機1', '用戶2'@'主機2';
-- 安全刪除（如果用戶不存在不會報錯）
DROP USER IF EXISTS '用戶名'@'主機名';

-- 示例1：刪除單個用戶
DROP USER 'test_user'@'localhost';
-- 示例2：刪除多個用戶
DROP USER 'user1'@'localhost', 'user2'@'%';
-- 示例3：安全刪除用戶
DROP USER IF EXISTS 'maybe_not_exist'@'localhost';
-- 示例4：刪除所有測試用戶
-- 先查詢要刪除的用戶
SELECT CONCAT('DROP USER ''', User, '''@''', Host, ''';') AS drop_command
FROM mysql.user 
WHERE User LIKE 'test_%';
-- 執(zhí)行生成的刪除命令
DROP USER 'test_user1'@'localhost';
DROP USER 'test_user2'@'%';
-- 驗證用戶刪除成功
SELECT Host, User FROM mysql.user WHERE User LIKE 'test_%';

-- 方法1：使用ALTER USER（推薦，MySQL 5.7+）
ALTER USER '用戶名'@'主機名' IDENTIFIED BY '新密碼';
-- 方法2：使用SET PASSWORD
SET PASSWORD FOR '用戶名'@'主機名' = '新密碼';
-- 方法3：修改當前用戶密碼
ALTER USER USER() IDENTIFIED BY '新密碼';
-- 或者
SET PASSWORD = '新密碼';

-- MySQL 5.7及以下版本（已過時，不推薦）
SET PASSWORD FOR 'user'@'host' = PASSWORD('new_password');
-- MySQL 8.0及以上版本（推薦）
ALTER USER 'user'@'host' IDENTIFIED BY 'new_password';

-- 示例1：root用戶修改其他用戶密碼
ALTER USER 'app_user'@'%' IDENTIFIED BY 'new_strong_password123!';
-- 示例2：用戶修改自己的密碼
ALTER USER USER() IDENTIFIED BY 'my_new_password123!';
-- 示例3：修改密碼并設置過期策略
ALTER USER 'temp_user'@'localhost' 
IDENTIFIED BY 'temp_password123!' 
PASSWORD EXPIRE INTERVAL 30 DAY;
-- 示例4：強制用戶下次登錄時修改密碼
ALTER USER 'new_employee'@'%' 
IDENTIFIED BY 'initial_password123!' 
PASSWORD EXPIRE;
-- 示例5：批量修改密碼（生成SQL語句）
SELECT CONCAT('ALTER USER ''', User, '''@''', Host, ''' IDENTIFIED BY ''new_password_', User, ''';') AS alter_command
FROM mysql.user 
WHERE User LIKE 'temp_%';
-- 驗證密碼修改（通過重新登錄測試）
-- mysql -u app_user -p -h localhost

-- 查看所有可用權限
SHOW PRIVILEGES;
-- 查看當前用戶權限
SHOW GRANTS;
-- 查看特定用戶權限
SHOW GRANTS FOR 'username'@'hostname';

-- 基本語法
GRANT 權限列表 ON 數(shù)據(jù)庫.表 TO '用戶名'@'主機名';
-- 完整語法
GRANT 權限列表 ON 數(shù)據(jù)庫.表 TO '用戶名'@'主機名' 
[WITH GRANT OPTION] 
[WITH MAX_QUERIES_PER_HOUR count]
[WITH MAX_UPDATES_PER_HOUR count]
[WITH MAX_CONNECTIONS_PER_HOUR count];

-- 單個權限
GRANT SELECT ON database.table TO 'user'@'host';
-- 多個權限
GRANT SELECT, INSERT, UPDATE ON database.table TO 'user'@'host';
-- 所有權限
GRANT ALL PRIVILEGES ON database.table TO 'user'@'host';
-- 特定列權限
GRANT SELECT (column1, column2), UPDATE (column1) ON database.table TO 'user'@'host';

-- 創(chuàng)建測試環(huán)境
CREATE DATABASE company_db;
USE company_db;
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10,2)
);
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    manager VARCHAR(100)
);
-- 示例1：授予數(shù)據(jù)庫的所有權限
CREATE USER 'db_admin'@'localhost' IDENTIFIED BY 'admin_pass123!';
GRANT ALL PRIVILEGES ON company_db.* TO 'db_admin'@'localhost';
-- 示例2：授予只讀權限
CREATE USER 'report_user'@'%' IDENTIFIED BY 'report_pass123!';
GRANT SELECT ON company_db.* TO 'report_user'@'%';
-- 示例3：授予特定表的增刪改查權限
CREATE USER 'hr_user'@'192.168.1.%' IDENTIFIED BY 'hr_pass123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON company_db.employees TO 'hr_user'@'192.168.1.%';
-- 示例4：授予列級別權限
CREATE USER 'public_user'@'%' IDENTIFIED BY 'public_pass123!';
GRANT SELECT (id, name, department) ON company_db.employees TO 'public_user'@'%';
-- 示例5：授予創(chuàng)建表權限
CREATE USER 'developer'@'localhost' IDENTIFIED BY 'dev_pass123!';
GRANT CREATE, ALTER, INDEX ON company_db.* TO 'developer'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON company_db.* TO 'developer'@'localhost';
-- 示例6：授予全局權限
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'backup_pass123!';
GRANT SELECT ON *.* TO 'backup_user'@'localhost';
GRANT RELOAD, LOCK TABLES ON *.* TO 'backup_user'@'localhost';
-- 示例7：授予權限并允許轉授（WITH GRANT OPTION）
CREATE USER 'team_lead'@'%' IDENTIFIED BY 'lead_pass123!';
GRANT SELECT, INSERT, UPDATE ON company_db.* TO 'team_lead'@'%' WITH GRANT OPTION;
-- 示例8：授予權限并設置資源限制
CREATE USER 'limited_user'@'%' IDENTIFIED BY 'limited_pass123!';
GRANT SELECT ON company_db.* TO 'limited_user'@'%' 
WITH MAX_QUERIES_PER_HOUR 1000 
WITH MAX_CONNECTIONS_PER_HOUR 10;
-- 刷新權限表（使權限立即生效）
FLUSH PRIVILEGES;
-- 驗證權限授予成功
SHOW GRANTS FOR 'db_admin'@'localhost';
SHOW GRANTS FOR 'report_user'@'%';
SHOW GRANTS FOR 'hr_user'@'192.168.1.%';

-- 基本語法
REVOKE 權限列表 ON 數(shù)據(jù)庫.表 FROM '用戶名'@'主機名';
-- 回收所有權限
REVOKE ALL PRIVILEGES ON 數(shù)據(jù)庫.表 FROM '用戶名'@'主機名';
-- 回收GRANT權限
REVOKE GRANT OPTION ON 數(shù)據(jù)庫.表 FROM '用戶名'@'主機名';

-- 示例1：回收特定權限
REVOKE INSERT, UPDATE ON company_db.employees FROM 'hr_user'@'192.168.1.%';
-- 示例2：回收數(shù)據(jù)庫的所有權限
REVOKE ALL PRIVILEGES ON company_db.* FROM 'developer'@'localhost';
-- 示例3：回收全局權限
REVOKE RELOAD ON *.* FROM 'backup_user'@'localhost';
-- 示例4：回收GRANT權限
REVOKE GRANT OPTION ON company_db.* FROM 'team_lead'@'%';
-- 示例5：回收列級別權限
REVOKE SELECT (salary) ON company_db.employees FROM 'public_user'@'%';
-- 示例6：完全移除用戶的所有權限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'limited_user'@'%';
-- 刷新權限表
FLUSH PRIVILEGES;
-- 驗證權限回收成功
SHOW GRANTS FOR 'hr_user'@'192.168.1.%';
SHOW GRANTS FOR 'developer'@'localhost';

-- 查看密碼策略
SHOW VARIABLES LIKE 'validate_password%';
-- 創(chuàng)建強密碼用戶示例
CREATE USER 'secure_user'@'localhost' IDENTIFIED BY 'SecurePass123!@#';

-- ? 錯誤做法：給予過多權限
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%';
-- ? 正確做法：只給必要權限
GRANT SELECT, INSERT, UPDATE ON specific_db.specific_table TO 'app_user'@'%';

-- 查看所有用戶及其基本權限信息
SELECT 
    User, 
    Host,
    Select_priv,
    Insert_priv,
    Update_priv,
    Delete_priv,
    Create_priv,
    Drop_priv
FROM mysql.user
WHERE User != '';
-- 查看特定用戶的所有權限
SHOW GRANTS FOR 'username'@'hostname';

-- 限制用戶連接來源
CREATE USER 'secure_user'@'192.168.1.%' IDENTIFIED BY 'SecurePass123!';
-- 而不是使用 '%' 允許任意IP連接
-- 設置連接限制
ALTER USER 'limited_user'@'%' 
WITH MAX_CONNECTIONS_PER_HOUR 10 
MAX_QUERIES_PER_HOUR 1000;