mysql DCL 授权语句

一、创建用户

  1. 语法：{username}用户名 @ {host}可访问地址 {password}密码
  2. CREATE USER 'username'@'host' IDENTIFIED BY 'password';
  4. 1) 创建任意远程连接账号
  5.   CREATE USER 'user1'@'%' IDENTIFIED BY  '123456'
  7. 2) 创建本地连接账号
  8.   CREATE USER 'user1'@'localhost' IDENTIFIED BY '123456';
  10. 3) 创建指定host远程连接账号, 如果用于代理, 则只需要授权代理服务器即可. 代理服务能访问 mysql 服务器
  11.   CREATE USER 'user1'@'180.166.126.94' IDENTIFIED BY '123456';
  13. 4) 删除用户
  14.   drop user 'user1'@'180.166.126.94';

二、授权用户

  • 授权本身，如果账号不存在也会创建账号，所以用授权来创建账号也不错
  • 授权文章
  1. 语法：{*.*} 数据库.表
  2.   GRANT ALL PRIVILEGES ON *.* TO '用户名'@'%' IDENTIFIED BY '登录密码' WITH GRANT OPTION;
  4. 1) 创建 test3 账号，授予任意host登录权限，并且授予所有数据库访问权限
  6.   GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
  9. 2) 授权 test1 账号 test 数据库权限
  11.   GRANT ALL PRIVILEGES ON  test.* TO  'user1'@'localhost' WITH GRANT OPTION ;
  14. 3) 授权指定权限 (SELECT,INSERT,UPDATE,DELETE,CREATE,DROP)
  16.   GRANT SELECT ON *.* to 'readonly'@'%' WITH GRANT OPTION;
  18.   GRANT ALL PRIVILEGES ON pm.* TO 'hadoop'@'%' WITH GRANT OPTION;
  20.   GRANT SELECT ON pm.* TO 'dev'@'%' WITH GRANT OPTION
  23. 4) 授权指定权限给函数权限
  24.   GRANT execute ON database.* to readonly@'%';
  27. 5) 权限
  28.   INSERT,DELETE,UPDATE,SELECT,EXECUTE,CREATE,ALTER,DROP,INDEX
  30.   EVENT,CREATE VIEW,FILE,LOCK TABLES,PROCESS,REFERENCES,REPLICATION CLIENT
  32.   更多详见: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html
  35. 6) mysql 8.0
  36.   # 创建用户
  37.   create user 'user1'@'xxx.com' IDENTIFIED BY 'xxx.passwd.com' ;
  39.   # 修改加密方式， 加密方式有 caching_sha2_password, mysql_native_password
  40.   ALTER USER 'user1'@'xxx.com' IDENTIFIED WITH mysql_native_password BY 'xxx.passwd.com';
  42.   # 授权账号, 创建和授权要分开(命令行登录授权)
  43.   GRANT INSERT,DELETE,UPDATE,SELECT,CREATE,ALTER,DROP ON test.* TO 'user1'@'xxx.com';

三、用户操作

  1. --
  2. SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
  4. -- 修改用户密码
  5. UPDATE mysql.user SET password=password("2345.com") WHERE user="root";
  7. -- 删除用户
  8. DELETE FROM mysql.user WHERE user='';
  10. -- 修改内容刷新
  11. flush privileges;