MySQL用户管理

1.为什么需要用户管理

• 如果只使用root用户，就可以操控所有的库，是存在安全隐患的，因此需要设置一些权限。比如张三只能操控库1，李四只能操控库2

2.用户管理

2.1查看用户信息

• MySQL中的用户信息，都存储在系统数据库mysql的user表中

  mysql>  
  +------------------------+-----------------------------------+------+-----+-----------------------+-------+
  | Field                  | Type                              | Null | Key | Default               | Extra |
  +------------------------+-----------------------------------+------+-----+-----------------------+-------+
  | Host                   | char(60)                          | NO   | PRI |                       |       |
  | User                   | char(32)                          | NO   | PRI |                       |       
  

• 筛选几个进行查看

  mysql> select User,Host,authentication_string from mysql.user;
  
  +---------------+-----------+-------------------------------------------+
  | User          | Host      | authentication_string                     |
  +---------------+-----------+-------------------------------------------+
  | root          | localhost | *A83CFD69FE10FCCC776762D4FFADDF20AC6BCAB3 |
  | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
  | mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
  | root          | %         | *A83CFD69FE10FCCC776762D4FFADDF20AC6BCAB3 |
  +---------------+-----------+-------------------------------------------+
  

  • 字段解释
    • host：表示这个用户可以从哪个主机登陆，如果是localhost，表示只能从本机登陆
    • user：用户名
    • authentication_string：用户密码通过password函数加密后的

2.2创建用户

• 语法：create user ‘用户名’@‘登陆主机/ip’ identified by ‘密码’

  • %从任意处登录，一般只有root用户设置

  • 普通用户一般只会设置在某个地方登录来保证安全性，%一般只会设置root用户

    //创建一个只可以在本地登录的用户
    create user 'ych'@'localhost' identified by '_aaa123456BBB';
    //创建可以在任意地方登录的用户
    mysql> create user 'ych2'@'%' identified by '_aaa123456BBB';
    
    //查看
    mysql> select user,host from mysql.user;
    +---------------+-----------+
    | user          | host      |
    +---------------+-----------+
    | root          | %         |
    | ych2          | %         |
    | mysql.session | localhost |
    | mysql.sys     | localhost |
    | root          | localhost |
    | ych           | localhost |
    +---------------+-----------+
    

    

2.3删除用户

• 语法:drop user ‘用户名’@‘主机名’

  //ych用户被删除
  mysql> drop user 'ych'@'localhost';
  
  mysql> select user,host from mysql.user; 
  +---------------+-----------+
  | user          | host      |
  +---------------+-----------+
  | root          | %         |
  | ych2          | %         |
  | mysql.session | localhost |
  | mysql.sys     | localhost |
  | root          | localhost |
  +---------------+-----------+
  

2.4修改用户密码

• 自己修改密码：set password=password(‘新的密码’);
• root用户修改指定用户的密码：set password for ‘用户名’@‘主机名’=password(‘新的密码’)；

3.数据库的权限

3.1给用户授权

• 查看数据库权限：

  • show grants for 用户名;
  • 查看权限列表 select *from mysql.user;

• 刚创建的用户没有任何权限。需要给用户授权

  • 新用户只能看到自己的数据库，因为没有权限

    //看不到root用户下的数据库
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    +--------------------+
    
    //查看root的权限
    mysql> show grants for root;
    +-------------------------------------------------------------+
    | Grants for root@%                                           |
    +-------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION | -> 拥有所有权限
    +-------------------------------------------------------------+
    
    //查看ych权限，可以看到是没有权限打开的
    There is no such grant defined for user 'ych' on host '%'
    

• 语法：grant 权限列表 on 库.对象名 to ‘用户名’@'登陆位置[identified by ‘密码’]

  • 权限列表：多个权限用逗号分开,如果使用all则赋予所有权利

  • 库.对象名 :库.* 表示某个数据库中的所有数据对象(表，视图，存储过程等)

  • identified by：可选,如果用户存在，赋予权限的同时修改密码,如果该用户不存在，就是创建用户

    //给ych2用户，添加base2库下，所有表的select和update权限
    mysql> grant select,update on base2.* to 'ych2'@'%';
    
    //添加权限后进行查看
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | base2              |
    +--------------------+
    
    mysql> show grants for ych2;
    +-------------------------------------------------+
    | Grants for ych2@%                               |
    +-------------------------------------------------+
    | GRANT USAGE ON *.* TO 'ych2'@'%'                |  -> 在base2下有select和update权限
    | GRANT SELECT, UPDATE ON `base2`.* TO 'ych2'@'%' |
    +-------------------------------------------------+
    
    mysql> select *from t1;
    +------+-----------+
    | id   | name      |
    +------+-----------+
    |    1 | 曹孟德    |
    |    2 | 曹植      |
    |    3 | 曹丕      |
    |    4 | 许褚      |
    +------+-----------+
    

• 权限刷新：flush privileges

3.2回收权限

• 语法：revoke 权限列表 on 库.对象名 from ‘用户名’@‘登陆位置’

  //回收ych2 select权限
  mysql> revoke select on base2.* from 'ych2'@'%';
  
  //只有update权限了
  mysql> show grants for ych2;
  +-----------------------------------------+
  | Grants for ych2@%                       |
  +-----------------------------------------+
  | GRANT USAGE ON *.* TO 'ych2'@'%'        |
  | GRANT UPDATE ON `base2`.* TO 'ych2'@'%' |
  +-----------------------------------------+
  
  //没有权限查看
  mysql> show tables;
  +-----------------+
  | Tables_in_base2 |
  +-----------------+
  | t               |
  | t1              |
  | vt              |
  +-----------------+
  
  mysql> select *from t;
  ERROR 1142 (42000): SELECT command denied to user 'ych2'@'113.132.140.168' for table 't'