Mysql资料 用户权限详解

时间：2022-04-25 08:50:19

一.MySQL权限详解

设置MySQL用户资源限制

  • 通过设置全局变量max_user_connections可以限制所有用户在同一时间连接MySQL实例的数量，但此参数无法对每个用户区别对待，所以MySQL提供了对每个用户的资源限制管理
  • MAX_QUERIES_PER_HOUR：一个用户在一个小时内可以执行查询的次数（基本包含所有语句）
  • MAX_UPDATES_PER_HOUR：一个用户在一个小时内可以执行修改的次数（仅包含修改数据库或表的语句）
  • MAX_CONNECTIONS_PER_HOUR：一个用户在一个小时内可以连接MySQL的时间
  • MAX_USER_CONNECTIONS：一个用户可以在同一时间连接MySQL实例的数量
  • 从5.0.3版本开始，对用户‘user’@‘%.example.com’的资源限制是指所有通过example.com域名主机连接user用户的连接，而不是分别指从host1.example.com和host2.example.com主机过来的连接

用户资源限制执行操作

  • 通过执行create user/alter user设置/修改用户的资源限制
mysql> CREATE USER 'wsp'@'localhost' IDENTIFIED BY

'mysql' WITH MAX_QUERIES_PER_HOUR 20

MAX_UPDATES_PER_HOUR 10

MAX_CONNECTIONS_PER_HOUR 5

MAX_USER_CONNECTIONS 2;

# 取消某项资源限制既是把原先的值修改成 0

mysql> ALTER USER 'wsp'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;

# 当针对某个用户的 max_user_connections非 0时，则忽略全局系统参数 max_connections，反之则全局系统 max_connections参数生效

mysql> ALTER USER 'wsp'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 0;

二.MySQL权限级别介绍

MySQL权限级别

  • 全局性的管理权限，作用于整个MySQL实例级别
  • 数据库级别的权限，作用于某个指定的数据库上或者所有的数据库上
  • 数据库对象级别的权限，作用于指定的数据库对象上（表、视图等）或者所有的数据库对象上
  • 权限存储在mysql库的user, db, tables_priv, columns_priv, and procs_priv这几个系统表中，待MySQL实例启动后就加载到内存中

MySQL创建权限

  • All/All Privileges权限代表全局或者全数据库对象级别的所有权限
  • Alter权限代表允许修改表结构的权限，但必须要求有create和insert权限配合。如果是rename表名，则要求有alter和drop原表，create和insert新表的权限
  • Alter routine权限代表允许修改或者删除存储过程、函数的权限
  • Create权限代表允许创建新的数据库和表的权限
  • Create routine权限代表允许创建存储过程、函数的权限
  • Create tablespace权限代表允许创建、修改、删除表空间和日志组的权限
  • Create temporary tables权限代表允许创建临时表的权限
  • Create user权限代表允许创建、修改、删除、重命名user的权限
  • Create view权限代表允许创建视图的权限

MySQL删除与插入权限

  • Delete权限代表允许删除行数据的权限
  • Drop权限代表允许删除数据库、表、视图的权限，包括truncate table命令
  • Event权限代表允许查询，创建，修改，删除MySQL事件
  • Execute权限代表允许执行存储过程和函数的权限
  • File权限代表允许在MySQL可以访问的目录进行读写磁盘文件操作，可使用的命令包括load data infile,select … into outfile,load file()函数
  • Grant option权限代表是否允许此用户授权或者收回给其他用户你给予的权限
  • Index权限代表是否允许创建和删除索引
  • Insert权限代表是否允许在表里插入数据，同时在执行analyze table,optimize table,repair table语句的时候也需要insert权限
  • Lock权限代表允许对拥有select权限的表进行锁定，以防止其他链接对此表的读或写

MySQL修改与触发器权限

  • Shutdown权限代表允许关闭数据库实例，执行语句包括mysqladmin shutdown
  • Super权限代表允许执行一系列数据库管理命令，包括kill强制关闭某个连接命令，change master to创建复制关系命令，以及create/alter/drop server等命令
  • Trigger权限代表允许创建，删除，执行，显示触发器的权限
  • Update权限代表允许修改表中的数据的权限
  • Usage权限是创建一个用户之后的默认权限，其本身代表连接登录权限
mysql> create user abc@localhost;

mysql> show grants for abc@localhost;

+-----------------------------------------+

| Grants for abc@localhost                |

+-----------------------------------------+

| GRANT USAGE ON *.* TO 'abc'@'localhost' |

+-----------------------------------------+

MySQL系统操作权限

  • Process权限代表允许查看MySQL中的进程信息，比如执行show processlist, mysqladmin processlist, show engine等命令
  • Reference权限是在5.7.6版本之后引入，代表是否允许创建外键
  • Reload权限代表允许执行flush命令，指明重新加载权限表到系统内存中，refresh命令代表关闭和重新开启日志文件并刷新所有的表
  • Replication client权限代表允许执行show master status,show slave status,show binary logs命令
  • Replication slave权限代表允许slave主机通过此用户连接master以便建立主从复制关系
  • Select权限代表允许从表中查看数据，某些不查询表数据的select执行则不需要此权限，如Select 1+1，Select PI()+2；而且select权限在执行update/delete语句中含有where条件的情况下也是需要的
  • Show databases权限代表通过执行show databases命令查看所有的数据库名
  • Show view权限代表通过执行show create view命令查看视图创建的语句

User系统权限表

  • 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代表此用户被锁住，无法使用

系统权限表字段长度限制表

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

    Mysql资料 用户权限详解
  • 字段user,password,authencation_string,db,table_name大小写敏感
  • 字段host,column_name,routine_name大小写不敏感

相关文章