MySQL的权限系统

一、MySQL权限系统

一）MySQL权限系统介绍

权限系统的作用：授予来自某个主机的某个用户可以查询、插入、修改、删除等数据库操作的权限
不能明确指定拒绝某个用户的连接
权限控制（授权与回收）的执行语句包括create user，grant，revoke
授权后的权限都会存放在MySQL的内部数据库（MySQL）中，并在数据库启动之后把权限信息复制到内存中
MySQL用户的认证信息不光包括用户名，还要包括发起的主机

SHOW GRANTS FOR ‘joe’@‘office.host.com’;
SHOW GRANTS FOR ‘joe’@‘home.host.com’;

查看mysql实例默认root用户的权限(来自localhost)
All/All Privileges权限代表全局或全数据库对象级别的所有权限
Alter权限代表允许修改表结构的权限，但必须要求有create和insert权限配合。如果是rename表名，则要求有alter和drop原表， create和insert新表的权限
alter routine权限代表允许修改或删除存储过程、函数的权限
Create权限代表允许创建新的数据库和表的权限

Create routine权限代表允许创建存储过程、函数的权限
Create user权限代表允许创建、修改、删除、重命名user的权限
create view权限代表允许创建视图的权限

delete权限代表允许删除行数据的权限
drop权限代表允许删除数据库、表、视图的权限，包括truncate table命令
Event权限代表允许查询，创建，修改，删除MySQL事件
Execute权限代表允许执行存储过程和函数的权限
Grant option权限代表是否允许此用户授权或者收回给其他用户你给予的权限
index权限代表是否允许创建和删除索引
Insert权限代表是否允许在表里插入数据，同时在执行analyze table,optimizetable,repair table语句的时候也需要insert权限
Reference权限是在5.7.6版本之后引入，代表是否允许创建外键 Select权限代表允许从表中查看数据，某些不查询表数据的select执行则不需要此权限，如Select 1+1， Select PI()+2；而且select权限在执行update/delete语句中含有where条件的情况下也是需要的
Show databases权限代表通过执行show databases命令查看所有的数据库名
Show view权限代表通过执行show create view命令查看视图创建的语句
Shutdown权限代表允许关闭数据库实例，执行语句包括mysqladmin shutdown
Super权限代表允许执行一系列数据库管理命令，包括kill强制关闭某个连接命令， change master to创建复制关系命令，以及create/alter/drop server等命令
Trigger权限代表允许创建，删除，执行，显示触发器的权限
Update权限代表允许修改表中的数据的权限
Usage权限是创建一个用户之后的默认权限，其本身代表连接登录权限

create user testuser@localhost;
show grants for testuer@localhost;

二）系统权限表

　　1、权限存储位置

　　权限存储在mysql库的user,db, tables_priv, columns_priv, andprocs_priv这几个系统表中，待MySQL实例启动后就加载到内存中

User表：存放用户账户信息以及全局级别（所有数据库）权限，决定了来自哪些主机的哪些用户可以访问数据库实例，如果有全局权限则意味着对所有数据库都有此权限
Db表：存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库
Tables_priv表：存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的这个表
Columns_priv表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的这个字段
Procs_priv表：存放存储过程和函数级别的权限

　　2、User权限表结构中的特殊字段

Plugin,password,authentication_string三个字段存放用户认证信息
Password_expired设置成’Y’则表明允许DBA将此用户的密码设置成过期而且过期后要求用户的使用者重置密码（alter user/set password重置密码）
Password_last_changed作为一个时间戳字段代表密码上次修改时间，执行create user/alter user/set password/grant等命令创建用户或修改用户密码时此数值自动更新
Password_lifetime代表从password_last_changed时间开始此密码过期的天数
Account_locked代表此用户被锁住，无法使用

　　3、Tables_priv和columns_priv权限值

　　4、procs_priv权限表结构

Routine_type是枚举类型，代表是存储过程还是函数
Timestamp和grantor两个字段暂时没用

　　5、权限认证中的大小写敏感问题

字段user,password,authencation_string,db,table_name大小写敏感
字段host,column_name,routine_name大小写不敏感

三）MySQL授权用户

　　1、MySQL的授权用户由两部分组成：用户名和登录主机名

表达用户的语法为‘user_name’@‘host_name’
单引号不是必须，但如果其中包含特殊字符则是必须的
‘’@‘localhost’代表匿名登录的用户
Host_name可以使主机名或者ipv4/ipv6的地址。 Localhost代表本机， 127.0.0.1代表ipv4的本机地址， ::1代表ipv6的本机地址
Host_name字段允许使用%和_两个匹配字符，比如’%’代表所有主机， ’%.mysql.com’代表来自mysql.com这个域名下的所有主机， ‘192.168.1.%’代表所有来自192.168.1网段的主机

　　2、MySQL修改权限的生效

执行Grant,revoke,set password,rename user命令修改权限之后， MySQL会自动将修改后的权限信息同步加载到系统内存中
如果执行insert/update/delete操作上述的系统权限表之后，则必须再执行刷新权限命令才能同步到系统内存中，刷新权限命令包括： flush privileges/mysqladmin flush-privileges/mysqladmin reload
如果是修改tables和columns级别的权限，则客户端的下次操作新权限就会生效
如果是修改database级别的权限，则新权限在客户端执行use database命令后生效
如果是修改global级别的权限，则需要重新创建连接新权限才能生效
--skip-grant-tables可以跳过所有系统权限表而允许所有用户登录，只在特殊情况下暂时使用

　　3、用户管理

# 创建用户
    create user '用户名'@'IP地址' identified by '密码';
# 删除用户
    drop user '用户名'@'IP地址';
# 修改用户
    rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;
# 修改密码
    set password for '用户名'@'IP地址' = Password('新密码')
  
PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）

# 查看当前用户
        select user();
# 查看所有用户
        select host,user from mysql.user;
# 人性化显示所有用户
        SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
# 查看用户的所有权限
        show grants for 'nick'@'%';

　　4、设置MySQL用户的密码

　　创建用户时设置密码

create user 'username'@'%' identified by 'password';

　　修改其他用户密码的方式

###登录服务器修改密码
方法一
ALTER USER 'username'@'localhost' IDENTIFIED BY 'mypass';
方法二
SET PASSWORD FOR 'username'@'localhost' = PASSWORD('mypass');
方法三
GRANT USAGE ON *.* TO 'username'@'localhost' IDENTIFIED BY 'mypass';

###在命令行修改密码
方法四
mysqladmin -u username -h host_name password "new_password";

　　修改本身用户密码的方式

方法一
ALTER USER USER() IDENTIFIED BY 'mypass';

方法二
SET PASSWORD = PASSWORD('mypass');

四）设置MySQL用户密码过期策略

设置系统参数default_password_lifetime作用于所有的用户账户
- default_password_lifetime=180 设置180天过期
- default_password_lifetime=0 设置密码不过期
如果为每个用户设置了密码过期策略，则会覆盖上述系统参数
- ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
- ALTER USER ‘jeffrey’@‘localhost’ PASSWORD EXPIRE NEVER; 密码不过期
- ALTER USER ‘jeffrey’@‘localhost’ PASSWORD EXPIRE DEFAULT; 默认过期策略
手动强制某个用户密码过期
- ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;

五）MySQL用户lock

通过执行create user/alter user命令中带account lock/unlock子句设置用户的lock状态
    Create user语句默认的用户是unlock状态
        mysql> create user abc2@localhost identified by 'mysql' account lock;
        Query OK, 0 rows affected (0.01 sec)
Alter user语句默认不会修改用户的lock/unlock状态
        mysql> alter user 'mysql.sys'@localhost account lock;
        Query OK, 0 rows affected (0.00 sec)
        mysql> alter user 'mysql.sys'@localhost account unlock;
        Query OK, 0 rows affected (0.00 sec)
当客户端使用lock状态的用户登录MySQL时，会收到如此报错
        Access denied for user 'user_name'@'host_name'.
        Account is locked.

六）企业应用中的常规MySQL用户

企业生产系统中MySQL用户的创建通常由DBA统一协调创建，而且按需创建
    • DBA通常直接使用root用户来管理数据库
    • 通常会创建指定业务数据库上的增删改查、临时表、执行存储过程的权限给应用程序来连接数据库
        • Create user app_full identified by ‘mysql’;
        • Grant select,update,insert,delete,create temporary tables,execute on esn.* toapp_full@’10.0.0.%’;
        • mysql> show grants for app_full@'10.0.0.%';
        • +------------------------------------------------------------------------------------------------------------+
        • | Grants for app_full@10.0.0.% |
        • +------------------------------------------------------------------------------------------------------------+
        • | GRANT USAGE ON *.* TO 'app_full'@'10.0.0.%' |
        • | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE ON `esn`.*TO 'app_full'@'10.0.0.%' |
    • 通常也会创建指定业务数据库上的只读权限给特定应用程序或某些高级别人员来查询数据，防止数据被修改
        • Create user app_readonly identified by ‘mysql’;
        • Grant select on esn.* to app_readonly identified by ‘mysq’;

七）企业应用中的MySQL用户密码设定

企业生产系统中MySQL用户的密码设定有严格的规范，通常要有密码复杂度、密码长度等要求
搜索网上的密码生成器，能按要求生成随机密码

　　https://suijimimashengcheng.51240.com/

二、MySQL授权、回收

一）MySQL授权

　　MySQL 赋予用户权限命令的简单格式可概括为：grant 权限 on 数据库对象 to 用户

　　1、grant 普通数据用户，查询、插入、更新、删除数据库中所有表数据的权利

grant select, insert, update, delete on testdb.* to common_user@'%';

　　2、grant 数据库开发人员，创建表、索引、视图、存储过程、函数。。。等权限

grant create, alter, drop   on testdb.* to developer@'192.168.0.%' ;

　　grant 操作 MySQL 外键权限

grant references on testdb.* to developer@'192.168.0.%' ;

　　grant 操作 MySQL 临时表权限

grant create temporary tables on testdb.* to developer@'192.168.0.%' ;

　　grant 操作 MySQL 索引权限

grant index on  testdb.* to developer@'192.168.0.%' ;

　　grant 操作 MySQL 视图、查看视图源代码权限

grant create view on testdb.* to developer@'192.168.0.%' ; 
grant show   view on testdb.* to developer@'192.168.0.%' ;

　　grant 操作 MySQL 存储过程、函数权限

grant create routine on testdb.* to developer@'192.168.0.%' ; 
grant alter  routine on testdb.* to developer@'192.168.0.%' ; 
grant execute        on testdb.* to developer@'192.168.0.%' ;

　　3、grant 普通 DBA 管理某个 MySQL 数据库的权限

grant all privileges on testdb to dba@'localhost' ;

其中，关键字 “privileges” 可以省略。

　　4、grant 高级 DBA 管理 MySQL 中所有数据库的权限

grant all on *.* to dba@'localhost' ;

　　5、MySQL grant 权限，分别可以作用在多个层次上

　　　　grant 作用在整个 MySQL 服务器上：

grant select on *.* to dba@localhost ; -- dba 可以查询 MySQL 中所有数据库中的表。 
grant all    on *.* to dba@localhost ; -- dba 可以管理 MySQL 中的所有数据库

　　　　grant 作用在单个数据库上

grant select on testdb.* to dba@localhost ; -- dba 可以查询 testdb 中的表

　　　　 grant 作用在单个数据表上

grant select, insert, update, delete on testdb.orders to dba@localhost ;

　　　　grant 作用在表中的列上：

grant select(id, se, rank) on testdb.apache_log to dba@localhost ;

　　　　grant 作用在存储过程、函数上：

grant execute on procedure testdb.pr_add to 'dba'@'localhost' ;
grant execute on function  testdb.fn_add to 'dba'@'localhost' ;

二）查看、回收授权

　　1、查看用户授权情况

show grants for 'username'@'%'；
show create user 'username'@'%'\G

　　2、回收权限

revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：

revoke select on `sys`.`sys_config` from 'mysql.sys'@localhost;

三）MySQL grant、revoke 用户权限注意事项

grant, revoke 用户权限后，FLUSH PRIVILEGES; 这样权限才会生效；该用户只有重新连接 MySQL 数据库，权限才能生效。
如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 “grant option“
grant select on testdb.* to dba@localhost with grant option; 这个特性一般用不到。实际中，数据库权限最好由 DBA 来统一管理。

四）给指定前缀的库或表授权

　　1、给指定前缀的库授权

　　举例：对以"db_1"开头的数据库进行授权

grant all privileges on `db_1%`.* to dp_admin identified by 'password';
grant select on `db_1%`.* to dp_admin identified by 'password';

　　2、给指定前缀的表授权

　　通过下面的语句获取到授权列表

SELECT
    CONCAT( 'GRANT SELECT ON db1.', TABLE_NAME, ' to ''devops-admin'';' ) 
FROM
    INFORMATION_SCHEMA.TABLES 
WHERE
    TABLE_SCHEMA = 'db1' 
    AND TABLE_NAME LIKE 'table1_%';

　　获取具体的授权语句，然后执行

GRANT SELECT ON db1.table1-202201 to 'devops-admin';
GRANT SELECT ON db1.table1-202202 to 'devops-admin';
GRANT SELECT ON db1.table1-202203 to 'devops-admin';

posted @ 2020-10-30 23:28 思维无界限阅读(601) 评论(0) 编辑收藏举报

刷新页面返回顶部

思维无界限

MySQL的权限系统

一、MySQL权限系统

一）MySQL权限系统介绍

二）系统权限表

1、权限存储位置

2、User权限表结构中的特殊字段

3、Tables_priv和columns_priv权限值

4、procs_priv权限表结构

5、权限认证中的大小写敏感问题

三）MySQL授权用户

1、MySQL的授权用户由两部分组成： 用户名和登录主机名

2、MySQL修改权限的生效

3、用户管理

4、设置MySQL用户的密码

四）设置MySQL用户密码过期策略

五）MySQL用户lock

六）企业应用中的常规MySQL用户

七）企业应用中的MySQL用户密码设定

二、MySQL授权、回收

一）MySQL授权

1、grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利

2、grant 数据库开发人员，创建表、索引、视图、存储过程、函数。。。等权限

3、grant 普通 DBA 管理某个 MySQL 数据库的权限

4、grant 高级 DBA 管理 MySQL 中所有数据库的权限

5、MySQL grant 权限，分别可以作用在多个层次上

grant 作用在整个 MySQL 服务器上：

grant 作用在单个数据库上

grant 作用在单个数据表上

grant 作用在表中的列上：

grant 作用在存储过程、函数上：

二）查看、回收授权

1、查看用户授权情况

2、回收权限

三）MySQL grant、revoke 用户权限注意事项

四）给指定前缀的库或表授权

1、给指定前缀的库授权

2、给指定前缀的表授权

公告

　　1、权限存储位置

　　2、User权限表结构中的特殊字段

　　3、Tables_priv和columns_priv权限值

　　4、procs_priv权限表结构

　　5、权限认证中的大小写敏感问题

　　1、MySQL的授权用户由两部分组成：用户名和登录主机名

　　2、MySQL修改权限的生效

　　3、用户管理

　　4、设置MySQL用户的密码

　　1、grant 普通数据用户，查询、插入、更新、删除数据库中所有表数据的权利

　　2、grant 数据库开发人员，创建表、索引、视图、存储过程、函数。。。等权限

　　3、grant 普通 DBA 管理某个 MySQL 数据库的权限

　　4、grant 高级 DBA 管理 MySQL 中所有数据库的权限

　　5、MySQL grant 权限，分别可以作用在多个层次上

　　　　grant 作用在整个 MySQL 服务器上：

　　　　grant 作用在单个数据库上

　　　　 grant 作用在单个数据表上

　　　　grant 作用在表中的列上：

　　　　grant 作用在存储过程、函数上：

　　1、查看用户授权情况

　　2、回收权限

　　1、给指定前缀的库授权

　　2、给指定前缀的表授权