MySQL用户管理
账户管理的重要性
- 在MySQL中可以通过账户控制允许或不允许用户执行操作
- 可以精细分配权限给不同职能的账户
- 避免使用root账户
- 应用不能直接使用root
- 防止维护期出错
- 限制特定权限账户确保数据完整性
- 允许特定授权账户完成期工作
- 阻止未经授权的用户访问超出其特权的数据
理解MySQL连接和查询流程
查询MySQL账户及验证
- 5.7以前
- select user,host,password from mysql.user;
- 5.7以后
- select user,host,authentication_string from mysql.user;
- MySQL将使用以下三项进行验证
- 客户端主机名，注意事项
- 创建用户名时指定来源主机名
- 连接数据库时，也可以使用主机名
- user(),current_user的区别
- user()当前连接的用户，current_user()是权限表中定义的用户
hetan@127.0.0.1 [(none)]>select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| hetan | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
hetan@127.0.0.1 [(none)]>select user(),current_user();
+-----------------+----------------+
| user() | current_user() |
+-----------------+----------------+
| hetan@localhost | hetan@% |
+-----------------+----------------+
1 row in set (0.00 sec)
创建,修改和删除账户
- 创建用户
- 用户名建议8-16字符，密码16-32个字符
- create user 用户名@主机 identified by '密码';
- 主机名是精确匹配，例如两个用户名：1、hetan@% 2、hetan@192.168.1.100 现在一个用户从192.168.1.100机器上连接到服务器匹配的主机名会是第二个
- %和区别：%是匹配一串字符，匹配得是一个字符
- 使用%的危害
- 在主机名ip段中某一个ip不让连接，怎么操作
- create user 'tdev'@'192.168.1.101' identified by 'otherpwd';
- 风险提示
- 不创建无用户名的账号
- 不创建没有密码的账号
- 在可能的情况下，不要使用主机通配符
- 给账户设置密码
- create user ... identified by '密码';
- grant ... identified by '密码';
- set password for 'hetan'@'%' = '密码';
- mysqladmin password （不推荐）
- update 授权表（不推荐）
- alter user 'hetan'@'%' identified by '密码';
- 确认密码
- 查看没有密码的用户
- select user,host from mysql.user where password='';
- select user,host from mysql.user where authentication_string='';
- 让用户口令失效，登录后必须改密码
- alter user 'hetan'@'%' password expire;
- 要对应版本的mysqlclient
- 删除用户
- drop user 'hetan'@'%';
- 直接删除该用户
- 从授权表中删除该用户记录
- 删除用户时不带主机名，删除得是'用户名'@'%'
- rename user 'hetan'@'%' to 'hetan1'@'%';
- 更改账号的名称
- 保留权限，可以更改：==用户名和主机部分==
授权,修改和撤销用户权限
- 授权
- 用户连入mysql后
- 合理控制授予权限也是mysql dba的一个重要职责
- 如果要创建和root一样的用户要加上WITH GRANT OPTION
- 用户权限
- 只读用户
- 一般开发账号
- 业务库，权限：insert，update，delete，select，call
- 管理用户
- 全局级别，权限：insert，update，delete，create，alter，drop，file，process，shutdown，super
- 权限展示
- 注意以下权限
- file：允许用户指示MySQL服务器在服务器主机文件系统中读取和写入文件
- MySQL5.7需要配合
- 在配置文件中加上secure_file_priv=路径参数
- process：允许用户使用show processlist语句，管理中常用语句
- super：允许用户终止其他客户机连接，或者更改服务器的运行时配置。执行：kill，set，shutdown
- ALL：授予所有特权（但不能向其他用户授予特权）
- grant all ... with grant option 授予所有特权（可以向其他账户授予特权）
- grant语句
- grant语句用于创建或是给现有的用户添加权限
- grant select on world_innodb.* to 'hetan'@'localhost' indentified by 'zhishutang';
- 关键点描述
- 权限列表，多个权限可以用逗号分隔如：insert，update（不区别大小写）
- 授权对象：
- 所有对象，全局：*.*
- 数据库级别:<db_name>.*
- 表级别：<db_name>.<table_name>
- 要创建或是授权的用户：'user_name'@'hostname'
- 密码：可选
- 查看用户权限
- show grants;
- show grants for current_user();
- show grangs for 'root'@'localhost';
- 查看可以授予哪些权限
- 权限控制表
- mysql.user:每个创建的用户都会有一条记录
- mysql.db:限制用户作用于特定的DB
- mysql.tables_priv:用于表级别的权限控制
- mysql.procs_priv:用于存储过程和函数权限控制
- MySQL启动时从mysql库中把权限读取加载到内存中；如果通过DML更新权限表需要借助于：flush privileges;生效
- ==特别提示：不要对权限表进行DML操作==
- 撤销用户权限
- 使用revoke语句撤销对用户的权限
- revoke delete，insert，update，on world_innodb.* from 'zst'@'%';
- revoke all privileges,grant option from 'zst'@'%';
- revoke语法
- revoke关键字：指定要撤销的特权列表
- on子句：指定要撤销特权的级别【全局级时可以不用带】
- from子句：指定账户名称
禁用验证控制
- 场景：密码忘了，升级过程中
- --skip-grant-tables，加到配置文件mysqld中
- 无需用户和密码登录，登录后禁止使用create user，grant，revoke，set，password
- 建议配合：禁止提供网络验证使用
- 禁止提供网络验证使用
- 还有别的方法没?
- 可以将user表复制到另一个数据库中,在表中复制添加一个用户,将密码改成新密码,在将user表考回原数据库,再用kill -HUP `pidof mysqld`重新加载文件,就可以用新用户新密码登录了
MySQL常用client命令及GUI工具推荐
- 重要的mysql客户端程序
- mysql:将SQL语句发送到服务器
- mysqladmin:用于管理MySQL服务器,在shell层次交互
- mysqldump:备份数据库
- mysqlbinlog:解析mysql的binary log以及重放binary log
- 不需要知道:mysqlimport 将文件加载到数据库(有点load data的感觉);mysqlsalp MySQL官方自带的一个简单的压力测试工具
- mysql提示符
- prompt
- 也可以把参数写到mysql可以读到的配置文件中
- /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
- 忘记了可以用这个命令看mysql --help|grep my.cnf
- mysql安全更新
- --safe-update
- 阻止mysql客户端使用没有带where条件的语句
- update和delete仅在包含where子句(该子句通过键值对明确标识了要更新或删除的记录)或limit子句时才允许使用
- 将单表select语句中的输出限制为不超过1000行,但语句包含limit子句时除外
- 仅当MySQL为处理查询所检查的行不超过1000000时,才允许使用多表select语句
- mysqladmin
- DBA的命令行客户机
- 多项功能
- "强制回应(ping)"服务器
- 关闭服务器
- 创建和删除数据库
- 显示服务器和版本信息
- 显示或重置服务器状态变量
- 设置口令
- 重新装入授权表
- 刷新日志文件和高速缓存
- 启动和停止复制
- 显示客户机信息
实战部分
用户名忘了怎么处理?
实际工作中用户名和密码这块需要注意什么?
- 推荐做法
- 应用程序和用户名密码分开存储
- 用户名长度8-16个字符
- 密码长度推荐:16-32个字符,复杂密码策略
- 可能的情况下实现各组配置加密
- 每个业务系统,只能看自己的配置
- 访问别人的配置只能通过API调用
