Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 权限控制
|
username: TiDBer_sX3j5LdU
[TiDB Usage Environment] Production Environment
[TiDB Version]
Requirement: Assume users A and B both create tables in the test database. For tables created by A, B should only have select permissions, or B should have no permissions on tables created by A.
TiDB’s permissions are the same as MySQL’s, based on db and table, not user.
What kind of business scenario requires this operation? Are you trying to achieve multi-tenant isolation? Wouldn’t it be better to base it on the database?
If you really want to do it, you can only create a new one and authorize it once. Since you want to separate it so clearly, why not separate the databases?
Tested it, and it works.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 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
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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> show databases;
+--------------------+
| Database |
+--------------------+
| INFORMATION_SCHEMA |
| test |
+--------------------+
2 rows in set (0.00 sec)
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
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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> show databases;
+--------------------+
| Database |
+--------------------+
| INFORMATION_SCHEMA |
| test |
+--------------------+
2 rows in set (0.00 sec)
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
Because multiple people are using the same database, I want to set restrictions on the tables… Granting permissions table by table is too troublesome…
Yes, B needs to be granted separately. If you want B to have query permissions for all tables in the test database, you can use grant select on test.* to 'B'@'192.168.%';. If tables are not frequently added, you can write a script for batch authorization, or you can use roles. If there are many users, managing roles might be more convenient.
The feature you want should be role-based access control, which can flexibly implement various access control permissions. See if it meets your needs.
For the same database, you can flexibly set different permissions for development roles, operations roles, QA roles, etc.
For more details, you can refer to:
Everyone is suffering, just create two databases to solve it: test_A and test_B.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: