用户管理

与苟富贵

已于 2024-07-25 19:17:50 修改

阅读量731

点赞数 15

文章标签：数据库

于 2024-07-25 19:17:12 首次发布

本文链接：https://blog.csdn.net/weixin_63631452/article/details/140694709

版权

一：权限表

1：user 表

user 表包含了所有用户级别的全局权限设置。每个条目代表一个用户，并且该用户可以在任何主机上登录。

字段:
- Host: 用户连接到 MySQL 服务器时所使用的主机名或 IP 地址。
- User: 用户名。
- Password: 密码的加密形式。
- ssl_cipher: 当前连接的 SSL 加密套件。
- x509_issuer: 当前连接的 X.509 证书颁发者。
- x509_subject: 当前连接的 X.509 证书主题。
- authentication_string: 密码字符串（在 MySQL 8.0 中取代了 Password）。
- plugin: 认证插件的名字。
- authentication_data: 额外的认证数据。
- password_expired: 密码是否已过期。
- password_last_changed: 密码最后一次更改的时间戳。
- password_lifetime: 密码的有效期限（天数）。
- account_locked: 账户是否被锁定。
- Create_routine: 是否可以创建存储过程或函数。
- Grant_option: 是否可以授予其他用户权限。
- Reset_connection: 是否可以重置会话变量。
- Resource_group: 用户所属的资源组。
- Resource_group_priority: 用户在资源组中的优先级。
- sql_flags: SQL 模式标志。
- max_questions: 每小时最大查询数。
- max_updates: 每小时最大更新数。
- max_connections: 每小时最大连接数。
- max_user_connections: 最大并发连接数。
- plugin: 认证插件名称。
- authentication_string: 加密后的密码。
- 其他字段根据版本可能有所不同。

2：db表和host表

db 表用于指定用户对特定数据库的权限。

字段:
- Host: 主机名或 IP 地址。
- Db: 数据库名。
- Select_priv: 读取权限。
- Insert_priv: 插入权限。
- Update_priv: 更新权限。
- Delete_priv: 删除权限。
- Create_priv: 创建权限。
- Drop_priv: 删除权限。
- Grant_priv: 授予权限。
- Index_priv: 创建索引权限。
- Alter_priv: 修改表结构权限。
- Create_tmp_table_priv: 创建临时表权限。
- Lock_tables_priv: 锁定表权限。
- Create_view_priv: 创建视图权限。
- Show_view_priv: 查看视图定义权限。
- Create_routine_priv: 创建存储过程或函数权限。
- Alter_routine_priv: 修改存储过程或函数权限。
- Execute_priv: 执行存储过程或函数权限。
- Event_priv: 创建事件权限。
- Trigger_priv: 创建触发器权限。
- Create_tablespace_priv: 创建表空间权限。

host 表通常不被直接使用，它包含了主机名和 IP 地址的映射，用于 db 和 tables_priv 表。

3：tables_priv表和columns_priv表

tables_priv 表用于指定用户对特定表的权限。

字段:
- Host: 主机名或 IP 地址。
- Db: 数据库名。
- Table_name: 表名。
- 权限字段与 db 表类似。

columns_priv 表用于指定用户对特定列的权限。

字段:
- Host: 主机名或 IP 地址。
- Db: 数据库名。
- Table_name: 表名。
- Column_name: 列名。
- Timestamp: 权限最后更新的时间戳。
- Column_priv: 列权限（如 SELECT, INSERT, UPDATE 等）。

4：procs_priv表

procs_priv 表用于指定用户对特定存储过程或函数的权限。

字段:
- Host: 主机名或 IP 地址。
- Db: 数据库名。
- Proc: 存储过程或函数名。
- Proc_type: 类型（PROCEDURE 或 FUNCTION）。
- Grantor: 授予权限的用户。
- Proc_priv: 存储过程或函数权限（如 EXECUTE）。
- Timestamp: 权限最后更新的时间戳。

二：账户管理

1：登录和退出MySQL服务器

登录MySQL服务器:

1  -- 使用 mysql 命令行工具登录 MySQL 服务器
2  -- -u 参数指定用户名
3  -- -p 参数提示输入密码
4  mysql -u [username] -p

退出MySQL服务器:

1  -- 使用 EXIT 或 QUIT 命令退出 MySQL 命令行工具
2  EXIT;

2：新建普通用户

（1）使用CREATE USER或GRANT语句。

1  -- 创建一个新用户 newuser，允许从本地主机登录，并设置密码
2  CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
3
4  -- 授予新用户对 mydb 数据库的 SELECT 和 INSERT 权限
5  GRANT SELECT, INSERT ON mydb.* TO 'newuser'@'localhost';
6
7  -- 必须执行 FLUSH PRIVILEGES 以确保权限更改立即生效
8  FLUSH PRIVILEGES;

（2）直接操作MySQL授权表。

1  -- 直接向 mysql.user 表插入一条记录来创建用户
2  -- 注意这种方式不推荐，因为可能会导致权限问题
3  INSERT INTO mysql.user (Host, User, authentication_string) VALUES ('localhost', 'newuser', PASSWORD('password'));
4
5  -- 确保权限更改立即生效
6  FLUSH PRIVILEGES;

3：删除普通用户

（1）使用DROP USER语句删除用户

1  -- 删除用户 newuser，只允许从本地主机登录
2  DROP USER 'newuser'@'localhost';
3
4  -- 确保权限更改立即生效
5  FLUSH PRIVILEGES;

（2）使用DELETE语句删除用户

1  -- 从 mysql.user 表中删除用户 newuser，只允许从本地主机登录
2  -- 注意这种方式不推荐，因为可能会导致权限问题
3  DELETE FROM mysql.user WHERE User = 'newuser' AND Host = 'localhost';
4
5  -- 确保权限更改立即生效
6  FLUSH PRIVILEGES;

4:root用户修改自己的密码

（1）使用mysqladmin命令在命令行指定新密码

1  -- 使用 mysqladmin 命令修改 root 用户的密码
2  -- -u 参数指定用户名
3  -- password 子命令用于设置密码
4  mysqladmin -u root password 'new_password'

（2）修改mysql数据库的user表

1  -- 更新 mysql.user 表中 root 用户的密码
2  -- 注意这种方式不推荐，因为可能会导致权限问题
3  UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'root';
4
5  -- 确保权限更改立即生效
6  FLUSH PRIVILEGES;

（3）使用SET语句修改root用户的密码

1  -- 使用 SET PASSWORD 语句来修改 root 用户的密码
2  SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
3
4  -- 确保权限更改立即生效
5  FLUSH PRIVILEGES;

5：root用户修改普通用户密码

（1）使用SET语句修改普通用户的密码

1  -- 使用 SET PASSWORD 语句来修改普通用户 newuser 的密码
2  SET PASSWORD FOR 'newuser'@'localhost' = PASSWORD('new_password');
3
4  -- 确保权限更改立即生效
5  FLUSH PRIVILEGES;

（2）使用UPDATE语句修改普通用户的密码

1  -- 更新 mysql.user 表中 newuser 用户的密码
2  -- 注意这种方式不推荐，因为可能会导致权限问题
3  UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'newuser' AND Host = 'localhost';
4
5  -- 确保权限更改立即生效
6  FLUSH PRIVILEGES;

（3）使用GRANT语句修改普通用户密码

1  -- 使用 GRANT 语句来修改普通用户 newuser 的密码
2  -- 并赋予所有权限
3  GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' IDENTIFIED BY 'new_password' WITH GRANT OPTION;
4
5  -- 确保权限更改立即生效
6  FLUSH PRIVILEGES;

6：普通用户修改密码

使用 SET 语句修改自己的密码:

1  -- 使用 SET PASSWORD 语句来修改自己的密码
2  -- 注意普通用户需要有 ALTER USER 权限才能修改自己的密码
3  SET PASSWORD = PASSWORD('new_password');
4
5  -- 确保权限更改立即生效
6  FLUSH PRIVILEGES;

7：root用户密码丢失的解决办法

（1）使用--skip-grant-tables选项启动MySQL服务

1  -- 使用 mysqld 命令启动 MySQL 服务
2  -- --skip-grant-tables 参数使得 MySQL 不加载权限表
3  mysqld --skip-grant-tables &

（2）使用root用户登录和重新设置密码

1  -- 使用 mysql 命令行工具登录 MySQL 服务器
2  -- 此时不需要密码
3  mysql -u root
4
5  -- 使用 USE 语句切换到 mysql 数据库
6  USE mysql;
7
8  -- 更新 mysql.user 表中 root 用户的密码
9  -- 注意这里不使用 FLUSH PRIVILEGES，因为我们还没有启用权限检查
10  UPDATE user SET authentication_string = PASSWORD('new_password') WHERE User = 'root';
11
12  -- 退出 MySQL 命令行工具
13  EXIT;

（3）加载权限表

1  -- 重启 MySQL 服务以加载权限表
2  systemctl restart mysqld

如果你是通过手动启动的服务，请停止服务后再次启动：

1  -- 停止 mysqld 服务
2  kill %1
3  -- 重新启动 mysqld 服务
4  mysqld &

这里的 %1 是启动服务时的进程 ID。

请注意，直接修改 mysql 数据库中的权限表并不是推荐的做法，因为这可能会导致权限问题或者安全风险。在大多数情况下，使用 GRANT 和 REVOKE 语句来管理权限是更好的选择。此外，FLUSH PRIVILEGES 语句非常重要，因为它会确保所有的权限更改立即生效。

三：权限管理

1：MySQL的各种权限

MySQL 提供了多种权限类型，这些权限可以应用于不同的层级，从全局级别到特定的数据库、表、列甚至存储过程。以下是一些主要的权限类型及其用途：

全局权限:
- SELECT: 允许用户查询数据。
- INSERT: 允许用户向表中插入新行。
- UPDATE: 允许用户更新表中的数据。
- DELETE: 允许用户从表中删除数据。
- CREATE: 允许用户创建新的数据库或表。
- DROP: 允许用户删除现有的数据库或表。
- GRANT OPTION: 允许用户授予其他用户权限。
- RELOAD: 允许用户重新加载权限表和刷新缓存。
- SHUTDOWN: 允许用户关闭服务器。
- PROCESS: 允许用户显示或杀死不属于自己的进程。
- FILE: 允许用户导入和导出文件。
- SUPER: 允许用户修改全局变量和其他超级权限。
- REPLICATION CLIENT: 允许用户复制客户端权限。
- REPLICATION SLAVE: 允许用户复制从属权限。
- CREATE TEMPORARY TABLES: 允许用户创建临时表。
- LOCK TABLES: 允许用户锁定表。
- EXECUTE: 允许用户执行存储过程或函数。
- CREATE VIEW: 允许用户创建视图。
- SHOW VIEW: 允许用户查看视图定义。
- CREATE ROUTINE: 允许用户创建存储过程或函数。
- ALTER ROUTINE: 允许用户修改存储过程或函数。
- CREATE USER: 允许用户创建新的用户账户。
- EVENT: 允许用户创建事件。
- TRIGGER: 允许用户创建触发器。
- CREATE TABLESPACE: 允许用户创建表空间。
数据库级别权限:
- 上述所有权限也可以应用于特定的数据库。
表级别权限:
- 上述所有权限也可以应用于特定的表。
列级别权限:
- SELECT, INSERT, UPDATE, REFERENCES 权限可以应用于特定的列。
存储过程和函数级别权限:
- EXECUTE 权限可以应用于特定的存储过程或函数。

2：授权

全局权限:

1 -- 授予用户 johndoe 从任意主机登录的全部权限
2 GRANT ALL PRIVILEGES ON *.* TO 'johndoe'@'%';

数据库级别权限:

1 -- 授予用户 johndoe 对 mydb 数据库的所有权限
2 GRANT ALL PRIVILEGES ON mydb.* TO 'johndoe'@'%';

表级别权限:

1 -- 授予用户 johndoe 对 mydb 数据库中的 employees 表的 SELECT 和 INSERT 权限
2 GRANT SELECT, INSERT ON mydb.employees TO 'johndoe'@'%';

列级别权限:

1 -- 授予用户 johndoe 对 mydb 数据库中的 employees 表的 id 列的 SELECT 权限
2 GRANT SELECT ON mydb.employees(id) TO 'johndoe'@'%';

存储过程/函数级别权限:

1 -- 授予用户 johndoe 对 mydb 数据库中的 get_employee_info 存储过程的 EXECUTE 权限
2 GRANT EXECUTE ON PROCEDURE mydb.get_employee_info TO 'johndoe'@'%';

3：收回权限

全局权限:

1 -- 收回用户 johndoe 的所有权限
2 REVOKE ALL PRIVILEGES ON *.* FROM 'johndoe'@'%';

数据库级别权限:

1 -- 收回用户 johndoe 对 mydb 数据库的所有权限
2 REVOKE ALL PRIVILEGES ON mydb.* FROM 'johndoe'@'%';

表级别权限:

1 -- 收回用户 johndoe 对 mydb 数据库中的 employees 表的 SELECT 和 INSERT 权限
2 REVOKE SELECT, INSERT ON mydb.employees FROM 'johndoe'@'%';

列级别权限:

1 -- 收回用户 johndoe 对 mydb 数据库中的 employees 表的 id 列的 SELECT 权限
2 REVOKE SELECT ON mydb.employees(id) FROM 'johndoe'@'%';

存储过程/函数级别权限:

1 -- 收回用户 johndoe 对 mydb 数据库中的 get_employee_info 存储过程的 EXECUTE 权限
2 REVOKE EXECUTE ON PROCEDURE mydb.get_employee_info FROM 'johndoe'@'%';

4：查看权限

查看用户的权限可以通过查询 mysql 数据库中的权限表来完成。以下是一些示例：

查看全局权限:

1 -- 查看用户 johndoe 的全局权限
2 SELECT * FROM mysql.user WHERE User = 'johndoe' AND Host = '%';

查看数据库级别权限:

1  -- 查看用户 johndoe 对 mydb 数据库的权限
2  SELECT * FROM mysql.db WHERE User = 'johndoe' AND Host = '%' AND Db = 'mydb';

查看表级别权限:

1  -- 查看用户 johndoe 对 mydb 数据库中的 employees 表的权限
2  SELECT * FROM mysql.tables_priv WHERE User = 'johndoe' AND Host = '%' AND Db = 'mydb' AND Table_name = 'employees';

查看列级别权限:

1 -- 查看用户 johndoe 对 mydb 数据库中的 employees 表的 id 列的权限
2 SELECT * FROM mysql.columns_priv WHERE User = 'johndoe' AND Host = '%' AND Db = 'mydb' AND Table_name = 'employees' AND Column_name = 'id';

查看存储过程/函数级别权限:

1 -- 查看用户 johndoe 对 mydb 数据库中的 get_employee_info 存储过程的权限
2 SELECT * FROM mysql.procs_priv WHERE User = 'johndoe' AND Host = '%' AND Db = 'mydb' AND Proc = 'get_employee_info';

示例

假设我们有一个名为 mydb 的数据库，其中包含一个名为 employees 的表，以及一个名为 get_employee_info 的存储过程。下面是如何为用户 johndoe 授予权限、收回权限以及查看权限的示例。

授予权限:

1 -- 授予用户 johndoe 对 mydb 数据库中的 employees 表的 SELECT 和 INSERT 权限
2 GRANT SELECT, INSERT ON mydb.employees TO 'johndoe'@'%';
3
4 -- 授予用户 johndoe 对 mydb 数据库中的 get_employee_info 存储过程的 EXECUTE 权限
5 GRANT EXECUTE ON PROCEDURE mydb.get_employee_info TO 'johndoe'@'%';

收回权限:

1 -- 收回用户 johndoe 对 mydb 数据库中的 employees 表的 INSERT 权限
2 REVOKE INSERT ON mydb.employees FROM 'johndoe'@'%';
3
4 -- 收回用户 johndoe 对 mydb 数据库中的 get_employee_info 存储过程的 EXECUTE 权限
5 REVOKE EXECUTE ON PROCEDURE mydb.get_employee_info FROM 'johndoe'@'%';

查看权限:

1 -- 查看用户 johndoe 对 mydb 数据库中的 employees 表的权限
2 SELECT * FROM mysql.tables_priv WHERE User = 'johndoe' AND Host = '%' AND Db = 'mydb' AND Table_name = 'employees';
3
4 -- 查看用户 johndoe 对 mydb 数据库中的 get_employee_info 存储过程的权限
5 SELECT * FROM mysql.procs_priv WHERE User = 'johndoe' AND Host = '%' AND Db = 'mydb' AND Proc = 'get_employee_info';

请注意，在进行任何权限更改后，都需要执行 FLUSH PRIVILEGES 命令来确保更改立即生效。