mysql数据库管理基础之用户权限

我是修沟的修狗
882 阅读10分钟

控制权限和权限表

在实际的生产环境中，数据库的安全非常重要。不安全的数据库将面临数据丢失、系统崩溃等威胁。为了保障数据库的安全，MySQL 数据库提供了完善的管理机制和操作手段，如权限管理、数据备份与恢复、多数据库同步等。权限控制，用于防止个别用户的恶意企图，也用于防止用户无意的错误，是 MySQL 服务器安全的基础。

现实中，常见以下控制需求：

  • 少数用户需要建表和删表权限，多数只需要进行读写操作；
  • 对表中的数据，少数用户需要修改权，多数只需要进行读取；
  • 允许某些用户添加数据，但是不能删除数据；
  • 对某些用户的登录时间和地点进行限制；
  • 让某些用户不能直接访问数据，只允许通过函数或者存储过程访问数据。

上述这些需求，要求为不同用户分配不同权限。MySQL 是一个多用户数据库，可以为不同用户分配不同的权限。

MySQL 通过权限表来控制用户对数据库的访问，权限表存放在 mysql 同名的这个数据库中，主要的权限表有：user，db，host，table_priv，columns_priv 和 procs_priv。

用户权限表：user 表

user 表是 MySQL 中最重要的一个权限表，该表中配置的所有权限都是全局级的，适用于所有数据库。该表有很多字段，大致可以分为 4 类：用户字段、权限字段、安全字段和资源控制字段，用户字段存储了用户连接 MySQL 数据库时需要输入的信息（如用户名、密码），权限字段决定了用户的权限，用来描述在全局范围内允许对数据和数据库进行的操作，安全字段主要用来判断用户是否能够登录成功（如密码是否过期），资源控制字段用来限制用户使用的资源（如每小时允许执行查询操作的次数）。权限字段名称均以 priv 为后缀，如果想查看 root 用户是否具有增、删、改、查的权限，其 SQL 语句如下：

USE mysql;
SELECT user, SELECT_priv, insert_priv, update_priv, delete_priv FROM user WHERE user = 'root';

image.png root 用户是 MySQL 的超级用户，拥有 MySQL 所有权限，所以上表呈现了四个“Y”（Yes）。

特定的用户特定的权限：db 表

db 表也是 MySQL 数据库中非常重要的权限表，db 表存储了用户对某个数据库的操作权限，决定用户能在哪个主机操作哪个数据库。该表字段也分为用户字段和权限字段，db 表中的权限列和 user 表中的权限列大致相同，只是 user 表中的权限是针对所有数据库的，而 db 表中的权限只针对指定的数据库。查看用户对某数据库是否具有增、删、改、查等操作权限，其 SQL 语句如下：

USE mysql;
SELECT user, db, select_priv, insert_priv, update_priv, delete_priv FROM db;

image.png 可见，用户 mysql.session 对数据库 performance_schema 只拥有查询权限。

单表权限设置：tables_priv 表

tables_priv 表用来对单个表进行权限设置。tables_priv 表共有 8 个字段：host、db、user、table_name、grantor、timestamp、table_priv 以及 column_priv。查看该表信息结果如下：

USE mysql;
SELECT * FROM tables_priv;

image.png 各字段说明如下：

  • Host：主机名；
  • Db：数据库名；
  • User：用户名；
  • Table_name：表名；
  • Grantor：修改该记录的用户；
  • Timestamp：修改该记录的时间；
  • Table_priv：对表操作的权限，包括 SELECT、INSERT、UPDATE、DELETE、CREAT、DROP 等；
  • Column_priv：对表中字段的操作权限，包括 SELECT、INSERT、UPDATE、REFERENCE 等。

单列权限设置：column_priv 表

columns_priv 表用来对单个数据列进行权限设置。该表共有 7 个字段：host、db、user、table_name、column_name、timestamp 以及 column_priv。其中，column_name 用来指定对哪一列进行权限设置，其它字段和 tables_priv 表字段含义相同。该表信息如下：

USE mysql;
DESC columns_priv;

image.png MySQL 权限判定优先级顺序是 user 表 → db 表 → tables_priv 表 → column_priv 表，先检查全局权限表 user，如果 user 中对应的权限为 Y，则此用户对所有数据库的权限都为 Y，将不再检查 db, tables_priv, columns_priv；如果为 N，则到 db 表中检查此用户对应的具体数据库，并得到 db 中为 Y 的权限；如果 db 中为 N，则检查 tables_priv 中此数据库对应的具体表，取得表中的权限 Y，以此类推。因此为了做到“最小权限”原则，建议从列、表级配置开始，尽量不给全库或全局的权限。

储存过程和储存函数权限：procs_priv 表

procs_priv 表可以对存储过程和存储函数进行权限设置。该表共有 8 个字段：host、db、user、routine_name、routine_type、grantor、proc_priv 以及 timestamp。输入以下命令查询表信息：

USE mysql;
DESC procs_priv;

image.png 各字段说明如下：

  • Host、Db 和 User：分别表示主机名、数据库名和用户名；
  • Routine_name：存储过程或函数名；
  • Routine_type：存储过程或函数的类型。该字段有两个值，FUNCTION 和 PROCEDURE。FUNCTION 示自定义函数，PROCEDURE 表示存储过程；
  • Grantor：插入或修改该记录的用户；
  • Proc_priv：拥有的权限，包括 Execute、Alter Routine 和 Grant 三种；
  • Timestamp：记录更新时间。

如果要修改权限，可以通过 UPDATE 语句更新这些权限表，也可以使用 GRANT 语句为用户赋权，用 REVOKE 语句取消授权，初学者掌握 GRANT 和 REVOKE 即可

添加、查看和删除用户

MySQL 是一个多用户数据库管理系统，分为两种：root 用户和普通用户。root 用户又叫超级管理员用户，拥有对数据库操作的所有权限；普通用户的权限由 root 用户或者拥有分配权的用户进行分配。

添加用户

刚安装好 MySQL 系统时，只存在 root 用户，该用户由 MySQL 服务器自动创建，并且被赋予了操作 MySQL 的所有权限。

在对 MySQL 的日常管理和实际操作中，为了避免恶意用户滥用 root 账户操控数据库，尽可能不用或少用 root 账户登录数据库系统，以确保数据库的安全，因此需要创建一系列普通用户。

使用 CREATE USER 语句创建新用户，其语法如下：

CREATE USER '账号' [IDENTIFIED BY '密码'];

其中，“账号”的格式为 user_name@host_name。user_name 是账号名称，host_name 为主机名称，即指定账户的同时又指定发起连接的客户端主机名字。也可以不指定主机名，默认为 %，表示匹配任意客户端主机。

IDENTIFIED BY 子句用于指定账户的密码，该子句若省略，表示创建的用户没有密码。

例如创建一个名为 “zhangsan”，密码 “123” 且不能远程登录的普通用户，其 SQL 语句如下：

CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123';

查看用户

进入 mysql 数据库，在 USER 表中查看新插入的用户记录。其 SQL 语句如下：

SELECT user, host FROM user;

修改用户密码

修改用户密码使用 ALTER 子句，其语法如下：

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

接下来以 “zhangsan” 用户为例，修改密码为 “123456”，其 SQL 语句如下所示：

ALTER USER 'zhangsan'@'localhost' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

其中，其中 FLUSH PRIVILEGES 表示刷新用户权限。

删除用户

  1. 使用 DROP 子句删除用户，其语法如下：

    DROP USER 用户名@主机名;

    例如删除 “zhangsan” 用户：

    DROP USER zhangsan@localhost;

  2. 使用 DELETE FROM 子句对 USER 表的记录进行删除，其 SQL 语句如下：

    DELETE FROM USER WHERE USER = 'zhangsan' AND host = 'localhost';

用户权限管理之查看用户权限

查看用户权限

语法为：

SHOW GRANTS FOR 用户名@主机名;

查看普通用户“zhangsan”的权限，其 SQL 语句如下：

SHOW GRANTS FOR zhangsan@localhost;

用户权限管理之权限授予

使用关键字 GRANT，其语法格式如下：

GRANT 权限名称[(列名)][, 权限名称(列名)] ON 权限级别 TO 用户 [with option]

参数说明如下：

权限名称：如 SELECT、UPDATE、INSERT、DELETE 等数据库操作。

列名：可以给某一列或多列指定权限，省略则表示所有列。

权限级别：有以下几种情况。

  • 列权限：表中的一个具体列相关，影响权限表 columns_priv；
  • 表权限：和一个表中的所有数据相关，影响权限表 tables_priv；
  • 数据库权限：和一个数据库中的所有表相关，影响权限表 db；
  • 用户权限：和 MySQL 中所有数据库相关，影响权限表 user。

指定权限级别可以有以下几种格式：

  • *：表示当前数据库中的所有表；
  • _._：表示所有数据库中的所有表；
  • db_name.*：表示指定数据库的所有表，db_name 为数据库名；
  • db_name.tb_name：表示指定数据库中的指定表或视图，db_name 为数据库名，tb_name 为表名或视图名；
  • tb_name：表示指定表或视图；
  • db_name.routine_name：表示指定数据库中指定存储过程或函数，db_name 为数据库名，routine_name 为存储过程或函数名。

如果想给某个用户所有权限，将 权限名称[(列名)][, 权限名称(列名)] ON 权限级别 替换为 ALL PRIVILEGES。

用户：该参数格式为 user_name@host_name

with option：可选项，进行权限限制。

用户权限管理之权限限制与撤销

权限限制

在 GRANT 语法中，可使用 WITH 对权限进行限制，主要包括：

  • GRANT OPTION：表示将自己拥有的权限转移给其它用户；
  • max_queries_per_hour count：表示限制每小时可以查询数据库次数，count 表示具体数字，0 表示不限制。
  • max_update_per_hour count：表示限制每小时可以修改数据库次数，count 表示具体数字，0 表示不限制。
  • max_connections_per_hour count：表示限制每小时可以连接数据库次数，count 表示具体数字，0 表示不限制。
  • max_user_connections count：表示限制同时连接 MySQL 数据库的最大用户数。

权限撤销

当某个用户不再适合拥有某个权限的时候，我们需要撤销权限。撤销权限使用关键字 REVOKE，其语法有两种格式。

格式一：

REVOKE 权限名称[(列名)] ON 权限级别 FROM 用户;

该语法用于回收某个用户的特定权限。

格式二：

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户;

该语法用于回收某个用户的所有权限。

例如：回收 “zhangsan” 用户在 mysql 数据库中的 SELECT 权限，其 SQL 语句如下：

REVOKE SELECT ON mysql.* FROM zhangsan@localhost;

如果回收所有权限，SQL 语句如下：

REVOKE ALL PRIVILEGES, GRANT OPTION FROM zhangsan@localhost;

相关实验

第一个

  1. 启动 MySQL ；
  2. 创建一个本地用户 Rong，密码为 123456 ；
  3. 授予访问 performance_schema 的权限 ；
create user Rong@localhost IDENTIFIED BY '123456';

GRANT SELECT ON performance_scheme.* TO Rong@localhost;

第二个

  1. 启动 MySQL ；
  2. 切换到 mysql 数据库，查询具有修改权限的 root 用户的权限信息。 select * from user where User='root';

第三个

  1. 创建一个 名为 Rong 的普通用户，密码设置为 123456，且不允许远程登录。

第四个

-- 1. 创建数据库用户user_1和user_2，密码都为1234（假设服务器名为localhost）。
USE MYSQL;
CREATE USER user_1@LOCALHOST IDENTIFIED BY '1234';
CREATE USER user_2@LOCALHOST IDENTIFIED BY '1234';
-- 2. 将用户user_2的名称修改为user_3。
rename user user_2@localhost to user_3@localhost;
-- 3. 将用户user_3的密码修改为123456。
ALTER USER 'user_3'@'localhost' IDENTIFIED BY '123456'; 
-- 4. 删除用户user_3。
drop user user_3@localhost;
-- 5. 以user_1用户身份登录MySQL。
-- 这个语句在window或者linux里面使用，不是mysql里面 
mysql -uuser_1 -p1234 
-- 6. 授予用户user_1对学生作业管理系统数据库中student表的所有操作权限及查询操作权限。
GRANT all on table scdb.student to user_1@localhost; 
-- 7. 授予用户user_1对student表进行插入、修改、删除操作权限。
grant insert,update,DELETE on table scdb.student to user_1@localhost;
-- 8. 授予用户user_1对数据库学生作业管理系统的所有权限。
grant all on table scdb.* to user_1@localhost; -- 9. 授予user_1在course表上的select权限，并允许其将该权限授予其他用户。
grant select on table scdb.course to user_1@localhost;
-- 10. 回收user_1的student表上的select权限。 REVOKE select on scdb.student from 
user_1@localhost;
avatar
文章
阅读
粉丝