SQLAdmin 在 CentOS 的权限分配策略
一 策略总览
二 数据库层权限设计
方案A 全库维护账户(仅限受控主机)
CREATE USER 'sqladmin'@'localhost' IDENTIFIED BY 'StrongPass!';GRANT ALL PRIVILEGES ON *.* TO 'sqladmin'@'localhost' WITH GRANT OPTION;CREATE USER 'sqladmin'@'10.0.1.%' IDENTIFIED BY 'StrongPass!'; GRANT ALL PRIVILEGES ON *.* TO 'sqladmin'@'10.0.1.%' WITH GRANT OPTION;FLUSH PRIVILEGES;方案B 单库运维账户(推荐日常使用)
CREATE USER 'sqladmin'@'localhost' IDENTIFIED BY 'StrongPass!';GRANT ALL PRIVILEGES ON mydb.* TO 'sqladmin'@'localhost';GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.mytable TO 'sqladmin'@'localhost';FLUSH PRIVILEGES;方案C 只读审计账户
CREATE USER 'sqlro'@'localhost' IDENTIFIED BY 'ReadOnly!';GRANT SELECT ON *.* TO 'sqlro'@'localhost';(或限定到指定库/表)FLUSH PRIVILEGES;可选 多环境分权
'sqladmin_dev'@'10.0.1.%'、'sqladmin_prod'@'10.0.2.%',分别授予对应库权限,避免跨环境误操作。三 系统层权限与访问控制
sudo useradd -m -s /bin/bash sqladminsudo usermod -aG wheel sqladminsudo visudo 添加 sqladmin ALL=(ALL) NOPASSWD: /usr/bin/systemctl mysqld, /usr/bin/mysql, /usr/bin/mysqldump(按需最小化)sudo firewall-cmd --permanent --zone=public --add-port=3306/tcp && sudo firewall-cmd --reloadsudo firewall-cmd --permanent --zone=public --add-port=5432/tcp && sudo firewall-cmd --reloadsudo setsebool -P httpd_can_network_connect_db 1(仅当 Web 服务需要连接数据库时启用)journalctl -u mysqld -f。四 数据库引擎差异要点
CREATE USER sqladmin WITH PASSWORD 'StrongPass!';GRANT ALL PRIVILEGES ON DATABASE mydb TO sqladmin;ALTER ROLE sqladmin WITH SUPERUSER;(慎用,遵循最小权限)。五 安全加固清单
SELECT user,host FROM mysql.user; SHOW GRANTS FOR 'sqladmin'@'localhost';SELECT, SHOW VIEW, LOCK TABLES, RELOAD 等必要权限),保留离线/异地副本并定期校验可恢复性。