MySQL 的账户设置
应用 docker
装置 MySQL
并疾速启动,当初咱们进入 docker
容器。
➜ ~ docker exec -it mysql8 /bin/bash
root@dedd71769326:/#
MySQL 数据库连贯
MySQL 命令语法
用户名是你登录的用户,主机名或者 IP 地址为可选项,如果是本地连接则不须要设置,近程连贯服务端则须要填写,明码是对应用户的明码。
mysql –u 用户名 [–h 主机名或者 IP 地址,- P 端口号] –p 明码
-u
:登录的用户名。-h
:近程主机名或 IP 地址,不填写则默认本地地址。-P
:MySQL
端口号,默认为 3306。-p
:该登录用户对应的登录明码。
root@dedd71769326:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.21 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL 账户查看
因为 root
权限很高,所以个别我的项目上会调配不同的账户和权限供程序员操作。
查看已有账户
mysql> select user from mysql.user;
+------------------+
| user |
+------------------+
| root |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
5 rows in set (0.03 sec)
为什么有两条 root
信息?咱们来具体看一下。
mysql> select user, host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
这里 host
字段代表容许任意 ip 地址登录 MySQL
。目前root
账户容许近程和本地登录。
查看以后账户
mysql> select current_user;
+----------------+
| current_user |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
如果咱们应用内部电脑连贯
mysql> select current_user;
+----------------+
| current_user |
+----------------+
| root@% |
+----------------+
1 row in set (0.00 sec)
则示意以后登陆 root
账户容许近程和本地登录。
MySQL 账户创立
MySQL 命令语法
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 'tian'@'localhost' IDENTIFIED BY 'password';
加上认证插件
CREATE USER 'tian'@'localhost' IDENTIFIED WITH sha256_password BY 'password';
指定明码过期,以便用户第一次应用的时候须要批改明码
CREATE USER 'tian'@'localhost' IDENTIFIED BY 'new_password' PASSWORD EXPIRE;
也能够指定每隔一段时间批改一次新密码
CREATE USER 'tian'@'localhost' IDENTIFIED BY 'new_password' PASSWORD EXPIRE INTERVAL 180 DAY;
能够指定加密连贯
-- 不应用加密连贯
CREATE USER 'tian'@'localhost' REQUIRE NONE;-- 应用加密连贯
CREATE USER 'tian'@'localhost' REQUIRE SSL;
-- 应用加密连贯,并要求客户端提供无效证书
CREATE USER 'tian'@'localhost' REQUIRE X509;
CREATE USER 'tian'@'localhost' REQUIRE ISSUER 'CA 颁发的无效 X.509 证书';
CREATE USER 'tian'@'localhost' REQUIRE SUBJECT '蕴含主题的无效 X.509 证书';
CREATE USER 'tian'@'localhost' REQUIRE CIPHER '指定的加密办法';
能够指定资源管制
-- 单位小时内,账户被容许查问 500 次,更新 100 次,单位小时内最大连接数不限度。最大并发连接数不限度
CREATE USER 'tian'@'localhost' WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100 MAX_CONNECTIONS_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
能够锁定账户
-- 锁定
CREATE USER 'tian'@'localhost' ACCOUNT LOCK
-- 解锁
ALTER USER 'tian'@'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]
如果你要删除账户
DROP USER 'tian'@'localhost';
如果你要批改名称
RENAME USER 'tian'@'localhost' TO 'tina'@'127.0.0.1';
MySQL 角色创立
MySQL8 里新退出了对于角色的治理,上面就简略的说一下如何应用:
角色能够了解为一组权限的汇合,而后将角色赋给某个帐户,该帐户就领有了角色对应的权限,每个帐户能够领有多个角色,就像游戏里,你能够有很多名称一样。
-- 名字标准
'role_name'@'host_name'
-- 通常仅应用用户名局部指定角色名称,并隐式应用主机名局部 '%',主机名局部没有任何意义
'admin'
创立角色
-- 省略主机名,默认为 '%'
CREATE ROLE 'admin', 'dev';
-- 这种也能够,然而没意义
CREATE ROLE 'app'@'localhost';
移除角色
DROP ROLE 'admin', 'dev';
MySQL 账户更新
MySQL 命令语法
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 'tian'@'localhost' IDENTIFIED BY 'new_password';
批改认证插件
ALTER USER 'tian'@'localhost' IDENTIFIED WITH mysql_native_password;
批改明码和插件
ALTER USER 'tian'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';
批改角色
-- 授予自定义角色
ALTER USER 'tian'@'localhost' DEFAULT ROLE your_role_name;
-- 无角色
ALTER USER 'tian'@'localhost' DEFAULT ROLE NONE;
-- 所有角色
ALTER USER 'tian'@'localhost' DEFAULT ROLE ALL;
批改加密形式
-- 只有账户明码正确,毋庸加密连贯
ALTER USER 'tian'@'localhost' REQUIRE NONE;
-- 须要加密连贯
ALTER USER 'tian'@'localhost' REQUIRE SSL;
...
批改资源拜访
-- 单位小时内,最大查问数量和更新数量
ALTER USER 'tian'@'localhost' WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;
指定明码过期
ALTER USER 'tian'@'localhost' PASSWORD EXPIRE;
批改锁定解锁
ALTER USER 'tian'@'localhost' ACCOUNT LOCK;
ALTER USER 'tian'@'localhost' ACCOUNT UNLOCK;
MySQL 账户受权
MySQL 命令语法
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
不能 再一个语句中同时授予权限和角色。
- 有 ON,是授予权限
- 无 ON,是授予角色
-- 授予数据库 db1 的所有权限给指定账户
GRANT ALL ON db1.* TO 'tian'@'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 *.*
语法
上面是权限列表
mysql> show privileges;
+----------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+----------------------------+---------------------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |
| Create role | Server Admin | To create new roles |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create view | Tables | To create new views |
| Create user | Server Admin | To create new users |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases, tables, and views |
| Drop role | Server Admin | To drop roles |
| Event | Server Admin | To create, alter, drop and execute events |
| Execute | Functions,Procedures | To execute stored routines |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| Proxy | Server Admin | To make proxy user possible |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Show view | Tables | To see views with SHOW CREATE VIEW |
| Shutdown | Server Admin | To shut down the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Trigger | Tables | To use triggers |
| Create tablespace | Server Admin | To create/alter/drop tablespaces |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges - allow connect only |
| XA_RECOVER_ADMIN | Server Admin | |
| SHOW_ROUTINE | Server Admin | |
| RESOURCE_GROUP_USER | Server Admin | |
| SET_USER_ID | Server Admin | |
| SESSION_VARIABLES_ADMIN | Server Admin | |
| CLONE_ADMIN | Server Admin | |
| PERSIST_RO_VARIABLES_ADMIN | Server Admin | |
| ROLE_ADMIN | Server Admin | |
| BACKUP_ADMIN | Server Admin | |
| CONNECTION_ADMIN | Server Admin | |
| RESOURCE_GROUP_ADMIN | Server Admin | |
| INNODB_REDO_LOG_ARCHIVE | Server Admin | |
| BINLOG_ENCRYPTION_ADMIN | Server Admin | |
| REPLICATION_SLAVE_ADMIN | Server Admin | |
| SYSTEM_VARIABLES_ADMIN | Server Admin | |
| GROUP_REPLICATION_ADMIN | Server Admin | |
| SYSTEM_USER | Server Admin | |
| APPLICATION_PASSWORD_ADMIN | Server Admin | |
| TABLE_ENCRYPTION_ADMIN | Server Admin | |
| SERVICE_CONNECTION_ADMIN | Server Admin | |
| AUDIT_ADMIN | Server Admin | |
| BINLOG_ADMIN | Server Admin | |
| ENCRYPTION_KEY_ADMIN | Server Admin | |
| INNODB_REDO_LOG_ENABLE | Server Admin | |
| REPLICATION_APPLIER | Server Admin | |
+----------------------------+---------------------------------------+-------------------------------------------------------+
58 rows in set (0.00 sec)
权限范畴示例
-- 数据库权限
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';
受权之后能够应用 flush
命令使其立刻失效
FLUSH PRIVILEGES
FLUSH 语法
FLUSH [NO_WRITE_TO_BINLOG | LOCAL] {flush_option [, flush_option] ...
| tables_option
}
flush_option: {
BINARY LOGS
| ENGINE LOGS
| ERROR LOGS
| GENERAL LOGS
| HOSTS
| LOGS
| PRIVILEGES
| OPTIMIZER_COSTS
| RELAY LOGS [FOR CHANNEL channel]
| SLOW LOGS
| STATUS
| USER_RESOURCES
}
tables_option: {
TABLES
| TABLES tbl_name [, tbl_name] ...
| TABLES WITH READ LOCK
| TABLES tbl_name [, tbl_name] ... WITH READ LOCK
| TABLES tbl_name [, tbl_name] ... FOR EXPORT
}
FLUSH PRIVILEGES 蕴含以下操作
- 从新加载
mysql
零碎数据库中的grant
表中的权限信息,并革除caching_sha2_password
身份验证插件应用的内存缓存。 - 服务器读取蕴含动静特权调配的
global_grants
表,并注册其中的任何未注册特权。 - 服务器通过
GRANT、CREATE USER、CREATE SERVER 和 INSTALL PLUGIN
语句将信息缓存到内存中。对应的REVOKE、DROP USER、DROP SERVER 和 UNINSTALL
插件语句不会开释这些内存,因而对于执行许多导致缓存的语句实例的服务器,内存使用量将会减少。能够应用刷新特权开释此缓存内存。
FLUSH TABLES 蕴含以下操作
敞开所有关上的表,强制敞开所有正在应用的表,并刷新筹备好的语句缓存。
REVOKE 语法
既然能够受权,那么就能够撤销
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user_or_role [, user_or_role] ...
REVOKE ALL [PRIVILEGES], GRANT OPTION
FROM user_or_role [, user_or_role] ...
REVOKE PROXY ON user_or_role
FROM user_or_role [, user_or_role] ...
REVOKE role [, role] ...
FROM user_or_role [, user_or_role] ...
user_or_role: {
user
| role
}
user:
(see Section 6.2.4,“Specifying Account Names”)
role:
(see Section 6.2.5,“Specifying Role Names”.
REVOKE
能够实现权限或者角色的撤销(前提:领有 GRANT 权限和 REVOKE 权限)
-- 撤销用户的 INSERT 权限
REVOKE INSERT ON *.* FROM 'tian'@'localhost';
-- 撤销用户的指定角色
REVOKE 'role1', 'role2' FROM 'user1'@'localhost', 'user2'@'localhost';
-- 撤销角色的 INSERT 权限
REVOKE SELECT ON world.* FROM 'role3';
撤销所有权限(只能撤销权限,不能撤销角色)
-- 从账户或者角色上撤销所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_or_role [, user_or_role] ...
-- 撤销账户
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'tian'@'localhost'
-- 撤销角色
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'role3'
在全局上撤销权限(.)
-- 全局上撤销所有权限
REVOKE ALL ON *.* FROM 'tian'@'localhost';