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 domain.info 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
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
is working like stated in documentation.<<priv_type = DROP>>
ON <<priv_level=mytest.*>>
FROM <<user=mu
>>