当前位置：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   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！