MySQL权限学习
一、用户权限的介绍
用户权限管理的作用：
1.限制用户访问那些库\表
2、限制用户对那些表执行select，create，delete，drop，alter等操作；
3、限制用户登陆的IP或者域名；
4、自己是有有权限授权给其他人。
与权限有关的表 ： user，db，tables_priv、columns_priv、proxies_priv
1.1 权限有关的表 user表
分为用户列，权限列，安全列，资源控制列。
Select_priv：用户可以通过SELECT命令选择数据。
Insert_priv：用户可以通过INSERT命令插入数据;
Update_priv：用户可以通过UPDATE命令修改现有数据;
Delete_priv：用户可以通过DELETE命令删除现有数据;
Create_priv：用户可以创建新的数据库和表;
Drop_priv：用户可以删除现有数据库和表;
Reload_priv：用户可以执行刷新和重新加载MySQL所用各种内部缓存的特定命令,包括日志、权限、主机、查询和表;重新加载权限表;
Shutdown_priv：用户可以关闭MySQL服务器;在将此权限提供给root账户之外的任何用户时,都应当非常谨慎;
Process_priv：用户可以通过SHOW PROCESSLIST命令查看其他用户的进程;服务器管理;
File_priv：用户可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令;加载服务器上的文件;
1.2 db表
存取用户对某一个数据库的权限，决定用户能从哪个主机访问哪个数据库，
1.3 tables_priv
针对表设置操作权限。
1.4 columns_priv
针对表的列设置操作权限，
二、用户权限的管理
MySQL的权限就是： 用户+IP/主机，
例如 root@127.0.0.1 root@localhost root@192.169.1.6
2.1 创建用户
mysql> create user gy@'%' identified by 'gy'; Query OK, 0 rows affected (0.00 sec) mysql> create user gy@'localhost' identified by 'gy'; Query OK, 0 rows affected (0.00 sec)
这其实是两个用户，
2.2 给用户授权
grant 权限 on 库名.表名
mysql> grant all privileges on *.* to 'gy'@'localhost' identified by 'gy' with grant option; Query OK, 0 rows affected, 1 warning (0.03 sec)
也可以不加密码
mysql> grant all privileges on *.* to 'gy'@'%';
Query OK, 0 rows affected (0.03 sec)
2.3回收权限
mysql> revoke all privileges on *.* from 'gy'@'%'; Query OK, 0 rows affected (0.01 sec)
三、密码管理
3.1 修改密码
[root@localhost ~]# mysqladmin -uroot -p password 'rootroot'; Enter password: mysqladmin: [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
使用修改mysql.user的方法修改密码：
mysql> update mysql.user set authentication_string=PASSWORD('rootroot') where user='root'; Query OK, 1 row affected, 1 warning (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
使用set语句：
mysql> set password=PASSWORD('rootroot'); Query OK, 0 rows affected, 1 warning (0.01 sec)
使用alter
mysql> alter user 'gy'@'%' identified by 'rootroot'; Query OK, 0 rows affected (0.00 sec)
四、SSL认证
4.1 检查openssl是否安装
[root@localhost ~]# openssl version; OpenSSL 1.0.2k-fips 26 Jan 2017
4.1 创建密钥
[root@localhost ~]# mysql_ssl_rsa_setup --datadir=/mysql/data --user=mysql --uid=mysql Generating a 2048 bit RSA private key ....+++ ............................................................................................................. Generating a 2048 bit RSA private key .........................................+++ .....................+++ writing new private key to 'server-key.pem' ----- Generating a 2048 bit RSA private key .......+++ ....................+++ writing new private key to 'client-key.pem'
4.3 修改 my.cnf
[mysqld]
ssl-ca=/mysql/data/ca.pem
ssl-cert=/mysql/data/server-cert.pem
ssl-key=/mysql/data/server-key.pem
4.4 重启数据库、查看参数
mysql> show global variables like '%ssl%'; +---------------+-----------------------------+ | Variable_name | Value | +---------------+-----------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /mysql/data/ca.pem | | ssl_capath | | | ssl_cert | /mysql/data/server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | /mysql/data/server-key.pem | +---------------+-----------------------------+
查看版本
mysql> show global variables like 'tls_version'; +---------------+---------------+ | Variable_name | Value | +---------------+---------------+ | tls_version | TLSv1,TLSv1.1 | +---------------+---------------+ 1 row in set (0.02 sec)
4.5 创建一个测试用户
mysql> create user pg@'%' identified by 'root'; Query OK, 0 rows affected (0.01 sec) mysql> grant all privileges on *.* to 'pg'@'%'; Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
4.6 使用ssl登陆
[root@localhost ~]# mysql -uroot -p --ssl-mode=required
查看ssl状态，连接已经使用了ssl
mysql> status -------------- mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapper Connection id: 4 Current database: Current user: root@localhost SSL: Cipher in use is DHE-RSA-AES256-SHA
--------------------------------------------------------------------------------------------------
如果强制让用户使用ssl认证，则在用户创建的时候就要加参数
mysql> create user zg@'%' identified by 'root' require x509; Query OK, 0 rows affected (0.04 sec)
mysql> grant all privileges on *.* to 'zg'@'%';
Query OK, 0 rows affected (0.03 sec)
登陆测试：
[root@localhost ~]# mysql -uzg -p --ssl-ca=/mysql/data/ca.pem --ssl-cert=/mysql/data/client-cert.pem --ssl-key=/mysql/data/client-key.pem Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.7.20-log Source distribution Copyright (c) 2000, 2017, 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. You are enforcing ssl conection via unix socket. Please consider switching ssl off as it does not make connection via unix socket any more secure. mysql>
取消ssl认证
mysql> alter user zg@'%' require none; Query OK, 0 rows affected (0.01 sec)
测试
[root@localhost ~]# mysql -uzg -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 5.7.20-log Source distribution Copyright (c) 2000, 2017, 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>
五、查看MySQL的最大连接数
5.1
mysql> show status like '%connect%'; +-----------------------------------------------+---------------------+ | Variable_name | Value | +-----------------------------------------------+---------------------+ | Aborted_connects | 0 | | Connection_errors_accept | 0 | | Connection_errors_internal | 0 | | Connection_errors_max_connections | 0 | | Connection_errors_peer_address | 0 | | Connection_errors_select | 0 | | Connection_errors_tcpwrap | 0 | | Connections | 7 | | Locked_connects | 0 | | Max_used_connections | 4 | | Max_used_connections_time | 2022-10-02 09:17:34 | | Performance_schema_session_connect_attrs_lost | 0 | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_finished_connects | 0 | | Threads_connected | 4 | +-----------------------------------------------+---------------------+ 16 rows in set (0.00 sec)
5.2 可以看最大连接数
mysql> show variables like '%conn%'; +-----------------------------------------------+-----------------+ | Variable_name | Value | +-----------------------------------------------+-----------------+ | character_set_connection | utf8 | | collation_connection | utf8_general_ci | | connect_timeout | 10 | | disconnect_on_expired_password | ON | | init_connect | | | max_connect_errors | 100 | | max_connections | 151 | | max_user_connections | 0 | | performance_schema_session_connect_attrs_size | 512 | +-----------------------------------------------+-----------------+ 9 rows in set (0.01 sec)
5.3 当前执行的链接
mysql> show processlist; +----+------+-----------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------+------------------+ | 3 | root | localhost | NULL | Sleep | 969 | | NULL | | 4 | root | localhost | NULL | Sleep | 740 | | NULL | | 5 | root | localhost | NULL | Query | 0 | starting | show processlist | | 6 | yj | localhost | NULL | Sleep | 480 | | NULL | +----+------+-----------+------+---------+------+----------+------------------+ 4 rows in set (0.00 sec)
