权限控制

TiDBer_sX3j5LdU · 2023 年12 月 27 日 01:14

【 TiDB 使用环境】生产环境
【 TiDB 版本】
需求：假设AB用户都在test库下创建表，A新建的表，B只能select。或者B 对A 创建的表没有任何权限。

tidb菜鸟一只 · 2023 年12 月 27 日 01:25

tidb的权限跟mysql是一样的，基于db和table，而不是user

zhanggame1 · 2023 年12 月 27 日 02:14

新建一个表授权一次

forever · 2023 年12 月 27 日 02:24

这种实现不了

Inkjade · 2023 年12 月 27 日 02:30

你基于什么样的业务场景需要该操作。你是想实现多个租户隔离吗。
基于数据库不是更好吗？

kelvin · 2023 年12 月 27 日 02:32

真要做那就只能新建一个，授权一次。既然要分那么清楚为何不分库呢？

春风十里 · 2023 年12 月 27 日 03:16

测试了一下，也可以实现

mysql> create database test;
Query OK, 0 rows affected (0.22 sec)

mysql> create user ‘A’@‘192.168.%’ identified by ‘yx123456’;
Query OK, 0 rows affected (0.04 sec)

mysql> create user ‘B’@‘192.168.%’ identified by ‘yx123456’;
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> grant all privileges on test.* to ‘A’@‘192.168.%’;
Query OK, 0 rows affected (0.05 sec)

mysql> grant create on test.* to ‘B’@‘192.168.%’;
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> exit
Bye
[root@zabbix_server ~]#
[root@zabbix_server ~]# mysql -uA -pyx123456 -P4000 -h192.168.117.15
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 952107038
Server version: 8.0.11-TiDB-v7.5.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 8.0 compatible

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> use test;
Database changed
mysql> create table A_t1 (tid int);
Query OK, 0 rows affected (0.17 sec)

mysql> insert into A_t1 values (1);
Query OK, 1 row affected (0.02 sec)

mysql> exit
Bye
[root@zabbix_server ~]# mysql -uB -pyx123456 -P4000 -h192.168.117.15
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 952107040
Server version: 8.0.11-TiDB-v7.5.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 8.0 compatible

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> use test;
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> show tables;
±---------------+
| Tables_in_test |
±---------------+
| A_t1 |
±---------------+
1 row in set (0.00 sec)

mysql> select * from A_t1;
ERROR 1142 (42000): SELECT command denied to user ‘B’@‘192.168.%’ for table ‘a_t1’
mysql> create table B_t1 (tid int);
Query OK, 0 rows affected (0.15 sec)

mysql> insert into B_t1 values (1);
ERROR 1142 (42000): INSERT command denied to user ‘B’@‘192.168.%’ for table ‘b_t1’
mysql> exit
Bye
[root@zabbix_server ~]# mylogin
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 952107042
Server version: 8.0.11-TiDB-v7.5.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 8.0 compatible

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> grant insert ,update ,delete ,select on test.B_t1 to B;
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql> grant insert ,update ,delete ,select on test.B_t1 to ‘B’@‘192.168.%’;
Query OK, 0 rows affected (0.04 sec)

mysql> exit
Bye
[root@zabbix_server ~]# mysql -uB -pyx123456 -P4000 -h192.168.117.15
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 952107044
Server version: 8.0.11-TiDB-v7.5.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 8.0 compatible

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> use test;
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> show tables;
±---------------+
| Tables_in_test |
±---------------+
| A_t1 |
| B_t1 |
±---------------+
2 rows in set (0.01 sec)

mysql> select * FROM A_t1;
ERROR 1142 (42000): SELECT command denied to user ‘B’@‘192.168.%’ for table ‘a_t1’
mysql> select * from B_t1;
Empty set (0.00 sec)
mysql> insert into A_t1 values (1);
ERROR 1142 (42000): INSERT command denied to user ‘B’@‘172.17.%’ for table ‘a_t1’
mysql> delete from A_t1;
ERROR 1142 (42000): DELETE command denied to user ‘B’@‘172.17.%’ for table ‘a_t1’
mysql> update A_t1 set tid=2 ;
ERROR 1142 (42000): SELECT command denied to user ‘B’@‘172.17.%’ for table ‘a_t1’
mysql> insert into B_t1 values (1);
Query OK, 1 row affected (0.02 sec)

mysql> update B_T1 set tid=2 ;
Query OK, 1 row affected (0.12 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> delete from B_t1;
Query OK, 1 row affected (0.02 sec)

mysql> select * from B_t1;
Empty set (0.00 sec)

mysql> exit
Bye

TiDBer_sX3j5LdU · 2023 年12 月 27 日 05:17

因为多人使用库是同一个，就想在表上进行限制。。一张一张表授权又太麻烦了。。

TiDBer_sX3j5LdU · 2023 年12 月 27 日 05:22

grant insert ,update ,delete ,select on test.B_t1 to B;
您还是对单表进行了授权的，，

春风十里 · 2023 年12 月 27 日 09:54

是的，是需要对B单独授权的，如果想要B有test库的所有表查询权限可以 grant select on test.* to 'B‘@’192.168.%’；
如果表不是经常增加，可以写好脚本批量授权，也可以使用角色role，这样用户多的话可能操作role更方便

Jellybean · 2023 年12 月 27 日 09:57

你想要的功能特性，应该是基于角色的访问控制，基于它可以很灵活实现各个访问权限控制。看看是否符合你的需求场景。
对于同一个数据库，可以灵活设置开发角色、运维角色、QA角色等不同权限。

具体可以参考：
https://docs.pingcap.com/zh/tidb/stable/role-based-access-control#基于角色的访问控制

andone · 2023 年12 月 27 日 10:02

tidb用户权限参考mysql

TIDB-Learner · 2023 年12 月 27 日 10:06

使用角色可以实现吧

ShawnYan · 2023 年12 月 28 日 00:56

大家都痛苦，直接创建两个库就都解决了 test_A 和 test_B

dba远航 · 2023 年12 月 29 日 01:03

你只能每个进行单独授权

system · 2024 年2 月 27 日 01:03

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。