当前位置:Gxlcms > 数据库问题 > MySQL创建用户与授权

MySQL创建用户与授权

时间:2021-07-01 10:21:17 帮助过:3人阅读

CURRENT_USER();

技术图片

CREATE USER 语法

  1. <span style="color: #0000ff;">CREATE</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">IF NOT EXISTS</span><span style="color: #ff0000;">]</span>
  2. <span style="color: #ff00ff;">user</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">auth_option</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">, user [auth_option</span><span style="color: #ff0000;">]</span><span style="color: #000000;">] ...
  3. </span><span style="color: #0000ff;">DEFAULT</span> ROLE role <span style="color: #ff0000;">[</span><span style="color: #ff0000;">, role </span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ...
  4. </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">REQUIRE {NONE | tls_option [[AND</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> tls_option] ...}]
  5. </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">WITH resource_option [resource_option</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ...]
  6. </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">password_option | lock_option</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ...
  7. </span><span style="color: #ff00ff;">user</span><span style="color: #000000;">:
  8. (see Section </span><span style="color: #800000; font-weight: bold;">6.2</span>.<span style="color: #800000; font-weight: bold;">4</span><span style="color: #000000;">, “Specifying Account Names”)
  9. auth_option: {
  10. IDENTIFIED </span><span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">auth_string</span><span style="color: #ff0000;">‘</span>
  11. <span style="color: #808080;">|</span> IDENTIFIED <span style="color: #0000ff;">WITH</span><span style="color: #000000;"> auth_plugin
  12. </span><span style="color: #808080;">|</span> IDENTIFIED <span style="color: #0000ff;">WITH</span> auth_plugin <span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">auth_string</span><span style="color: #ff0000;">‘</span>
  13. <span style="color: #808080;">|</span> IDENTIFIED <span style="color: #0000ff;">WITH</span> auth_plugin <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">hash_string</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">
  14. }
  15. tls_option: {
  16. SSL
  17. </span><span style="color: #808080;">|</span><span style="color: #000000;"> X509
  18. </span><span style="color: #808080;">|</span> CIPHER <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">cipher</span><span style="color: #ff0000;">‘</span>
  19. <span style="color: #808080;">|</span> ISSUER <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">issuer</span><span style="color: #ff0000;">‘</span>
  20. <span style="color: #808080;">|</span> SUBJECT <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">subject</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">
  21. }
  22. resource_option: {
  23. MAX_QUERIES_PER_HOUR </span><span style="color: #ff00ff;">count</span>
  24. <span style="color: #808080;">|</span> MAX_UPDATES_PER_HOUR <span style="color: #ff00ff;">count</span>
  25. <span style="color: #808080;">|</span> MAX_CONNECTIONS_PER_HOUR <span style="color: #ff00ff;">count</span>
  26. <span style="color: #808080;">|</span> MAX_USER_CONNECTIONS <span style="color: #ff00ff;">count</span><span style="color: #000000;">
  27. }
  28. password_option: {
  29. PASSWORD EXPIRE </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">DEFAULT | NEVER | INTERVAL N DAY</span><span style="color: #ff0000;">]</span>
  30. <span style="color: #808080;">|</span> PASSWORD HISTORY {<span style="color: #0000ff;">DEFAULT</span> <span style="color: #808080;">|</span><span style="color: #000000;"> N}
  31. </span><span style="color: #808080;">|</span> PASSWORD REUSE INTERVAL {<span style="color: #0000ff;">DEFAULT</span> <span style="color: #808080;">|</span> N <span style="color: #ff00ff;">DAY</span><span style="color: #000000;">}
  32. </span><span style="color: #808080;">|</span> PASSWORD REQUIRE <span style="color: #0000ff;">CURRENT</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">DEFAULT | OPTIONAL</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
  33. }
  34. lock_option: {
  35. ACCOUNT LOCK
  36. </span><span style="color: #808080;">|</span><span style="color: #000000;"> ACCOUNT UNLOCK
  37. }</span>

user 即账户名称,语法是  ‘user_name‘@‘host_name‘ ,其中主机地址可以写为 ‘%‘表示接受任何地址的连接。

auth_option 即身份验证方式,可以指定密码以及认证插件(mysql_native_password、sha256_password、caching_sha2_password)。

tls_option 即加密连接选项

resource_option 即用户资源限制,比如每小时最大连接数

password_option 即密码额外的控制,比如设定失效时间

lock_option 账户锁定选项,由管理员上锁或者解锁(ACCOUNT LOCK | ACCOUNT UNLOCK)。

 

最简单的就是指定账户名+密码

  1. <span style="color: #0000ff;">CREATE</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> IDENTIFIED <span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">password</span><span style="color: #ff0000;">‘</span>;

 

加上认证插件

  1. <span style="color: #0000ff;">CREATE</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> IDENTIFIED <span style="color: #0000ff;">WITH</span> sha256_password <span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">password</span><span style="color: #ff0000;">‘</span>;

 

指定密码过期,以便用户第一次使用的时候需要修改密码

  1. <span style="color: #0000ff;">CREATE</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> IDENTIFIED <span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">new_password</span><span style="color: #ff0000;">‘</span> PASSWORD EXPIRE;

 

也可以指定每隔一段时间修改一次新密码

  1. <span style="color: #0000ff;">CREATE</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> IDENTIFIED <span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">new_password</span><span style="color: #ff0000;">‘</span> PASSWORD EXPIRE INTERVAL <span style="color: #800000; font-weight: bold;">180</span> <span style="color: #ff00ff;">DAY</span>;

 

可以指定加密连接

  1. <span style="color: #008080;">--</span><span style="color: #008080;"> 不使用加密连接</span>
  2. <span style="color: #0000ff;">CREATE</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span><span style="color: #000000;"> REQUIRE NONE;
  3. </span><span style="color: #008080;">--</span><span style="color: #008080;"> 使用加密连接</span>
  4. <span style="color: #0000ff;">CREATE</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span><span style="color: #000000;"> REQUIRE SSL;
  5. </span><span style="color: #008080;">--</span><span style="color: #008080;"> 使用加密连接,并要求客户端提供有效证书</span>
  6. <span style="color: #0000ff;">CREATE</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span><span style="color: #000000;"> REQUIRE X509;
  7. </span><span style="color: #0000ff;">CREATE</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> REQUIRE ISSUER <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">CA颁发的有效X.509证书</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  8. </span><span style="color: #0000ff;">CREATE</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> REQUIRE SUBJECT <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">包含主题的有效X.509证书</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  9. </span><span style="color: #0000ff;">CREATE</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> REQUIRE CIPHER <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">指定的加密方法</span><span style="color: #ff0000;">‘</span>;

 

可以指定资源控制

  1. <span style="color: #008080;">--</span><span style="color: #008080;"> 单位小时内,账户被允许查询500次,更新100次,单位小时内最大连接数不限制。最大并发连接数不限制</span>
  2. <span style="color: #0000ff;">CREATE</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">WITH</span> MAX_QUERIES_PER_HOUR <span style="color: #800000; font-weight: bold;">500</span> MAX_UPDATES_PER_HOUR <span style="color: #800000; font-weight: bold;">100</span> MAX_CONNECTIONS_PER_HOUR <span style="color: #800000; font-weight: bold;">0</span> MAX_USER_CONNECTIONS <span style="color: #800000; font-weight: bold;">0</span>;

 

可以锁定账户

  1. <span style="color: #008080;">--</span><span style="color: #008080;"> 锁定</span>
  2. <span style="color: #0000ff;">CREATE</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span><span style="color: #000000;"> ACCOUNT LOCK
  3. </span><span style="color: #008080;">--</span><span style="color: #008080;"> 解锁</span>
  4. <span style="color: #0000ff;">ALTER</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> ACCOUNT UNLOCK

 

最后完整的命令选项大概这个样子

  1. <span style="color: #0000ff;">CREATE</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">user_name</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">host_name</span><span style="color: #ff0000;">‘</span> IDENTIFIED <span style="color: #ff0000;">[</span><span style="color: #ff0000;">WITH auth_plugin</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">auth_string</span><span style="color: #ff0000;">‘</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">REQUIRE NONE(SSL,X509)</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">WITH MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">PASSWORD EXPIRE</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">ACCOUNT LOCK</span><span style="color: #ff0000;">]</span>

 

ALTER USER语法

  1. <span style="color: #0000ff;">ALTER</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">IF EXISTS</span><span style="color: #ff0000;">]</span>
  2. <span style="color: #ff00ff;">user</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">auth_option</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">, user [auth_option</span><span style="color: #ff0000;">]</span><span style="color: #000000;">] ...
  3. </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">REQUIRE {NONE | tls_option [[AND</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> tls_option] ...}]
  4. </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">WITH resource_option [resource_option</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ...]
  5. </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">password_option | lock_option</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ...
  6. </span><span style="color: #0000ff;">ALTER</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">IF EXISTS</span><span style="color: #ff0000;">]</span> <span style="color: #ff00ff;">USER</span><span style="color: #000000;">() user_func_auth_option
  7. </span><span style="color: #0000ff;">ALTER</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">IF EXISTS</span><span style="color: #ff0000;">]</span>
  8. <span style="color: #ff00ff;">user</span> <span style="color: #0000ff;">DEFAULT</span><span style="color: #000000;"> ROLE
  9. {NONE </span><span style="color: #808080;">|</span> <span style="color: #808080;">ALL</span> <span style="color: #808080;">|</span> role <span style="color: #ff0000;">[</span><span style="color: #ff0000;">, role </span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ...}
  10. </span><span style="color: #ff00ff;">user</span><span style="color: #000000;">:
  11. (see Section </span><span style="color: #800000; font-weight: bold;">6.2</span>.<span style="color: #800000; font-weight: bold;">4</span><span style="color: #000000;">, “Specifying Account Names”)
  12. auth_option: {
  13. IDENTIFIED </span><span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">auth_string</span><span style="color: #ff0000;">‘</span>
  14. <span style="color: #ff0000;">[</span><span style="color: #ff0000;">REPLACE ‘current_auth_string‘</span><span style="color: #ff0000;">]</span>
  15. <span style="color: #ff0000;">[</span><span style="color: #ff0000;">RETAIN CURRENT PASSWORD</span><span style="color: #ff0000;">]</span>
  16. <span style="color: #808080;">|</span> IDENTIFIED <span style="color: #0000ff;">WITH</span><span style="color: #000000;"> auth_plugin
  17. </span><span style="color: #808080;">|</span> IDENTIFIED <span style="color: #0000ff;">WITH</span> auth_plugin <span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">auth_string</span><span style="color: #ff0000;">‘</span>
  18. <span style="color: #ff0000;">[</span><span style="color: #ff0000;">REPLACE ‘current_auth_string‘</span><span style="color: #ff0000;">]</span>
  19. <span style="color: #ff0000;">[</span><span style="color: #ff0000;">RETAIN CURRENT PASSWORD</span><span style="color: #ff0000;">]</span>
  20. <span style="color: #808080;">|</span> IDENTIFIED <span style="color: #0000ff;">WITH</span> auth_plugin <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">auth_string</span><span style="color: #ff0000;">‘</span>
  21. <span style="color: #808080;">|</span><span style="color: #000000;"> DISCARD OLD PASSWORD
  22. }
  23. user_func_auth_option: {
  24. IDENTIFIED </span><span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">auth_string</span><span style="color: #ff0000;">‘</span>
  25. <span style="color: #ff0000;">[</span><span style="color: #ff0000;">REPLACE ‘current_auth_string‘</span><span style="color: #ff0000;">]</span>
  26. <span style="color: #ff0000;">[</span><span style="color: #ff0000;">RETAIN CURRENT PASSWORD</span><span style="color: #ff0000;">]</span>
  27. <span style="color: #808080;">|</span><span style="color: #000000;"> DISCARD OLD PASSWORD
  28. }
  29. tls_option: {
  30. SSL
  31. </span><span style="color: #808080;">|</span><span style="color: #000000;"> X509
  32. </span><span style="color: #808080;">|</span> CIPHER <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">cipher</span><span style="color: #ff0000;">‘</span>
  33. <span style="color: #808080;">|</span> ISSUER <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">issuer</span><span style="color: #ff0000;">‘</span>
  34. <span style="color: #808080;">|</span> SUBJECT <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">subject</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">
  35. }
  36. resource_option: {
  37. MAX_QUERIES_PER_HOUR </span><span style="color: #ff00ff;">count</span>
  38. <span style="color: #808080;">|</span> MAX_UPDATES_PER_HOUR <span style="color: #ff00ff;">count</span>
  39. <span style="color: #808080;">|</span> MAX_CONNECTIONS_PER_HOUR <span style="color: #ff00ff;">count</span>
  40. <span style="color: #808080;">|</span> MAX_USER_CONNECTIONS <span style="color: #ff00ff;">count</span><span style="color: #000000;">
  41. }
  42. password_option: {
  43. PASSWORD EXPIRE </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">DEFAULT | NEVER | INTERVAL N DAY</span><span style="color: #ff0000;">]</span>
  44. <span style="color: #808080;">|</span> PASSWORD HISTORY {<span style="color: #0000ff;">DEFAULT</span> <span style="color: #808080;">|</span><span style="color: #000000;"> N}
  45. </span><span style="color: #808080;">|</span> PASSWORD REUSE INTERVAL {<span style="color: #0000ff;">DEFAULT</span> <span style="color: #808080;">|</span> N <span style="color: #ff00ff;">DAY</span><span style="color: #000000;">}
  46. </span><span style="color: #808080;">|</span> PASSWORD REQUIRE <span style="color: #0000ff;">CURRENT</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">DEFAULT | OPTIONAL</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
  47. }
  48. lock_option: {
  49. ACCOUNT LOCK
  50. </span><span style="color: #808080;">|</span><span style="color: #000000;"> ACCOUNT UNLOCK
  51. }</span>

选项和创建的差不多,这里不做解释了。

修改自己当前的密码

  1. <span style="color: #0000ff;">ALTER</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff00ff;">USER</span>() IDENTIFIED <span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘new_</span><span style="color: #ff0000;">password</span><span style="color: #ff0000;">‘</span>;

 

修改账户密码

  1. <span style="color: #0000ff;">ALTER</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> IDENTIFIED <span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">new_password</span><span style="color: #ff0000;">‘</span>;

 

修改认证插件

  1. <span style="color: #0000ff;">ALTER</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> IDENTIFIED <span style="color: #0000ff;">WITH</span> mysql_native_password;

 

修改密码和插件

  1. <span style="color: #0000ff;">ALTER</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> IDENTIFIED <span style="color: #0000ff;">WITH</span> mysql_native_password <span style="color: #0000ff;">BY</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">new_password</span><span style="color: #ff0000;">‘</span>;

 

修改角色

  1. <span style="color: #008080;">--</span><span style="color: #008080;"> 授予自定义角色</span>
  2. <span style="color: #0000ff;">ALTER</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">DEFAULT</span><span style="color: #000000;"> ROLE your_role_name;
  3. </span><span style="color: #008080;">--</span><span style="color: #008080;"> 无角色</span>
  4. <span style="color: #0000ff;">ALTER</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">DEFAULT</span><span style="color: #000000;"> ROLE NONE;
  5. </span><span style="color: #008080;">--</span><span style="color: #008080;"> 所有角色</span>
  6. <span style="color: #0000ff;">ALTER</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">DEFAULT</span> ROLE <span style="color: #808080;">ALL</span>;

 

修改加密方式

  1. <span style="color: #008080;">--</span><span style="color: #008080;"> 只有账户密码正确,无须加密连接</span>
  2. <span style="color: #0000ff;">ALTER</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span><span style="color: #000000;"> REQUIRE NONE;
  3. </span><span style="color: #008080;">--</span><span style="color: #008080;"> 需要加密连接</span>
  4. <span style="color: #0000ff;">ALTER</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span><span style="color: #000000;"> REQUIRE SSL;
  5. ...</span>

 

修改资源访问

  1. <span style="color: #008080;">--</span><span style="color: #008080;"> 单位小时内,最大查询数量和更新数量</span>
  2. <span style="color: #0000ff;">ALTER</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">WITH</span> MAX_QUERIES_PER_HOUR <span style="color: #800000; font-weight: bold;">500</span> MAX_UPDATES_PER_HOUR <span style="color: #800000; font-weight: bold;">100</span>;

 

指定密码过期

  1. <span style="color: #0000ff;">ALTER</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> PASSWORD EXPIRE;

 

修改锁定解锁

  1. <span style="color: #0000ff;">ALTER</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span><span style="color: #000000;"> ACCOUNT LOCK;
  2. </span><span style="color: #0000ff;">ALTER</span> <span style="color: #ff00ff;">USER</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> ACCOUNT UNLOCK;

 

GRANT语法

  1. <span style="color: #0000ff;">GRANT</span><span style="color: #000000;">
  2. priv_type </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">(column_list)</span><span style="color: #ff0000;">]</span>
  3. <span style="color: #ff0000;">[</span><span style="color: #ff0000;">, priv_type [(column_list)</span><span style="color: #ff0000;">]</span><span style="color: #000000;">] ...
  4. </span><span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">object_type</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> priv_level
  5. </span><span style="color: #0000ff;">TO</span> user_or_role <span style="color: #ff0000;">[</span><span style="color: #ff0000;">, user_or_role</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ...
  6. </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">WITH GRANT OPTION</span><span style="color: #ff0000;">]</span>
  7. <span style="color: #ff0000;">[</span><span style="color: #ff0000;">AS user
  8. [WITH ROLE
  9. DEFAULT
  10. | NONE
  11. | ALL
  12. | ALL EXCEPT role [, role </span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ...
  13. </span><span style="color: #808080;">|</span> role <span style="color: #ff0000;">[</span><span style="color: #ff0000;">, role </span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ...
  14. ]
  15. ]
  16. }
  17. </span><span style="color: #0000ff;">GRANT</span> PROXY <span style="color: #0000ff;">ON</span><span style="color: #000000;"> user_or_role
  18. </span><span style="color: #0000ff;">TO</span> user_or_role <span style="color: #ff0000;">[</span><span style="color: #ff0000;">, user_or_role</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ...
  19. </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">WITH GRANT OPTION</span><span style="color: #ff0000;">]</span>
  20. <span style="color: #0000ff;">GRANT</span> role <span style="color: #ff0000;">[</span><span style="color: #ff0000;">, role</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ...
  21. </span><span style="color: #0000ff;">TO</span> user_or_role <span style="color: #ff0000;">[</span><span style="color: #ff0000;">, user_or_role</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ...
  22. </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">WITH ADMIN OPTION</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
  23. object_type: {
  24. </span><span style="color: #0000ff;">TABLE</span>
  25. <span style="color: #808080;">|</span> <span style="color: #0000ff;">FUNCTION</span>
  26. <span style="color: #808080;">|</span> <span style="color: #0000ff;">PROCEDURE</span><span style="color: #000000;">
  27. }
  28. priv_level: {
  29. </span><span style="color: #808080;">*</span>
  30. <span style="color: #808080;">|</span> <span style="color: #808080;">*</span>.<span style="color: #808080;">*</span>
  31. <span style="color: #808080;">|</span> <span style="color: #ff00ff;">db_name</span>.<span style="color: #808080;">*</span>
  32. <span style="color: #808080;">|</span> <span style="color: #ff00ff;">db_name</span><span style="color: #000000;">.tbl_name
  33. </span><span style="color: #808080;">|</span><span style="color: #000000;"> tbl_name
  34. </span><span style="color: #808080;">|</span> <span style="color: #ff00ff;">db_name</span><span style="color: #000000;">.routine_name
  35. }
  36. user_or_role: {
  37. </span><span style="color: #ff00ff;">user</span>
  38. <span style="color: #808080;">|</span><span style="color: #000000;"> role
  39. }
  40. </span><span style="color: #ff00ff;">user</span><span style="color: #000000;">:
  41. (see Section </span><span style="color: #800000; font-weight: bold;">6.2</span>.<span style="color: #800000; font-weight: bold;">4</span><span style="color: #000000;">, “Specifying Account Names”)
  42. role:
  43. (see Section </span><span style="color: #800000; font-weight: bold;">6.2</span>.<span style="color: #800000; font-weight: bold;">5</span>, “Specifying Role Names”)

GRANT语法使得管理员能够授予账户权限或者角色,但是GRANT不能再一个语句中同时授予权限和角色。

  • 有ON,是授予权限
  • 无ON,是授予角色
  1. <span style="color: #008080;">--</span><span style="color: #008080;"> 授予数据库db1的所有权限给指定账户</span>
  2. <span style="color: #0000ff;">GRANT</span> <span style="color: #808080;">ALL</span> <span style="color: #0000ff;">ON</span> db1.<span style="color: #808080;">*</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">jeffrey</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  3. </span><span style="color: #008080;">--</span><span style="color: #008080;"> 授予角色给指定的账户</span>
  4. <span style="color: #0000ff;">GRANT</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">role1</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">role2</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">user1</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">user2</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  5. </span><span style="color: #008080;">--</span><span style="color: #008080;"> 授予数据库world的SELECT权限给指定的角色</span>
  6. <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">ON</span> world.<span style="color: #808080;">*</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">role3</span><span style="color: #ff0000;">‘</span>;

 

 基本语法

  1. <span style="color: #0000ff;">GRANT</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">权限</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">ON</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">数据库名</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">表名</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">user_name</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span><span style="color: #000000;"> ...;
  2. </span><span style="color: #008080;">--</span><span style="color: #008080;"> 授予所有数据库的权限</span>
  3. <span style="color: #0000ff;">GRANT</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">权限</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">ON</span> <span style="color: #808080;">*</span>.<span style="color: #808080;">*</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">user_name</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">localhost</span><span style="color: #ff0000;">‘</span> ...;

 

注:全局权限是管理或适用于给定服务器上的所有数据库。要分配全局权限,请使用 ON *.*语法

下面是权限列表

技术图片 

权限范围示例

  1. <span style="color: #008080;">--</span><span style="color: #008080;"> 数据库权限</span>
  2. <span style="color: #0000ff;">GRANT</span> <span style="color: #808080;">ALL</span> <span style="color: #0000ff;">ON</span> mydb.<span style="color: #808080;">*</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">user_name</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">host_name</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  3. </span><span style="color: #008080;">--</span><span style="color: #008080;"> 表权限</span>
  4. <span style="color: #0000ff;">GRANT</span> <span style="color: #808080;">ALL</span> <span style="color: #0000ff;">ON</span> mydb.mytable <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">user_name</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">host_name</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  5. </span><span style="color: #008080;">--</span><span style="color: #008080;"> 列权限</span>
  6. <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">SELECT</span> (col1), <span style="color: #0000ff;">INSERT</span> (col1, col2) <span style="color: #0000ff;">ON</span> mydb.mytable <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">user_name</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">host_name</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  7. </span><span style="color: #008080;">--</span><span style="color: #008080;"> 存储过程权限</span>
  8. <span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">CREATE</span> ROUTINE <span style="color: #0000ff;">ON</span> mydb.<span style="color: #808080;">*</span> <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">user_name</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">host_name</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  9. </span><span style="color: #0000ff;">GRANT</span> <span style="color: #0000ff;">EXECUTE</span> <span style="color: #0000ff;">ON</span> <span style="color: #0000ff;">PROCEDURE</span> mydb.myproc <span style="color: #0000ff;">TO</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">user_name</span><span style="color: #ff0000;">‘</span>@<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">host_name</span><span style="color: #ff0000;">‘</span>;

 

MySQL创建用户与授权

标签:列表   技术   简单   数据   ide   alt   控制   option   hash   

人气教程排行