[MDEV-13398] Can't distinguish between DROP DATABASE and DROP TABLE permissions on a database Created: 2017-07-28  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 5.5, 10.0, 10.1.25, 10.2.6, 10.3
Fix Version/s: 10.4

Type: Bug Priority: Trivial
Reporter: Hartmut Holzgraefe Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 0
Labels: upstream

Issue Links:
Duplicate
duplicates MDEV-15491 Allow wildcards in table names of GRA... Stalled

 Description   

Feature request:

GRANT DROP ON TABLE db_name.* does not only give a user the privilege to drop any table within the database db_name, but also the database itself.

There is no way to just give permission to drop any table, but not the database itself.

Not sure how to actually solve this in a backwards compatible way, adding an extra ON DATABASE object_type wouldn't really work out as this would require to change the current behavior of ON TABLE.

Maybe an extra DROP_TABLE privilege would work out best. So

GRANT DROP ON TABLE db_name.*

would give DROP permissions on both tables within the database and the database itself, while

GRANT DROP_TABLE ON TABLE db_name.*

would only allow to drop tables, but not the database itself.

When specifying an explicit table level grant

GRANT DROP_TABLE ON TABLE db_name.tab_name

on the other hand DROP and DROP_TABLE would be synonyms for the same privilege.

 Comments   
Comment by Elena Stepanova [ 2017-08-09 ]

I think the fact that GRANT DROP ON TABLE db_name.* is accepted at all is actually an upstream bug. It's not documented (in MySQL manual), and it's not consistent with other GRANT commands where object_type clause is present.

The manual says:

The object_type clause, if present, should be specified as TABLE, FUNCTION, or PROCEDURE when the following object is a table, a stored function, or a stored procedure.

So, it does not assume wild cards. Other variants of such GRANTs, indeed, don't allow wild cards:

MariaDB [test]> GRANT EXECUTE ON FUNCTION db.* TO foo@localhost;
 
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used
 
MariaDB [test]> 
MariaDB [test]> GRANT EXECUTE ON FUNCTION db.f TO foo@localhost;
 
Query OK, 0 rows affected (0.00 sec)

For DROP ON TABLE, while it is accepted, the TABLE clause is simply ignored.

MariaDB [test]> GRANT DROP ON TABLE db.* TO foo@localhost;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> SHOW GRANTS FOR foo@localhost;
 
+---------------------------------------------------------+
 
| Grants for foo@localhost                                |
 
+---------------------------------------------------------+
 
| GRANT USAGE ON *.* TO 'foo'@'localhost'                 |
 
| GRANT DROP ON `db`.* TO 'foo'@'localhost'               |
 
| GRANT EXECUTE ON FUNCTION `db`.`f` TO 'foo'@'localhost' |
 
+---------------------------------------------------------+
 
3 rows in set (0.00 sec)

So, since it has never really been a documented behavior, maybe it's not such a disaster to break compatibility and fix it any way we want. I'll leave it to serg to decide what's best here, and change the fix version accordingly.

Comment by Nick Bolton [ 2017-09-07 ]

Note: DROP Drop_priv
applies to Databases, tables AND views
(but not other types of objects such as indexes, functions etc.)

Also - why would a user not be given a DROP grant on a new table or view they create?
It seems incorrect that a user can create objects but be unable to remove that object by default.

Drop sequence / function / procedure / table / index etc. all seem to work in inconsistent ways from each other r.e. what permissiodn gives the DROP right.

Comment by Hartmut Holzgraefe [ 2017-09-07 ]

"Also - why would a user not be given a DROP grant on a new table or view they create?"

We don't know the creator / owner of a table ... but that's a completely different story

Comment by Nick Bolton [ 2017-09-07 ]

Rather than creator - I should have said "schema owner"...
You DO know that.

Generated at Thu Feb 08 08:05:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.