关于mysql:MySQL的账户设置

应用 docker 装置 MySQL 并疾速启动，当初咱们进入 docker 容器。

➜  ~ docker exec -it mysql8 /bin/bash
root@dedd71769326:/#

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.

因为 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 命令语法

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';

MySQL8 里新退出了对于角色的治理，上面就简略的说一下如何应用：
角色能够了解为一组权限的汇合，而后将角色赋给某个帐户，该帐户就领有了角色对应的权限，每个帐户能够领有多个角色，就像游戏里，你能够有很多名称一样。

-- 名字标准
'role_name'@'host_name'
-- 通常仅应用用户名局部指定角色名称，并隐式应用主机名局部 '%'，主机名局部没有任何意义
'admin'

创立角色

-- 省略主机名，默认为 '%'
CREATE ROLE 'admin', 'dev';
-- 这种也能够，然而没意义
CREATE ROLE 'app'@'localhost';

移除角色

DROP ROLE 'admin', 'dev';

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 命令语法

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 [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
    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';

MySQL 的账户设置

MySQL 数据库连贯

MySQL 账户查看

查看已有账户

查看以后账户

MySQL 账户创立

MySQL 角色创立

MySQL 账户更新

MySQL 账户受权

FLUSH 语法

REVOKE 语法