时间：2021-07-01 10:21:17 帮助过：3人阅读
CREATE USER [IF NOT EXISTS] user [auth_option] [, user [auth_option]] ... DEFAULT ROLE role [, role ] ... [REQUIRE {NONE | tls_option [[AND] tls_option] ...}] [WITH resource_option [resource_option] ...] [password_option | lock_option] ... user: (see Section 6.2.4, “Specifying Account Names”) auth_option: { IDENTIFIED BY ‘auth_string‘ | IDENTIFIED WITH auth_plugin | IDENTIFIED WITH auth_plugin BY ‘auth_string‘ | IDENTIFIED WITH auth_plugin AS ‘hash_string‘ } tls_option: { SSL | X509 | CIPHER ‘cipher‘ | ISSUER ‘issuer‘ | SUBJECT ‘subject‘ } resource_option: { MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count } password_option: { PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY] | PASSWORD HISTORY {DEFAULT | N} | PASSWORD REUSE INTERVAL {DEFAULT | N DAY} | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL] } lock_option: { ACCOUNT LOCK | ACCOUNT UNLOCK }
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）。
最简单的就是指定账户名+密码
CREATE USER ‘jeffrey‘@‘localhost‘ IDENTIFIED BY ‘password‘;
加上认证插件
CREATE USER ‘jeffrey‘@‘localhost‘ IDENTIFIED WITH sha256_password BY ‘password‘;
指定密码过期，以便用户第一次使用的时候需要修改密码
CREATE USER ‘jeffrey‘@‘localhost‘ IDENTIFIED BY ‘new_password‘ PASSWORD EXPIRE;
也可以指定每隔一段时间修改一次新密码
CREATE USER ‘jeffrey‘@‘localhost‘ IDENTIFIED BY ‘new_password‘ PASSWORD EXPIRE INTERVAL 180 DAY;
可以指定加密连接
-- 不使用加密连接 CREATE USER ‘jeffrey‘@‘localhost‘ REQUIRE NONE； -- 使用加密连接 CREATE USER ‘jeffrey‘@‘localhost‘ REQUIRE SSL; -- 使用加密连接，并要求客户端提供有效证书 CREATE USER ‘jeffrey‘@‘localhost‘ REQUIRE X509; CREATE USER ‘jeffrey‘@‘localhost‘ REQUIRE ISSUER ‘CA颁发的有效X.509证书‘; CREATE USER ‘jeffrey‘@‘localhost‘ REQUIRE SUBJECT ‘包含主题的有效X.509证书‘; CREATE USER ‘jeffrey‘@‘localhost‘ REQUIRE CIPHER ‘指定的加密方法‘;
可以指定资源控制
-- 单位小时内，账户被允许查询500次，更新100次，单位小时内最大连接数不限制。最大并发连接数不限制 CREATE USER ‘jeffrey‘@‘localhost‘ WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100 MAX_CONNECTIONS_PER_HOUR 0 MAX_USER_CONNECTIONS 0；
可以锁定账户
-- 锁定 CREATE USER ‘jeffrey‘@‘localhost‘ ACCOUNT LOCK -- 解锁 ALTER USER ‘jeffrey‘@‘localhost‘ ACCOUNT UNLOCK
最后完整的命令选项大概这个样子
CREATE USER ‘user_name‘@‘host_name‘ IDENTIFIED [WITH auth_plugin] BY ‘auth_string‘ [REQUIRE NONE(SSL,X509)] [WITH MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count] [PASSWORD EXPIRE] [ACCOUNT LOCK]
ALTER USER [IF EXISTS] user [auth_option] [, user [auth_option]] ... [REQUIRE {NONE | tls_option [[AND] tls_option] ...}] [WITH resource_option [resource_option] ...] [password_option | lock_option] ... ALTER USER [IF EXISTS] USER() user_func_auth_option ALTER USER [IF EXISTS] user DEFAULT ROLE {NONE | ALL | role [, role ] ...} user: (see Section 6.2.4, “Specifying Account Names”) auth_option: { IDENTIFIED BY ‘auth_string‘ [REPLACE ‘current_auth_string‘] [RETAIN CURRENT PASSWORD] | IDENTIFIED WITH auth_plugin | IDENTIFIED WITH auth_plugin BY ‘auth_string‘ [REPLACE ‘current_auth_string‘] [RETAIN CURRENT PASSWORD] | IDENTIFIED WITH auth_plugin AS ‘auth_string‘ | DISCARD OLD PASSWORD } user_func_auth_option: { IDENTIFIED BY ‘auth_string‘ [REPLACE ‘current_auth_string‘] [RETAIN CURRENT PASSWORD] | DISCARD OLD PASSWORD } tls_option: { SSL | X509 | CIPHER ‘cipher‘ | ISSUER ‘issuer‘ | SUBJECT ‘subject‘ } resource_option: { MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count } password_option: { PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY] | PASSWORD HISTORY {DEFAULT | N} | PASSWORD REUSE INTERVAL {DEFAULT | N DAY} | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL] } lock_option: { ACCOUNT LOCK | ACCOUNT UNLOCK }
选项和创建的差不多，这里不做解释了。
修改自己当前的密码
ALTER USER USER() IDENTIFIED BY ‘new_password‘;
修改账户密码
ALTER USER ‘jeffrey‘@‘localhost‘ IDENTIFIED BY ‘new_password‘;
修改认证插件
ALTER USER ‘jeffrey‘@‘localhost‘ IDENTIFIED WITH mysql_native_password;
修改密码和插件
ALTER USER ‘jeffrey‘@‘localhost‘ IDENTIFIED WITH mysql_native_password BY ‘new_password‘;
修改角色
-- 授予自定义角色 ALTER USER ‘jeffrey‘@‘localhost‘ DEFAULT ROLE your_role_name; -- 无角色 ALTER USER ‘jeffrey‘@‘localhost‘ DEFAULT ROLE NONE; -- 所有角色 ALTER USER ‘jeffrey‘@‘localhost‘ DEFAULT ROLE ALL;
修改加密方式
-- 只有账户密码正确，无须加密连接 ALTER USER ‘jeffrey‘@‘localhost‘ REQUIRE NONE; -- 需要加密连接 ALTER USER ‘jeffrey‘@‘localhost‘ REQUIRE SSL; ...
修改资源访问
-- 单位小时内，最大查询数量和更新数量 ALTER USER ‘jeffrey‘@‘localhost‘ WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;
指定密码过期
ALTER USER ‘jeffrey‘@‘localhost‘ PASSWORD EXPIRE;
修改锁定解锁
ALTER USER ‘jeffrey‘@‘localhost‘ ACCOUNT LOCK; ALTER USER ‘jeffrey‘@‘localhost‘ ACCOUNT UNLOCK;
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_or_role [, user_or_role] ... [WITH GRANT OPTION] [AS user [WITH ROLE DEFAULT | NONE | ALL | ALL EXCEPT role [, role ] ... | role [, role ] ... ] ] } GRANT PROXY ON user_or_role TO user_or_role [, user_or_role] ... [WITH GRANT OPTION] GRANT role [, role] ... TO user_or_role [, user_or_role] ... [WITH ADMIN OPTION] object_type: { TABLE | FUNCTION | PROCEDURE } priv_level: { * | *.* | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_name } user_or_role: { user | role } user: (see Section 6.2.4, “Specifying Account Names”) role: (see Section 6.2.5, “Specifying Role Names”)
GRANT语法使得管理员能够授予账户权限或者角色，但是GRANT不能再一个语句中同时授予权限和角色。
-- 授予数据库db1的所有权限给指定账户 GRANT ALL ON db1.* TO ‘jeffrey‘@‘localhost‘; -- 授予角色给指定的账户 GRANT ‘role1‘, ‘role2‘ TO ‘user1‘@‘localhost‘, ‘user2‘@‘localhost‘; -- 授予数据库world的SELECT权限给指定的角色 GRANT SELECT ON world.* TO ‘role3‘;
基本语法
GRANT [权限] ON [数据库名].[表名] TO ‘user_name‘@‘localhost‘ ...; -- 授予所有数据库的权限 GRANT [权限] ON *.* TO ‘user_name‘@‘localhost‘ ...;
注：全局权限是管理或适用于给定服务器上的所有数据库。要分配全局权限，请使用 ON *.*语法
下面是权限列表
权限范围示例
-- 数据库权限 GRANT ALL ON mydb.* TO ‘user_name‘@‘host_name‘; -- 表权限 GRANT ALL ON mydb.mytable TO ‘user_name‘@‘host_name‘; -- 列权限 GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytable TO ‘user_name‘@‘host_name‘; -- 存储过程权限 GRANT CREATE ROUTINE ON mydb.* TO ‘user_name‘@‘host_name‘; GRANT EXECUTE ON PROCEDURE mydb.myproc TO ‘user_name‘@‘host_name‘;
MySQL创建用户与授权
标签：列表 技术 简单 数据 ide alt 控制 option hash