(Newbie Q) Usage granted to ROLE - but can't revoke it?

Help Security
1

I’m a newbie to administering memSQL and we’re running 7.1.4

I made a couple of mistakes (on grants) and am trying to reverse the grant (revoke) but the revoke doesn’t seem to work.

Here’s the grant:

GRANT USAGE ON `drug_manufacturing_dm`.* TO ROLE 'ROLE_B2B_LANDING_DDL' WITH GRANT OPTION ;

Two problems with this:
1.) The drug_manufacturing_dm database doesn’t actually exist - the db name is different
2.) This grant should have gone to a different ROLE (and correct database name)

So I’m trying to revoke it like so:

revoke usage on drug_manufacturing_dm.* from role 'ROLE_B2B_LANDING_DDL';
revoke usage on `drug_manufacturing_dm`.* from role 'ROLE_B2B_LANDING_DDL';

Both of those appear to execute fine, but the grant remains:

show grants for role 'ROLE_B2B_LANDING_DDL';

GRANT USAGE ON `drug_manufacturing_dm`.* TO ROLE 'ROLE_B2B_LANDING_DDL' WITH GRANT OPTION

What am I missing here?

2

I was able to do it like this:

memsql> show grants for role 'r1';
+-------------------------------------------------------+
| Grants for role r1                                    |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO ROLE 'r1'                       |
| GRANT USAGE ON `db1`.* TO ROLE 'r1' WITH GRANT OPTION |
+-------------------------------------------------------+
2 rows in set (0.00 sec)

memsql> revoke grant option on `db1`.* from role 'r1';
Query OK, 0 rows affected (0.01 sec)

memsql> show grants for role 'r1';
+---------------------------------+
| Grants for role r1              |
+---------------------------------+
| GRANT USAGE ON *.* TO ROLE 'r1' |
+---------------------------------+

Which I saw here:

Not particularly obvious; it appears you have to remove GRANT OPTION entirely. That might revoke other privileges which you’d have to add back. Maybe there’s a better way that’s more targeted to the particular grant you want, but I couldn’t find one.

3

Wow - thanks for that. I wouldn’t have ever gotten to that method I think. I’ll try it and see if that drops any other permissions and re-add them if so.

Much appreciated!