MySQL登录需要具备三个条件 用户名、密码、IP
**权限相关**
- 所有库权限：查看mysql.user
- 指定库权限：查看mysql.db
- 指定表权限：查看mysql.table_priv
- 指定列权限：查看mysql.columns_priv
**常用权限**
- SQL语句：SELECT、INSERT、UPDATAE、INDEX
- 存储过程：CREATE ROUTINE、ALTER ROUTINE、EXECUTE、TRIGGER
- 管理权限：SUPER、RELOAD、SHOW DATABASE、SHUTDOWN、GRANT OPTION
所有权限参考文献:[https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html](https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html)
**用户相关操作**
创建一个用户 winston 密码 12345678 任何IP都可以访问
(root@localhost)[(none)]> create user 'winston'@'%' identified by '12345678';
Query OK, 0 rows affected (0.07 sec)
# 换一台服务器
[root@localhost ~]# mysql -h192.168.153.139 -uwinston -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.25 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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.
(winston@192.168.153.139)[(none)]>
删除一个用户，在另一台服务器上进行登录
(root@localhost)[(none)]> drop user 'winston'@'%';
Query OK, 0 rows affected (0.01 sec)
[root@localhost ~]# mysql -h192.168.153.139 -uwinston -p
Enter password:
ERROR 1045 (28000): Access denied for user 'winston'@'192.168.153.139' (using password: YES)
查看权限，
# 查看当前用户的权限
(root@localhost)[(none)]> show grants ;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
# USAGE 只有连接权限
[root@localhost ~]# mysql -h192.168.153.139 -ushaco -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.25 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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.
(shaco@192.168.153.139)[(none)]> show grants;
+-----------------------------------+
| Grants for shaco@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'shaco'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)
授予权限， 举例：SELECT, INSERT, UPDATE, DELETE
grant 权限 on 作用域（DB_name.table_name） to 用户
(root@localhost)[(none)]> grant select,delete,update,insert on *.* to 'shaco';
Query OK, 0 rows affected (0.00 sec)
(shaco@192.168.153.139)[(none)]> show grants;
+------------------------------------------------------------+
| Grants for shaco@% |
+------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'shaco'@'%' |
+------------------------------------------------------------+
1 row in set (0.00 sec)
关于创建与授权合并的问题，是可以用一个语句执行，但是会有 warning 提示不是主流做法。因而最好的办法就是先创建、再授权。
(root@localhost)[(none)]> grant select,delete,update,insert on test.* to 'tom'@'192.168.153.%' identified by '12345678';
Query OK, 0 rows affected, 1 warning (0.05 sec)
(root@localhost)[(none)]> show warnings\G
*************************** 1\. row ***************************
Level: Warning
Code: 1287
Message: Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement.
1 row in set (0.00 sec)
更改密码，用alter语句
(root@localhost)[(none)]> alter user 'shacoK'@'192.168.153.%' identified by '1234';
Query OK, 0 rows affected (0.00 sec)
更改和添加权限 都是用 grant
(root@localhost)[(none)]> grant select on *.* to 'shacoK'@'192.168.153.%';
Query OK, 0 rows affected (0.00 sec)
(root@localhost)[(none)]> show grants for 'shacoK'@'192.168.153.%';
+-------------------------------------------------+
| Grants for shacoK@192.168.153.% |
+-------------------------------------------------+
| GRANT SELECT ON *.* TO 'shacoK'@'192.168.153.%' |
+-------------------------------------------------+
1 row in set (0.00 sec)
撤销权限 revoke …… from ……
注：收回全部权限并不代表删除用户
(root@localhost)[(none)]> revoke select on *.* from 'shacoK'@'192.168.153.%';
Query OK, 0 rows affected (0.00 sec)
(root@localhost)[(none)]> show grants for 'shacoK'@'192.168.153.%';
+------------------------------------------------+
| Grants for shacoK@192.168.153.% |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'shacoK'@'192.168.153.%' |
+------------------------------------------------+
1 row in set (0.00 sec)
授予其他用户权限的权限 grant …… to …… with grant option
(root@localhost)[(none)]> grant select on *.* to 'shacoK'@'192.168.153.%' with grant option;
Query OK, 0 rows affected (0.00 sec)
(root@localhost)[(none)]> show grants for 'shacoK'@'192.168.153.%';
+-------------------------------------------------------------------+
| Grants for shacoK@192.168.153.% |
+-------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'shacoK'@'192.168.153.%' WITH GRANT OPTION |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)
**与权限相关的表**
MySQL中创建的用户都在 mysql.user 中，authentication_string 字段用来记录密码 ，5.7才有。之前的版本都是password。
(root@localhost)[mysql]> select host,user,authentication_string from user ;
+-----------------------+---------------+-------------------------------------------+
| host | user | authentication_string |
+-----------------------+---------------+-------------------------------------------+
| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| localhost.localdomain | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | NULL |
| localhost.localdomain | | NULL |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| % | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| % | shaco | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 |
| 192.168.153.% | shacoK | *A4B6157319038724E3560894F7F932C8886EBFCF |
+-----------------------+---------------+-------------------------------------------+
11 rows in set (0.02 sec)
User 表
其中每个列代表着一种权限，User表中每个列表示全局的权限，Y：yes；N:no
(root@localhost)[mysql]> select * from user limit 1\G
*************************** 1\. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
password_expired: N
password_last_changed: 2020-07-27 18:37:28
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
DB 表
记录只有操作特定数据库权限的用户
(root@localhost)[mysql]> select * from db limit 1\G
*************************** 1\. row ***************************
Host: %
Db: test
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
1 row in set (0.00 sec)
table_priv 表 展示具体到某一张表的全限，与DB表效果一样，多了个 table_name 的列
columns_priv 表 ，分的太细了，没啥意思。
**注意：不要在这4张表中进行修改来达到授权的目的。**
**登录限制**
当用户同时连接的最大次数是1
(root@localhost)[mysql]> alter user 'shacoK'@'192.168.153.%' with max_user_connections 1;
Query OK, 0 rows affected (0.00 sec)
[root@localhost ~]# mysql -h192.168.153.139 -ushacoK -p
Enter password:
ERROR 1226 (42000): User 'shacoK' has exceeded the 'max_user_connections' resource (current value: 1)
用户单位时间内登陆次数
(root@localhost)[(none)]> alter user 'shacoK'@'192.168.153.%' with max_connections_per_hour 3;
Query OK, 0 rows affected (0.02 sec)
[root@localhost ~]# mysql -h192.168.153.139 -ushacoK -p
Enter password:
ERROR 1226 (42000): User 'shacoK' has exceeded the 'max_connections_per_hour' resource (current value: 3)
