Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 单表授权后，Navicat没有显示该表所在的数据库
【TiDB Usage Environment】Production Environment
【TiDB Version】7.1.2
【Reproduction Path】
grant select on db_1.test to ‘user’@‘%’;
【Encountered Problem: Problem Phenomenon and Impact】
The db_1 database is not displayed in Navicat, but executing show databases shows db_1. Navicat retrieves database information through the SQL query SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA. Executing this SQL query does not return db_1.
This might be because Navicat uses the SQL query SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA to retrieve database information, but this query does not return the database db_1 that you have been granted access to.
This is because in TiDB, granting permissions only affects the user’s permissions on tables and does not change the database metadata information. Therefore, even if you have granted SELECT permission on the table db_1.test, the information in the database metadata still does not include db_1.
Although Navicat may not display db_1 when retrieving database information, you can still view the database list by executing the SHOW DATABASES command, which should include db_1.
If you need to display the database db_1 in Navicat, you can try reconnecting to TiDB or executing the SHOW DATABASES command in Navicat to refresh the database list.
My cousin is becoming more and more professional.
I have encountered this situation with DBeaver before. You can refresh the connection or reconnect, and it should work.
It doesn’t work in Navicat mainly because there is no corresponding value in information_schema.SCHEMATA. This phenomenon does not exist in MySQL either. I think it’s a bug.
I roughly understand what you mean. If you only grant permissions to a single table, you won’t be able to see the database information in SCHEMATA. In TiDB, it seems that you need whole database permissions to see the database name in SCHEMATA. I just tested it, and it does seem to be somewhat inconsistent with MySQL. I’m not sure if this is the expected behavior.
Since the schema is not displayed, does this mean that the statement to grant query permissions was unsuccessful?
No, the authorization was successful. Querying data and using “show databases” are both fine. It’s just that the corresponding database name cannot be seen in information_schema.SCHEMATA. The authentication here seems to be different from MySQL.
That could indeed be a bug.
You need to set this up in Navicat, manually add the database you authorized, and then refresh it to see the database with the single table you authorized.
This method works, and I am currently doing the same, but it is still inconvenient.
You can try switching to DBeaver. I switched from Navicat to DBeaver myself.
Try both DBeaver and DataGrip. Currently, Navicat can only be used this way.
|
username: Zealot
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: