一、MYSQL基础篇

原创已于 2023-11-30 20:20:50 修改 · 1.2k 阅读

4 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #数据库 #mysql用户权限

于 2021-03-06 20:52:58 首次发布

mysql 专栏收录该内容

4 篇文章

订阅专栏

本文详细介绍了MySQL数据库的安装、数据库服务器、用户与权限管理，包括角色、权限分配示例，以及多租户模式的应用。涵盖了从基础配置到高级权限控制的全面内容。

一、前言

MYSQL数据库安装

Windows版参考：https://www.cnblogs.com/winton-nfs/p/11524007.html

Linux版本参考：https://www.cnblogs.com/levia/p/11396512.html

本文安装的版本为linux版本8.0.23，下载地址：https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.23-1.el7.x86_64.rpm-bundle.tar

MYSQL官方文档：MySQL :: MySQL Documentation

MYSQL数据库定义

数据库服务器：整个MYSQL数据库软件，包括运行在它之上的所有数据库即为数据库服务器。MYSQL是一个单进程数据库，一个数据库服务器可以创建多个数据库，所有数据库运行在同一个进程中，数据库服务mysqld启动，则所有数据库都启动了。

数据库：一个数据库服务器可以创建多个数据库，表、触发器等对象都属于数据库，用户不属于数据库。由于MYSQL是单进程，一个数据库服务器上的多个数据库中的表可以很方便的关联。系统自带名称为mysql的数据库，保存所有数据库系统对象。

模式：在mysql中，模式和数据库是同等概念。

用户：与oracle不同，mysql用户是建立在数据库服务器层面上的，用户不属于某一个数据库，通过对用户赋权的方式让用户可以访问数据库。系统自带root用户，拥有所有权限。

表：表属于数据库，用户可以单独获取某张表的访问权限。

重要sql如下：

-- 查询所有数据库
show databases; 
 -- 查询当前数据库
select database();
-- 保存用户及用户的全局权限，如grant create on *.* to u1;即u1拥有创建数据库和所有数据库建表的权限
select * from mysql.user;
-- 保存用户对应的数据库权限，如grant select on db1.* to u1;即u1拥有db1所有表的查询权限
select * from mysql.db;
-- 保存用户对应的表权限，如grant select on db1.t1 to u1;即u1拥有db1.t1表的查询权限
select * from mysql.tables_priv;
-- 保存用户对应的列权限，如grant select(id,name) on db1.t1 to u1;即u1拥有db1.t1表id,name两列的查询权限
select * from mysql.columns_priv;
-- 保存用户对应存储过程的权限
select * from mysql.procs_priv;

-- 查看当前用户被赋予的权限
show grants;
-- 查看u1被赋予的权限
show grants for  'u1'

二、数据库服务器

windows环境启动数据库

管理员权限启动dos，执行：

net start mysql #启动mysql数据库

net stop mysql #停止mysql数据库

mysqld --console --skip-grant-tables --shared-memory #启动数据库（不校验权限的方式）

linux环境启动数据库

systemctl start mysqld #启动mysql数据库

systemctl stop mysqld #停止mysql数据库

连接数据库

mysql -u root -p #连接本地数据库，host为localhost，即登录'root'@'localhost'用户，密码后续输入

mysql -P 3306 -h 192.168.1.101 -u root -p #连接远程数据库，host为本机ip地址，即登录'root'@'本机ip'用户

mysql -u root -p1111 #连接命令中带密码1111（注意-p和密码之间不能有空格）

mysql -u root -p dbname #登录后切换到指定数据库dbname

三、数据库

#创建数据库
create database db1;
create database if not exists db1;
#删除数据库（包括数据库所有数据及文件）
drop database db1;
drop database if exists db1;
#查询数据库
show databases; #所有数据库
select database(); #当前数据库
#切换当前数据库
use db1;

四、用户

MySQL的用户认证形式是: 用户名+主机。比如test@192.168.1.101和test@192.168.1.102是不一样的用户，第1个用户只允许在192.168.1.101上登录访问mysql，第2个用户值允许在192.168.1.102上登录访问mysql。

注意：Linux版mysql默认秘密安全级别较高，为了测试方便先将密码等级修改到最低。


#查看密码安全级别
show variables like 'validate_password%';
#修改密码安全级别
set global validate_password.policy=LOW; 
set global validate_password.length=4; #密码长度最小为4

#查询用户
select * from mysql.user; #所有用户
select current_user(),user(); #当前用户

#注意：创建用户,create user/alter user/drop user等命令不指定host时默认都是%
#前提：以下假设数据库部署在192.168.1.101上

#以下命令：创建用户u1@localhost,只允许在本机登录:mysql -u u1 -p
create user if not exists 'u1'@'localhost' identified by '1111';
#以下命令：创建用户u2@%,可以在任何机器上登录,本机:mysql -u u2 -p,非本机:mysql -h 192.168.1.101 -u u2 -p
create user if not exists 'u2'@'%' identified by '1111';
#以下命令：创建用户u3@%,不指定host默认%,可以在任何机器上登录
create user if not exists 'u3' identified by '1111';
#以下命令：创建用户u4@192.168.1.%，只允许192.168.1网段机器可以登录
create user if not exists 'u4'@'192.168.1.%' identified by '1111';

#以下命令：修改u1@localhost用户密码
alter user 'u1'@'localhost' identified by '1111';
#以下命令：修改u2@%用户密码
alter user 'u2'@'%' identified by '1111';
#以下命令：修改u3@%用户密码,不指定host默认%
alter user 'u3' identified by '1111';
#以下命令：修改'u4'@'192.168.1.%'用户密码
alter user 'u4'@'192.168.1.%' identified by '1111';

#删除用户u1@localhost
drop user if exists 'u1'@'localhost';
#删除用户u2@%
drop user if exists 'u2'@'%';
#删除用户u3@%,不指定host默认%
drop user if exists 'u3';
#删除用户'u4'@'192.168.1.%'
drop user if exists 'u4'@'192.168.1.%';

#修改用户名，修改后root用户为所有服务器都可以登录，修改后必须执行flush privileges刷新MySQL的系统权限相关表或重启mysql服务器，否则会出现拒绝访问
update user set host = "%" where user='root';
flush privileges;
 
#示例：创建3个test用户，host不同，表示3个不同的用户
#前提：假设数据库服务器部署在192.168.1.101

#创建用户只允许本机登录，本机执行:mysql -u test -p ,密码1111可以登录
create user if not exists 'test'@'localhost' identified by '1111'; 
#，创建用户只允许192.168.1.102登录，192.168.1.102上执行:mysql -h 192.168.1.101 -u test -p,密码2222,可以登录成功
create user if not exists 'test'@'192.168.1.102' identified by '2222';
#创建用户任何机器都可以登录，非192.168.1.102的任何机器上执行mysql -h 192.168.1.101 -u test -p,密码3333,可以登录成功；若192.168.1.102上登录,会登录到密码2222的用户上
create user if not exists 'test'@'%' identified by '3333';

五、角色

角色为权限的集合。

#创建角色
create role r1; #默认%
create role r2@localhost;
#删除角色
drop role r1; #默认%
drop role r2@localhost;

六、权限

mysql角色和用户在权限上具有同等地位：

1.权限可以直接赋给角色或用户；

2.角色也可以赋给另一个角色或赋给另一个用户

3.用户也可以赋给另一个角色或赋给另一个用户

参考：[玩转MySQL之三]MySQL用户及权限 - 知乎

mysql中存在4个控制权限的表，分别为user表，db表，tables_priv表，columns_priv表。
mysql权限表的验证过程为：
1.先从user表中的Host,User,Password这3个字段中判断连接的ip、用户名、密码是否存在，存在则通过验证。
2.通过身份认证后，进行权限分配，按照user，db，tables_priv，columns_priv的顺序进行验证。即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db, tables_priv,columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推。

赋权方式

注意：下列sql中用户替换为角色也可以。

grant all on *.* to 'user'@'host'; #数据库服务器级别权限，保存在mysql.user表中，用户拥有数据库服务器上所有数据库及所有表的所有权限
grant all on mydb.* to 'user'@'host'; #数据库级别权限，保存在mysql.db表中，用户拥有mydb数据库的所有权限
grant all on mydb.mytable to 'user'@'host'; #表级别权限，保存在mysql.tables_priv表中，用户拥有mydb.mytable表的所有权限
grant all (col1,col2,col3) on mydb.mytable to 'user'@'host'; #列级别权限，保存在mysql.columns_priv表中，用户拥有mydb.mytable(col1,col2,col3)这3个列的所有权限
grant all on procedure mydb.myproc to 'user'@'host'; #子程序级别权限，保存在mysql.procs_priv表中，用户拥有mydb.myproc子程序的所有权限

#权限保存表
select * from mysql.user;
select * from mysql.db;
select * from mysql.tables_priv;
select * from mysql.columns_priv;
select * from mysql.procs_priv;

#赋予权限，给同一个用户/角色赋予不同级别的权限时，对应表中记录都存在，最终拥有的权限为最高级别权限，如下最终拥有的权限为数据库服务器权限
grant all on *.* to 'u1'@'localhost'; #拥有整个数据库服务器的权限
grant all on db1.* to 'u1'@'localhost'; #拥有数据库db1的所有权限，包括创建和删除该数据库
grant all on db1.tb1 to 'u1'@'localhost'; #拥有表db1.tb1的所有权限，包括创建和删除该表
grant select,update,insert(id,name) on db1.tb1 to 'u1'@'localhost';


#撤销权限
revoke all on *.* from 'u1'@'localhost'; #只执行该命令时，会撤*.*级别权限和所有更低级别权限，即*.* db1.* db1.tb1 这3个级别权限都会被删除
revoke all on db1.* from 'u1'@'localhost';#只执行该命令时，会撤销db1.*和db1.tb1级别权限，*.*级别权限未被撤销，所以u1还是拥有数据库服务器所有权限；db1可以是一个不存在的数据库，可以先赋权后创建数据库
revoke all on db1.tb1 from 'u1'@'localhost';#只执行该命令时，只会撤销db1.tb1级别全新啊。
revoke select,update,insert(id,name) on db1.tb1 from 'u1'@'localhost';

#用户和角色之间相互赋权
grant r1,u1 to r2;
grant r1,u1 to u2;

#查询通过grant赋予的权限
show grants; #查看当前用户被赋予权限
show grants for 'u1'@'localhost'; #查看指定用户/角色被赋予权限

权限种类

七、示例脚本

创建示例数据库、用户，并赋权。

shell终端执行：mysql -u root -p1111 < ./sampdb.sql

#文件sampdb.sql
#创建数据库mydb
create database if not exists mydb; 
#创建用户utest
create user if not exists 'utest'@'%' identified by '1111';
#数据库mydb所有权限赋给用户utest
grant all on mydb.* to 'utest'@'%';

shell终端执行：mysql -u utest -p'1111' mydb ，mysql中执行以下脚本，查看用户、数据库、授权都已执行成功。

#查询当前用户和数据库
select user(),database(); 
#查询当前用户的授权信息
show grants;

八、多租户模式

mysql本身为单进程数据库，在一个数据库服务器中创建多个数据库消耗的资源非常少，天然支持多租户模式。