MySQL 账户户管理
MySQL权限处理原则
权限，通常都是用户A拥有对象B的权限。在Mysql中，加了一个维度，从哪儿来？即来源主机。
权限认证的目的，都是为了让用户只能做允许他做的事情。MySQL采用的是白名单的权限策略。也就是说，明确指定了哪些用户能够做什么，但没法明确的指定某些用户不能做什么，对权限的验证主要是通过mysql库下的几个数据字典表，来实现不同粒度的权限需求。
权限的处理逻辑，MySQL在检查用户连接时可以分为两个阶段：
1）能不能连接。
在其他大型数据库中，直接指定用户即可登录数据库，但在MySQL数据库中，则额外还需要有主机这一维度，用户和主机(‘user’@‘host’)组成一个唯一账户，登录MySQL数据库时，实际上是通过账户进行验证。
host：IP地址，主机名，或者可以被正确解析至IP地址的域名，支持通配符。
host可以指定为通配符，有2中：1）%：对应任意长度的任意字符；2）_:对应一位长度的任意字符。
host为空或%，表示任意主机。
可以有如下形式：
localhost
www.enmo.com
10.1.11.30
10.1.11.% 或 10.1.11._
10.1.11.0/255.255.255.0
user：建议少于10个字符，不限制使用特殊字符。也可以为空。表示任意用户，随便输一个用户名，均可登录。
所以在MySQL里面，不再通过用户名来确认唯一一条记录，而是通过’user’@'host’来确认记录是否唯一。user表中每一条记录都是一个独立的账户，每一个独立的账户都可以拥有各自的权限设置。
匹配原则：精确匹配。最明确的值优先匹配。
MySQL5.7之后，user字典表中的password字段用authentication_string代替。
所以唯一确定一行记录，可以用下面的查看：
root@postgre 11:28: [(none)]> select user,host,authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user | host | authentication_string |
+------------------+-----------+------------------------------------------------------------------------+
| test1 | % | |
| test3 | % | $A$005$=/qZ{p`rOCdlk@%8gwPEVnL4MRENc6wfwdyXOVhfPGvIl2Zad3Uvr5NV13. |
| test2 | 10.1.11.% | $A$005$u
A0Z!QnZ%[5 UR6uWrKcIMxG2cAm5qEEzsqzt6PNPiV00rxK9H35zT5eYy5 |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root | localhost | |
| enmo_app | zhuo%.com | |
+------------------+-----------+------------------------------------------------------------------------+
8 rows in set (0.00 sec)
user名字，host来源，authentication_string密码。密码和user都可以为空，表示登录不用密码，随便哪个用户等可以登录。
验证时，需要检查3项值：user字典表中user，host，authentication_string。
2）能不能执行操作
连接数据库成功后，能不能执行操作。就是要授权，授权的原则就是按照粒度。
查看没有密码的用户：
root@postgre 22:46: [(none)]> select user,host from mysql.user where authentication_string='';
+----------+-----------+
| user | host |
+----------+-----------+
| test1 | % |
| root | localhost |
用户管理
创建用户
用户和主机(‘user’@‘host’)组成一个唯一账户
用户连接匹配原则：精确匹配原则。
最简单的用户创建，无密码，无主机来源。
root@postgre 21:04: [(none)]> create user test1;
Query OK, 0 rows affected (0.06 sec)
root@postgre 21:04: [(none)]> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| test1 | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
登录测试，随便哪个客户端，不用密码都可以登录：
[root@oracle19c ~]# /usr/local/mysql/bin/mysql -h 10.1.11.30 -P3306 -utest1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> select user(),current_user();
+------------------+----------------+
| user() | current_user() |
+------------------+----------------+
| test1@10.1.11.10 | test1@% |
+------------------+----------------+
1 row in set (0.00 sec)
创建本地test2用户，只能本地登录。
root@postgre 21:05: [(none)]> create user test2@localhost identified by 'test2';
Query OK, 0 rows affected (0.04 sec)
root@postgre 21:05: [(none)]> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| test1 | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| test2 | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
修改密码
test3@postgre 22:26: [(none)]> alter user test3 identified by 'mysql';
Query OK, 0 rows affected (0.04 sec)
alter user ‘root’@‘%’ identified by ‘xxxx’;
alter user ‘root’@‘localhost’ identified by ‘xxxx’;
查看权限
show grants
查看帮助
root@postgre 22:31: [(none)]> help show grants
Name: 'SHOW GRANTS'
Description:
Syntax:
SHOW GRANTS
[FOR user_or_role
[USING role [, role] ...]]
user_or_role: {
user (see )
| role (see .
}
。。。
mysql> SHOW GRANTS FOR 'jeffrey'@'localhost';
+------------------------------------------------------------------+
| Grants for jeffrey@localhost |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `jeffrey`@`localhost` |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO `jeffrey`@`localhost` |
+------------------------------------------------------------------+
The host part, if omitted, defaults to '%'. For additional information
about specifying account and role names, see
https://dev.mysql.com/doc/refman/8.0/en/account-names.html, and
https://dev.mysql.com/doc/refman/8.0/en/role-names.html.
To display the privileges granted to the current user (the account you
are using to connect to the server), you can use any of the following
statements:
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
。。。。
查看当前登录用户的权限，有以下三种：
test1@postgre 22:34: [(none)]> select user(),current_user();
+-----------------+----------------+
| user() | current_user() |
+-----------------+----------------+
| test1@localhost | test1@% |
+-----------------+----------------+
1 row in set (0.00 sec)
test1@postgre 22:34: [(none)]> SHOW GRANTS;
+-----------------------------------+
| Grants for test1@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)
test1@postgre 22:34: [(none)]> SHOW GRANTS FOR CURRENT_USER;
+-----------------------------------+
| Grants for test1@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)
test1@postgre 22:34: [(none)]> SHOW GRANTS FOR CURRENT_USER();
+-----------------------------------+
| Grants for test1@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)
查看非当前用户：
root@postgre 22:33: [(none)]> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| test1 | % |
| test2 | 10.1.11.% |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
root@postgre 22:36: [(none)]> show grants for 'test2'@'10.1.11.%';
+-------------------------------------------+
| Grants for test2@10.1.11.% |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `test2`@`10.1.11.%` |
+-------------------------------------------+
1 row in set (0.00 sec)
删除用户
删除用户要精确，用户名@来源
root@postgre 21:10: [(none)]> drop user test2;
ERROR 1396 (HY000): Operation DROP USER failed for ‘test2’@‘%’
从日志都都可以看出，不带来源，删除的是’test2’@'%'账号，而这个账号我们库里并没有。
如果我们不知道库里有哪些账号，可以查询user表。
root@postgre 21:11: [(none)]> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| test1 | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| test2 | localhost |
+------------------+-----------+
6 rows in set (0.01 sec)
root@postgre 21:17: [(none)]> drop user test2@localhost;
Query OK, 0 rows affected (0.04 sec)
root@postgre 21:18: [(none)]> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| test1 | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.01 sec)
客户端连接并没有实际断开，之前连接进来的客户端，依然还能够进行查询。
test2@postgre 13:32: [(none)]> show tables;
ERROR 1046 (3D000): No database selected
test2@postgre 13:33: [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
test2@postgre 13:33: [(none)]> select user(),current_user();
+-----------------+-----------------+
| user() | current_user() |
+-----------------+-----------------+
| test2@localhost | test2@localhost |
+-----------------+-----------------+
1 row in set (0.00 sec)
他的权限是固定在THD中。句柄里的。如果退出之前的已有连接，就连不上了。
[root@postgre ~]# /usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql3306/my3306.cnf -S /tmp/mysql3306.sock -utest2 -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test2'@'localhost' (using password: YES)
drop user不会自动中止已连接的用户会话，也就是说被删的用户如果在删前已经连接上了服务器，并且连接尚未中断，那它此时还能继续执行一定的操作，只是他的身份已经变成了黑户。
利用精确匹配拒绝某台应用连接
create user test2@‘10.1.11.%’ identified by ‘mysql’;
grant all privileges on . to test2@‘10.1.11.%’;
create user test2@‘10.1.11.10’ identified by ‘oracle’;
创建模糊匹配用户并授权：
root@postgre 10:47: [(none)]> create user test2@'10.1.11.%' identified by 'mysql';
Query OK, 0 rows affected (0.03 sec)c)
root@postgre 13:04: [(none)]> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| test1 | % |
| test2 | 10.1.11.% |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
root@postgre 22:43: [(none)]> grant all privileges on *.* to test2@'10.1.11.%';
Query OK, 0 rows affected (0.07 sec)
客户端10.1.11.10此时可以登录：
[root@oracle19c ~]# /usr/local/mysql/bin/mysql -h 10.1.11.30 -P3306 -utest2 -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> select user(),current_user();
+------------------+-----------------+
| user() | current_user() |
+------------------+-----------------+
| test2@10.1.11.10 | test2@10.1.11.% |
+------------------+-----------------+
1 row in set (0.00 sec)
创建精确账号，换一个密码：
root@postgre 13:08: [(none)]> create user test2@'10.1.11.10' identified by 'oracle';
Query OK, 0 rows affected (0.03 sec)
root@postgre 13:08: [(none)]> select user,host from mysql.user;
+------------------+------------+
| user | host |
+------------------+------------+
| test1 | % |
| test2 | 10.1.11.% |
| test2 | 10.1.11.10 |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+------------+
7 rows in set (0.00 sec)
客户端登录测试
客户端利用原来的密码已经登录不上去：
[root@oracle19c ~]# /usr/local/mysql/bin/mysql -h 10.1.11.30 -P3306 -utest2 -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test2'@'10.1.11.10' (using password: YES)
必须是新密码才能登录
[root@oracle19c ~]# /usr/local/mysql/bin/mysql -h 10.1.11.30 -P3306 -utest2 -poracle
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> select user(),current_user();
+------------------+------------------+
| user() | current_user() |
+------------------+------------------+
| test2@10.1.11.10 | test2@10.1.11.10 |
+------------------+------------------+
1 row in set (0.00 sec)
原来的mysql密码已经不能登录，只能登录oracle，精确创建的。
精准匹配的原则，最小化匹配。（另一个用处就是，如上，先创建一个%的，在创建个具体ip的，那么只有10.1.11.10这台机器登录不了mysql，要登陆，必须输入正确密码，但密码是新的。而剩余的机器都可以登录。变相达到了错误应用利用自己知道的密码连接不上数据库的目的。）
补充：
user()表示当前的登录用户
current_user()表示对应于mysql.user表里对应的账号。
授权
root@postgre 23:23: [(none)]> help grant;
Name: 'GRANT'
Description:
Syntax:
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 (see https://dev.mysql.com/doc/refman/8.0/en/account-names.html)
| role (see https://dev.mysql.com/doc/refman/8.0/en/role-names.html)
}
The GRANT statement enables system administrators to grant privileges
and roles, which can be granted to user accounts and roles. These
syntax restrictions apply:
o GRANT cannot mix granting both privileges and roles in the same
statement. A given GRANT statement must grant either privileges or
roles.
o The ON clause distinguishes whether the statement grants privileges
or roles:
o With ON, the statement grants privileges.
o Without ON, the statement grants roles.
o It is permitted to assign both privileges and roles to an account,
but you must use separate GRANT statements, each with syntax
appropriate to what is to be granted.
For more information about roles, see
https://dev.mysql.com/doc/refman/8.0/en/roles.html.
To grant a privilege with GRANT, you must have the GRANT OPTION
privilege, and you must have the privileges that you are granting.
(Alternatively, if you have the UPDATE privilege for the grant tables
in the mysql system schema, you can grant any account any privilege.)
When the read_only system variable is enabled, GRANT additionally
requires the CONNECTION_ADMIN privilege (or the deprecated SUPER
privilege).
GRANT either succeeds for all named users and roles or rolls back and
has no effect if any error occurs. The statement is written to the
binary log only if it succeeds for all named users and roles.
The REVOKE statement is related to GRANT and enables administrators to
remove account privileges. See [HELP REVOKE].
Each account name uses the format described in
https://dev.mysql.com/doc/refman/8.0/en/account-names.html. Each role
name uses the format described in
https://dev.mysql.com/doc/refman/8.0/en/role-names.html. For example:
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';
GRANT SELECT ON world.* TO 'role3';
The host name part of the account or role name, if omitted, defaults to
'%'.
Normally, a database administrator first uses CREATE USER to create an
account and define its nonprivilege characteristics such as its
password, whether it uses secure connections, and limits on access to
server resources, then uses GRANT to define its privileges. ALTER USER
may be used to change the nonprivilege characteristics of existing
accounts. For example:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
ALTER USER 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
From the mysql program, GRANT responds with Query OK, 0 rows affected
when executed successfully. To determine what privileges result from
the operation, use SHOW GRANTS. See [HELP SHOW GRANTS].
URL: https://dev.mysql.com/doc/refman/8.0/en/grant.html
1、用户连入MySQL后
2、用户是谁；
3、用户的权限是什么？
3.1 全局
3.2 数据库
3.3 表
3.4 列
3.5 程序：procedure和function
4、权限可以操作社么？
合理控制授予权限是MySQL DBA的一个重要职责。
所有的权限如下：
root@postgre 02:28: [(none)]> 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 |
| SHOW_ROUTINE | Server Admin | |
| RESOURCE_GROUP_USER | Server Admin | |
| REPLICATION_APPLIER | Server Admin | |
| PASSWORDLESS_USER_ADMIN | Server Admin | |
| INNODB_REDO_LOG_ENABLE | Server Admin | |
| XA_RECOVER_ADMIN | Server Admin | |
| GROUP_REPLICATION_STREAM | Server Admin | |
| GROUP_REPLICATION_ADMIN | Server Admin | |
| FLUSH_USER_RESOURCES | Server Admin | |
| FLUSH_TABLES | Server Admin | |
| PERSIST_RO_VARIABLES_ADMIN | Server Admin | |
| ROLE_ADMIN | Server Admin | |
| BACKUP_ADMIN | Server Admin | |
| CONNECTION_ADMIN | Server Admin | |
| SET_USER_ID | Server Admin | |
| SESSION_VARIABLES_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 | |
| SYSTEM_USER | Server Admin | |
| APPLICATION_PASSWORD_ADMIN | Server Admin | |
| TABLE_ENCRYPTION_ADMIN | Server Admin | |
| SERVICE_CONNECTION_ADMIN | Server Admin | |
| AUDIT_ADMIN | Server Admin | |
| AUTHENTICATION_POLICY_ADMIN | Server Admin | |
| BINLOG_ADMIN | Server Admin | |
| ENCRYPTION_KEY_ADMIN | Server Admin | |
| CLONE_ADMIN | Server Admin | |
| FLUSH_OPTIMIZER_COSTS | Server Admin | |
| FLUSH_STATUS | Server Admin | |
+-----------------------------+---------------------------------------+-------------------------------------------------------+
65 rows in set (0.00 sec)
所有权限，就有Server Admin，Databases，Tables，Functions,Procedures。
参考：https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html
全局： *.*
数据库： mysql.*
表： mysql.test1
列： select (列明) on mysql.test1
全局：
赋予全局create权限，所有的对象都可以create
root@postgre 19:17: [(none)]> grant create on *.* to test1;
Query OK, 0 rows affected (0.01 sec)
root@postgre 19:17: [(none)]> show grants for test1;
+------------------------------------+
| Grants for test1@% |
+------------------------------------+
| GRANT CREATE ON *.* TO `test1`@`%` |
+------------------------------------+
1 row in set (0.00 sec)
他就只有create权限，insert权限都没有。
test1@postgre 19:18: [test2]> create database test6;
Query OK, 1 row affected (0.00 sec)
test1@postgre 19:18: [test2]> use test6;
Database changed
test1@postgre 19:18: [test6]> create table test1 (id int);
Query OK, 0 rows affected (0.03 sec)
test1@postgre 19:18: [test6]> insert into test1 values(1);
ERROR 1142 (42000): INSERT command denied to user 'test1'@'localhost' for table 'test1'
数据库：
赋予数据库test6，select权限，test6库里所有的对象都可以select，包括查看database。
root@postgre 19:42: [(none)]> revoke CREATE ON *.* from test1;
Query OK, 0 rows affected (0.04 sec)
root@postgre 19:42: [(none)]> show grants for test1;
+-----------------------------------+
| Grants for test1@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)
root@postgre 19:42: [(none)]> grant select on test6.* to test1;
Query OK, 0 rows affected (0.03 sec)
root@postgre 19:43: [(none)]> show grants for test1;
+------------------------------------------+
| Grants for test1@% |
+------------------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%` |
| GRANT SELECT ON `test6`.* TO `test1`@`%` |
+------------------------------------------+
2 rows in set (0.01 sec)
验证：
只能select，其他的都不行。
[root@postgre ~]# /usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql3306/my3306.cnf -utest1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 48
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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.
test1@postgre 19:43: [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test6 |
+--------------------+
2 rows in set (0.00 sec)
test1@postgre 19:43: [(none)]> use test6;
Database changed
test1@postgre 19:43: [test6]> show tables;
+-----------------+
| Tables_in_test6 |
+-----------------+
| test1 |
+-----------------+
1 row in set (0.01 sec)
test1@postgre 19:43: [test6]> select * from test1;
Empty set (0.00 sec)
test1@postgre 19:43: [test6]> insert into test1 values(2);
ERROR 1142 (42000): INSERT command denied to user 'test1'@'localhost' for table 'test1'
test1@postgre 19:43: [test6]>
表：
root@postgre 19:43: [(none)]> revoke SELECT ON `test6`.* from test1;
Query OK, 0 rows affected (0.03 sec)
root@postgre 19:45: [(none)]> show grants for test1;
+-----------------------------------+
| Grants for test1@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)
root@postgre 19:46: [(none)]> grant insert on test6.test1 to test1;
Query OK, 0 rows affected (0.04 sec)
root@postgre 19:46: [(none)]> show grants for test1;
+------------------------------------------------+
| Grants for test1@% |
+------------------------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%` |
| GRANT INSERT ON `test6`.`test1` TO `test1`@`%` |
+------------------------------------------------+
2 rows in set (0.00 sec)
对表test1只能insert，select都没权限。
test1@postgre 19:47: [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test6 |
+--------------------+
2 rows in set (0.00 sec)
test1@postgre 19:47: [(none)]> use test6;
Database changed
test1@postgre 19:47: [test6]> show tables;
+-----------------+
| Tables_in_test6 |
+-----------------+
| test1 |
+-----------------+
1 row in set (0.00 sec)
test1@postgre 19:47: [test6]> commit;
Query OK, 0 rows affected (0.00 sec)
test1@postgre 19:47: [test6]> select * from test1;
ERROR 1142 (42000): SELECT command denied to user 'test1'@'localhost' for table 'test1'
权限总的来说，还是按照最小化，最精确的原则匹配。
有几个特殊权限：
ALL [PRIVILEGES]：
These privilege specifiers are shorthand for “all privileges available at a given privilege level” (except GRANT OPTION). For example, granting ALL at the global or table level grants all global privileges or all table-level privileges, respectively.
根据不通的level，是全局，还是库，表等。拥有所有的权限。
具体如下：
1)全局赋予all权限
root@postgre 18:46: [(none)]> grant all on *.* to test1;
Query OK, 0 rows affected (0.02 sec)
root@postgre 18:47: [(none)]> show grants for test1;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test1@% |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `test1`@`%` |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `test1`@`%` |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
替换完成后，
也就是前面show privileges显示的所有权限。
对于全局的all权限，就是数据库所有权限。
2）数据库赋予all权限
root@postgre 18:48: [(none)]> grant all on test2.* to test1;
Query OK, 0 rows affected (0.01 sec)
root@postgre 19:00: [(none)]> show grants for test1;
+--------------------------------------------------+
| Grants for test1@% |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%` |
| GRANT ALL PRIVILEGES ON `test2`.* TO `test1`@`%` |
+--------------------------------------------------+
2 rows in set (0.00 sec)
也就是除过show privileges里面的Server Admin权限，剩下的权限都有。（DML，DDL）。对该库下面的对象可以任意操作。
3）具体对象赋予all权限
root@postgre 19:02: [(none)]> grant all on test2.test4 to test1;
Query OK, 0 rows affected (0.01 sec)
root@postgre 19:02: [(none)]> show grants for test1;
+--------------------------------------------------------+
| Grants for test1@% |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%` |
| GRANT ALL PRIVILEGES ON `test2`.`test4` TO `test1`@`%` |
+--------------------------------------------------------+
2 rows in set (0.00 sec)
验证：
test1@postgre 19:05: [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test2 |
+--------------------+
2 rows in set (0.01 sec)
test1@postgre 19:05: [(none)]> use test2;
Database changed
test1@postgre 19:05: [test2]> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| test4 |
+-----------------+
1 row in set (0.00 sec)
test1@postgre 19:05: [test2]> select * from test4;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
test1@postgre 19:05: [test2]> insert into test4 values(2);
Query OK, 1 row affected (0.00 sec)
test1@postgre 19:05: [test2]> commit
-> ;
Query OK, 0 rows affected (0.00 sec)
对此表可以进行任意操作。
super：
查询具有super权限的用户：
SELECT GRANTEE FROM INFORMATION_SCHEMA.USER_PRIVILEGES
WHERE PRIVILEGE_TYPE = 'SUPER';
root@postgre 19:07: [(none)]> grant super on *.* to test1;
Query OK, 0 rows affected, 1 warning (0.01 sec)
root@postgre 19:07: [(none)]> show grants for test1;
+-----------------------------------+
| Grants for test1@% |
+-----------------------------------+
| GRANT SUPER ON *.* TO `test1`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)
root@postgre 21:51: [(none)]> grant super on test6.* to test1;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
权限比all大。自己琢磨。相当于oracle中的sys用户和拥有dba角色的用户了。
super是个global privileges，只能*.*，不能数据库授予。
Usage：
No privileges - allow connect only。
相当于oracle中的connect，resource角色权限，只能登录，在不能干啥。但是不同的是它不用单独赋予，create user的时候会自动赋予这个权限。
参数skip_name_resolve
本文的测试环境如下：
1）有2台服务器，ip为10.1.11.30的机器上面安装了mysql数据库，并正常启动。ip为10.1.11.10的机器上面安装了mysql客户端，仅作连接server用。
参数的基本含义验证
skip_name_resolve 字面意思：跳过名字解析，也就是跳过域名反解析。
此参数是个布尔值，所以要么出现在配置文件中，表示开启on。要么就不出现在配置文件中，默认就是off。开启如下：
[mysqld]
skip_name_resolve = 1
此时mysql数据库中有如下用户：
我们的配置文件中是开启的，现在设置注释掉，关闭，重启mysql，
root@postgre 19:40: [(none)]> shutdown;
Query OK, 0 rows affected (0.00 sec)
[root@postgre ~]# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
[1] 10909
root@postgre 19:44: [(none)]> select user,host from mysql.user;
+------------------+------------+
| user | host |
+------------------+------------+
| test1 | % |
| test2 | 10.1.11.% |
| | 10.1.11.10 |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+------------+
7 rows in set (0.01 sec)
我们在本地连接，新开一个窗口：
[root@postgre ~]# /usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql3306/my3306.cnf -h127.0.0.1 -P3306 -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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@postgre 19:45: [(none)]> select user(),current_user();
+----------------+----------------+
| user() | current_user() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)
root@postgre 19:45: [(none)]> \s
--------------
/usr/local/mysql/bin/mysql Ver 8.0.27 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id: 10
Current database:
Current user: root@localhost
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.27 MySQL Community Server - GPL
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 3306
Binary data as: Hexadecimal
Uptime: 4 min 5 sec
Threads: 3 Questions: 20 Slow queries: 0 Opens: 2926 Flush tables: 3 Open tables: 4 Queries per second avg: 0.081
--------------
root@postgre 19:45: [(none)]> show global variables like '%skip%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| replica_skip_errors | OFF |
| skip_external_locking | ON |
| skip_name_resolve | OFF |
| skip_networking | OFF |
| skip_replica_start | OFF |
| skip_show_database | OFF |
| skip_slave_start | OFF |
| slave_skip_errors | OFF |
| sql_replica_skip_counter | 0 |
| sql_slave_skip_counter | 0 |
+--------------------------+-------+
10 rows in set (0.00 sec)
注意以下几点：
1、登录的时候用的-h127.0.0.1，而从users表中可以看出，我们的来源主机并没有127.0.0.1这个地址，只有localhost和10.1.11.x网段，按理说用户名+来源主机不和user表匹配，会登录拒绝，但是我们还是登录成功了。
2、输出结果：| skip_name_resolve | OFF | 此时参数关闭
3、输出结果：Connection: 127.0.0.1 via TCP/IP —是通过ip地址连接的
4、输出结果：user():root@localhost。 当前登录用户来源主机尽然为localhost，而不是ip地址。
这就是这个参数的作用，mysql可以通过域名解析把127.0.0.1解析为localhost，相当于ip地址被反解析为域名了。查询当前登录用户，user()，尽然不是127.0.0.1，而是localhost，证明也被反解析了。
那么是通过什么反解析的哪？当然不是DNS就是本地解析hosts文件。我们没有DNS，肯定是hosts文件解析：
所以此参数的含义就是，on，表示禁用域名反解析。off，开启域名反解析。off的时候，开启了域名反解析，虽然我们登录的时候输入的是ip地址，而库里面的权限表user是域名。也就是库里的用户名+来源主机和客户端登录的用户名+主机不一致，但是能ip地址能通过hosts文件被解析为域名，所以我们仍然能够正常登录。
域名登录验证
我们创建一个域名用户，无密码。
root@postgre 19:51: [(none)]> create user 'enmo_app'@'zhuo%.com';
Query OK, 0 rows affected (0.05 sec)
root@postgre 19:53: [(none)]> select user,host from mysql.user;
+------------------+------------+
| user | host |
+------------------+------------+
| test1 | % |
| test2 | 10.1.11.% |
| | 10.1.11.10 |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| enmo_app | zhuo%.com |
+------------------+------------+
8 rows in set (0.00 sec)
客户端此时验证登录
[root@oracle19c ~]# /usr/local/mysql/bin/mysql -h10.1.11.30 -P3306 -uenmo_app
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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@postgre 21:56: [(none)]> show global variables like '%skip%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| replica_skip_errors | OFF |
| skip_external_locking | ON |
| skip_name_resolve | OFF |
| skip_networking | OFF |
| skip_replica_start | OFF |
| skip_show_database | OFF |
| skip_slave_start | OFF |
| slave_skip_errors | OFF |
| sql_replica_skip_counter | 0 |
| sql_slave_skip_counter | 0 |
+--------------------------+-------+
10 rows in set (0.03 sec)
mysql> select user(),current_user();
+---------------------+----------------+
| user() | current_user() |
+---------------------+----------------+
| enmo_app@10.1.11.10 | @10.1.11.10 |
+---------------------+----------------+
1 row in set (0.00 sec)
尽然能登录，我们发现在权限表user里面有一条
记录，根据精确匹配原则，数据库匹配到了这条，所以能登录，删除此纪录。
root@postgre 19:53: [(none)]> drop user ''@'10.1.11.10';
Query OK, 0 rows affected (0.03 sec)
root@postgre 19:55: [(none)]> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| test1 | % |
| test2 | 10.1.11.% |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| enmo_app | zhuo%.com |
+------------------+-----------+
7 rows in set (0.00 sec)
客户端验证
[root@oracle19c ~]# /usr/local/mysql/bin/mysql -h10.1.11.30 -P3306 -uenmo_app
ERROR 1045 (28000): Access denied for user 'enmo_app'@'10.1.11.10' (using password: NO)
此时已经登录不了了。
原因是什么哪？就是因为权限记录表中的这条记录| enmo_app | zhuo%.com |，主机来源是zhuo%.com，而我们的客户端主机名（oracle19c）不是这个，所以登录不上。主机来源错误，也就相当于用户名错误。
那么解决方案哪？
我们可以参考上面哪个参数，在服务器端利用hosts文件，把客户端ip地址解析为这个域名。
服务器端hosts文件修改：
[root@postgre ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.1.11.10 zhuo.zhuo.com
[root@postgre ~]# ping zhuo.zhuo.com
PING zhuo.zhuo.com (10.1.11.10) 56(84) bytes of data.
64 bytes from zhuo.zhuo.com (10.1.11.10): icmp_seq=1 ttl=64 time=0.319 ms
64 bytes from zhuo.zhuo.com (10.1.11.10): icmp_seq=2 ttl=64 time=0.512 ms
说明客户端ip 10.1.11.10 可以被解析为zhuo.zhuo.com，符合user表里面的行记录。
此时肯定能登录了。
客户端：
[root@oracle19c ~]# /usr/local/mysql/bin/mysql -h10.1.11.30 -P3306 -uenmo_app
ERROR 1045 (28000): Access denied for user 'enmo_app'@'10.1.11.10' (using password: NO)
还是不能登录。服务器端需要重启或者flush。
[root@postgre ~]# /usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql3306/my3306.cnf -S /tmp/mysql3306.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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@postgre 19:59: [(none)]> flush hosts;
Query OK, 0 rows affected, 1 warning (0.00 sec)
客户端验证：
[root@oracle19c ~]# /usr/local/mysql/bin/mysql -h10.1.11.30 -P3306 -uenmo_app
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> select user(),current_user();
+------------------------+--------------------+
| user() | current_user() |
+------------------------+--------------------+
| enmo_app@zhuo.zhuo.com | enmo_app@zhuo%.com |
+------------------------+--------------------+
1 row in set (0.00 sec)
可以登录了。
还可以查看登录信息
mysql> show processlist;
+----+----------+---------------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+---------------------+------+---------+------+-------+------------------+
| 16 | enmo_app | zhuo.zhuo.com:12084 | NULL | Query | 0 | init | show processlist |
+----+----------+---------------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
注意host 字段的值。ip已经在服务器端被解析为我们定义的主机名了。
我们可以在服务器端的hosts文件中定义ip地址的解析域名，或者通过DNS。
root用户连接不上数据库
也可能与此参数有关。
如前面解释，当为on时，不会进行域名反解析。那么用ip登录，就有可有登录失败。如下：
开启此参数
root@postgre 21:57: [(none)]> show variables like '%skip%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| replica_skip_errors | OFF |
| show_create_table_skip_secondary_engine | OFF |
| skip_external_locking | ON |
| skip_name_resolve | ON |
| skip_networking | OFF |
| skip_replica_start | OFF |
| skip_show_database | OFF |
| skip_slave_start | OFF |
| slave_skip_errors | OFF |
| sql_replica_skip_counter | 0 |
| sql_slave_skip_counter | 0 |
+-----------------------------------------+-------+
11 rows in set (0.02 sec)
另一个窗口登录验证：
[root@postgre ~]# /usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql3306/my3306.cnf -h127.0.0.1 -P3306 -uroot
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: NO)
所以当禁用域名反解析，而我们使用127.0.0.1登录，权限字典表user里面没有来源为ip的用户，就有可能登录失败。
总结：
参数skip_name_resolve需要设置此参数的几种场景：
1、远程连接 mysql 很慢，但是本地连接 mysql 很快， ping 和 route 网络通信都是正常的。因为要解析，所以肯定会耗时。
2、刚开始连接正常，后面运行着，连接越来越慢。
3、如果环境中存在DNS，是通过域名解析登录的，那么此参数 skip_name_resolve不能出现在配置文件中。需要禁用DNS的话，那么就必须设置此参数 skip_name_resolve=1。
UNIX Domain Socket IPC
mysql的连接有两种方式：
1）unix domian socket：unix套接字连接。一般只用于本地连接，指定-S，socket的路具体径，只能用于mysql.user表里对应host字段为localhost的用户进行连接。就跟oracle的os认证类似，直接sqlplus/as sysdba连接进去
2)TCP/IP协议连接。一般带-P参数指定要连接的机器地址，适用本地连接或者远程连接。
root@localhost [(none)]>create user test@localhost identified by 'test';
Query OK, 0 rows affected (0.01 sec)
root@localhost [(none)]>select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| test | localhost |
+---------------+-----------+
[root@testbed mysql3306]# /usr/local/mysql/bin/mysql -S /tmp/mysql3306.sock -utest -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.42-log MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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.
test@localhost [(none)]>select user(),current_user();
+----------------+----------------+
| user() | current_user() |
+----------------+----------------+
| test@localhost | test@localhost |
+----------------+----------------+
1 row in set (0.00 sec)
[root@testbed mysql3306]# /usr/local/mysql/bin/mysql -utest -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[root@testbed mysql3306]# /usr/local/mysql/bin/mysql -h 10.1.11.250 -utest -p
Enter password:
ERROR 1130 (HY000): Host '10.1.11.250' is not allowed to connect to this MySQL server
[root@testbed mysql3306]# /usr/local/mysql/bin/mysql -h 127.0.0.1 -utest -p
Enter password:
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server
[root@testbed mysql3306]# /usr/local/mysql/bin/mysql -utest -p
localhost创建的用户对应只能通过-S参数指定socket文件，本地进行登录。
root@localhost [(none)]>create user zhuo identified by 'zhuo';
Query OK, 0 rows affected (0.01 sec)
root@localhost [(none)]>select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| zhuo | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| test | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)
[root@testbed mysql3306]# /usr/local/mysql/bin/mysql -h 127.0.0.1 -uzhuo -p
zhuo@127.0.0.1 [(none)]>select user(),current_user();
+----------------+----------------+
| user() | current_user() |
+----------------+----------------+
| zhuo@127.0.0.1 | zhuo@% |
+----------------+----------------+
1 row in set (0.00 sec)
[root@testbed mysql3306]# /usr/local/mysql/bin/mysql -h 10.1.11.250 -uzhuo -p
zhuo@10.1.11.250 [(none)]>select user(),current_user();
+------------------+----------------+
| user() | current_user() |
+------------------+----------------+
| zhuo@10.1.11.250 | zhuo@% |
+------------------+----------------+
[root@testbed mysql3306]# /usr/local/mysql/bin/mysql -S /tmp/mysql3306.sock -uzhuo -p
zhuo@localhost [(none)]>select user(),current_user();
+----------------+----------------+
| user() | current_user() |
+----------------+----------------+
| zhuo@localhost | zhuo@% |
+----------------+----------------+
user(),代表从哪台机器登录进来的，current_user()代表登录用的是数据库里的哪个用户。
-S就代表了此用户是localhost。
MySQL 8.0用户管理增强
密码过期
设置用户密码过期
root@postgre 21:00: [(none)]> alter user test1 password expire;
Query OK, 0 rows affected (0.03 sec)
可以登录，但是当你第一次登录成功后，必须修改密码，修改密码前，不能做任何操作。
[root@postgre ~]# /usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql3306/my3306.cnf -utest1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 63
Server version: 8.0.27
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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.
test1@postgre 21:00: [(none)]>
test1@postgre 21:00: [(none)]> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
test1@postgre 21:00: [(none)]> alter user test1 identified by 'mysql';
Query OK, 0 rows affected (0.02 sec)
test1@postgre 21:01: [(none)]> exit
Bye
[root@postgre ~]# /usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql3306/my3306.cnf -utest1
ERROR 1045 (28000): Access denied for user 'test1'@'localhost' (using password: NO)
[root@postgre ~]# /usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql3306/my3306.cnf -utest1 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 65
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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.
test1@postgre 21:01: [(none)]>
此功能相当于，用户第一次登录必须修改密码。
双密码
先创建一个用户
root@postgre 20:48: [(none)]> create user test4 identified by 'mysql';
Query OK, 0 rows affected (0.05 sec)
root@postgre 20:48: [(none)]> select user,host,authentication_string from mysql.user;
+------------------+------------+------------------------------------------------------------------------+
| user | host | authentication_string |
+------------------+------------+------------------------------------------------------------------------+
| test1 | % | |
| test3 | % | $A$005$.(sLu}E`d /-zf:R6wMGQIMwF/E0KatfFGyaW3NgZ1lvGIpIkMOGJx.AFaC |
| test4 | % | $A$005$z}"THd1HAFs.@4~(6XkcYvXu6RKx6N.OLzEIawcr8zBltDVv77x.jqsAiR8 |
| test2 | 10.1.11.% | $A$005$u
A0Z!QnZ%[5 UR6uWrKcIMxG2cAm5qEEzsqzt6PNPiV00rxK9H35zT5eYy5 |
| test2 | 10.1.11.10 | $A$005$5(c'+xxh0w'EXwlYHvmFmHbgXJ0hntHgGoY1fDRAo.6faSvlyrdZIt9B |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root | localhost | |
| enmo_app | zhuo%.com | |
+------------------+------------+------------------------------------------------------------------------+
10 rows in set (0.00 sec)
root@postgre 20:48: [(none)]> show grants for test4;
+-----------------------------------+
| Grants for test4@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO `test4`@`%` |
+-----------------------------------+
客户端可以正常登录。
[root@oracle19c ~]# /usr/local/mysql/bin/mysql -h10.1.11.30 -P3306 -utest4 -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 57
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> exit
设置双密码
root@postgre 20:48: [(none)]> alter user test4 identified by 'oracle' retain current password;
Query OK, 0 rows affected (0.04 sec)
那么两个密码都可以登陆了
[root@oracle19c ~]# /usr/local/mysql/bin/mysql -h10.1.11.30 -P3306 -utest4 -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 59
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> exit
Bye
[root@oracle19c ~]# /usr/local/mysql/bin/mysql -h10.1.11.30 -P3306 -utest4 -poracle
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 60
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> exit
Bye
设置之前的密码不能登录
root@postgre 20:49: [(none)]> alter user test4 discard old password;
Query OK, 0 rows affected (0.02 sec)
之前的密码已经不能登录了
[root@oracle19c ~]# /usr/local/mysql/bin/mysql -h10.1.11.30 -P3306 -utest4 -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test4'@'zhuo.zhuo.com' (using password: YES)
[root@oracle19c ~]# /usr/local/mysql/bin/mysql -h10.1.11.30 -P3306 -utest4 -poracle
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 62
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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>
核心命令
> alter user test4 identified by 'oracle' retain current password;
> alter user test4 discard old password;
245
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
