MySQL权限管理
一、MySQL权限管理
二、用户管理
创建用户
(root@localhost) [(none)]> create user 'david'@'%' identified by '1234'; Query OK, 0 rows affected (0.01 sec)
删除用户
(root@localhost) [(none)]> drop user 'david'@'%'; Query OK, 0 rows affected (0.01 sec)
查看当前用户的权限
(david@localhost) [(none)]> show grants; +-----------------------------------+ | Grants for david@% | +-----------------------------------+ | GRANT USAGE ON *.* TO 'david'@'%' | +-----------------------------------+ 1 row in set (0.00 sec)
查看指定用户权限
(root@localhost) [(none)]> show grants for 'root'; +-------------------------------------------------------------+ | Grants for root@% | +-------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION | +-------------------------------------------------------------+ 1 row in set (0.00 sec)
三、权限管理
给用户授权
(root@localhost) [(none)]> grant select,update,create on *.* to 'david'@'%'; Query OK, 0 rows affected (0.04 sec)
查看所授予的权限
(root@localhost) [(none)]> show grants for 'david'@'%'; +----------------------------------------------------+ | Grants for david@% | +----------------------------------------------------+ | GRANT SELECT, UPDATE, CREATE ON *.* TO 'david'@'%' | +----------------------------------------------------+ 1 row in set (0.00 sec)
修改密码
(root@localhost) [(none)]> alter user 'david'@'%' identified by '4567'; Query OK, 0 rows affected (0.01 sec)
回收权限
(root@localhost) [(none)]> revoke create on *.* from 'david'@'%'; Query OK, 0 rows affected (0.01 sec)
with grant options选项，有将自己有的权限授权其他用户的权限
(root@localhost) [(none)]> grant select,update,create on *.* to 'david'@'%' with grant option; Query OK, 0 rows affected (0.02 sec)
四、用户权限保存的地方
user表
(root@localhost) [mysql]> select user,host,authentication_string from user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | % | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | jack | % | *A4B6157319038724E3560894F7F932C8886EBFCF | | long | % | *A4B6157319038724E3560894F7F932C8886EBFCF | | sun | % | *A4B6157319038724E3560894F7F932C8886EBFCF | | janwen | % | *A4B6157319038724E3560894F7F932C8886EBFCF | | bao | % | *A4B6157319038724E3560894F7F932C8886EBFCF | | admin | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | | david | % | *91C56982E0A5E9D4CEB104DC61A1538840B5D77D | +---------------+-----------+-------------------------------------------+ 10 rows in set (0.01 sec)
五、MySQL的连接方式
Socket连接
[root@mysql ~]# mysql -S /tmp/mysql.sock -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.26-log 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. (root@localhost) [(none)]>
连入数据库后，输入\s 可以查看连接方式
(root@localhost) [(none)]> \s -------------- mysql Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using EditLine wrapper Connection id: 6 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.26-log MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 6 hours 40 min 52 sec Threads: 3 Questions: 113 Slow queries: 0 Opens: 141 Flush tables: 1 Open tables: 134 Queries per second avg: 0.004
六、连接工具workbench
https://dev.mysql.com/downloads/workbench/
七、SSL连接
(root@localhost) [mysql]> show variables like '%ssl%'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | | +---------------+----------+ 9 rows in set (0.02 sec)
安装SSL连接
[root@mysql ~]# mysql_ssl_rsa_setup Generating a 2048 bit RSA private key ....................................+++ .........................................................+++ writing new private key to 'ca-key.pem' ----- 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' -----
修改权限
[root@mysql mysql3306]# chown -R mysql:mysql data
重启数据库，查看参数
(root@localhost) [(none)]> show variables like '%ssl%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | ca.pem | | ssl_capath | | | ssl_cert | server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | server-key.pem | +---------------+-----------------+ 9 rows in set (0.01 sec)
八、密码插件
安装密码插件
(root@localhost) [(none)]> INSTALL PLUGIN validate_password SONAME 'validate_password.so'; Query OK, 0 rows affected (0.03 sec)
查看安装的插件
(root@localhost) [(none)]> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
查看validate参数
(root@localhost) [(none)]> show variables like 'validate%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password_check_user_name | OFF | | validate_password_dictionary_file | | | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | MEDIUM | | validate_password_special_char_count | 1 | +--------------------------------------+--------+ 7 rows in set (0.00 sec)
