2024年8月5日（glibc的安装及MySQL的安全用户角色权限）

1、清空etc目录下的my.cnf文件

[root@localhost ~]# ls -l /etc/my.cnf
-rw-r--r--. 1 root root 570 6月   8 2017 /etc/my.cnf
[root@localhost ~]# rm -rf /etc/my.cnf
[root@localhost ~]# yum -y remove mariadb
[root@localhost ~]# find / -name "*mysql*" -exec rm -rf {} \;

2、上传包，解压

[root@localhost ~]# tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar 

[root@localhost ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz

[root@localhost ~]# cd mysql-8.0.33-linux-glibc2.12-x86_64
[root@localhost mysql-8.0.33-linux-glibc2.12-x86_64]# ls

[root@localhost mysql-8.0.33-linux-glibc2.12-x86_64]# vim support-files/mysql.server 

3、将项目文件移动到/usr/local/mysql/目录下

[root@localhost ~]# cp -r mysql-8.0.33-linux-glibc2.12-x86_64/ /usr/local/mysql/

[root@localhost ~]# yum list installed | grep libaio
libaio.x86_64                         0.3.109-13.el7                   @anaconda
[root@localhost ~]# echo $?
0

4、创建账户

[root@localhost ~]# useradd -r -s /sbin/nologin mysql

5、在/usr/local/mysql/目录下创建mysql-files目录

[root@localhost ~]# mkdir /usr/local/mysql/mysql-files

6、修改mysql-files的权限为750 所属组都为mysql

[root@localhost ~]# chown mysql:mysql /usr/local/mysql/mysql-files/
[root@localhost ~]# chmod 750 /usr/local/mysql/mysql-files/
[root@localhost ~]# ll /usr/local/mysql/
drwxr-x---  2 mysql mysql      6 8月   5 09:55 mysql-files

7、初始化数据库

[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/

[root@localhost ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data

8、把mysql.server文件放到/etc/init.d/目录下，方便启动MySQL服务，service mysql start

[root@localhost ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8
[root@localhost ~]# service mysql8 start

mysql> insert into user values(1,"abc","123","c");

mysql> insert into user values(2,"qwe","432","b");

mysql> insert into user values(3,"xss","456","a");

[root@mysql ~]# vim mysql.sh

（1）创建账户

create user 'xiaojiang'@'%' identified by 'Zhang@2002';
 eg:创建三个账号，abc[abcd] , ccc[a1b2c3] ,ddd[231343]

（2）给权限

grant all on *.* to 'xiaojiang'；

（3）创建库

create database if not exists test;

（4）创建表

use test;
create table user(
    id int primary key,
    username varchar(45) not null,
    password varchar(45) not null
);

（5）添加数据

insert into test.user values(1,"zhangsan","123");
insert into test.user values(2,"lisi","456");
insert into test.user values(3,"wangwu","789");
insert into test.user values(4,"zhaoliu","aaa");

添加lilaosi账号，修改密码，查看mysql.user中的lilaosi的信息

create user 'lilaosi'@'%' identified by 'lilaoshi_123';
alter user 'lilaosi'@'%' identified by 'Lilaosi_123';
select host,user from mysql.user;

使用root账号，为lilaosi账号添加test库存中所有的表的所有权限

mysql -uroot -pZhang@2002

grant all on test.* to 'lilaosi';
#lilaosi就获得了test库中所有表的操作权限；但是由于root没有个lilaosimysql库的
权限，所以lilaosi账号无法查看mysql库

二、安全用户角色权限

（一）密码安全策略
1、查看密码策略
mysql> show variables like 'validate%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| 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      |
+--------------------------------------+--------+
2、 修改策略
mysql>setglobal validate_password.length=0;
mysql>setglobal validate_password.policy=LOW;
mysql>showvariableslike'validate%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 4      |
| validate_password.mixed_case_count   | 0      |
| validate_password.number_count       | 0      |
| validate_password.policy             | LOW    |
| validate_password.special_char_count | 0      |
+--------------------------------------+--------+
（二）用户
1、创建用户
mysql>create user'efgh'@'%' identified by 'efgh';
Query OK,0 rows affected (0.01 sec)
mysql>select host,user from mysql.user;
+-----------+------------------+
| host      |user              |
+-----------+------------------+
|%          | efgh             |
|%          | root             |
|%          | zhangmin         |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | test1            |
+-----------+------------------+
2、删除用户
mysql>drop user'xiaojiang';
Query OK,0 rows affected (0.02 sec)
3、修改用户
mysql>alter user'zhangmin' identified by 'abc123';
Query OK,0 rows affected (0.01 sec)
（三）角色
1、创建角色
mysql>create role 'jingli';
Query OK,0 rows affected (0.00 sec)
2、查看角色
mysql> select host,user from mysql.user;
3、修改角色
ALTER ROLE 'role_name' [...属性...];
4、删除角色
DROP ROLE 'role_name';
（四）权限
1、刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
2、为root账号添加权限
mysql> grant system_user on *.* to "root";
3、新增权限
mysql> grant insert,delete,update,select on test.user to 'jingli';
4、修改权限
mysql> show grants for "efgh";
+----------------------------------+
| Grants for efgh@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO `efgh`@`%` |
+----------------------------------+
1 row in set (0.00 sec)
5、查看权限
mysql> show grants for 'bbb';
6、删除权限
mysql>revoke all on*.* from "efgh";
Query OK,0 rows affected (0.00 sec)
练习1
1.添加aaa账户，设置密码aaaa
drop user aaa;
create user 'aaa'@'%' identified by 'aaaa';
2.使用aaa账户访问mysql服务
mysql -h127.0.0.1 -P3306 -uaaa -paaaa
3.查看test数据库发现么有权限
show databases;
4.退出并使用root账户登录
quit|exit
mysql -h127.0.0.1 -P3306 -uroot -proot0000
5.为aaa账户添加查看test.user表的权限
grant select on test.user to 'aaa';
6.退出root，使用aaa账户登录
quit|exit
mysql -h127.0.0.1 -P3306 -uaaa -paaaa
7.查看数据库，查看表，查看表内容 能够正常查看
show databases;
user test;
show tables;
select * from user;
8.输入数据，没有权限
insert into user values(5,"ermazi","ermazi");####
9.退出aaa使用root登录
quit|exit
mysql -h127.0.0.1 -P3306 -uroot -proot0000
10.为aaa添加insert权限
grant insert on test.user to 'aaa';
11.退出root使用aaa登录
exit|quit
mysql -h127.0.0.1 -P3306 -uaaa -paaaa
12.向user表添加一行新的数据
insert into test.user values(6,"zhangsanfeng","zhangsanfen");
13.修改user中一行的数据的password（密码）为111，没有update权限
update test.user set password='zsf' where username-'zhangsanfeng';