权限生效小知识
MySQL的授权用户由两部分组成：'用户名'@'主机名/IP/IP段'
注意：包含特殊字符则必须加上引号
| 字符 | 含义 |
|---|---|
| %和_ | %代表所有主机，'10.10.1.%'代表所有来自10.10.1网段的主机 |
| 域名 | '%.mysql.com'代表来自mysql.com这个域名下的所有主机 |
| localhost/127.0.0.1/::1 | 本机/ipv4/ipv6的本机地址 |
| 同一个用户@IP | IP是包含关系，小范围IP的权限生效 |
| 同一个用户@IP | 权限是包含关系，最大范围生效，存在的权限都可以使用 |
| dba@% | 本地执行[mysql -udba -p -P -h 本机IP地址]可以登录； |
| 所有账号本地登录都依赖sock文件 | 账号有密码情况下仅指定sock文件不能登录；删除sock文件使用账号密码本地登录也会失败。 |
- 执行grant,revoke,setpassword,renameuser命令修改权限之后，MySQL会自动将修改后的权限信息同步加载到系统内存中；执行insert/update/delete系统权限表之后，须执行刷新权限命令才能同步系统内存中：flush privileges/mysqladmin flush-privileges/mysqladmin reload
- 修改tables和columns级别权限，客户端下次操作新权限就会生效
- 修改database级别权限，新权限在客户端执行use database命令后生效
- 修改global级别权限，需要重新创建连接新权限才能生效
- --skip-grant-tables 可以跳过所有系统权限表允许所有用户登录，只在特殊情况下暂时使用
连接MySQL常见报错问题
- 网络问题
can not connect to mysql server on 'xxxxx'
#检查防火墙是否限制了MySQL的端口
#检查MySQL服务是否关闭
#检查selinux 是否强制
- 权限表中有账号没地址
host 'xxxxx' is not allowed to connect to this mysql server
- 权限表中没有账号或密码错误
Access denied for user 'dba'@'xx.xx.xx.x'
- 权限表中没有具体权限
'DROP' command denied to user 'dba'@'localhost' for table 'xxx';
Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
授权原则
- 生产系统中MySQL用户创建由DBA统一协调按需创建，DBA通常直接使用root用户来管理数据库；
- 通常会创建指定业务数据库上的增删改查、临时表、执行存储过程的权限给应用程序来连接数据库；
- 通常会创建指定业务数据库上的只读权限给特定应用程序或某些高级别人员来查询数据，防止数据被修改；
- 修改mysql的端口，通过防火墙限制访问mysql端口的ip
- root账号设置强密码并指定只容许本地登录，其他用户设置强密码并严格指定对应账号的访问ip
- 如果需要记录登录信息，可以开启查询日志，查询日志会记录登录和查询语句
- mysql进程运行账号 :
linux : 新建mysql账号，安装时指定以mysql账户运行，给程序所在目录读取权限，data所在目录读取和写入权限；
windows : 禁止使用local system运行mysql账户，新建帐号给mysql程序所在目录读取权限和data目录读取和写入权限
- 用户授权权限最小原则 : 单数据库授权，权限够用即可，单数据库账户不能拥有super/process/file等全局权限，可避免安全隐患
- 删除无用数据库 : test数据库对新建的账户默认有权限
| 授权语句关键点 | 含义 |
|---|---|
| 访问地址 | 要访问的数据库服务器ip和port |
| 权限 | 具体需要的权限，开发一般授予 select,update,insert,delete,create temporary tables,execute |
| 用户名 | 具体访问人dev_name 、op_name、 test_name等 |
| 访问源地址 | 访问人的客户端ip 或者 应用程序ip |
常用操作需要的权限举例
-- 执行show processlist
只有连接权限时，只能查看自己的连接
具有process权限时，可以看到所有连接
-- 查看information_schema库下表
process (innodb相关表需要process权限)
-- mysql逻辑备份mysqldump所需权限
select+lock tables
【--single-transaction 参数下不需要锁表，所以不需要lock tables权限】
【要备份触发器存储过程等，需要添加TRIGGER、EVENT、EXECUTE 等权限】
【MySQL版本5.7测试】
账号权限：只有lock tables
备份语句 mysqldump -u -p -A>
备份结果：文件内容只有创建数据库语句
-- mysql逻辑恢复最小权限
GRANT SELECT, INSERT, CREATE, DROP, ALTER ON *.*
如果有外键关联：REFERENCES
如果有触发器或者存储过程:??待测试
-- rename
INSERT, CREATE, DROP, ALTER
-- truncat
清空表需要有drop权限（授权后需要重新使用表或重新登录）
-- usage
可以执行show databases看到information_schema的所有表结构，能看到除innodb表之外的表数据；
执行select sleep(10);等内置函数；
show processlist 查看该用户所有会话连接；
可以执行kill命令 杀掉该用户所有连接；
-- select *.*
可以执行show databases看到所有库表,查看所有库表结构和数据
-- references
字段级 用户可以将其它表的一个字段作为某一个表的外键约束
用户创建和删除举例
--创建用户
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin';
CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'obscure';
CREATE USER 'custom'@'%.example.com' IDENTIFIED BY 'obscure';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON expenses.* TO 'custom'@'host47.exa.com';
-- 5.7之前可以使用grant直接创建账户
-- 用户重命名
RENAME USER mhh@localhost TO miao@localhost;
-- 删除用户
DROP USER test@localhost;
--如果用户已存在：ERROR 1396 (HY000): Operation CREATE USER failed for 'dba'@'localhost'
授权和回收举例
-- 列授权
GRANT select(id, num, class) ON testdb.log TO dba@localhost;
-- 存储过程授权
GRANT execute ON procedure testdb.pr_add TO 'dba'@'localhost';
-- 函数授权
GRANT execute ON function testdb.fn_add TO 'dba'@'localhost';
-- 加期限授权
ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE INTERVAL 30 DAY; 密码30天后过期
ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE INEVER; 密码不过期
ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE DEFAULT; 默认过期策略
-- 设置MySQL用户密码过期策略【系统参数，作用于所有用户】
default_password_lifetime=180 设置180天过期
default_password_lifetime=0 设置密码不过期
--手动强制某个用户密码过期
ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE;
SELECT 1;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
ALTER USER USER() IDENTIFIED BY 'new_password';
Query OK, 0 rows affected (0.01 sec)
SELECT 1;
|1|
-- 回收权限
REVOKE drop ON test.* FROM test@localhost;
用户资源限制
-- 通过执行create user/alter user设置/修改用户的资源限制
CREATE USER 'username'@'localhost' IDENTIFIED BY 'xxxxx' WITH MAX_QUERIES_PER_HOUR 20 MAX_UPDATES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 5 MAX_USER_CONNECTIONS 2;
ALTER USER 'username'@'localhost' WITH MAX_USER_CONNECTIONS 10;
• MAX_QUERIES_PER_HOUR：一个用户在一个小时内可以执行查询的次数(基本包含所有语句)
• MAX_UPDATES_PER_HOUR：一个用户在一个小时内可以执行修改的次数(仅包含修改数据库或表的语句)
• MAX_CONNECTIONS_PER_HOUR：一个用户在一个小时内可以连接MySQL的时间
• MAX_USER_CONNECTIONS：一个用户可以在同一时间连接MySQL实例的数量
-- 取消某项资源限制既是把原先的值修改成0
ALTER USER 'username'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 0;
-- 当针对某个用户的max_user_connections非0时，则忽略全局系统参数 max_user_connections，反之则全局系统参数生效
修改root密码
--V5.6
update mysql.user set password=password('xxxx') where user='root' and host = 'localhost';flush privileges;
--V5.7
update mysql.user set authentication_string=password('pwd ') where user='root' and host = 'localhost';flush privileges;
--V5.8
alter user 'root'@'localhost' identified by '';
ALTER USER 'dba'@'localhost' IDENTIFIED BY 'dba140';--需要alter权限
SET PASSWORD FOR 'dba'@'localhost' = PASSWORD('xxx');--不需要具体权限
GRANT USAGE ON *.* TO 'dba'@'localhost' IDENTIFIED BY 'xxx'; --需要grant权限
mysqladmin -u user_name -h host_name password "new_password";
--修改本身用户密码方式:
ALTER USER USER() IDENTIFIED BY 'mypass';
SET PASSWORD = PASSWORD('mypass');
MySQL用户lock
--通过执行create user/alter user命令中带account lock/unlock子句设置用户的lock状态
--create user语句默认的用户是unlock状态
create user username@localhost identified by 'xxx' account lock;
--alter user语句默认不会修改用户的lock/unlock状态
alter user 'mysql.sys'@localhost account lock;
alter user 'mysql.sys'@localhost account unlock;
-- 当客户端使用lock状态的用户登录MySQL时，会收到报错
Access denied for user 'user_name'@'host_name'. Account is locked.
权限统计
mysql -uxxx -pxxx -NBe "select concat(\"'\",user,\"'\",'@',\"'\",host,\"'\") from mysql.user order by user desc;" |while read userhost ;do mysql -uxxx -pxxx -NBe "show grants for ${userhost};";done >/tmp/pri.sql
