MySQL基础管理01之用户与权限管理

MySQL基础管理01之用户与权限管理

1.用户管理

①用户定义

MySQL用户作用:登录 、管理对象(库、表)
MySQL用户的定义:用户名@'白名单',例如pingface@'localhost'
白名单:允许登录数据库的主机名单
    
常用的: 
    pingface@'localhost'  -- 只允许本机oldboy登录
    pingface@'10.0.0.%'   -- 允许10.0.0.0/24网段,以pingface用户登录数据库
    pingface@'10.0.0.5%'  -- 允许50-59段的地址,以pingface用户登录数据库
    pingface@'10.0.0.0/255.255.254.0' -- 允许10.0.0.0/23网段,以pingface用户登录数据库

不常用:
    pingface@'%'          -- 允许所有网络地址,以pingface用户登录数据库
    pingface@'db02'
    pingface@'pingface.com'

②用户管理

  • 查询
# 查询用户、白名单、密码以及密码设置插件
mysql> select user,host,authentication_string,plugin from mysql.user;

# 查询表定义或表结构
mysql> desc mysql.user;

# 查看帮助
通过 help<关键字> 查看对应关键字的用法,以查看`create user`的用法为例:
mysql> help create user;
Name: 'CREATE USER'
Description:
Syntax:
CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...
    
# 开启日志
mysql> tee /tmp/myoperation.log
Logging to file '/tmp/myoperation.log'
  • 增加用户
# 新增用户test 允许登录10.0.0.0/24网段 密码为123456
mysql> create user IF NOT EXISTS test@'10.0.1.%' identified by '123456';
  • MySQL8.0+版本新特性

(1)必须先建用户,再授权

# 在8.0以前的版本,直接授权等同于创建新用户并且授权;但是8.0以后的版本必须要先创建用户,才能给用户授权!
create user zabbix@'%' identified by '123';
grant all on *.* to zabbix@'%';

(2)采用了全新的密码加密插件

# 8.0+采用了全新的密码加密插件(caching_sha2_password),部分场景下,需要将加密插件替换为5.7版本支持的mysql_native_password类型插件。
# 主从、MHA、客户端软件

# 配置方法: 即创建用户时就指定创建密码的插件类型
create user zabbix@'%' identified  by '123';
grant all on *.* to zabbix@'%';
	替换为: 
create user zabbix2@'%' identified  with mysql_native_password   by '123';
grant all on *.* to zabbix2@'%';
  • 修改

(1)修改密码

alter user root@'10.0.1.%' identified by '123456';

(2)用户锁定

# 锁定用户  相当于禁止用户登录,MySQL一般不删除用户,如果需要删除用户可以使用用户锁定,推荐!
alter user test@'10.0.1.%' ACCOUNT LOCK;

# 解锁
alter user test@'10.0.1.%' ACCOUNT UNLOCK;

(3)密码过期

alter user oldboy@'10.0.1.%' PASSWORD EXPIRE;
  • 删除
# 删除用户推荐使用上述的用户锁定,不推荐直接删除。
drop user test@'10.0.1.%';

2.权限管理

MySQL中的作用是约束、控制用户能够对MySQL中的对象(库、表)做什么样的操作,权限属于用户的属性。对比Linux中的文件权限,是约束、控制用户能够对 Linux 文件 做什么样的操作,Linux文件权限属于文件的属性。MySQL8.0 以前以命令为授权粒度,8.0 以后加入了role(角色)方式授权,但是仍然向下兼容以前的授权方式。可以通过show privileges;命令查看授权列表。其中ALL是比较特殊的权限,是包含了权限列表中除Grant option以外的所有权限。

①授权

使用超级管理员用户root给其他用户授权

  grant  all   on   *.*     to   test@'10.0.1.%'   identified by '123'
# grant  权限   on  权限范围  to    用户                identified by 密码  ;
# 权限范围指的是用户能够操作的对象是哪些,某个库的某张表。

# 例如:授权一个普通用户test@'10.0.0.%',权限为 select 、update、delete、insert,范围:test.*
grant select,update,delete,insert on test.*  to test@'10.0.1.%' identified by '123';

②查看授权

show grants for test@'10.0.1.%';

③回收权限

注意:MySQL不能重复授权,来达到修改权限的目录。是相加关系。

revoke delete on test.* from 'test'@'10.0.1.%' ;
show grants for test@'10.0.1.%';

④MySQL8.0中的角色

MySQL8.0以后的版本增加role角色来管理授权,其流程是首先创建角色,然后给角色授权,然后创建用户,给用户绑定已经授权的角色

  • 创建角色
CREATE ROLE 'app_developer', 'app_read', 'app_write';
  • 角色授权
GRANT ALL ON app_db.* TO 'app_developer';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
  • 创建用户
CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';
CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';
CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';
  • 绑定角色
GRANT 'app_developer' TO 'dev1'@'localhost';
GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';

3.连接管理

①自带的客户端程序连接

客户端连接程序也就是命令,主要有mysqlmysqladminmysqldump

(1)本地连接

本地连接使用的是本地的socket文件来进行连接通信的。使用mysql命令,参数有-u -p -S -h -P -e <

前提:提前创建可以本地连接的用户,例如 : test@'localhost'

grant all on *.* to test@'localhost' identified by '123';
$ mysql -utest -p123 -S /tmp/mysql.sock

# 说明:  什么情况下可以不加-S 参数?
1. 配置文件中 [mysql] 或者 [client]下已经制定socket正确位置。
2. 二进制版本的软件,如果服务器启动时,将socket存放到了/tmp/mysql.sock。

(2)远程登陆

前提:提前创建能够用做远程登录的用户,例如:test1@'10.0.0.%'

grant all on *.* to test1@'10.0.0.%' identified by '123';

# 在另外一台远程主机上使用一下命令远程连接
$ mysql -utest1 -p123 -h 10.0.0.51 -P 3306 

(3)免交互执行MySQL内置命令

$ mysql -uroot -p123 -e "select user,host from mysql.user;" 2>/dev/null

(4)导入SQL脚本

$ mysql -uroot -p123 < /tmp/world.sql

(5)内置功能
这里说的内置功能指的是mysql客户端连接上数据库之后,在 mysql> 命令提示符下可以使用的操作,下面列出部分常用的操作:

  • help:获取客户端可使用的命令;
  • \c:禁止当前输入命令的执行;
  • CTRL + L:清屏;
  • CTRL + D:退出会话;
  • \G:格式化输出查询的结果;
  • source:导入执行一个系统中的 SQL 文件;
  • tee:记录 mysql> 命令提示符下的标准输出到指定文件;
  • notee:取消 tee 的记录功能;
  • status:查看服务器的状态属性信息;
  • connect:重新连接当前 MySQL 服务;
  • system:在 mysql> 命令提示符下执行 shell 命令;
  • use:切换到指定的数据库;
  • help contents:获取可以在服务端使用的命令;

(6)mysqladmin

  • 修改密码
$ mysqladmin -uroot -p123 password 123456
  • 关闭数据库
$ mysqladmin -uroot -p123456  shutdown 
  • 刷新日志(二进制日志)
$ mysqladmin -uroot -p123 flush-logs

②第三方开发工具

  • sqlyog
  • navicat
  • workbench