Comments - SHOW GRANTS

8 years, 4 months ago Cyril Ogana

Hi,

I am using MariaDB 10.1.8 on Ubuntu Trusty. When logged in as root with global privileges, I am unable to discern the grants for roles by running for example 'SHOW GRANTS FOR research' I get an error like below

ERROR 1141 (42000): There is no such grant defined for user 'research' on host '%'

However when I run 'SET ROLE research; SHOW GRANTS;' i am able to list the grants.

Regards

 
8 years, 4 months ago Ian Gilfillan

You need to provide more details so that someone can try recreate this, or better see what the reason could be.

 
8 years, 4 months ago Cyril Ogana

Okay. Here is some more info:

1) Platform: - MariaDB 10.1.8 - Centos 6.7

2) Created Database: CREATE DATABASE dataservice; CREATE TABLE operations ( id INT(11) NOT NULL UNSIGNED PRIMARY KEY, description VARCHAR(45) NOT NULL

3) Create role: CREATE ROLE research; GRANT SHOW DATABASES ON *.* TO research; GRANT SELECT, INSERT, UPDATE ON dataservice.operations TO research; FLUSH PRIVILEGES;

4) Try to view the grants: SET ROLE research;

SHOW GRANTS FOR research

(Result is: ERROR 1141 (42000): There is no such grant defined for user 'research' on host '%')

SHOW GRANTS; (This works)

5) NB: Using default my.ini settings, nothing has been modified. Attached is a pasteboard http://pasteboard.co/2fV4Ibvk.png

 
8 years, 4 months ago Cyril Ogana

Just to note, all this is being done with root user. If i issue the research role to root user then it can execute SHOW GRANTS FOR research. I assumed ROOT should be able to see the grants because it has rights for grants?

 
8 years, 4 months ago Ian Gilfillan

I can't recreate, as when I do the same workflow on 10.1.8 Ubuntu, I can see the grants. I ran the following statements, taken from your code above, with fixed syntax.

CREATE DATABASE dataservice;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE dataservice;
Database changed
MariaDB [dataservice]> CREATE TABLE operations ( 
  id INT(11) UNSIGNED NOT NULL PRIMARY KEY, 
  description VARCHAR(45) NOT NULL
);
Query OK, 0 rows affected (0.73 sec)

MariaDB [dataservice]> GRANT SELECT, INSERT, UPDATE 
  ON dataservice.operations TO research;
Query OK, 0 rows affected (0.00 sec)

MariaDB [dataservice]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

MariaDB [dataservice]> SHOW GRANTS FOR research;
+--------------------------------------------------------------------------+
| Grants for research                                                      |
+--------------------------------------------------------------------------+
| GRANT SHOW DATABASES ON *.* TO 'research'                                |
| GRANT SELECT, INSERT, UPDATE ON `dataservice`.`operations` TO 'research' |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Doing the same with SET ROLE research before the SHOW makes no difference.

I suggest reporting this as a bug, as perhaps someone can find an environment where this fails, or find the problem in your situation.

 
8 years, 4 months ago Cyril Ogana

Thanks for the feedback @greenman

Very well, I shall do as you advise.

Please note that

1) I am also unable to reproduce on Windows 7 as per the snaggy below it works as expected http://snag.gy/9d5PA.jpg

2) However, I have reproduced it in Ubuntu 14.04. Apologies there is some earlier confusion above, the initial try was on Centos 6.7 (see link below) http://snag.gy/872B4.jpg

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.