MySQL权限异常排查：用户无法登录或操作的解决方案

create user 'dev'@'localhost';      -- 仅本机 socket 连接
create user 'dev'@'192.168.1.%';   -- 192.168.1.0/24 网段
create user 'dev'@'%';             -- 任意主机（含远程）

error 1045 (28000): access denied for user 'app_user'@'192.168.1.10' (using password: yes)

-- 以 root 登录后执行
select host, user from mysql.user where user = 'app_user';

-- 查看用户认证方式
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 连接（生产环境慎用）
create user 'app_user'@'%' identified by 'strongpass123!';

-- 授予必要权限
grant select, insert, update, delete on myapp.* to 'app_user'@'%';

-- 刷新权限
flush privileges;

-- 将用户改为 mysql_native_password（兼容性最好）
alter user 'app_user'@'%' identified with mysql_native_password by 'strongpass123!';

-- 或创建时指定
create user 'app_user'@'%' 
identified with mysql_native_password by 'strongpass123!';

-- 解锁账户
alter user 'app_user'@'%' account unlock;

-- 重置密码（同时解除过期）
alter user 'app_user'@'%' identified by 'newpass456!';

-- 或直接设置永不过期
alter user 'app_user'@'%' password expire never;

-- 提高用户最大连接数
alter user 'app_user'@'%' with max_user_connections 50;

-- 或全局增加（需重启或动态设置）
set global max_connections = 500;

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

-- 查看用户所有权限
show grants for 'app_user'@'%';

-- 查看特定数据库权限
select * from mysql.db where user = 'app_user' and db = 'mydb';

-- 查看表级权限
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';

-- 授予单表权限
grant select, insert on mydb.users to 'app_user'@'%';

-- 授予整个数据库权限
grant all privileges on myapp.* to 'app_user'@'%';

-- 授予列级权限（敏感字段保护）
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&" + // 必要时启用（有安全风险）
             "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);
                // 可触发告警或返回友好提示
                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()) {
            // 尝试执行一个需要权限的操作
            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);
            // 可选择退出应用或进入降级模式
            system.exit(1);
        }
    }
}

spring:
  datasource:
    hikari:
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      maximum-pool-size: 20
      # 关键：验证连接有效性
      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

-- 示例：创建只读报表用户
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.%';

-- 创建角色
create role 'app_read', 'app_write';

-- 授予权限给角色
grant select on myapp.* to 'app_read';
grant insert, update, delete on myapp.* to 'app_write';

-- 用户继承角色
grant 'app_read', 'app_write' to 'app_user'@'%';

-- 激活角色（或设为默认）
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 = '%';