04MySQL权限管理

MySQL 权限

在实际应用中，为了防止因操作不当，导致数据被误删。所以，一般不会直接在 root 用户下直接进行数据库的操作，我们通常会登录 root 账户后，创建一些新用户并赋予这些用户一些操作数据库的权限。

MySQL 的用户权限可分为三大级别：

• 全局性的管理权限：作用于整个 MySQL 实例级别。
• 数据库级别的权限：作用于某个指定的或者所有的数据库上。
• 数据库对象级别的权限：作用于指定单个数据库对象，如表、视图等，或者所有的数据库对象上。

账户权限信息被存储在 MySQL 数据库中的 user，db，host，tables_priv，columns_priv，procs_priv 这几个系统表中。

• user 表：记录允许连接到服务器的账户信息。
• db 表：记录用户对某个数据库操作的权限信息。
• host 表：记录主机上的用户对数据库拥有的权限信息。
• tables 表：记录用户对某个表的操作权限信息。
• columns_priv表：记录用户对表里的某一列的操作权限信息。
• procs_priv 表：记录用户对存储过程和存储函数的操作权限信息。

首先介绍一下账户操作的基本命令：

• -h 为主机名，默认是 localhost。
• -u 为用户名。
• -p 为登录密码。

所以，我们进入 MySQL 会使用命令 mysql -u root -p。

查看用户

使用以下语句来查看一下，在 MySQL 数据库的 user 表中已经拥有哪些用户：

SELECT user,host FROM mysql.user;

这里的查询结果会显示 user 表中的 user 和 host 这两个字段的信息。提醒一下，user 是用户名，host 是指定登录的主机号，也就是 ip地址。

查看用户权限

查看这些用户各自拥有哪些权限，查询语句如下所示：

# 查看 user 表中所有信息
SELECT * FROM mysql.user\G;

后面加一个 \G 是让这些信息能够更清晰的显示，你们可以试一试不加 \G 来查询。
在这个语句的基础上加一个 WHERE 条件语句可用来查看单一用户权限：

SELECT * FROM mysql.user WHERE user='<用户名>'\G;

例如，我们查看一下 root 用户的权限信息。

SELECT * FROM mysql.user WHERE user='root'\G;

这里有一篇文章详细介绍了授权信息，用户管理权限。

MySQL 用户账户的管理

添加用户

在 MySQL 中常用的添加用户的语句，有以下两种方式：

CREATE USER '<新用户的账户>' IDENTIFIED BY '<用户密码>';

# *.* 表示对所有数据库生效
# 这里的主机号就是 ip 地址
GRANT <权限> on *.* TO <用户名>@'<主机号>'IDENTIFIED BY '<密码>';

使用 CREATE USER 来创建用户，相当于在 user 表中添加一条新的记录，用户是没有任何权限的，我们需要其他操作来授予权限给用户，而使用 GRANT 语句在创建用户的同时给用户授权。

例如，我们添加一个用户名为 Lotus 的新用户账号，其密码为 123。

# 创建新用户
CREATE USER 'Lotus' IDENTIFIED BY '123';
# 查看用户信息
SELECT user,host FROM mysql.user;

我们使用第二种创建方式，添加一个名为 Cici 的用户，设置密码为 123，允许所有 ip 连接，并获得所有权限。

# ALL PRIVILEGES 表示获得所有权限
GRANT ALL PRIVILEGES ON *.*
TO Cici@'%'IDENTIFIED BY '123';

修改用户

对于已经存在的用户，我们也可以进行修改，语句格式如下所示：

RENAME USER '<旧用户名>'@'<旧主机号>' TO '<新用户名>'@'<新主机号>';

例如，我们修改 Lotus 用户的用户名和 ip 地址。

RENAME USER 'Lotus'@'%' TO 'Li'@'localhost';

删除用户

如果不想要该用户了，我们可以使用以下两种删除语句：

DROP USER '<用户名>'@'<主机名>'

DELETE FROM mysql.user WHERE Host='<主机名>' AND User='<用户名>'

限制账户资源

MySQL 中常见的资源列有以下几种：

• max_questions：用户每小时允许执行的查询操作次数。
• max_updates：用户每小时允许执行更新操作次数。
• max_connections：用户每小时允许执行的连接操作次数。
• max_user_connections：用户允许同时建立的连接次数。

限制客户端使用 MySQL 服务器资源的一种方法是将全局 MAX_USER_CONNECTIONS 系统变量设置为非零值，这会限定账号建立同时连接的数量。

例如，我们重新建立一个名为 Lotus 的用户，并对该用户进行资源限制。

# 在创建账户之后进行资源限制
GRANT SELECT ON Lotus.*
TO 'Lotus'@'%'
WITH MAX_QUERIES_PER_HOUR 3
MAX_USER_CONNECTIONS 5;

我们也可以使用 ALTER USER 语句来修改现有的资源。例如，我们把用户 Lotus 的资源限制信息进行修改，把用户在一个小时内的执行查询的次数从 3 次修改为 50 次。

ALTER USER 'Lotus'@'%' WITH MAX_QUERIES_PER_HOUR 50;

我们也可以在创建账户时，进行资源限制。例如，我们再创建一个名为 Ann 的用户。

CREATE USER 'Ann'@'%' IDENTIFIED BY '123'

# 设置用户在一小时内可以执行 20 次查询
WITH MAX_QUERIES_PER_HOUR 20

# 设置用户在一小时内可以修改 20 次
MAX_UPDATES_PER_HOUR 20

# 设置用户在一小时内可以连接 MySQL 5 次
MAX_CONNECTIONS_PER_HOUR 5

# 设置所有用户在同一时间连接 MySQL 的最大连接数为 5
MAX_USER_CONNECTIONS 5;

我们还可以使用下面的方式来查看自己设定的用户权限。

SELECT user,host,u.max_questions,u.max_updates,u.max_connections,u.max_user_connections FROM mysql.user u WHERE u.user='Ann'\G;

设置账户密码

修改用户密码常用的有以下两种方式：

SET PASSWORD FOR '<用户名>'@' <主机号>'=PASSWORD('<新密码>');

GRANT USAGE ON *.* TO '<用户名>'@'<主机号>'IDENTIFIED BY '<新密码>';

若已经完成了对数据库的操作，我们可以使用 quit 来退出 MySQL。