DBA专栏
01-MySQL基础入门
02-MySQL基础入门-部署与管理体系
03-MySQL-基础入门-用户与权限
04-MySQL基础入门-索引
相信可能就有无限可能
-
+
首页
03-MySQL-基础入门-用户与权限
## 1.MySQL用户介绍 用户功能 ```sql 登录数据库 管理数据库对象 ``` 用户的组成 ```sql 用户名@'白名单';比如 test@127.0.0.1 test@192.168.10.10是两个不一样的用户 #存在哪里?mysql.user即用户权限表 select user,host from mysql.user; #用户分类,主要体现在白名单上 地址列表粒度: - 所有网段: 'dbadmin'@% - 指定网段: 'dbadmin'@'10.%' or '192.168.%' or '172.21.120.%' - 具体IP: 'dbadmin'@'192.168.120.21' - 主机名: 'dbamdin'@localhost ``` 用户创建 - 通过create user语句创建用户 - 在执行CREATE USER或CRANT语句后,MySQL服务器会修改相应的用户权限表,添加或修改用户及权限。 ```sql #8.0版本 create user saos_damin@'%' identified by '3edfud'; grant all privileges on *.* to 'saos_damin'@'%' WITH GRANT OPTION; #赋予超级权限 WITH GRANT OPTION 表示可以创建用户的权限。 #5.7版本 grant all privileges on *.* to 'saos_damin'@'%' identified by '3edfud' ; ``` ## 2.MySQL的权限分类及存储 ### 2.1.MySQL用户权限层级 - 全局权限:对单个MySQL实例有所有库表的访问权限,一般为dba所有 ```sql # *.* 表示数据库库的所有库和表,ALL对应所有权限,存储在mysql.user表中 GRANT ALL ON *.* TO 'user'@'host'; ``` - **数据库层级**:数据库权限适用于一个给定数据库中的所有目标,这些权限存储在mysql.db表中。 ```sql GRANT ALL ON test.* TO 'user'@'host'; #对 test库下的所有具有所有权限 ``` - **表层级**: 表权限适用于一个给定表中的所有列,存储 mysql.tables_priv表中。 ```sql GRANT ALL ON mydb.mytable TO 'user'@'host'; #对mydb库的mytable表具有所有权限 ``` - **列层级**:列权限使用于一个给定表中的单一列,这些权限存储在mysql.columns_priv表中。 大家知道就行,很少使用 ```sql # test数据库下的mytable表的columne列col1, col2, col3具有所有权限,很少使用 GRANT ALL (col1, col2, col3) ON test.mytable TO 'dbmadin'@'127.0.0.1'; #指定权限赋予 grant select(id,name),update(age) on test.mytable to 'dbmadin'@'localhost'; ``` ### 2.2.MySQL权限分类-静态权限与动态权限 > https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_select - 静态权限: - built in to the server,在mysql实例中设置单一权限 - 通过 grant and revoke进行权限授予 or 回收 - 动态权限:defined at runtime,运行时候定义,通过组件or插件使用才有 #### 2.2.1静态权限: - **数据权限**:库、表和字段三种级别 ```sql 1.select: -查询库表权限范围内的数据通过sql语句 2.insert: - 数据通过sql语句插入权限范围内的表 - ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE 也需要insert权限 3.update: - 更新库表数据 4.DELETE: - 删除表数据 5.File: - LOAD DATA and SELECT ..INTO OUTFILE语句权限 - 可以在mysql实例读写文件 ``` - **管理权限**:DBA使用权限,比如:开关机,创建用户,主从连接 ```sql 1.CREATE - 创建库表权限 2.alter - DDL权限 更改库表结构 or 索引 3.INDEX - 创建 or 删除索引权限 4.DROP - 删库 表 表分区 视图权限 5.SHOW VIEW:查看视图权限 ``` - 结构与程序权限:主要是触发器、存储过程、函数以及对表结构更改权限。 ```sql GRANT:权限授予权限 SUPER:超级权限 SET GLOBAL 参数等 ``` ![image-20240315131122532](https://img.sunrisenan.com/img/2024/03/15/131124445.png) - 注释 - Global(Server administration)对应 mysql.user 表 - Database 对应 mysql.db 表 - Tables 对应 mysql.tables_priv 表 - Columns 对应 mysql.columns_priv 表 - Stored routines 对应 mysql.procs_priv 表 #### 2.2.2.动态权限 ### 2.3.MySQL访问控制 MySQL访问控制的两个阶段: - MySQL权限控制主要是集中在5张表中,user,db,tables_priv,columns_priv,procs_priv每次启动会直接加载到内存中 - 用户连接检查阶段 - a. 用户连接时,MySQ server首先从user表里匹配host, user, password,匹配不到则拒绝 Mysql client的连接 - b. check mysql.user表的max_connections和max_user_connections,如果超过上限则拒 绝连接 - c. check user表的SSL安全连接,配置SSL,检查用户证书 - d. 三个检查通过,MySQL server端与MySQL client端建立connection,连接建立后,当用户执行SQL语句时,执行SQL语句检查。 - 执行SQL语句时的检查 - e. 从user表里检查max_questions和max_updates,超过则拒绝执行SQL。 - f. 检查user表,看是否具有相应的全局性权限,如果有,则执行,没有则继续下一步检查 - g. 接着到db表,看是否具有数据库级别的权限,如果有,则执行,没有则继续下一步检查 - h. 最后到tables_priv, columns_priv, procs_priv表里查看是否具有相应对象的权限 ### 2.4.权限表字段详解 - user表 - 基于服务器范围的所有权限, - 用户拥有服务器中所有数据库的select权限,user表中的Select_priv列为Y - 如果用户单单只拥有某个一数据库的select权限那么user表中的Select_priv为N - 会在DB表中记录一条信息在DB表中的select_priv为Y。 ```sql #只对saos_test库有select权限 create user saos_test@'%' identified by 'test123'; grant select on *.* to 'saos_test'@'%'; #看下权限,是否在user 中只Select_priv Y select * from mysql.user where user='saos_test'; #库级别权限,user priv全为N create user saos_db@'%' identified by 'test123'; grant select on saos_db.* to 'saos_db'@'%'; select * from mysql.user where user='saos_db'\G select * from mysql.db where user='saos_db'\G ``` MySQL 在安装时会自动创建一个名为 mysql 的数据库,mysql 数据库中存储的都是用户权限表。用户登录以后,MySQL 会根据这些权限表的内容为每个用户赋予相应的权限。 user 表是 MySQL 中最重要的一个权限表,用来记录允许连接到服务器的账号信息。需要注意的是,在 user 表里启用的所有权限都是全局级的,适用于所有数据库。 user 表中的字段大致可以分为 4 类,分别是用户列、权限列、安全列和资源控制列,下面主要介绍这些字段的含义。 **用户列** 用户列存储了用户连接 MySQL 数据库时需要输入的信息。需要注意的是 MySQL 5.7 版本不再使用 Password 来作为密码的字段,而改成了 authentication_string。 MySQL 5.7 版本的用户列如表 1 所示。 | 字段名 | 字段类型 | 是否为空 | 默认值 | 说明 | | --------------------- | -------- | -------- | ------ | ------ | | Host | char(60) | NO | 无 | 主机名 | | User | char(32) | NO | 无 | 用户名 | | authentication_string | text | YES | 无 | 密码 | 用户登录时,如果这 3 个字段同时匹配,MySQL 数据库系统才会允许其登录。创建新用户时,也是设置这 3 个字段的值。修改用户密码时,实际就是修改 user 表的 authentication_string 字段的值。因此,这 3 个字段决定了用户能否登录。 **权限列** 权限列的字段决定了用户的权限,用来描述在全局范围内允许对数据和数据库进行的操作。 权限大致分为两大类,分别是高级管理权限和普通权限: - 高级管理权限主要对数据库进行管理,例如关闭服务的权限、超级权限和加载用户等; - 普通权限主要操作数据库,例如查询权限、修改权限等。 user 表的权限列包括 Select_priv、Insert_ priv 等以 priv 结尾的字段,这些字段值的数据类型为 ENUM,可取的值只有 Y 和 N:Y 表示该用户有对应的权限,N 表示该用户没有对应的权限。从安全角度考虑,这些字段的默认值都为 N。 | 字段名 | 字段类型 | 是否为空 | 默认值 | 说明 | | ---------------------- | ------------- | -------- | ------ | ------------------------------------------------------------ | | Select_priv | enum('N','Y') | NO | N | 是否可以通过SELECT 命令查询数据 | | Insert_priv | enum('N','Y') | NO | N | 是否可以通过 INSERT 命令插入数据 | | Update_priv | enum('N','Y') | NO | N | 是否可以通过UPDATE 命令修改现有数据 | | Delete_priv | enum('N','Y') | NO | N | 是否可以通过DELETE 命令删除现有数据 | | Create_priv | enum('N','Y') | NO | N | 是否可以创建新的数据库和表 | | Drop_priv | enum('N','Y') | NO | N | 是否可以删除现有数据库和表 | | Reload_priv | enum('N','Y') | NO | N | 是否可以执行刷新和重新加载MySQL所用的各种内部缓存的特定命令,包括日志、权限、主机、查询和表 | | Shutdown_priv | enum('N','Y') | NO | N | 是否可以关闭MySQL服务器。将此权限提供给root账户之外的任何用户时,都应当非常谨慎 | | Process_priv | enum('N','Y') | NO | N | 是否可以通过SHOW PROCESSLIST命令查看其他用户的进程 | | File_priv | enum('N','Y') | NO | N | 是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令 | | Grant_priv | enum('N','Y') | NO | N | 是否可以将自己的权限再授予其他用户 | | References_priv | enum('N','Y') | NO | N | 是否可以创建外键约束 | | Index_priv | enum('N','Y') | NO | N | 是否可以对索引进行增删查 | | Alter_priv | enum('N','Y') | NO | N | 是否可以重命名和修改表结构 | | Show_db_priv | enum('N','Y') | NO | N | 是否可以查看服务器上所有数据库的名字,包括用户拥有足够访问权限的数据库 | | Super_priv | enum('N','Y') | NO | N | 是否可以执行某些强大的管理功能,例如通过KILL命令删除用户进程;使用SET GLOBAL命令修改全局MySQL变量,执行关于复制和日志的各种命令。(超级权限) | | Create_tmp_table_priv | enum('N','Y') | NO | N | 是否可以创建临时表 | | Lock_tables_priv | enum('N','Y') | NO | N | 是否可以使用LOCK TABLES命令阻止对表的访问/修改 | | Execute_priv | enum('N','Y') | NO | N | 是否可以执行存储过程 | | Repl_slave_priv | enum('N','Y') | NO | N | 是否可以读取用于维护复制数据库环境的二进制日志文件 | | Repl_client_priv | enum('N','Y') | NO | N | 是否可以确定复制从服务器和主服务器的位置 | | Create_view_priv | enum('N','Y') | NO | N | 是否可以创建视图 | | Show_view_priv | enum('N','Y') | NO | N | 是否可以查看视图 | | Create_routine_priv | enum('N','Y') | NO | N | 是否可以更改或放弃存储过程和函数 | | Alter_routine_priv | enum('N','Y') | NO | N | 是否可以修改或删除存储函数及函数 | | Create_user_priv | enum('N','Y') | NO | N | 是否可以执行CREATE USER命令,这个命令用于创建新的MySQL账户 | | Event_priv | enum('N','Y') | NO | N | 是否可以创建、修改和删除事件 | | Trigger_priv | enum('N','Y') | NO | N | 是否可以创建和删除触发器 | | Create_tablespace_priv | enum('N','Y') | NO | N | 是否可以创建表空间 | 如果要修改权限,可以使用 GRANT 语句为用户赋予一些权限,也可以通过 UPDATE 语句更新 user 表的方式来设置权限。 **安全列** 安全列主要用来判断用户是否能够登录成功,user 表中的安全列如表 3 所示: | 字段名 | 字段类型 | 是否为空 | 默认值 | 说明 | | --------------------- | --------------------------------- | -------- | --------------------- | ------------------------------------------------------------ | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | 支持ssl标准加密安全字段 | | ssl_cipher | blob | NO | | 支持ssl标准加密安全字段 | | x509_issuer | blob | NO | | 支持x509标准字段 | | x509_subject | blob | NO | | 支持x509标准字段 | | plugin | char(64) | NO | mysql_native_password | 引入plugins以进行用户连接时的密码验证,plugin创建外部/代理用户 | | password_expired | enum('N','Y') | NO | N | 密码是否过期 (N 未过期,y 已过期) | | password_last_changed | timestamp | YES | | 记录密码最近修改的时间 | | password_lifetime | smallint(5) unsigned | YES | | 设置密码的有效时间,单位为天数 | | account_locked | enum('N','Y') | NO | N | 用户是否被锁定(Y 锁定,N 未锁定) | 注意:即使 password_expired 为“Y”,用户也可以使用密码登录 MySQL,但是不允许做任何操作。 通常标准的发行版不支持 ssl,读者可以使用 SHOW VARIABLES LIKE "have_openssl" 语句来查看是否具有 ssl 功能。如果 have_openssl 的值为 DISABLED,那么则不支持 ssl 加密功能。 **资源控制列** 资源控制列的字段用来限制用户使用的资源,user 表中的资源控制列如表 4 所示。 | 字段名 | 字段类型 | 是否为空 | 默认值 | 说明 | | -------------------- | ---------------- | -------- | ------ | -------------------------------- | | max_questions | int(11) unsigned | NO | 0 | 规定每小时允许执行查询的操作次数 | | max_updates | int(11) unsigned | NO | 0 | 规定每小时允许执行更新的操作次数 | | max_connections | int(11) unsigned | NO | 0 | 规定每小时允许执行的连接操作次数 | | max_user_connections | int(11) unsigned | NO | 0 | 规定允许同时建立的连接次数 | 以上字段的默认值为 0,表示没有限制。一个小时内用户查询或者连接数量超过资源控制限制,用户将被锁定,直到下一个小时才可以在此执行对应的操作。可以使用 GRANT 语句更新这些字段的值。 - db表:如果授予一个用户单独某个数据库的权限,就会在db表中记录一条相关信息 - tables_priv表 - columns_priv表 - procs_priv表 ## 3.用户管理 ### 3.1用户创建 - 用户创建create user 语句 记录在响应权限表 ```sql #1.正常形式 create user saos_damin@'%' identified by '3edfud'; grant all privileges on *.* to 'saos_damin'@'%' WITH GRANT OPTION; ; #赋 予超级权限 #2.隐式创建 grant all privileges on *.* to 'saos_damin'@'%' identified by '3edfud' ; #3.查看用户权限 mysql> show grants for 'saos_damin'@'%'; ``` - root 用户创建 ```sql mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'root' WITH GRANT OPTION; #user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中加载到内存 里,不然其他session无法连接。 mysql> flush privileges; ``` - DML用户创建 ```sql mysql> GRANT UPDATE, DELETE, INSERT, SELECT ON *.* TO 'test'@'%' identified by 'test' ; mysql> flush privileges; # MAX_QUERIES_PER_HOUR, # MAX_CONNECTIONS_PER_HOUR # MAX_UPDATES_PER_HOUR # MAX_USER_CONNECTIONS count 单个用户过多连接数 ## 设置为0表示不限制,一般外包人员设置情况多 ``` - 备份用户 ```sql mysql> GRANT SELECT,EVENT,SHOW DATABASES,LOCK TABLES,SUPER,REPLICATION CLI ENT ON *.* TO 'backup'@'localhost' identified by 'backup'; mysql> flush privileges; ``` - 备份恢复用户权限 ```sql mysql> GRANT INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO 'restore'@'localhost' identified by '123456'; mysql> flush privileges; ``` - 复制用户 ```sql mysql> GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED BY '123456'; mysql> flush privileges; ``` ### 3.2.用户删除(生产环境注意事项) - 生产中非常非常需要注意的谨慎操作,极其容易造成失误,一定要准备好回滚语句和检查网段权限后在操作. - 操作后一定要double check ```sql #操作前检查用户是否有连接,分析连接池 select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip; #删除普通用户 mysql> drop user 'USERNAME'@'HOST'; #操作后,看下用户删除是否正确,尤其是网段 select user,host from mysql.user; # 删除MySQL默认账户; mysql> drop user 'root'@'127.0.0.1'; ``` ### 3.3.修改用户密码 - 通过mysqladmin工具 ```sql # 给root@localhost用户登录mysql设置密码为"redhat"; shell> mysqladmin -uqianlong -ptest123 -h127.0.0.1 password "123456"; ``` - 通过直接修改mysql.user表的用户记录,5.7版本 ```sql # MySQL 5.7 mysql> update mysql.user set authentication_string=PASSWORD('test123') whe re user='qianlong'; mysql> flush privileges; ``` - set password语句 5.7版本 ```sql #5.7 mysql> set password for 'qianlong'@'localhost'=PASSWORD('test123'); mysql> flush privileges; ``` - 8.0 密码修改 ```sql alter user 'qianlong'@'localhost' identified with mysql_native_password by 'test123'; ``` ### 3.4.密码找回(课件小练习) ```sql #1.修改配置文件 shell>cat /etc/my.cnf [mysqld] --skip-networking --skip-grant-tables #2.重启MySQL Server #3.设置root用户密码 mysql> flush privileges; mysql> alter user root@'localhost' identified by '123456'; #4.重启MySQL Server ``` ### 3.5.用户权限收回 > 使用REVOKE收回权限之后,用户帐户的记录将从db、host、tables_priv、columns_priv表中删除,但是用户帐号依然在user表中保存。 ```sql # 移除saos_test用户对于saos_ab.*的权限; Mysql> revoke all on saos_ab.* from 'saos_test'@'%'; # 刷新授权表; Mysql> flush privileges; ``` ### 3.6.权限练习实战 ```sql 1. grant 所有权限 mysql> grant all privileges on *.* to 'db_test'@'192.168.%'; mysql> flush privileges; 2. grant super权限在*.*上(super权限可以对全局变量更改); mysql> grant super on *.* to 'db_test'@'192.168.%'; mysql> flush privileges; 3. grant某个库下所有表的所有权限 mysql> grant all privileges on DB_NAME.* to 'db_test'@'192.168.%'; mysql> flush privileges; 4. grant某个库下所有表的select权限 mysql>grant select on DB_NAME.* to 'db_test'@'192.168.%'; mysql> flush privileges; 5. grant某个库下某个表的insert权限 mysql> grant insert on DB_NAME.TABLE_NAME to 'db_test'@'192.168.%''; mysql> flush privileges; 6. grant某个库下某个表的update权限 mysql>grant update on DB_NAME.TABLE_NAME to 'db_test'@'192.168.%'; mysql> flush privileges; 7. grant某个库下某个表的某个字段update权限 mysql> grant update(COLUMN_NAME) on DB_NAME.TABLE_NAME to 'db_test'@'192.168.%'; mysql> flush privileges; 8.通过GRANT语句中的USAGE权限,可以创建账户而不授予任何权限 mysql> grant usage on *.* to 'db_test'@'192.168.%'; mysql> flush privileges; 9. grant创建、修改、删除MySQL数据表结构权限 mysql> grant create on testdb.* to developer@'192.168.0.%'; mysql> grant alter on testdb.* to developer@'192.168.0.%'; mysql> grant drop on testdb.* to developer@'192.168.0.%'; mysql> flush privileges; 10. grant操作MySQL外键权限 mysql> grant references on testdb.* to developer@'192.168.0.%'; mysql> flush privileges; 11. grant操作MySQL临时表权限 mysql> grant create temporary tables on testdb.* to developer@'192.168.0.%'; mysql> flush privileges; 12. grant操作MySQL索引权限 mysql> grant index on testdb.* to developer@'192.168.0.%'; mysql> flush privileges; 13.grant操作MySQL视图、查看视图源代码权限 mysql> grant create view on testdb.* to developer@'192.168.0.%'; mysql> grant show view on testdb.* to developer@'192.168.0.%'; mysql> flush privileges; 14. grant操作MySQL存储过程、存储函数权限 mysql> grant create routine on testdb.* to developer@'192.168.0.%'; mysql> grant alter routine on testdb.* to developer@'192.168.0.%'; mysql> grant execute on testdb.* to developer@'192.168.0.%'; mysql> flush privileges; 16.生命周期用户权限 ALTER USER 'qianlong'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY; 17.用户锁定,锁定后登入错误 提示Account is locked. ALTER USER 'qianlong'@'localhost' ACCOUNT LOCK; 15.PROXY特殊权限如果想让某个用户具有给他人赋予权限的能力,那么就需要proxy权限了。当你给一个用户赋予all权限之后,你查看mysql.user表会发现Grant_priv字段还是为N,表示其没有给他人赋予权限的权限。查看一下系统默认的超级管理员权限 mysql> show grants for 'root'@'localhost'; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec) 可以看到其本身有PROXY权限,并且这个语句跟一般授权语句还不太一样。所以如果想让一个远程用户有给他人赋予权限的能力,就需要给此用户PROXY权限,如下: mysql> grant all on *.* to 'test'@'%' identified by 'helloWORD'; mysql> GRANT PROXY ON ''@'' TO 'test'@'%' WITH GRANT OPTION; mysql> flush privileges; ``` ## 4、用户授权原则 ### 4.1 最小化权限 ```sql #例如 如果是业务 申请权限 那么就是 create user saos_damin@'%' identified by '3edfud'; grant select,insert,update,delete on *.* to 'saos_damin'@'%'; #如果是DBA自己的账户 create user oldboy_dba@'%' identified by '3edfud'; grant drop,index,alter,super,create,select,insert,update,delete,trigger,process,execute,show view,Create View,Create User,Create Role,show databaseson *.* to 'oldboy_dba'@'%'; #dba 账户基本够用 ``` ### 4.2 密码安全 密码要符合 长度、大小写数字混合、特殊字符等 分享一个小命令 ```sql $ mkpasswd -l 16 -d 4 -s 0 zmzpO4wx4acJz87t -c 定义在密码中小写字母字符的最小数目,默认值是2 -C 定义在密码中大写字母字符的最小数目,默认值是2 -s 定义在密码中特殊字符的最小数目,默认值是1 如果设置为1 则有特殊字符 -p 指定程序来设置密码。默认情况下,如果存在使用/etc/yppasswd,否则使用/bin/passwd -d 定义密码的最小数目,默认值是2 -l 定义口令的长度,默认值为9 -v 导致密码设置互动可见 ``` ### 4.3 最小化网络授权 ```sql 上面讲过: 用户名@'白名单';比如 test@127.0.0.1 test@192.168.10.10是两个不一样的用户 那么 test@192.168.10.% 和test@192.168.10.10 是一个用户嘛? 不是的 这属于2个用户 如果ip是 192.168.10.10 就会走 test@192.168.10.10 用户的权限。 如果ip 是 192.168.10.20 就会走 test@192.168.10.% 用户权限。 在网络授权中,可以按照实际情况分配。 dba 账户 只授权堡垒机的ip 业务ip 只授权 test@192.168.10.1 这样 如果业务ip 有 100个 建立100个 test@192.168.10.1 用户。 这么创建用户有哪些好处 1、安全,即使业务把账户泄漏了 那么必须登录到业务的服务器 才来链接到mysql。而不是 在内网某个网段就可以登录。 2、方便排查问题。 很方便统计出来 那个用户那个ip 访问量比较多。 主要是为了安全考虑。 ```
李延召
2024年3月15日 16:47
转发文档
收藏文档
上一篇
下一篇
手机扫码
复制链接
手机扫一扫转发分享
复制链接
Markdown文件
分享
链接
类型
密码
更新密码