Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: tidb怎么创建一个用户只允许对指定库里面的多张表有查询权限？
[TiDB Usage Environment] Online
[TiDB Version] 5.4.1
[Encountered Problem] When creating a user in TiDB with query permissions only for a specific table in a specified database, the user cannot see the database and table when viewed through Navicat. However, the database and table can be seen when connected via the command line. My current requirement is: to create a user with query permissions for 10 specific tables in a specified database, and no permissions for other tables. How should I create this user?
The command to grant privileges, and what are the user’s privileges after granting? What are the privileges of the user connected via the command line (show grants;)?
After connecting via the command line, I can see the databases and tables, but I can’t see them when connecting with Navicat.
Hello! I need to create a user with view permissions for multiple tables. How can I do this using SQL statements? Currently, I can only grant permissions for one table.
Can you see the content with select if you can see the table in the command line?
Just add this permission:
grant references on test.* to siteadmin_zy00001@'%';
Thank you for the explanation! However, after using grant select on siteadmin_zy00001.game to test@'%'; grant references on siteadmin_zy00001.* to test@'%';, it worked. But now I want to set query permissions for multiple tables. How should the SQL be written? There are over 100 tables under the siteadmin_zy00001 database, and I want to set view-only permissions for 10 tables.
Sure, thank you! I now need to set multiple tables to have read-only permissions.
Thank you! I just tested it and it works. It seems that permissions need to be set table by table, and it doesn’t seem possible to set query permissions for multiple tables at once.
This way, there are multiple tables.
Yes! Thank you very much.
Yes, just 10 tables, writing 10 grant statements will do.
Is the syntax grant references on siteadmin_zy00001.* to test@’%’; unique to TiDB? What is its function? I usually use grant all privileges to give a user read and write permissions on a database.
Granting all privileges is too excessive and not conducive to permission control. Grant ref is supported in MySQL, you can refer to the MySQL official documentation.
The official statement is that creating foreign keys is allowed. 
Is there a relationship between queries and references? That’s rare.
It seems I’m not the only one who hasn’t used this syntax 
This topic will be automatically closed 60 days after the last reply. No new replies are allowed.
|
username: TiDBer_5GvAkLi0
|
username: 
|
username: 
|
username: 
|
username: 
|
username: