花于陌上开个人站

第八章 用户与权限 v1.0 ​

一、权限表 ​

MySQL 安装时会自动创建一个名为 mysql 的数据库，其中保存用户权限表。用户登录后，MySQL 会根据权限表内容为用户赋予相应权限。

user 表是 MySQL 中重要的权限表，用来记录允许连接服务器的账号信息。在 user 表中启用的权限属于全局级，适用于所有数据库。表中的字段大致可以分为用户列、权限列、安全列和资源控制列。

db 表存储用户对某个数据库的操作权限。user 表中的权限针对所有数据库，而 db 表中的权限只针对指定数据库。其他常见权限表还有 tables_priv 表和 columns_priv 表。

二、用户的创建、修改与删除 ​

1.创建用户 ​

sql
CREATE DATABASE <数据库>;
CREATE USER <用户名> IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON <数据库>.* TO <用户名>@'%';

MySQL 提供以下几种创建用户的方法：

  1. 使用 CREATE USER 语句创建用户。
  2. 在 mysql.user 表中添加用户。
  3. 使用 GRANT 语句创建用户。
sql
CREATE USER <用户名>
[IDENTIFIED BY [PASSWORD] 'password']
[,<用户名> [IDENTIFIED BY [PASSWORD] 'password']];

也可以使用 INSERT 或 GRANT：

sql
INSERT INTO mysql.user(Host, User, authentication_string)
VALUES ('hostname', 'username', PASSWORD('password'));
sql
GRANT <权限列表> ON database.table TO '<用户名>'
[IDENTIFIED BY [PASSWORD] 'password'];

创建用户后，可以使用 FLUSH PRIVILEGES; 刷新权限。

2.修改用户 ​

sql
RENAME USER <旧用户> TO <新用户>;
sql
# root用户修改普通用户密码
SET PASSWORD FOR 'username'@'hostname' = PASSWORD('newpwd');
# 用户修改当前用户密码
SET PASSWORD = PASSWORD('newpwd');
bash
mysqladmin -u username -h hostname -p password "newpwd"

3.删除用户 ​

sql
DROP USER <用户1> [, <用户2>, ...];
DELETE FROM mysql.user WHERE Host='hostname' AND User='username';

三、用户权限管理 ​

1.查看用户权限 ​

sql
SELECT * FROM mysql.user [WHERE ...];
SHOW GRANTS FOR 'username'@'hostname';

2.添加用户权限 ​

sql
GRANT <权限列表> [(column_list)] ON database.table
TO <用户名> [IDENTIFIED BY [PASSWORD] 'password']
[WITH with_option <选项参数>];

常见选项参数：

  • GRANT OPTION：被授权用户可以将这些权限赋予其他用户。
  • MAX_QUERIES_PER_HOUR count：设置每小时允许执行的查询次数。
  • MAX_UPDATES_PER_HOUR count：设置每小时允许执行的更新次数。
  • MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立的连接数。
  • MAX_USER_CONNECTIONS count：设置单个用户可以同时建立的连接数。

授予列权限时，权限只能指定为 SELECT、INSERT 和 UPDATE，并且需要指定列名列表。

3.回收用户权限 ​

sql
REVOKE <权限列表> [(column_list)]
ON database.table
FROM <用户名> [, <用户名>, ...];

REVOKE ALL PRIVILEGES, GRANT OPTION
FROM <用户名> [, <用户名>] ...;