The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

revoking drop privileges

hello - i am trying to get "revoke drop" working and not having any success. i am able to do "REVOKE DROP" but it does not seem to have any effect :

from root:

mysql --host=localhost --user=root --password=ROOTPASSWORD myDatabase ;

REVOKE DROP ON *.*    FROM   'myUser'@'localhost' ;
flush privileges ;

mysql --host=localhost --user=myUser --password=XXXX myDatabase ;

CREATE TABLE  testTest (testCol text  not null )  ;
INSERT INTO testTest VALUES  ( 'xxxxxxxxxxxxxxx') ;
DROP TABLE  testTest ;
Query OK, 0 rows affected (2.47 sec)

any suggestions what i may be doing incorrectly??

Server version: 10.2.24-MariaDB MariaDB Server

Linux 3.10.0-957.12.2.el7.x86_64 #1 SMP Tue May 14 21:24:32 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

Answer Answered by Anel Husakovic in this comment.

Hi,

you didn't show output of show grant for myUser@localhost, but let's look in the example.

Consider this example where we have some user mu@localhost,which in beginning has no privileges:

MariaDB [(none)]> show grants for mu@localhost;
+----------------------------------------+
| Grants for mu@localhost                |
+----------------------------------------+
| GRANT USAGE ON *.* TO 'mu'@'localhost' |
+----------------------------------------+
1 row in set (0.00 sec)

Now if you want to connect to some specific database myTest you can't

./client/mysql -u mu mytest
ERROR 1044 (42000): Access denied for user 'mu'@'localhost' to database 'mytest'

But, by default you can connect to database test which is according to my opinion out of scope * and there you can do what ever you want (create, update, drop) even without privileges:

MariaDB [test]> select user(),current_user();
+--------------+----------------+
| user()       | current_user() |
+--------------+----------------+
| mu@localhost | mu@localhost   |
+--------------+----------------+

MariaDB [test]> show grants;
+----------------------------------------+
| Grants for mu@localhost                |
+----------------------------------------+
| GRANT USAGE ON *.* TO 'mu'@'localhost' |
+----------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> create table t(t int); insert into t values (1); select * from t;
ERROR 1050 (42S01): Table 't' already exists
Query OK, 1 row affected (0.00 sec)

+------+
| t    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

So let's consider now one specific database mytest:

MariaDB [(none)]> select user(),current_user();
+----------------+----------------+
| user()         | current_user() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mytest             |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> grant all privileges on mytest.* to mu@localhost; 
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for mu@localhost;
+--------------------------------------------------------+
| Grants for mu@localhost                                |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mu'@'localhost'                 |
| GRANT ALL PRIVILEGES ON `mytest`.* TO 'mu'@'localhost' |
+--------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> revoke drop on mytest.* from mu@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for mu@localhost\G
*************************** 1. row ***************************
Grants for mu@localhost: GRANT USAGE ON *.* TO 'mu'@'localhost'
*************************** 2. row ***************************
Grants for mu@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mytest`.* TO 'mu'@'localhost'
2 rows in set (0.00 sec)

We can see that we now don't have only DROP privilege, so let's try to drop table with specific user:

MariaDB [mytest]> select user(),current_user();
+--------------+----------------+
| user()       | current_user() |
+--------------+----------------+
| mu@localhost | mu@localhost   |
+--------------+----------------+
1 row in set (0.00 sec)

MariaDB [mytest]> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| t                |
+------------------+
1 row in set (0.00 sec)

MariaDB [mytest]> create table m(t int); insert into m values(1); select * from m;
Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

+------+
| t    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

MariaDB [mytest]> drop table m;
ERROR 1142 (42000): DROP command denied to user 'mu'@'localhost' for table 'm'

With this we can verify that REVOKE <<priv_type = DROP>> ON <<priv_level=mytest.*>> FROM <<user=mu>> is working like stated in documentation.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.