user-grant-access creating grant rules for non existing databases

[shweta]

HOW TO REPRODUCE:

1.Create a user. (if does not exists)

ubuntu@shweta-test:~$ trove user-list b3e56616-fec8-4ff3-a31a-0260e80c5744
+-------+------+-----------+
| name | host | databases |
+-------+------+-----------+
| user1 | % | |
+-------+------+-----------+

2. Run user-grant-access API with valid user and invalid database names

ubuntu@shweta-test:~$ trove user-grant-access b3e56616-fec8-4ff3-a31a-0260e80c5744 user1 asd
ubuntu@shweta-test:~$ trove user-grant-access b3e56616-fec8-4ff3-a31a-0260e80c5744 user1 jawkdweuif
ubuntu@shweta-test:~$ trove user-grant-access b3e56616-fec8-4ff3-a31a-0260e80c5744 user1 database1
ubuntu@shweta-test:~$ trove user-grant-access b3e56616-fec8-4ff3-a31a-0260e80c5744 user1 datasd

3. Now run user-show API. It will list databases on which user is suppose to have access . It includes all the invalid database names which were passed in user-grant-access API.

ubuntu@shweta-test:~$ trove user-show b3e56616-fec8-4ff3-a31a-0260e80c5744 user1
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Property | Value |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| databases | [{u'name': u'asd'}, {u'name': u'database1'}, {u'name': u'datasd'}, {u'name': u'jawkdweuif'}, {u'name': u'nodb'}, {u'name': u'qweqrwqwe'}] |
| host | % |
| name | user1 |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+

4. List the databases on the instance.
ubuntu@shweta-test:~$ trove database-list b3e56616-fec8-4ff3-a31a-0260e80c5744
+--------------------+
| name |
+--------------------+
| database1 |
| performance_schema |
| test |
+--------------------+

[Pradeep Kumar Chandani (pradeep-chandani)]

This is invalid bug...as in Mysql, user can be granted on databases which doesn't exits.

Find below queries for reference-

mysql> show databases;
+---------------------+
| Database |
+---------------------+
| information_schema |
| debdb2 |
| #mysql50#lost+found |
| mysql |
| performance_schema |
| test |
| testdb |
| testdb1 |
| testdb3 |
+---------------------+
9 rows in set (0.69 sec)

mysql> select User,Host from mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| @Db#$jai123 | % |
| debuser2 | % |
| jai | % |
| jai$@ | % |
| debian-sys-maint | localhost |
| os_admin | localhost |
| root | localhost |
+------------------+-----------

mysql> Grant ALL ON debdb2333.* TO 'debuser2@%' ;
Query OK, 0 rows affected (0.01 sec)

mysql> select grantee,table_schema from information_schema.SCHEMA_PRIVILEGES where privilege_type != 'USAGE' group by grantee , table_schema;
+-------------------+--------------+
| grantee | table_schema |
+-------------------+--------------+
| ''@'%' | test |
| ''@'%' | test\_% |
| '@Db#$jai123'@'%' | db |
| 'debuser2'@'%' | db1 |
| 'debuser2@%'@'%' | debdb2 |
| 'debuser2@%'@'%' | debdb211 |
| 'debuser2@%'@'%' | debdb2333 |
| 'jai$@'@'%' | db |
| 'jai'@'%' | adf |
| 'jai'@'%' | asdf |
| 'jai'@'%' | db |
| 'jai'@'%' | debdb22 |
| 'jai'@'%' | sdf |
+-------------------+--------------+
13 rows in set (0.02 sec)

So from above queries we can see that if we fire grant queries for database that dont exits, it gives the grants the access of that database to user even that database doesn't exists.