CODEWITHSUNDEEP
mysql
Peter
Peter

Reputation: 2181

mysql: revoke privilege "if exists"?

In a script, I want to make sure, a mysql user has no privileges at all on a specific database. So I do:

revoke all privileges on `testdb`.* from 'testuser'@'%'

But if the user does not have any privileges on this database I get this error:

There is no such grant defined for user 'testuser' on host '%'

What is absolutely correct, but my script throws an error now and stops. I do not want to make my script ignoring all errors from this statement.

So is there something like

revoke all privileges if exist ...

I could not find anything about that in the mysql manual.

Upvotes: 10

Views: 2952

Answers (2)

Rapsey
Rapsey

Reputation: 589

For future search engine users landing on this question:

To avoid an error if the grant does not exist:

REVOKE IF EXISTS ALL PRIVILEGES ON `testdb`.* FROM 'testuser'@'%';

To also avoid an error if the user does not exist:

REVOKE IF EXISTS ALL PRIVILEGES ON `testdb`.* FROM 'testuser'@'%' IGNORE UNKNOWN USER;

Please note that both of these language features are only available in MySQL 8.0 or higher: https://dev.mysql.com/doc/refman/8.0/en/revoke.html

Upvotes: 0

jlh
jlh

Reputation: 4717

The mysql command has an -f option which prevents the script from aborting on errors, so you might want to try this:

mysql -u myuser -f < script.sql

Of course this will also ignore other errors which you might not want to be ignored...

Also this unfortunately does not work in combination with the -e option. However if you run mysql from a bash script anyway this can easily be circumvented by using a heredoc:

mysql -u myuser -f <<EOF
REVOKE ALL PRIVILEGES ...
EOF

Upvotes: 1

Related Questions