CREATE USER 'dev'@'localhost';      -- 僅本機 socket 連接
CREATE USER 'dev'@'192.168.1.%';   -- 192.168.1.0/24 網(wǎng)段
CREATE USER 'dev'@'%';             -- 任意主機（含遠程）

ERROR 1045 (28000): Access denied for user 'app_user'@'192.168.1.10' (using password: YES)

-- 以 root 登錄后執(zhí)行
SELECT Host, User FROM mysql.user WHERE User = 'app_user';

-- 查看用戶認(rèn)證方式
SELECT User, Host, plugin FROM mysql.user WHERE User = 'app_user';

SELECT User, Host, account_locked, password_expired 
FROM mysql.user 
WHERE User = 'app_user';

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';

-- 查看用戶連接限制
SELECT User, Host, max_connections FROM mysql.user WHERE User = 'app_user';

-- 允許從任意 IP 連接（生產(chǎn)環(huán)境慎用）
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPass123!';

-- 授予必要權(quán)限
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_user'@'%';

-- 刷新權(quán)限
FLUSH PRIVILEGES;

-- 將用戶改為 mysql_native_password（兼容性最好）
ALTER USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'StrongPass123!';

-- 或創(chuàng)建時指定
CREATE USER 'app_user'@'%' 
IDENTIFIED WITH mysql_native_password BY 'StrongPass123!';

-- 解鎖賬戶
ALTER USER 'app_user'@'%' ACCOUNT UNLOCK;

-- 重置密碼（同時解除過期）
ALTER USER 'app_user'@'%' IDENTIFIED BY 'NewPass456!';

-- 或直接設(shè)置永不過期
ALTER USER 'app_user'@'%' PASSWORD EXPIRE NEVER;

-- 提高用戶最大連接數(shù)
ALTER USER 'app_user'@'%' WITH MAX_USER_CONNECTIONS 50;

-- 或全局增加（需重啟或動態(tài)設(shè)置）
SET GLOBAL max_connections = 500;

ERROR 1142 (42000): SELECT command denied to user 'app_user'@'192.168.1.10' for table 'users'

-- 查看用戶所有權(quán)限
SHOW GRANTS FOR 'app_user'@'%';

-- 查看特定數(shù)據(jù)庫權(quán)限
SELECT * FROM mysql.db WHERE User = 'app_user' AND Db = 'mydb';

-- 查看表級權(quán)限
SELECT * FROM mysql.tables_priv WHERE User = 'app_user' AND Db = 'mydb';

-- 查看視圖定義
SHOW CREATE VIEW v_users;

-- 查看存儲過程安全上下文
SELECT security_type, definer FROM information_schema.routines 
WHERE routine_name = 'my_proc';

-- 授予單表權(quán)限
GRANT SELECT, INSERT ON mydb.users TO 'app_user'@'%';

-- 授予整個數(shù)據(jù)庫權(quán)限
GRANT ALL PRIVILEGES ON myapp.* TO 'app_user'@'%';

-- 授予列級權(quán)限（敏感字段保護）
GRANT SELECT (id, name) ON mydb.users TO 'report_user'@'%';

ALTER DEFINER = CURRENT_USER SQL SECURITY INVOKER 
PROCEDURE my_proc();

-- 正確寫法
SELECT * FROM `order` WHERE `status` = 'paid';

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version> <!-- 使用 8.0.11+ 支持 caching_sha2_password -->
</dependency>

String url = "jdbc:mysql://192.168.1.100:3306/myapp?" +
             "useSSL=false&" +
             "allowPublicKeyRetrieval=true&" + // 必要時啟用（有安全風(fēng)險）
             "serverTimezone=UTC";

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class DatabasePermissionChecker {

    private static final Logger log = LoggerFactory.getLogger(DatabasePermissionChecker.class);

    public void executeQuery(Connection conn, String sql) {
        try (Statement stmt = conn.createStatement()) {
            stmt.executeQuery(sql);
        } catch (SQLException e) {
            int errorCode = e.getErrorCode();
            String sqlState = e.getSQLState();

            if (errorCode == 1045 || "28000".equals(sqlState)) {
                log.error("?? LOGIN DENIED: Check username, password, host, or auth plugin.");
                throw new SecurityException("Database login failed", e);
            } 
            else if (errorCode == 1142 || "42000".equals(sqlState)) {
                log.error("?? COMMAND DENIED: User lacks privilege for: {}", sql);
                // 可觸發(fā)告警或返回友好提示
                throw new AccessDeniedException("Insufficient database privileges");
            }
            else {
                throw new RuntimeException("Database error", e);
            }
        }
    }
}

@Component
public class DatabaseHealthCheck implements ApplicationRunner {

    @Autowired
    private DataSource dataSource;

    @Override
    public void run(ApplicationArguments args) throws Exception {
        try (Connection conn = dataSource.getConnection()) {
            // 嘗試執(zhí)行一個需要權(quán)限的操作
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("SELECT 1 FROM information_schema.tables LIMIT 1");
            }
            log.info("? Database connection and basic permissions verified.");
        } catch (SQLException e) {
            log.error("? Database permission or connection check FAILED!", e);
            // 可選擇退出應(yīng)用或進入降級模式
            System.exit(1);
        }
    }
}

spring:
  datasource:
    hikari:
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      maximum-pool-size: 20
      # 關(guān)鍵：驗證連接有效性
      connection-test-query: SELECT 1
      # 或 MySQL 專用
      # connection-init-sql: SET NAMES utf8mb4

ALTER USER 'secure_user'@'%' REQUIRE SSL;

String url = "jdbc:mysql://host/db?useSSL=true&requireSSL=true";

ALTER USER 'secure_user'@'%' REQUIRE NONE;

[mysqld]
skip-name-resolve

-- 示例：創(chuàng)建只讀報表用戶
CREATE USER 'reporter'@'10.0.0.%' IDENTIFIED BY '...';
GRANT SELECT ON sales.orders TO 'reporter'@'10.0.0.%';
GRANT SELECT (id, name) ON hr.employees TO 'reporter'@'10.0.0.%';

-- 創(chuàng)建角色
CREATE ROLE 'app_read', 'app_write';

-- 授予權(quán)限給角色
GRANT SELECT ON myapp.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON myapp.* TO 'app_write';

-- 用戶繼承角色
GRANT 'app_read', 'app_write' TO 'app_user'@'%';

-- 激活角色（或設(shè)為默認(rèn)）
SET DEFAULT ROLE 'app_read', 'app_write' TO 'app_user'@'%';

-- 查找擁有 ALL PRIVILEGES 的用戶（高危?。?
SELECT User, Host FROM mysql.user 
WHERE Select_priv='Y' AND Insert_priv='Y' AND Update_priv='Y' AND Delete_priv='Y' 
AND Drop_priv='Y';

-- 查找可以從任意主機連接的用戶
SELECT User, Host FROM mysql.user WHERE Host = '%';