Trove 'user-show-access' CLI's result includes deleted databases

[liuqing]

For example:

1.Create a user 'foo' and two databases 'db1' 'db2' for a mysql instance, then grant the user 'foo' access to 'db1' and 'db2';
2.Delete the database 'db1';
3.Execute 'trove user-show-access ...' CLI, the result still includes 'db1'.

[Fan Zhang (fanzhang)]

I don't think it's a bug.

First, command 'trove user-show-access' corresponds to 'select * from information_schema.SCHEMA_PRIVILEGES', you can find out in the codes https://github.com/openstack/trove/blob/efd2ad9ee922c800e959c09c1061552d9307e61f/trove/guestagent/datastore/mysql_common/service.py#L231.

What user-show-access shows is only relevant to mysql command 'GRANT', it means that if you grant select privilege on db1 and db2 to user foo, you can find something like this:
mysql> select * from information_schema.SCHEMA_PRIVILEGES;
+----------------+---------------+--------------+-------------------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------------+---------------+--------------+-------------------------+--------------+
| 'foo'@'%' | NULL | db1 | SELECT | NO |
| 'foo'@'%' | NULL | db2 | SELECT | NO |
+----------------+---------------+--------------+-------------------------+--------------+

What if you grant select privilege on a database that's not exist to user foo?

The answer is that you can see something new from information_schema.SCHEMA_PRIVILEGES like this:
mysql> select * from information_schema.SCHEMA_PRIVILEGES;
+----------------+---------------+--------------+-------------------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------------+---------------+--------------+-------------------------+--------------+
| 'foo'@'%' | NULL | db1 | SELECT | NO |
| 'foo'@'%' | NULL | db2 | SELECT | NO |
| 'foo'@'%' | NULL | db3 | SELECT | NO |
+----------------+---------------+--------------+-------------------------+--------------+

You can find out more here https://dev.mysql.com/doc/refman/5.7/en/schema-privileges-table.html.

[Fan Zhang (fanzhang)]

I am going to set this bug to invalid. But we can do some check work to achieve your goals. That need to be discussed.