MySql

DCL，用来管理数据库用户、控制数据库的访问权限相关。

用户管理

• 查询用户

  show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | elk_blog           |
  | milo_blog          |
  | mysql              |
  | performance_schema |
  | study              |
  | sys                |
  +--------------------+
  7 rows in set (0.00 sec)
  
  mysql> use mysql # 默认的mysql数据库下，有user表，就包含了当前数据库下的用户信息
  Database changed
  mysql> select Host, User from user;
  +-----------+---------------+
  | Host      | User          |
  +-----------+---------------+
  | localhost | mysql.session |
  | localhost | mysql.sys     |
  | localhost | root          |
  +-----------+---------------+
  3 rows in set (0.00 sec)
  

• 创建用户

  CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

  -- 创建用户 milo ，只能在当前主机localhost访问，密码为123456
  
   -- 该用户只是创建了，但是还没有任何数据库访问权限，且只能在本机访问。
   -- 如在服务器上创建的用户，在开发机上不能直接访问。
  mysql> create user 'milo'@'localhost' identified by '123456'; 
  Query OK, 0 rows affected (0.01 sec)
  
  
  -- 创建用户 xiaobo，可以在任意注意下访问该数据库，密码为 123456
  mysql> create user 'xiaobo'@'%' identified by '123456'; 
  Query OK, 0 rows affected (0.01 sec)
  

• 修改用户密码

  ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

  -- 修改milo的访问密码为 1234 
  
  -- 注意，刚刚milo给的是主机的访问权限，所以只能是localhost不能是%域
  mysql> alter user 'milo'@'localhost' identified with mysql_native_password by '1234';
  Query OK, 0 rows affected (0.00 sec)
  
  -- 恰比 xiaobo给的是任意权限，修改的时候不能是 localhost 域
  mysql> alter user 'xiaobo'@'localhost' identified with mysql_native_password by '1234';
  ERROR 1396 (HY000): Operation ALTER USER failed for 'xiaobo'@'localhost'
  

• 删除用户

  DROP USER '用户名'@'主机名';

  -- 同比，给的什么访问权限，删除的时候也需要匹配
  DROP USER 'xiaobo'@'localhost';
  

权限控制

常用权限：

更多权限请看权限一览表

• 查询权限

  SHOW GRANTS FOR '用户名'@'主机名';

  mysql> show grants for 'milo'@'localhost';
  +------------------------------------------+
  | Grants for milo@localhost                |
  +------------------------------------------+
  | GRANT USAGE ON *.* TO 'milo'@'localhost' |
  +------------------------------------------+
  1 row in set (0.00 sec)
  

• 授予权限

  GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

  mysql> grant all privileges on study.* to milo@localhost;
  Query OK, 0 rows affected (0.00 sec)
  mysql> show grants for 'milo'@'localhost';
  +---------------------------------------------------------+
  | Grants for milo@localhost                               |
  +---------------------------------------------------------+
  | GRANT USAGE ON *.* TO 'milo'@'localhost'                |
  | GRANT ALL PRIVILEGES ON `study`.* TO 'milo'@'localhost' |
  +---------------------------------------------------------+
  

  此时，milo用户就拥有了study的所有权限。

• 撤销权限

  REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

  mysql> revoke ALL ON study.* FROM 'milo'@'localhost';
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> show grants for 'milo'@'localhost';
  +------------------------------------------+
  | Grants for milo@localhost                |
  +------------------------------------------+
  | GRANT USAGE ON *.* TO 'milo'@'localhost' |
  +------------------------------------------+
  1 row in set (0.00 sec)
  

注意事项

• 多个权限用逗号分隔
• 授权时，数据库名和表名可以用 * 进行通配，代表所有