时间:2021-07-01 10:21:17 帮助过:3人阅读
- <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>
- <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;">] ...
- </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;"> ...
- </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] ...}]
- </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;"> ...]
- </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">password_option | lock_option</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ...
- </span><span style="color: #ff00ff;">user</span><span style="color: #000000;">:
- (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”)
- auth_option: {
- 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>
- <span style="color: #808080;">|</span> IDENTIFIED <span style="color: #0000ff;">WITH</span><span style="color: #000000;"> auth_plugin
- </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>
- <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;">
- }
- tls_option: {
- SSL
- </span><span style="color: #808080;">|</span><span style="color: #000000;"> X509
- </span><span style="color: #808080;">|</span> CIPHER <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">cipher</span><span style="color: #ff0000;">‘</span>
- <span style="color: #808080;">|</span> ISSUER <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">issuer</span><span style="color: #ff0000;">‘</span>
- <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;">
- }
- resource_option: {
- MAX_QUERIES_PER_HOUR </span><span style="color: #ff00ff;">count</span>
- <span style="color: #808080;">|</span> MAX_UPDATES_PER_HOUR <span style="color: #ff00ff;">count</span>
- <span style="color: #808080;">|</span> MAX_CONNECTIONS_PER_HOUR <span style="color: #ff00ff;">count</span>
- <span style="color: #808080;">|</span> MAX_USER_CONNECTIONS <span style="color: #ff00ff;">count</span><span style="color: #000000;">
- }
- password_option: {
- PASSWORD EXPIRE </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">DEFAULT | NEVER | INTERVAL N DAY</span><span style="color: #ff0000;">]</span>
- <span style="color: #808080;">|</span> PASSWORD HISTORY {<span style="color: #0000ff;">DEFAULT</span> <span style="color: #808080;">|</span><span style="color: #000000;"> N}
- </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;">}
- </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;">
- }
- lock_option: {
- ACCOUNT LOCK
- </span><span style="color: #808080;">|</span><span style="color: #000000;"> ACCOUNT UNLOCK
- }</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)。
最简单的就是指定账户名+密码
- <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>;
加上认证插件
- <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>;
指定密码过期,以便用户第一次使用的时候需要修改密码
- <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;
也可以指定每隔一段时间修改一次新密码
- <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>;
可以指定加密连接
- <span style="color: #008080;">--</span><span style="color: #008080;"> 不使用加密连接</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><span style="color: #000000;"> REQUIRE NONE;
- </span><span style="color: #008080;">--</span><span style="color: #008080;"> 使用加密连接</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><span style="color: #000000;"> REQUIRE SSL;
- </span><span style="color: #008080;">--</span><span style="color: #008080;"> 使用加密连接,并要求客户端提供有效证书</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><span style="color: #000000;"> REQUIRE X509;
- </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;">;
- </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;">;
- </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>;
可以指定资源控制
- <span style="color: #008080;">--</span><span style="color: #008080;"> 单位小时内,账户被允许查询500次,更新100次,单位小时内最大连接数不限制。最大并发连接数不限制</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> <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>;
可以锁定账户
- <span style="color: #008080;">--</span><span style="color: #008080;"> 锁定</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><span style="color: #000000;"> ACCOUNT LOCK
- </span><span style="color: #008080;">--</span><span style="color: #008080;"> 解锁</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
最后完整的命令选项大概这个样子
- <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>
- <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: #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;">] ...
- </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] ...}]
- </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;"> ...]
- </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">password_option | lock_option</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ...
- </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
- </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: #0000ff;">DEFAULT</span><span style="color: #000000;"> ROLE
- {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;"> ...}
- </span><span style="color: #ff00ff;">user</span><span style="color: #000000;">:
- (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”)
- auth_option: {
- 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>
- <span style="color: #ff0000;">[</span><span style="color: #ff0000;">REPLACE ‘current_auth_string‘</span><span style="color: #ff0000;">]</span>
- <span style="color: #ff0000;">[</span><span style="color: #ff0000;">RETAIN CURRENT PASSWORD</span><span style="color: #ff0000;">]</span>
- <span style="color: #808080;">|</span> IDENTIFIED <span style="color: #0000ff;">WITH</span><span style="color: #000000;"> auth_plugin
- </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>
- <span style="color: #ff0000;">[</span><span style="color: #ff0000;">REPLACE ‘current_auth_string‘</span><span style="color: #ff0000;">]</span>
- <span style="color: #ff0000;">[</span><span style="color: #ff0000;">RETAIN CURRENT PASSWORD</span><span style="color: #ff0000;">]</span>
- <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>
- <span style="color: #808080;">|</span><span style="color: #000000;"> DISCARD OLD PASSWORD
- }
- user_func_auth_option: {
- 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>
- <span style="color: #ff0000;">[</span><span style="color: #ff0000;">REPLACE ‘current_auth_string‘</span><span style="color: #ff0000;">]</span>
- <span style="color: #ff0000;">[</span><span style="color: #ff0000;">RETAIN CURRENT PASSWORD</span><span style="color: #ff0000;">]</span>
- <span style="color: #808080;">|</span><span style="color: #000000;"> DISCARD OLD PASSWORD
- }
- tls_option: {
- SSL
- </span><span style="color: #808080;">|</span><span style="color: #000000;"> X509
- </span><span style="color: #808080;">|</span> CIPHER <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">cipher</span><span style="color: #ff0000;">‘</span>
- <span style="color: #808080;">|</span> ISSUER <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">issuer</span><span style="color: #ff0000;">‘</span>
- <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;">
- }
- resource_option: {
- MAX_QUERIES_PER_HOUR </span><span style="color: #ff00ff;">count</span>
- <span style="color: #808080;">|</span> MAX_UPDATES_PER_HOUR <span style="color: #ff00ff;">count</span>
- <span style="color: #808080;">|</span> MAX_CONNECTIONS_PER_HOUR <span style="color: #ff00ff;">count</span>
- <span style="color: #808080;">|</span> MAX_USER_CONNECTIONS <span style="color: #ff00ff;">count</span><span style="color: #000000;">
- }
- password_option: {
- PASSWORD EXPIRE </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">DEFAULT | NEVER | INTERVAL N DAY</span><span style="color: #ff0000;">]</span>
- <span style="color: #808080;">|</span> PASSWORD HISTORY {<span style="color: #0000ff;">DEFAULT</span> <span style="color: #808080;">|</span><span style="color: #000000;"> N}
- </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;">}
- </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;">
- }
- lock_option: {
- ACCOUNT LOCK
- </span><span style="color: #808080;">|</span><span style="color: #000000;"> ACCOUNT UNLOCK
- }</span>
选项和创建的差不多,这里不做解释了。
修改自己当前的密码
- <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>;
修改账户密码
- <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>;
修改认证插件
- <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;">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>;
修改角色
- <span style="color: #008080;">--</span><span style="color: #008080;"> 授予自定义角色</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> <span style="color: #0000ff;">DEFAULT</span><span style="color: #000000;"> ROLE your_role_name;
- </span><span style="color: #008080;">--</span><span style="color: #008080;"> 无角色</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> <span style="color: #0000ff;">DEFAULT</span><span style="color: #000000;"> ROLE NONE;
- </span><span style="color: #008080;">--</span><span style="color: #008080;"> 所有角色</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> <span style="color: #0000ff;">DEFAULT</span> ROLE <span style="color: #808080;">ALL</span>;
修改加密方式
- <span style="color: #008080;">--</span><span style="color: #008080;"> 只有账户密码正确,无须加密连接</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><span style="color: #000000;"> REQUIRE NONE;
- </span><span style="color: #008080;">--</span><span style="color: #008080;"> 需要加密连接</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><span style="color: #000000;"> REQUIRE SSL;
- ...</span>
修改资源访问
- <span style="color: #008080;">--</span><span style="color: #008080;"> 单位小时内,最大查询数量和更新数量</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> <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>;
指定密码过期
- <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;
修改锁定解锁
- <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;
- </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;
- <span style="color: #0000ff;">GRANT</span><span style="color: #000000;">
- priv_type </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">(column_list)</span><span style="color: #ff0000;">]</span>
- <span style="color: #ff0000;">[</span><span style="color: #ff0000;">, priv_type [(column_list)</span><span style="color: #ff0000;">]</span><span style="color: #000000;">] ...
- </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
- </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;"> ...
- </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">WITH GRANT OPTION</span><span style="color: #ff0000;">]</span>
- <span style="color: #ff0000;">[</span><span style="color: #ff0000;">AS user
- [WITH ROLE
- DEFAULT
- | NONE
- | ALL
- | ALL EXCEPT role [, role </span><span style="color: #ff0000;">]</span><span style="color: #000000;"> ...
- </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;"> ...
- ]
- ]
- }
- </span><span style="color: #0000ff;">GRANT</span> PROXY <span style="color: #0000ff;">ON</span><span style="color: #000000;"> user_or_role
- </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;"> ...
- </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">WITH GRANT OPTION</span><span style="color: #ff0000;">]</span>
- <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;"> ...
- </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;"> ...
- </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">WITH ADMIN OPTION</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
- object_type: {
- </span><span style="color: #0000ff;">TABLE</span>
- <span style="color: #808080;">|</span> <span style="color: #0000ff;">FUNCTION</span>
- <span style="color: #808080;">|</span> <span style="color: #0000ff;">PROCEDURE</span><span style="color: #000000;">
- }
- priv_level: {
- </span><span style="color: #808080;">*</span>
- <span style="color: #808080;">|</span> <span style="color: #808080;">*</span>.<span style="color: #808080;">*</span>
- <span style="color: #808080;">|</span> <span style="color: #ff00ff;">db_name</span>.<span style="color: #808080;">*</span>
- <span style="color: #808080;">|</span> <span style="color: #ff00ff;">db_name</span><span style="color: #000000;">.tbl_name
- </span><span style="color: #808080;">|</span><span style="color: #000000;"> tbl_name
- </span><span style="color: #808080;">|</span> <span style="color: #ff00ff;">db_name</span><span style="color: #000000;">.routine_name
- }
- user_or_role: {
- </span><span style="color: #ff00ff;">user</span>
- <span style="color: #808080;">|</span><span style="color: #000000;"> role
- }
- </span><span style="color: #ff00ff;">user</span><span style="color: #000000;">:
- (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”)
- role:
- (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不能再一个语句中同时授予权限和角色。
- <span style="color: #008080;">--</span><span style="color: #008080;"> 授予数据库db1的所有权限给指定账户</span>
- <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;">;
- </span><span style="color: #008080;">--</span><span style="color: #008080;"> 授予角色给指定的账户</span>
- <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;">;
- </span><span style="color: #008080;">--</span><span style="color: #008080;"> 授予数据库world的SELECT权限给指定的角色</span>
- <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>;
基本语法
- <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;"> ...;
- </span><span style="color: #008080;">--</span><span style="color: #008080;"> 授予所有数据库的权限</span>
- <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 *.*
语法
下面是权限列表
权限范围示例
- <span style="color: #008080;">--</span><span style="color: #008080;"> 数据库权限</span>
- <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;">;
- </span><span style="color: #008080;">--</span><span style="color: #008080;"> 表权限</span>
- <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;">;
- </span><span style="color: #008080;">--</span><span style="color: #008080;"> 列权限</span>
- <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;">;
- </span><span style="color: #008080;">--</span><span style="color: #008080;"> 存储过程权限</span>
- <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;">;
- </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