MySQL用户及权限

1. MySQL根据对象级别划分的权限类别：

常见的权限类别：库级别、表级别、字段级别、管理类权限、程序类权限

• 管理类权限：

  • CREATE TEMPORARY TABLES 创建临时表，一般为16M;
  • CREATE USER：创建用户权限
  • FILE：在数据库导出某文件中，或者从文件加载至数据库中
  • SUPER：在数据库执行管理操作的权限；跟复制、授权等相关的高级管理权限
  • SHOW DATABASES： 一般授权给每个用户
  • RELOAD：重新装载授权表还有其他功能
  • SHOUTDOWN：关闭数据库权限
  • REPLICATION SLAVE：是否能创建连接实现从服务器以复制的方式复制的权限连接至数据服务器端
  • REPLICATION CLIENT：是否有权限从主服务器请求复制相关事件到本地，就是请求复制相关主数据库产生的时间
  • LOCK TABLES：请求在服务器上锁表权限
  • PROCESS：

• 程序类权限：

  • FUNCTION存储函数：CREATE,ALTER,DROP,EXCUTE
  • PROCEDURE存储过程：CREATE,ALTER,DROP,EXCUTE
  • TRIGGER触发器：CREATE,ALTER,DROP,EXCUTE
    • CREATE FUNCTION:创建存储函数权限
    • CREATE PROCEDURE:创建存储过程权限
    • CREATE TRIGGER:创建触发器权限
    • ALTER FUNCTION:修改存储函数权限
    • ALTER PROCEDURE:修改存储过程权限
    • ALTER TRIGGER:修改触发器权限
    • DROP FUNCTION:删除存储函数权限
    • DROP PROCEDURE:删除存储过程权限
    • DROP TEIGGER:删除触发器权限
    • EXCUTE FUNCTION:执行存储函数权限
    • EXCUTE PROCEDURE执行存储过程权限
    • EXCUTE TRIGGER:执行触发器权限

• 库和表级别的权限：TABLE,DATABASE

  • ALTER:可修改库或表权限；无需指明关键字，因为ALTER就代表仅对二者有效
  • CREATE:创建库或表权限
  • CREATE VIEW:创建视图权限
  • DROP:删除库或表权限
  • INDEX:创建过删除索引权限
  • SHOW VIEW:查看视图权限
  • GRANT OPTION:能够把自己获得的权限赠送给其他用户一个副本的权限；一般手机时不建议使用with grant option

• 数据操作的权限：

  • SELECT
  • INSERT
  • DELETE
  • UPDATE

• 字段级别权限：

  • SELECT(col1,col2,...):定义查看指定字段的权限
  • UPDATE(col1,col2,...):定义更新/改指定字段权限
  • INSERT(col1,col2,...):定义插入指定字段的权限
    注：delete删就删一行，所以delete没有字段操作

• 所有权限： ALL PRIVIEGES, ALL

2. 用户权限都保存在元数据数据库中

• 元数据数据库：mysql
  • 授权表：db，host，user；这三个表是限制哪些用户通过哪些主机连接到当前数据库对哪些库做访问；
  • columns_priv,tables_priv,procs_priv,proxies_priv:用来设定专门的权限
• 用户账号：‘USERNAME‘@‘HOST‘
  • USERNAME：账号名称；
• HOST：此账号可以通过哪些客户端主机请求创建连接线程；
  • %：任意长度的任意字符；
  • _：任意单个字符；
  • skip_name_resolve=ON：跳过名称解析
• 创建用户：
  CREATE USER ‘user‘@‘host’ [IDENTIFIED BY [PASSWORD] ‘password‘] [,‘user‘@‘host‘ [IDENTIFIED BY [PASSWORD] ‘password‘]...]
• 重命名：
  RENAME USER old_user TO new_user[,old_user TO new_user] ...
• 删除用户：
  DROP USER ‘user‘@‘host‘ [,‘user‘@‘host‘] ...
• 让MySQL重新加载授权表
  FLUSH PRIVILEGES
• 修改用户密码：
  (1) SET PASSWORD [FOR ‘user‘@‘host‘] = PASSWORD(‘cleartext password‘);
  (2) UPDATE mysql.user SET Password=PASSWORD(‘cleartext password‘) WHERE User=‘USERNAME‘ AND Host=‘HOST‘;
  (3) mysqladmin -uUSERNAME -hHOST -p password ‘NEW_PASS‘;
  使其生效：FLUSH PRIVILEGES
• 忘记管理员密码的解决办法：
  (1) 启动mysqld进程时，使用--skip-grant-tables 和 --skip-networking 选项；

  • CentOS 7: mariadb.serivce

  • CentOS 6: /etc/init.d/mysqld
    (2) 通过UPDATE 命令修改管理员密码
    (3) 以正常方式启动mysqld进程

    操作示例：以CentOS7为例
    [root@localhost ~]# systemctl stop mariadb
    [root@localhost ~]# vim /usr/lib/systemd/system/mariadb.service
    修改如下内容：
      ExecStart=/usr/bin/mysqld_safe --basedir=/usr --skip-grant-tables --skip-networking
    [root@localhost ~]# systemctl daemon-reload
    [root@localhost ~]# systemctl start mariadb.service
    [root@localhost ~]# mysql
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 2
    Server version: 5.5.60-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
    
    MariaDB [(none)]> UPDATE mysql.user SET password=PASSWORD(‘inspur‘) WHERE user=‘root‘;
    Query OK, 2 rows affected (0.02 sec)
    Rows matched: 3  Changed: 2  Warnings: 0
    
    MariaDB [(none)]> FLUSH PRIVILEGES;
    
    MariaDB [(none)]> EXIT
    Bye
    [root@localhost ~]# systemctl stop mariadb.service
    [root@localhost ~]# vim /usr/lib/systemd/system/mariadb.service 
      修改如下内容：
      ExecStart=/usr/bin/mysqld_safe --basedir=/usr
    [root@localhost ~]# systemctl deamon-reload