MySQL用户管理

账户管理的重要性

• 在MySQL中可以通过账户控制允许或不允许用户执行操作
• 可以精细分配权限给不同职能的账户
• 避免使用root账户
  • 将root账户改名，应用不要用root
• 应用不能直接使用root
• 防止维护期出错
• 限制特定权限账户确保数据完整性
• 允许特定授权账户完成期工作
• 阻止未经授权的用户访问超出其特权的数据

理解MySQL连接和查询流程

查询MySQL账户及验证

• 5.7以前
  • select user,host,password from mysql.user;
• 5.7以后
  • select user,host,authentication_string from mysql.user;
• MySQL将使用以下三项进行验证
  • 用户名
  • 密码
  • 客户端主机 -> ip，主机名
• 客户端主机名，注意事项
  • 创建用户名时指定来源主机名
  • 连接数据库时，也可以使用主机名
  • 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
• 用户权限
  • 只读用户
    • 全局，数据库或表级别权限，只用select
  • 一般开发账号
    • 业务库，权限：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';
• 查看可以授予哪些权限
  • show privileges;
• 权限控制表
  • 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
  • 建议配合：禁止提供网络验证使用
  • 禁止提供网络验证使用
    • --skip-networking
  • 还有别的方法没?
    • 可以将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>prompt \u@\h [\d]>
  • 也可以把参数写到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调用