Comments - revoking drop privileges

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.