![a0a2879e7b0425d06da79d7b0d10f3e0.png](https://img-blog.csdnimg.cn/img_convert/a0a2879e7b0425d06da79d7b0d10f3e0.png)
安装
王志强:CentOS7.X yum安装MySQL8.0zhuanlan.zhihu.com![2349553fdb07c6d8fd868f151cd99899.png](https://img-blog.csdnimg.cn/img_convert/2349553fdb07c6d8fd868f151cd99899.png)
账户与安全
查看当前用户
mysql> select host,user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
4 rows in set (0.00 sec)
用户创建和授权
创建用户和用户授权的命令5.7
mysql> GRANT ALL PRIVILEGES ON *.* TO 'zhiqiang'@'%' IDENTIFIED BY 'zhiqiang@19921224';
创建用户和用户授权的命令需要分开执行8.0
mysql> create user 'zhiqiang'@'%' identified by 'zhiqiang@19921224';
mysql> GRANT ALL PRIVILEGES on *.* to 'zhiqiang'@'%';
认证插件更新
查看默认的插件
mysql> show variables like 'default_authentication%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.25 sec)
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| zhiqiang | % | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | mysql_native_password |
修改认证插件
全局设置
vi /etc/my.cof 去掉前面注释
# default-authentication-plugin=mysql_native_password
单个用户修改插件
mysql
密码管理
查看相关变量
mysql> show variables like 'password%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| password_history | 0 |
| password_require_current | OFF |
| password_reuse_interval | 0 |
+--------------------------+-------+
3 rows in set (0.00 sec)
参数说明
password_history是可以动态调整的全局整型参数,默认值为0,可以设置为0- 4294967295之间的任意整数。该参数数值用于设置历史密码可以再次使用之前需要进行密码修改的次数。设置为0则不会对历史密码是否可以重用进行限制。
password_require_current设置为on的时候需要提供原来的密码,当当前用户的权限可以修改mysql.user时不受限制
全局设置
vi /etc/my.cof
//最好一行加
password_history =3
password_reuse_interval =3
持久化设置
mysql> set persist password_history =3;
mysql> show variables like 'password%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| password_history | 3 |
| password_require_current | OFF |
| password_reuse_interval | 0 |
+--------------------------+-------+
3 rows in set (0.00 sec)
原理:除了读取/etc/my.cnf配置文件还读取/var/lib/mysql/mysqld-auto.cnf配置文件以达到持久化效果
[root@VM_159_140_centos ~]# more /var/lib/mysql/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "password_history" : { "Value" : "3" , "Metadata" : { "Timestamp" : 1553319959202292 , "User" : "root" , "Host" : "loc
alhost" } } } }
单个用户设置password_history案例
mysql> alter user 'zhiqiang'@'%' password history 5
mysql> desc mysql.user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | caching_sha2_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
| Create_role_priv | enum('N','Y') | NO | | N | |
| Drop_role_priv | enum('N','Y') | NO | | N | |
| Password_reuse_history | smallint(5) unsigned | YES | | NULL | |
| Password_reuse_time | smallint(5) unsigned | YES | | NULL | |
| Password_require_current | enum('N','Y') | YES | | NULL | |
| User_attributes | json | YES | | NULL | |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.00 sec)
mysql> select user,host,Password_reuse_history from mysql.user;
+------------------+-----------+------------------------+
| user | host | Password_reuse_history |
+------------------+-----------+------------------------+
| zhiqiang | % | 5 |
| mysql.infoschema | localhost | NULL |
| mysql.session | localhost | NULL |
| mysql.sys | localhost | NULL |
| root | localhost | NULL |
+------------------+-----------+------------------------+
5 rows in set (0.00 sec)
mysql> alter user 'zhiqiang'@'%' identified by 'zhiqiang@19921224';
ERROR 3638 (HY000): Cannot use these credentials for 'zhiqiang@%' because they contradict the password history policy
mysql> desc mysql.password_history;
+--------------------+--------------+------+-----+----------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+----------------------+-------------------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Password_timestamp | timestamp(6) | NO | PRI | CURRENT_TIMESTAMP(6) | DEFAULT_GENERATED |
| Password | text | YES | | NULL | |
+--------------------+--------------+------+-----+----------------------+-------------------+
4 rows in set (0.00 sec)
角色管理
创建角色
mysql> create role 'demo_role';
创建完成之后,会生成一个无密码用户
授权
mysql> grant select,insert,update,delete on testdb.* to 'demo_role';
把角色给用户
mysql> create user 'demouser' identified by 'demouser@19921224';
mysql> grant 'demo_role' to 'demouser';
mysql> show grants for 'demouser';
+-----------------------------------------+
| Grants for demouser@% |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `demouser`@`%` |
| GRANT `demo_role`@`%` TO `demouser`@`%` |
+-----------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for 'demouser' using 'demo_role';
+----------------------------------------------------------------------+
| Grants for demouser@% |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `demouser`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `testdb`.* TO `demouser`@`%` |
| GRANT `demo_role`@`%` TO `demouser`@`%` |
+----------------------------------------------------------------------+
3 rows in set (0.00 sec)
为某个用户设置默认的角色
mysql> set default role 'demo_role' to 'demouser';
mysql> set default role all to 'demouser';
用户所设角色信息
mysql> select * from mysql.default_roles;
Empty set (0.00 sec)
mysql> select * from mysql.role_edges;
+-----------+-----------+---------+----------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+---------+----------+-------------------+
| % | demo_role | % | demouser | N |
+-----------+-----------+---------+----------+-------------------+
1 row in set (0.00 sec)
撤销权限
mysql> revoke select,insert,update on testdb.* from 'demo_role';
mysql> show grants for 'demouser' using 'demo_role';
+----------------------------------------------+
| Grants for demouser@% |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `demouser`@`%` |
| GRANT DELETE ON `testdb`.* TO `demouser`@`%` |
| GRANT `demo_role`@`%` TO `demouser`@`%` |
+----------------------------------------------+
3 rows in set (0.00 sec)
mysql> show grants for 'demouser' using 'demo_role';
+----------------------------------------------+
| Grants for demouser@% |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `demouser`@`%` |
| GRANT DELETE ON `testdb`.* TO `demouser`@`%` |
| GRANT `demo_role`@`%` TO `demouser`@`%` |
+----------------------------------------------+
3 rows in set (0.00 sec)