mysql 角色与权限管理_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.连接管理

①自带的客户端程序连接

客户端连接程序也就是命令，主要有mysql、mysqladmin、mysqldump。

(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