培训第二十一天（mysql用户创建与授权、角色创建）

上午

1、环境准备

 [root@mysql ~]# rm -rf /etc/my.cnf    //清空/etc目录下的my.cnf[root@mysql ~]# yum -y remove mariadb   //移除mariadb[root@mysql ~]# find / -name "*mysql*" -exec rm -rf {} \;    //删除mysql所有遗留文件

2、安装mysql绿包

 [root@mysql ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar [root@mysql ~]# lsmysql-8.0.33-linux-glibc2.12-x86_64.tarmysql-8.0.33-linux-glibc2.12-x86_64.tar.xzmysql-router-8.0.33-linux-glibc2.12-x86_64.tar.xzmysql-test-8.0.33-linux-glibc2.12-x86_64.tar.xz[root@mysql ~]# tar -xf  mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz [root@mysql ~]# lsmysql-8.0.33-linux-glibc2.12-x86_64mysql-8.0.33-linux-glibc2.12-x86_64.tarmysql-8.0.33-linux-glibc2.12-x86_64.tar.xzmysql-router-8.0.33-linux-glibc2.12-x86_64.tar.xzmysql-test-8.0.33-linux-glibc2.12-x86_64.tar.xz[root@mysql ~]# cd mysql-8.0.33-linux-glibc2.12-x86_64/[root@mysql mysql-8.0.33-linux-glibc2.12-x86_64]# lsbin  docs  include  lib  LICENSE  man  README  share  support-files

3、配置mysql工作环境

 [root@mysql ~]# vim support-......     //查看配置文件/basedir[root@mysql ~]# cp -r mysql-8.0.33-linux-glibc2.12-x86_64  /usr/local/mysql/   //将项目文件移动到/usr/local/mysql[root@mysql ~]# tree /usr/local/mysql[root@mysql ~]# yum list installed | grep libaio   //查看libaio是否存在[root@mysql ~]# echo $?   //查看上一命令是否执行成功[root@mysql ~]# id mysql   //查看是否有mysql用户useradd -r -s /sbin/nologin mysql   //如果没有mysql用户，创建用户[root@mysql ~]# mkdir /usr/local/mysql/mysql-files        //在/usr/local/mysql目录下创建mysql-files目录[root@mysql ~]# chown mysql:mysql /usr/local/mysql/mysql-files/     //修改mysql-files所属的组和属主都是mysql[root@mysql ~]# chmod 750 /usr/local/mysql/mysql-files/      //修改mysql-files的权限为750[root@mysql ~]# ll /usr/local/mysql/总用量 292drwxr-xr-x.  2 root  root    4096 8月   5 09:55 bindrwxr-xr-x.  2 root  root      38 8月   5 09:55 docsdrwxr-xr-x.  3 root  root     282 8月   5 09:55 includedrwxr-xr-x.  6 root  root     201 8月   5 09:55 lib-rw-r--r--.  1 root  root  284945 8月   5 09:55 LICENSEdrwxr-xr-x.  4 root  root      30 8月   5 09:55 mandrwxr-x---.  2 mysql mysql      6 8月   5 09:56 mysql-files-rw-r--r--.  1 root  root     666 8月   5 09:55 READMEdrwxr-xr-x. 28 root  root    4096 8月   5 09:55 sharedrwxr-xr-x.  2 root  root      77 8月   5 09:55 support-files[root@mysql ~]# cd /usr/local/mysql/bin/     //cd到安装目录[root@mysql bin]# lsibd2sdi            mysqlcheck             mysqlpumpinnochecksum       mysql_config           mysql_secure_installationlz4_decompress     mysql_config_editor    mysqlshowmyisamchk          mysqld                 mysqlslapmyisam_ftdump      mysqld-debug           mysql_ssl_rsa_setupmyisamlog          mysqld_multi           mysql_tzinfo_to_sqlmyisampack         mysqld_safe            mysql_upgrademy_print_defaults  mysqldump              perrormysql              mysqldumpslow          zlib_decompressmysqladmin         mysqlimportmysqlbinlog        mysql_migrate_keyring[root@mysql bin]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/    //初始化数据库

 [root@mysql ~]# ls /usr/local/mysql/     //查看是否生成了data目录bin   docs     lib      man          README  support-filesdata  include  LICENSE  mysql-files  share[root@mysql ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data    //创建安全加密连接[root@mysql ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8    //将mysql.server文件放到/etc/init.d/目录下，方便启动mysql服务 service mysql start[root@mysql ~]# service mysql8 start    //启动mysql服务Starting MySQL.Logging to '/usr/local/mysql/data/mysql.err'........ SUCCESS![root@mysql ~]# systemctl stop firewalld   [root@mysql ~]# systemctl disable firewalldRemoved symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.[root@mysql ~]# /usr/local/mysql/bin/mysql -uroot -p    //进入mysql中Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 9Server version: 8.0.33Copyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> 

mysql -hip地址 -p3306 -uroot -p（远程连接使用）

4、mysql基础命令

（1）修改密码

 mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Root@123456';   

（2）授权远程登录

 mysql> create user 'root'@'%' identified with mysql_native_password by 'Root@123456';    //创建root远程登录

（3）刷新

 mysql> flush privileges;     //刷新操作

（4）查看表结构

 mysql> desc mysql.user;   //查看表结构

（5）查看用户与主机关系

 mysql> select host,user from mysql.user;   //查看用户与主机对应关系

5、mysql操作命令

（1）创建li用户

 mysql> create user 'li'@'%' identified by 'Root@123456';    //创建用户li，并且可以在所有主机连接mysql

（2）为li用户授权

 mysql> grant all on *.* to 'li';     //为li用户授予所有数据库的所有表格的所有权限Query OK, 0 rows affected (0.01 sec)

（3）使用li用户连接mysql

 [root@mysql ~]# /usr/local/mysql/bin/mysql -uli -pRoot@123456    //使用li用户连接mysql

（4）查看数据库

 mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.00 sec)

（5）创建tset数据库

 mysql> create database if not exists test charset utf8;   //创建一个名为test的数据库Query OK, 1 row affected, 1 warning (0.01 sec)

（6）使用数据库

 mysql> use test;    //使用test数据库Database changed

（7）创建user表

 mysql> create table `user` (            //创建user表`id` int NOT NULL,`username` varchar(45) NOT NULL,`password` varchar(45) NOT NULL,primary key ('id'));

（8）向表中插入数据

 mysql> insert into user values(1,"zhangsan","123"),(2,"lisi","456"),(3,"wangwu","789"),(4,"zhaoliu","aaa");    //为user表插入数据Query OK, 4 rows affected (0.03 sec)Records: 4  Duplicates: 0  Warnings: 0

（9）查看表内容

 mysql> select * from user;     //查看user表内容

 [root@mysql ~]# ln -s  /usr/local/mysql/bin/mysql /usr/bin      //创建软链接

下午

1、脚本安装mysql

 [root@mysql ~]# vim mysql.sh#!/bin/bashcp $1 /usr/local/mysql/mkdir /usr/local/mysql/mysql-files/grep /mysql/ /etc/passwordif [ $? -ne 0 ];thenuseradd -r -s /sbin/nologin mysqlfichown mysql:mysql /usr/local/mysql/mysql-fileschmod 750 /usr/local/mysql/mysql-files# init/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/# password​# servicecp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8# start serverservice mysql8 start

2、mysql命令回顾

（1）远程登录前提条件是mysql.user表中的host属性为%，如果是localhost就不允许远程登录，

 update mysql.user set host=“%” where user=“root”;    //修改表内容flush privileges;    //刷新

（2）远程管理，可以使用图形化工具，sqlyog，navicat，掌握命令工具，客户端工具mysql

（3）mysql -h10.0.0.3 -P3306 -uli -pRoot@123456

-h	主机ip或者是域名	如果是localhost或者是127.0.0.1可以省略
-P	端口	默认的是3306，如果是默认的，可以省略
-u	用户名	必须输入
-p	密码	可以不换行直接输入，也可以换行，不回显输入密码

创建用户

 create user 'li'@'%' identified by 'Root@123456';

给权限

 grant all on *.* to 'li';

创建库

 create database if not exists test;

创建表

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

添加数据

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

3、mysql用户权限设置

（1）创建lilaosi用户

 mysql> create user 'lilaosi'@'%' identified by  'Lilaosi@123456';    //添加lilaosi账号

（2）修改密码

 mysql> alter user 'lilaosi'@'%' identified by 'Lilaosi@123456';    //修改密码 

（3）查看用户与主机关系

 mysql> select host,user from mysql.user;  //查看mysql.user信息+-----------+------------------+| host      | user             |+-----------+------------------+| %         | li               || %         | lilaosi          || %         | root             || localhost | mysql.infoschema || localhost | mysql.session    || localhost | mysql.sys        || localhost | root             |+-----------+------------------+7 rows in set (0.00 sec)

（4）退出数据库

 mysql> quitBye

（5）使用lilaosi登录mysql（发现无任何权限）

 [root@mysql ~]# mysql -ulilaosi -pLilaosi@123456mysql> show databases;    //只有自己的权限+--------------------+| Database           |+--------------------+| information_schema || performance_schema |+--------------------+2 rows in set (0.04 sec)mysql> quitBye

（6）使用root用户为lilaosi用户赋予test数据库中所有表的所有权限

 [root@mysql ~]# mysql -uroot -pRoot@123456mysql> grant all on test.* to 'lilaosi';   //使用root账号为lilaosi账号添加test库中所有表的权限Query OK, 0 rows affected (0.00 sec)mysql> quitBye[root@mysql ~]# mysql -ulilaosi -pLilaosi@123456mysql> show databases;    //对test数据库有一定的权限+--------------------+| Database           |+--------------------+| information_schema || performance_schema || test               |+--------------------+3 rows in set (0.00 sec)（root没有给lilaosi mysql库的权限，所有lilaosi账户无法查看mysql库。）

（7）将system_user权限给root

 mysql> grant system_user on *.* to 'root';       //将system_user权限给root

（8）为aaa用户赋予查看test数据库user表的权限

 mysql> show grants for aaa;     //只有自己的权限+---------------------------------+| Grants for aaa@%                |+---------------------------------+| GRANT USAGE ON *.* TO `aaa`@`%` |+---------------------------------+1 row in set (0.00 sec)mysql> grant select on test.user to 'aaa';  //为aaa用户赋予test数据库user表的查看权限Query OK, 0 rows affected (0.00 sec)mysql> quitBye[root@mysql ~]# mysql -uaaa -p123mysql> select * from test.user;   //查看成功+----+----------+----------+| id | username | password |+----+----------+----------+|  1 | zhangsan | 123      ||  2 | lisi     | 456      ||  3 | wangwu   | 789      ||  4 | zhaoliu  | aaa      |+----+----------+----------+4 rows in set (0.00 sec)mysql> insert into user values(5,"ermazi","bbb");  //没有插入权限ERROR 1142 (42000): INSERT command denied to user 'aaa'@'localhost' for table 'user'mysql> update user set password="bbb" where username="zhaoliu";   //没有修改权限ERROR 1142 (42000): UPDATE command denied to user 'aaa'@'localhost' for table 'user'mysql> quitBye

（9）为aaa用户赋予test数据库user表插入权限

 ​[root@mysql ~]# mysql -uroot -pRoot@123456mysql> grant insert on test.user to 'aaa';  //赋予插入权限Query OK, 0 rows affected (0.01 sec)mysql> quitBye[root@mysql ~]# mysql -uaaa -p123mysql> insert into test.user values(5,"ermazi","bbb");    //插入成功Query OK, 1 row affected (0.01 sec)mysql> select * from test.user;+----+----------+----------+| id | username | password |+----+----------+----------+|  1 | zhangsan | 123      ||  2 | lisi     | 456      ||  3 | wangwu   | 789      ||  4 | zhaoliu  | aaa      ||  5 | ermazi   | bbb      |+----+----------+----------+5 rows in set (0.00 sec)mysql> update test.user set password='000' where username='ermazi';    //没有修改权限ERROR 1142 (42000): UPDATE command denied to user 'aaa'@'localhost' for table 'user'

4、命令练习

（1）创建三个账号，abc[abcd]，ccc[a1b2c3],ddd[231343]

 mysql> create user 'abc'@'%' identified by 'abcd';Query OK, 0 rows affected (0.00 sec)mysql> create user 'ccc'@'%' identified by 'a1b2c3';Query OK, 0 rows affected (0.01 sec)mysql> create user 'ddd'@'%' identified by '231343';Query OK, 0 rows affected (0.00 sec)mysql> select host,user from mysql.user;+-----------+------------------+| host      | user             |+-----------+------------------+| %         | aaa              || %         | abc              || %         | ccc              || %         | ddd              || %         | li               || %         | lilaosi          || %         | root             || localhost | mysql.infoschema || localhost | mysql.session    || localhost | mysql.sys        || localhost | root             |+-----------+------------------+11 rows in set (0.00 sec)

（2）删除用户lilaosi

 删除lilaosi用户mysql> drop user 'lilaosi';Query OK, 0 rows affected (0.02 sec)mysql> select host,user from mysql.user;+-----------+------------------+| host      | user             |+-----------+------------------+| %         | aaa              || %         | abc              || %         | ccc              || %         | ddd              || %         | li               || %         | root             || localhost | mysql.infoschema || localhost | mysql.session    || localhost | mysql.sys        || localhost | root             |+-----------+------------------+10 rows in set (0.00 sec)

（3）aaa，ccc，ddd三个账户的密码修改为123

 mysql> alter user 'aaa'@'%' identified by '123';Query OK, 0 rows affected (0.00 sec)mysql> alter user 'ccc'@'%' identified by '123';Query OK, 0 rows affected (0.00 sec)mysql> alter user 'ddd'@'%' identified by '123';Query OK, 0 rows affected (0.01 sec)

（4）权限添加练习

（1）添加aaa账户，设置密码aaaa

 mysql> create user 'aaa'@'%' identified by 'aaaa';Query OK, 0 rows affected (0.01 sec)

（2）使用aaa账户访问mysql服务

 [root@mysql ~]# mysql -uaaa -paaaa

（3）查看test数据库发现没有权限

 mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || performance_schema |+--------------------+2 rows in set (0.00 sec)

（4）退出并使用root账户登录

 mysql> quitBye[root@mysql ~]# mysql -uroot -pRoot@123456

（5）为aaa账户添加查看test.user表的权限

 mysql> grant select on test.user to 'aaa';Query OK, 0 rows affected (0.00 sec)

（6）退出root，使用aaa账户登录

 mysql> quitBye[root@mysql ~]# mysql -uaaa -paaaa

（7）查看数据库，查看表，查看表内容，能够正常查看

 mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || performance_schema || test               |+--------------------+3 rows in set (0.00 sec)mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+----------------+| Tables_in_test |+----------------+| user           |+----------------+1 row in set (0.00 sec)mysql> select * from test.user;+----+----------+----------+| id | username | password |+----+----------+----------+|  1 | zhangsan | 123      ||  2 | lisi     | 456      ||  3 | wangwu   | 789      ||  4 | zhaoliu  | aaa      ||  5 | ermazi   | bbb      |+----+----------+----------+5 rows in set (0.00 sec)

（8）输入数据，没有权限

 mysql> inster into user values(6,"aaa","aaaa");ERROR 1142 (42000): INSERT command denied to user 'aaa'@'localhost' for table 'user'

（9）退出aaa使用root登录

 mysql> quitBye[root@mysql ~]# mysql -uroot -pRoot@123456

（10）为aaa添加insert权限

 mysql> grant insert on test.user to 'aaa';Query OK, 0 rows affected (0.00 sec)

（11）退出root使用aaa登录

 mysql> quitBye[root@mysql ~]# mysql -uaaa -paaaa

（12）使用aaa账户，想user表中添加一行新的数据

 mysql> insert into test.user values(6,"aaa","aaaa");Query OK, 1 row affected (0.01 sec)

（13）修改user表中一行的数据的password（密码）为111，没有update权限

 mysql> update test.user set password="111" where username='aaa';ERROR 1142 (42000): UPDATE command denied to user 'aaa'@'localhost' for table 'user'

（14）为aaa用户一次性添加delect，update权限

 mysql> grant delete,update on test.user to 'aaa';Query OK, 0 rows affected (0.00 sec)mysql> show grants for 'aaa';+--------------------------------------------------------------------+| Grants for aaa@%                                                   |+--------------------------------------------------------------------+| GRANT USAGE ON *.* TO `aaa`@`%`                                    || GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.`user` TO `aaa`@`%` |+--------------------------------------------------------------------+2 rows in set (0.00 sec)

6、权限角色

（1）创建角色

 mysql> create role 'jingli';    //创建jingli角色Query OK, 0 rows affected (0.00 sec)mysql> create role 'yuangong';      //创建yonghu角色Query OK, 0 rows affected (0.00 sec)

（2）为角色赋予权限

 mysql> grant insert,delete,update,select on test.user to 'jingli';    //为jingli角色添加select，insert，delete，update权限Query OK, 0 rows affected (0.00 sec)mysql> show grants for 'jingli';      //查看jingli角色权限+-----------------------------------------------------------------------+| Grants for jingli@%                                                   |+-----------------------------------------------------------------------+| GRANT USAGE ON *.* TO `jingli`@`%`                                    || GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.`user` TO `jingli`@`%` |+-----------------------------------------------------------------------+2 rows in set (0.00 sec)mysql> grant select,insert on test.user to 'yuangong';     //为yuangong添加select，insert权限Query OK, 0 rows affected (0.00 sec)mysql> show grants for 'yuangong';     //查看yonghu角色权限+---------------------------------------------------------+| Grants for yuangong@%                                   |+---------------------------------------------------------+| GRANT USAGE ON *.* TO `yuangong`@`%`                    || GRANT SELECT, INSERT ON `test`.`user` TO `yuangong`@`%` |+---------------------------------------------------------+2 rows in set (0.00 sec)mysql> select host,user from mysql.user;    //查看角色保存的表格+-----------+------------------+| host      | user             |+-----------+------------------+| %         | aaa              || %         | abc              || %         | ccc              || %         | ddd              || %         | jingli           || %         | li               || %         | root             || %         | yuangong         || localhost | mysql.infoschema || localhost | mysql.session    || localhost | mysql.sys        || localhost | root             |+-----------+------------------+12 rows in set (0.00 sec)

（3）用户授予角色权限

 mysql> grant jingli to 'ddd';          //将ddd用户授予jingli角色，拥有jingli权限Query OK, 0 rows affected (0.00 sec)mysql> show grants for ddd;           //查看权限授权+---------------------------------+| Grants for ddd@%                |+---------------------------------+| GRANT USAGE ON *.* TO `ddd`@`%` || GRANT `jingli`@`%` TO `ddd`@`%` |+---------------------------------+2 rows in set (0.00 sec)mysql> flush privileges;            //刷新权限Query OK, 0 rows affected (0.00 sec)

（4）删除角色权限

 mysql> revoke all on *.* from "yuangong";    //删除yuangong所有权限Query OK, 0 rows affected (0.00 sec)mysql> show grants for "yuangong";           //查看yuangong权限+---------------------------------------+| Grants for yuangong@%                 |+---------------------------------------+| GRANT USAGE ON *.* TO `yuangong`@`%`  |+---------------------------------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

7、角色练习

新增bbb和ccc两个用户，bbb需要增删改查权限，ccc需要新增与查看权限

 mysql> create user 'bbb'@'%' identified by 'bbb';    //创建bbb用户Query OK, 0 rows affected (0.01 sec)mysql> create user 'ccc'@'%' identified by 'ccc';    //创建ccc用户Query OK, 0 rows affected (0.01 sec)mysql> grant jingli to 'bbb';          //将bbb用户授予jingli角色，拥有jingli权限Query OK, 0 rows affected (0.00 sec)mysql> grant yuangong to 'ccc';       //将ccc用户授予yuangong角色，拥有yuangong权限Query OK, 0 rows affected (0.00 sec)mysql> show grants for bbb;           //查看权限授权+---------------------------------+| Grants for bbb@%                |+---------------------------------+| GRANT USAGE ON *.* TO `bbb`@`%` || GRANT `jingli`@`%` TO `bbb`@`%` |+---------------------------------+2 rows in set (0.00 sec)mysql> show grants for ccc;           //查看权限授权+-----------------------------------+| Grants for ccc@%                  |+-----------------------------------+| GRANT USAGE ON *.* TO `ccc`@`%`   || GRANT `yuangong`@`%` TO `ccc`@`%` |+-----------------------------------+2 rows in set (0.00 sec)mysql> flush privileges;            //刷新权限Query OK, 0 rows affected (0.00 sec)