Database Authorization Issues

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

Original topic: 数据库授权问题

| username: 随缘天空

I want to grant permissions to a specific user. For a user with all permissions, such as admin, I want to revoke their permission to delete databases. So, I used the command REVOKE DROP ON . FROM ‘admin’@‘%’; to revoke the delete database permission, but now the corresponding tables cannot be deleted either. I would like to ask if it is possible to revoke only the delete database permission while retaining all permissions for tables and data.

| username: ShawnYan | Original post link

Just grant the permission to drop a specific table.

| username: 大飞哥online | Original post link

Give the corresponding permissions to delete the table.

| username: zhanggame1 | Original post link

Grant delete permissions individually by table name.

| username: Fly-bird | Original post link

First, remove all permissions, then grant insert, delete, update, and select permissions according to database name and table name.

| username: Soysauce520 | Original post link

Construct a SQL statement to grant permissions to a specific table.

| username: 普罗米修斯 | Original post link

Grant individual permissions to the table.

| username: 随缘天空 | Original post link

Does that mean the database can still be deleted?

| username: 随缘天空 | Original post link

It seems that tables and databases are synchronized. When I revoke the delete permission for the database, the delete permission for the tables also disappears.

| username: 随缘天空 | Original post link

It seems that the permissions for the database and the table are the same, making it impossible to have no delete permission for the database while still retaining delete permission for the table.

| username: 随缘天空 | Original post link

This is too troublesome, and we don’t know if the table will change dynamically.

| username: 随缘天空 | Original post link

How do I do it? My tables always remain consistent when I authorize.

| username: 随缘天空 | Original post link

It is estimated that the result will be the same, and the table is dynamically changing.

| username: ajin0514 | Original post link

So how do we solve it?

| username: 随缘天空 | Original post link

It is impossible to achieve complete synchronization. Either revoke the delete permissions for databases and tables, or grant specific users full permissions to operate their own databases while prohibiting them from operating other databases.

| username: zhanggame1 | Original post link

There’s no good solution; fine-grained permissions are just very troublesome.

| username: ajin0514 | Original post link

Learn a bit.

| username: 随缘天空 | Original post link

Yes, you can only control the general permissions, but not so precisely.

| username: 随缘天空 | Original post link

A piece of learning

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.