【MySQL】Linux下MySQL的目录结构、用户、权限与角色

Decade0712

于 2024-06-30 17:21:38 发布

阅读量1.1k

点赞数 22

分类专栏： Mysql 文章标签： mysql linux 数据库 sql 后端

本文链接：https://blog.csdn.net/decade0712/article/details/139902217

版权

Mysql 专栏收录该内容

18 篇文章 2 订阅

订阅专栏

一、Linux下MySQL的目录结构

1、MySQL相关目录

数据库文件存放路径：/var/lib/mysql
数据库命令存放路径：/user/bin和/user/sbin
配置文件目录：/usr/share/mysql-8.0/、/usr/share/mysql/和/etc/my.cnf

2、假设我们创建了一个数据库dbtest1，在这个数据库下创建了表test1

如果表使用InnoDB存储结构，在/var/lib/mysql/dbtest1下会生成如下文件
- 存储表结构（5.7版本存在）：test1.frm
- 存放此数据库的字符集、比较规则等配置（5.7版本存在）：db.opt
- 数据信息和索引信息存放：
  - 如果使用系统表空间存储模式：表数据会存放到/var/lib/mysql/ibdata1中
  - 如果使用独立表空间存储模式：/var/lib/mysql/dbtest1下会生成test1.idb（8.0版本没有.frm和.opt文件，独立表空间存储模式下，表结构、数据以及db.opt存放的配置信息都会存放到.idb文件中）
如果表使用MyISAM存储结构，在/var/lib/mysql/dbtest1下会生成如下文件
- 存储数据信息（如果采用独立表空间的存储模式）：test1.MYD（MyData）
- 存放索引信息文件：test1.MYI（MyIndex）
- 存储表结构：test1.frm（5.7版本）或test_xxx.sdi（8.0版本）
创建视图：MySQLl5.7会创建一个xxx_view.frm存放视图结构（8.0版本还没找到在哪，暂时留一个疑问）

决定使用系统表空间还是独立表空间，由参数innodb_file_per_table决定，可以在my.cnf下配置

innodb_file_per_table=0：使用系统表空间
innodb_file_per_table=1：使用独立表空间

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql>

这里的ON，表示使用独立表空间

3、可以使用命令ibd2sdi --dump-file=test1.txt test1.ibd查看存放表数据的文件test1.ibd

二、用户管理

1、查看当前系统中存在的用户
使用mysql这个database，执行select host,user from user;语句

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql>
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
4 rows in set (0.00 sec)

mysql>

2、创建用户CREATE USER

mysql> create user 'test001' identified by 'test001Pwd';
Query OK, 0 rows affected (0.01 sec)

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| %         | test001          |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
5 rows in set (0.00 sec)

mysql>
mysql> create user 'test001'@'localhost' identified by 'test001Pwd';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| %         | test001          |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | test001          |
+-----------+------------------+
6 rows in set (0.00 sec)

mysql>

我们发现，同一个用户test001，可以被创建多次
因为user表中，host+user字段是联合主键

联合主键：指的是把两个列看成是一个整体，这个整体是不为空，唯一，不重复

3、修改用户名（注意flush privileges）

mysql> create user 'test002'@'localhost' identified by 'test002Pwd';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| %         | test001          |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | test001          |
| localhost | test002          |
+-----------+------------------+
7 rows in set (0.00 sec)

mysql>
mysql> update user set user = 'test003' where user = 'test002' and host = 'localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| %         | test001          |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | test001          |
| localhost | test003          |
+-----------+------------------+
7 rows in set (0.00 sec)

mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>

我们新建了一个用户test002，修改用户名为test003后，
如果未执行flush privileges;，那么当我们尝试用test003登录，会发现失败
在这里插入图片描述

4、删除用户

# 方式一：使用DROP
# 默认删除host为%的用户
DROP USER user1,user2...;
# 删除指定host的用户
DROP USER 'test001'@'localhost';

# 方式二：使用delete（不推荐）
DELETE from mysql.user where user = 'test001' and host = 'localhost';
flush privileges;

5、设置用户密码

修改当前用户的密码，我们之前把test002改为test003，我们尝试改一下test003的密码
- 方式一：使用alter user user() identified by '新密码';
- 方式二：使用SET password = '新密码';

使用alter user user() identified by '新密码';把密码从test002Pwd改为test003

[root@myLinux2 ~]# mysql -utest003 -ptest002Pwd
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql>
mysql> alter user user() identified by 'test003';
Query OK, 0 rows affected (0.01 sec)

mysql> exit;
Bye
[root@myLinux2 ~]# mysql -utest003 -ptest002Pwd
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test003'@'localhost' (using password: YES)
[root@myLinux2 ~]#
[root@myLinux2 ~]# mysql -utest003 -ptest003
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.36 MySQL Community Server - GPL
...

使用SET password = '新密码';把密码从test003改为test003Pwd

mysql> SET password = 'test003Pwd';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@myLinux2 ~]# mysql -utest003 -ptest003
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test003'@'localhost' (using password: YES)
[root@myLinux2 ~]#
[root@myLinux2 ~]# mysql -utest003 -ptest003Pwd
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.36 MySQL Community Server - GPL
...
mysql>

修改其他用户的密码：需要是root用户或者被root用户赋予修改密码的权限
- 方式一：使用alter user 'user'@'host' identified by '新密码';
- 方式二：使用SET PASSWORD FOR 'user'@'host' = '新密码';

使用alter user 'user'@'host' identified by '新密码';修改test003的密码为test003

mysql> ALTER USER 'test003'@'localhost' identified by 'test003';
Query OK, 0 rows affected (0.01 sec)

在这里插入图片描述
使用SET PASSWORD FOR 'user'@'host' = '新密码';修改test003的密码为test003Pwd

mysql> SET PASSWORD FOR 'test003'@'localhost' = 'test003Pwd';
Query OK, 0 rows affected (0.00 sec)

在这里插入图片描述

三、权限管理

1、授权的原则

最小权限原则：只给予用户他职权范围内的最小权限，比如用户只能查询，就只给他select权限
创建用户的时候，限制用户的登陆主机，限制他的网段或IP
每个用户的密码都要满足密码复杂度
定期清理用户账号和权限

2、查看用户权限
使用show grants;或者show grants for 'test001'@'localhost';
在这里插入图片描述

3、授权方式（需要是root用户）

方式一：直接给用户授权：grant 权限1,权限2 on database名称.表名 to '用户名'@'host';

首先我们使用show grants;查看一下当前用户的权限
在这里插入图片描述
如果想给用户授权某个数据下的所有表的权限，那么database名称.表名需要换成database名称.*

mysql> grant select,update on dbtest1.* to 'test003'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql>

我们测试一下test003的权限，发现确实是只有select和update权限

mysql> show grants;
+--------------------------------------------------------------+
| Grants for test003@localhost                                 |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test003`@`localhost`                  |
| GRANT SELECT, UPDATE ON `dbtest1`.* TO `test003`@`localhost` |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| dbtest1            |
| information_schema |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

mysql>
mysql> use dbtest1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql> show tables;
+-------------------+
| Tables_in_dbtest1 |
+-------------------+
| test1             |
| test1_view        |
+-------------------+
2 rows in set (0.00 sec)

mysql>
mysql> select * from test1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | decade |
|    2 | 十年   |
+------+--------+
2 rows in set (0.00 sec)

mysql>
mysql> update test1 set name = 'decadeNew' where id = '1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | decadeNew |
|    2 | 十年      |
+------+-----------+
2 rows in set (0.00 sec)

mysql>
mysql> delete from test1 where id = '1';
ERROR 1142 (42000): DELETE command denied to user 'test003'@'localhost' for table 'test1'
mysql>

然后我们再把delete权限也给他加上

mysql> grant delete on dbtest1.* to 'test003'@'localhost';
Query OK, 0 rows affected (0.00 sec)

重新登录之后，删除权限生效

mysql> delete from test1 where id = '1';
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+------+--------+
| id   | name   |
+------+--------+
|    2 | 十年   |
+------+--------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> show grants;
+----------------------------------------------------------------------+
| Grants for test003@localhost                                         |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test003`@`localhost`                          |
| GRANT SELECT, UPDATE, DELETE ON `dbtest1`.* TO `test003`@`localhost` |
+----------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

如果想把所有表下的所有的权限都授予某个用户，那么可以使用
GRANT all privileges ON *.* TO '用户名'@'host';

方式二：把权限赋予给角色，然后把用户和角色进行绑定

4、权限的横向分组和纵向分组

横向分组：可以接触到的数据范围，例如数据库、表
纵向分组：对接触到的数据能访问到什么程度，如查看、更新、删除、插入等

5、收回权限（注意是FROM不是TO）
使用REVOKE 权限1,权限2 ON database名称.表名 FROM '用户名'@'host';

mysql> revoke select on dbtest1.test1 from 'test001'@'localhost';
Query OK, 0 rows affected (0.00 sec)

重新登陆后，发现权限已失效

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql>
mysql> show grants;
+---------------------------------------------+
| Grants for test001@localhost                |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `test001`@`localhost` |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT CURRENT_USER;
+-------------------+
| CURRENT_USER      |
+-------------------+
| test001@localhost |
+-------------------+
1 row in set (0.00 sec)

mysql>

如果回收某个用户所有表下的所有的权限，那么可以使用
REVOKE all privileges ON *.* FROM '用户名'@'host';

6、业务开发
实际业务开发时，访问数据库不应该使用root用户，可以单独创建一个业务账号，进行相应授权
防止root密码泄露之后，数据库处于一个危险状态

7、权限表（根据联合主键确定数据的唯一性）

user表：关键字段user、host、authentication_string（密码），决定用户能否正常登录
db表：记录用户对某个database拥有的权限信息
tables_priv：记录用户对某个表拥有的权限信息
columns_priv：记录用户对某个字段拥有的权限信息
procs_priv：记录用户对某个存储过程和函数拥有的权限信息

在MySQL启动时，服务器将这些表中的权限信息内容读入内存

8、MySQL访问控制

连接核实阶段：将当前用户信息和user表中的user、host、authentication_string（密码）进行匹配，决定用户能否正常登录
请求核实阶段：从user表到db表，然后一层一层向下检查当前用户的权限，确定是否满足当前请求所需权限，如果找到匹配结果，操作被允许，否则就就返回错误信息

四、角色管理

1、在MySQL中，角色是权限的集合，引入角色之后，方便管理权限相同的用户

2、创建角色

mysql> create user 'test002' identified by 'test002Pwd';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> create role 'manager'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT select,update on dbtest1.* to 'manager'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> show grants for 'manager'@'%';
+------------------------------------------------------+
| Grants for manager@%                                 |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%`                  |
| GRANT SELECT, UPDATE ON `dbtest1`.* TO `manager`@`%` |
+------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> revoke update on dbtest1.* from 'manager'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> show grants for 'manager'@'%';
+----------------------------------------------+
| Grants for manager@%                         |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%`          |
| GRANT SELECT ON `dbtest1`.* TO `manager`@`%` |
+----------------------------------------------+
2 rows in set (0.00 sec)

mysql>

上面我们测试了一下创建角色、赋予权限、查询角色权限和回收权限

同理，我们也可以使用下面的语句，将角色与所有权限进行绑定和解绑
GRANT all privileges on *.* to 'manager'@'%';
REVOKE all privileges on *.* FROM 'manager'@'%';

删除角色使用DROP ROLE '用户名'@'host'；

mysql> create role 'employee'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'employee'@'%';
+--------------------------------------+
| Grants for employee@%                |
+--------------------------------------+
| GRANT USAGE ON *.* TO `employee`@`%` |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> drop role 'employee'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> show grants for 'employee'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'employee' on host '%'
mysql>

3、给用户和角色进行绑定

mysql> create user 'test002' identified by 'test002Pwd';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show grants for 'test002'@'%';
+-------------------------------------+
| Grants for test002@%                |
+-------------------------------------+
| GRANT USAGE ON *.* TO `test002`@`%` |
+-------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> grant 'manager'@'%' to 'test002'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> show grants for 'test002'@'%';
+--------------------------------------+
| Grants for test002@%                 |
+--------------------------------------+
| GRANT USAGE ON *.* TO `test002`@`%`  |
| GRANT `manager`@`%` TO `test002`@`%` |
+--------------------------------------+
2 rows in set (0.00 sec)

mysql>

4、激活角色
注意：用户绑定之后还需要激活角色，权限才能生效

方式一：使用set default role激活

执行前使用select CURRENT_ROLE();查询当前用户的角色信息

mysql> select CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

mysql>
mysql> set default role 'manager'@'%' to 'test002'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql>

执行完成之后，用户重新登录，发现已拥有manager角色的权限

mysql> select CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `manager`@`%`  |
+----------------+
1 row in set (0.00 sec)

mysql> select * from dbtest1.test1;
+------+--------+
| id   | name   |
+------+--------+
|    2 | 十年   |
+------+--------+
1 row in set (0.00 sec)

mysql>

方式二：修改系统变量（设置到配置文件里也可以）
这样一来，后续再进行角色绑定，就不用手动激活了，只需要重新登录即可

mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF   |
+-----------------------------+-------+
1 row in set (0.00 sec)

mysql>
mysql> SET global activate_all_roles_on_login=ON;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | ON    |
+-----------------------------+-------+
1 row in set (0.01 sec)

mysql>

5、撤销用户角色
使用revoke '角色'@'host' from '用户'@'host';

mysql> revoke 'manager'@'%' from 'test002'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql>

这样一来，test002的角色就被撤销了
在这里插入图片描述

如有错误，欢迎指正！！！