Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
10.1.11
-
None
Description
It might be odd to edit mysql tables directly, but I believe it is common to delete rows from mysql.user and flush privileges to make sure that a user or role does not exist. It seems that any rows in tables_priv or columns_priv remain and it makes impossible to recreate the user or role or revoke all privileges:
MariaDB [mysql]> create role foo;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [mysql]> grant select on `bob_live_sg`.`catalog_config_shoes` to foo;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [mysql]> show grants for foo;
|
+---------------------------------------------------------------+
|
| Grants for foo |
|
+---------------------------------------------------------------+
|
| GRANT USAGE ON *.* TO 'foo' |
|
| GRANT SELECT ON `bob_live_sg`.`catalog_config_shoes` TO 'foo' |
|
+---------------------------------------------------------------+
|
2 rows in set (0.00 sec)
|
|
|
MariaDB [mysql]> delete from user where user='foo' and is_role='Y';
|
Query OK, 1 row affected (0.00 sec)
|
|
|
MariaDB [mysql]> flush privileges;
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
MariaDB [mysql]> show grants for foo;
|
ERROR 1141 (42000): There is no such grant defined for user 'foo' on host '%'
|
MariaDB [mysql]> delete from roles_mapping where role='foo';
|
Query OK, 1 row affected (0.00 sec)
|
|
|
MariaDB [mysql]> create role if not exists foo;
|
Query OK, 0 rows affected, 1 warning (0.00 sec)
|
|
|
Note (Code 1975): Can't create role 'foo'; it already exists
|
MariaDB [mysql]> revoke all,grant option from foo;
|
ERROR 1269 (HY000): Can't revoke all privileges for one or more of the requested users
|
MariaDB [mysql]>
|
|
MariaDB [mysql]> create user if not exists bar;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [mysql]> grant select on `bob_live_sg`.`catalog_config_shoes` to bar;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [mysql]> delete from user where user='bar' and is_role='N';
|
Query OK, 1 row affected (0.00 sec)
|
|
|
MariaDB [mysql]> flush privileges;
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
MariaDB [mysql]> select * from tables_priv where user='bar';
|
+------+-------------+------+----------------------+-------------------+---------------------+------------+-------------+
|
| Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
|
+------+-------------+------+----------------------+-------------------+---------------------+------------+-------------+
|
| % | bob_live_sg | bar | catalog_config_shoes | mariadb@localhost | 0000-00-00 00:00:00 | Select | |
|
+------+-------------+------+----------------------+-------------------+---------------------+------------+-------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [mysql]> create user if not exists bar;
|
Query OK, 0 rows affected, 1 warning (0.00 sec)
|
|
|
Note (Code 1973): Can't create user 'bar'@'%'; it already exists
|
MariaDB [mysql]> revoke all,grant option from bar;
|
ERROR 1269 (HY000): Can't revoke all privileges for one or more of the requested users
|
MariaDB [mysql]> grant usage on *.* to bar;
|
ERROR 1133 (28000): Can't find any matching row in the user table
|
|