How to Revoke DROP DATABASE Privileges but Retain DROP TABLE Privileges in TiDB?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TiDB数据库如何回收DROP DATABASE的权限,但是保留DROP TABLE的权限?

| username: FutureDB

[Test Environment for TiDB] Testing
[TiDB Version] V6.5.4
[Reproduction Path] What operations were performed that led to the issue
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

| username: 小龙虾爱大龙虾 | Original post link

Isn’t deleting all the tables the same as dropping the database? If you really want to achieve this, you can only grant permissions to individual tables. Refer to: 权限管理 | PingCAP 文档中心

| username: FutureDB | Original post link

Currently, it has been found that executing “revoke drop on test.* from ‘test_user’@‘%’” will also revoke the drop table permission for tables within the test database.

| username: FutureDB | Original post link

The database cannot be deleted, but some tables need to be dropped and recreated, so DROP TABLE permission is required.

| username: FutureDB | Original post link

Granting permissions to individual tables is too cumbersome. If there are thousands of tables, it’s not realistic to grant permissions one by one.

| username: 江湖故人 | Original post link

You can use dynamic SQL to generate grant statements.

| username: 路在何chu | Original post link

This doesn’t seem to work, it can only recycle drop.

| username: 啦啦啦啦啦 | Original post link

It’s better not to control this from the database itself. Use an online review tool instead. Directly connecting to the database is still dangerous.

| username: 江湖故人 | Original post link

Since it is a rebuild, why can’t we alter it and have to drop and recreate it?

| username: FutureDB | Original post link

Yes, the granularity control of the drop permission is too broad, not fine and flexible enough.

| username: FutureDB | Original post link

For example, I have a table that was initially set up as a clustered table with a random primary key. Now, I want to remove the random primary key, but it cannot be done with an alter command.

| username: FutureDB | Original post link

This should be controlled by the database, rather than implemented by an additional third-party tool.

| username: FutureDB | Original post link

Sorry for the trouble.

| username: 江湖故人 | Original post link

Try this:

select concat('grant drop on test.',table_name,' to \'test_user\'\@\'%\';') 
from information_schema.tables
where table_schema='test' and table_type='BASE TABLE';
| username: FutureDB | Original post link

There is no problem with this individually, but currently, the granularity of TiDB’s DROP permission control is too large. If DROP permission is granted, it means both DROP DATABASE and DROP TABLE are allowed, which could lead to a catastrophic situation. However, if DROP permission is revoked, both DROP DATABASE and DROP TABLE are disallowed.

| username: wangccsy | Original post link

You can revoke it based on the permissions.

| username: FutureDB | Original post link

Currently, there is no separate operation to revoke the DROP DATABASE privilege.

| username: 这里介绍不了我 | Original post link

Can’t this problem be solved by strengthening the process?

| username: linnana | Original post link

Only administrators should have the permission to drop database [table].

| username: linnana | Original post link

The permission to drop should not be granted during business initialization.