【MySQL学习】用户权限

mysql.user表

存放用户账户信息以及全局级别（所有数据库）权限，决定了来自哪些主机的哪些用户可以访问数据库实例，如果有全局权限则意味着对所有数据库都有此权限

SHOW FULL COLUMNS FROM mysql.`user`

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

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

mysql.user表中的host列含义

Host列指定了允许用户登录所使用的IP，比如user=root Host=192.168.1.1。这里的意思就是说root用户只能通过192.168.1.1的客户端去访问。　　而%是个通配符，如果Host=192.168.1.%，那么就表示只要是IP地址前缀为“192.168.1.”的客户端都可以连接。如果Host=%，表示所有IP都有连接权限。、　　这也就是为什么在开启远程连接的时候，大部分人都直接把Host改成%的缘故，为了省事。

查看用户列表

SELECT USER,HOST FROM mysql.`user`

查看用户授予的权限

show grants for root@'localhost';

权限包括

• All/All Privileges权限代表全局或者全数据库对象级别的所有权限
• Create权限代表允许创建新的数据库和表的权限
• Create user权限代表允许创建、修改、删除、重命名user的权限
• Create view权限代表允许创建视图的权限
• Delete权限代表允许删除行数据的权限
• Drop权限代表允许删除数据库、表、视图的权限，包括truncate table命令
• Select权限代表允许从表中查看数据
• Update权限代表允许修改表中的数据的权限
• 等等

增加用户

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    DEFAULT ROLE role [, role ] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

• auth_option: {
  IDENTIFIED BY ‘auth_string’
  | IDENTIFIED BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY ‘auth_string’
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin AS ‘auth_string’
  }

CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123456';

授予用户权限

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]
    [AS user
        [WITH ROLE
            DEFAULT
          | NONE
          | ALL
          | ALL EXCEPT role [, role ] ...
          | role [, role ] ...
        ]
    ]
}

• priv_type：授予的权限，包括全局权限，数据库权限，表权限，列权限等。

• priv_level: {
  *

  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
  }

全局权限

全局权限是管理权限，或者应用于给定服务器上的所有数据库，要分配全局权限，请使用*.*语法

GRANT ALL ON *.* TO 'zhangsan'@'localhost';
GRANT SELECT, INSERT ON *.* TO 'zhangsan'@'localhost';

数据库级权限

数据库权限应用于给定数据库中的所有对象。要分配数据库级别权限，请使用 db_name.*语法

GRANT all ON school.* TO 'zhangsan'@'localhost';
GRANT SELECT ,INSERT ON school.* TO 'zhangsan'@'localhost';

表级权限

表级权限应用于给定数据库中的给定表。要分配表级别权限，请使用 db_name.tbl_name语法

GRANT all ON school.user TO 'zhangsan'@'localhost';
GRANT SELECT ,INSERT ON school.user TO 'zhangsan'@'localhost';

列级权限

表级权限应用于给定数据库中的给定表的给定列.如下所示：

GRANT SELECT(id,name) ON school.user TO 'zhangsan'@'localhost';

提示：

​ 使用 grant、revoke 的时候都会修改 user 数据表，也会同步修改内存中的 hash 结构，即时生效，也就没有必要跟着执行 flush privileges 命令；

flush privileges 使用场景：加载用户权限。直接使用 DML 操作系统权限表，会导致权限数据跟内存中的权限数据不一致，这时就是需要使用 flush privileges 重建内存数据，达到权限一致状态。

回收权限

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user_or_role [, user_or_role] ...

REVOKE ALL ON school.* FROM zhangsan@localhost

修改用户密码

alter user 'zhangsan'@'localhost' identified by '1234567' --方法1 不好用 
SET PASSWORD FOR 'zhangsan'@'localhost'  = PASSWORD('1234567');-- 方法2

SELECT @@VERSION查看mysql版本，我的是5.6.28可能版本较低，方法1不能用。官网文档目前使用的是MYSQL8

删除用户

DROP USER [IF EXISTS] user [, user] ...

该命令删除一个或多个账号以及分配给他们的权限。

DROP USER zhangsan@localhost

参考文章：

官方文档

用户认证及权限控制

Mysql 用户权限管理