[MDEV-13655] SET ROLE does not properly grant privileges. - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2.8, 10.0(EOL), 10.1(EOL), 10.2(EOL)
Fix Version/s: 10.0.34, 10.1.30, 10.2.12
Component/s: Authentication and Privilege System
Labels:
- roles
Environment:
Docker:
Version 17.06.0-ce-mac18 (18433)
Channel: stable
d9b66511e0

MacOS Sierra 10.12.6

MariaDB Docker Image ID 58730544b81b (mariadb:latest at time of filing).

Sprint:
10.1.30

Description

Running SET ROLE statement still yields permission denied when trying to create a database.

root@6d623b7b5bcd:/# mysql -u root -ppassword

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 8

Server version: 10.2.8-MariaDB-10.2.8+maria~jessie mariadb.org binary distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create role s;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant select, insert, update, delete, lock tables, execute on t.* to s;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create role a;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant s to a;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on t.* to a;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create user u;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant a to u;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant s to u;

Query OK, 0 rows affected (0.00 sec)

Then,

root@6d623b7b5bcd:/# mysql -u u

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 9

Server version: 10.2.8-MariaDB-10.2.8+maria~jessie mariadb.org binary distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show grants;

+-------------------------------+

| Grants for u@%                |

+-------------------------------+

| GRANT a TO 'u'@'%'            |

| GRANT s TO 'u'@'%'            |

| GRANT USAGE ON *.* TO 'u'@'%' |

+-------------------------------+

3 rows in set (0.00 sec)

MariaDB [(none)]> create database t;

ERROR 1044 (42000): Access denied for user 'u'@'%' to database 't'

MariaDB [(none)]> set role a;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create database t;

ERROR 1044 (42000): Access denied for user 'u'@'%' to database 't'

MariaDB [(none)]> show grants;

+----------------------------------------------------------------------------+

| Grants for u@%                                                             |

+----------------------------------------------------------------------------+

| GRANT a TO 'u'@'%'                                                         |

| GRANT s TO 'u'@'%'                                                         |

| GRANT USAGE ON *.* TO 'u'@'%'                                              |

| GRANT s TO 'a'                                                             |

| GRANT USAGE ON *.* TO 'a'                                                  |

| GRANT ALL PRIVILEGES ON `t`.* TO 'a'                                       |

| GRANT USAGE ON *.* TO 's'                                                  |

| GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES, EXECUTE ON `t`.* TO 's' |

+----------------------------------------------------------------------------+

8 rows in set (0.00 sec)

MariaDB [(none)]> select current_role;

+--------------+

| current_role |

+--------------+

| a            |

+--------------+

1 row in set (0.00 sec)

Granting all privileges directly to the user allows the user to create the database.

Attachments

Activity

Ascending order - Click to sort in descending order

Dusty VonHandorf created issue - 2017-08-26 13:35

Dusty VonHandorf made changes - 2017-08-26 13:37

Field	Original Value	New Value
Description	Running SET ROLE statement still yields permission denied when trying to create a database. {{root@6d623b7b5bcd:/# mysql -u root -ppassword Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 10.2.8-MariaDB-10.2.8+maria~jessie mariadb.org binary distribution Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> create role s; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> grant select, insert, update, delete, lock tables, execute on t.* to s; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> create role a; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> grant s to a; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> grant all on t.* to a; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> create user u; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> grant a to u; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> grant s to u; Query OK, 0 rows affected (0.00 sec)}} Then, {{root@6d623b7b5bcd:/# mysql -u u Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.2.8-MariaDB-10.2.8+maria~jessie mariadb.org binary distribution Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show grants; +-------------------------------+ \| Grants for u@% \| +-------------------------------+ \| GRANT a TO 'u'@'%' \| \| GRANT s TO 'u'@'%' \| \| GRANT USAGE ON . TO 'u'@'%' \| +-------------------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]> create database t; ERROR 1044 (42000): Access denied for user 'u'@'%' to database 't' MariaDB [(none)]> set role a; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> create database t; ERROR 1044 (42000): Access denied for user 'u'@'%' to database 't' MariaDB [(none)]> show grants; +----------------------------------------------------------------------------+ \| Grants for u@% \| +----------------------------------------------------------------------------+ \| GRANT a TO 'u'@'%' \| \| GRANT s TO 'u'@'%' \| \| GRANT USAGE ON . TO 'u'@'%' \| \| GRANT s TO 'a' \| \| GRANT USAGE ON . TO 'a' \| \| GRANT ALL PRIVILEGES ON `t`.* TO 'a' \| \| GRANT USAGE ON . TO 's' \| \| GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES, EXECUTE ON `t`.* TO 's' \| +----------------------------------------------------------------------------+ 8 rows in set (0.00 sec) MariaDB [(none)]> select current_role; +--------------+ \| current_role \| +--------------+ \| a \| +--------------+ 1 row in set (0.00 sec)}} Granting all privileges directly to the user allows the user to create the database.	Running SET ROLE statement still yields permission denied when trying to create a database. {code:java} root@6d623b7b5bcd:/# mysql -u root -ppassword Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 10.2.8-MariaDB-10.2.8+maria~jessie mariadb.org binary distribution Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> create role s; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> grant select, insert, update, delete, lock tables, execute on t.* to s; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> create role a; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> grant s to a; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> grant all on t.* to a; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> create user u; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> grant a to u; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> grant s to u; Query OK, 0 rows affected (0.00 sec) {code} Then, {{root@6d623b7b5bcd:/# mysql -u u Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.2.8-MariaDB-10.2.8+maria~jessie mariadb.org binary distribution Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show grants; +-------------------------------+ \| Grants for u@% \| +-------------------------------+ \| GRANT a TO 'u'@'%' \| \| GRANT s TO 'u'@'%' \| \| GRANT USAGE ON . TO 'u'@'%' \| +-------------------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]> create database t; ERROR 1044 (42000): Access denied for user 'u'@'%' to database 't' MariaDB [(none)]> set role a; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> create database t; ERROR 1044 (42000): Access denied for user 'u'@'%' to database 't' MariaDB [(none)]> show grants; +----------------------------------------------------------------------------+ \| Grants for u@% \| +----------------------------------------------------------------------------+ \| GRANT a TO 'u'@'%' \| \| GRANT s TO 'u'@'%' \| \| GRANT USAGE ON . TO 'u'@'%' \| \| GRANT s TO 'a' \| \| GRANT USAGE ON . TO 'a' \| \| GRANT ALL PRIVILEGES ON `t`.* TO 'a' \| \| GRANT USAGE ON . TO 's' \| \| GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES, EXECUTE ON `t`.* TO 's' \| +----------------------------------------------------------------------------+ 8 rows in set (0.00 sec) MariaDB [(none)]> select current_role; +--------------+ \| current_role \| +--------------+ \| a \| +--------------+ 1 row in set (0.00 sec)}} Granting all privileges directly to the user allows the user to create the database.

Dusty VonHandorf made changes - 2017-08-26 13:37

Description

Running SET ROLE statement still yields permission denied when trying to create a database.

{code:java}
root@6d623b7b5bcd:/# mysql -u root -ppassword
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.8-MariaDB-10.2.8+maria~jessie mariadb.org binary distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create role s;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant select, insert, update, delete, lock tables, execute on t.* to s;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create role a;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant s to a;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on t.* to a;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create user u;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant a to u;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant s to u;
Query OK, 0 rows affected (0.00 sec)
{code}

Then,
{{root@6d623b7b5bcd:/# mysql -u u
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.8-MariaDB-10.2.8+maria~jessie mariadb.org binary distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show grants;
+-------------------------------+
| Grants for u@% |
+-------------------------------+
| GRANT a TO 'u'@'%' |
| GRANT s TO 'u'@'%' |
| GRANT USAGE ON *.* TO 'u'@'%' |
+-------------------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> create database t;
ERROR 1044 (42000): Access denied for user 'u'@'%' to database 't'
MariaDB [(none)]> set role a;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create database t;
ERROR 1044 (42000): Access denied for user 'u'@'%' to database 't'
MariaDB [(none)]> show grants;
+----------------------------------------------------------------------------+
| Grants for u@% |
+----------------------------------------------------------------------------+
| GRANT a TO 'u'@'%' |
| GRANT s TO 'u'@'%' |
| GRANT USAGE ON *.* TO 'u'@'%' |
| GRANT s TO 'a' |
| GRANT USAGE ON *.* TO 'a' |
| GRANT ALL PRIVILEGES ON `t`.* TO 'a' |
| GRANT USAGE ON *.* TO 's' |
| GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES, EXECUTE ON `t`.* TO 's' |
+----------------------------------------------------------------------------+
8 rows in set (0.00 sec)

MariaDB [(none)]> select current_role;
+--------------+
| current_role |
+--------------+
| a |
+--------------+
1 row in set (0.00 sec)}}

Granting all privileges directly to the user allows the user to create the database.

Running SET ROLE statement still yields permission denied when trying to create a database.

{code:java}
root@6d623b7b5bcd:/# mysql -u root -ppassword
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.8-MariaDB-10.2.8+maria~jessie mariadb.org binary distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create role s;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant select, insert, update, delete, lock tables, execute on t.* to s;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create role a;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant s to a;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on t.* to a;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create user u;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant a to u;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant s to u;
Query OK, 0 rows affected (0.00 sec)
{code}

Then,

{code:java}
root@6d623b7b5bcd:/# mysql -u u
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.8-MariaDB-10.2.8+maria~jessie mariadb.org binary distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show grants;
+-------------------------------+
| Grants for u@% |
+-------------------------------+
| GRANT a TO 'u'@'%' |
| GRANT s TO 'u'@'%' |
| GRANT USAGE ON *.* TO 'u'@'%' |
+-------------------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> create database t;
ERROR 1044 (42000): Access denied for user 'u'@'%' to database 't'
MariaDB [(none)]> set role a;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create database t;
ERROR 1044 (42000): Access denied for user 'u'@'%' to database 't'
MariaDB [(none)]> show grants;
+----------------------------------------------------------------------------+
| Grants for u@% |
+----------------------------------------------------------------------------+
| GRANT a TO 'u'@'%' |
| GRANT s TO 'u'@'%' |
| GRANT USAGE ON *.* TO 'u'@'%' |
| GRANT s TO 'a' |
| GRANT USAGE ON *.* TO 'a' |
| GRANT ALL PRIVILEGES ON `t`.* TO 'a' |
| GRANT USAGE ON *.* TO 's' |
| GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES, EXECUTE ON `t`.* TO 's' |
+----------------------------------------------------------------------------+
8 rows in set (0.00 sec)

MariaDB [(none)]> select current_role;
+--------------+
| current_role |
+--------------+
| a |
+--------------+
1 row in set (0.00 sec)
{code}

Granting all privileges directly to the user allows the user to create the database.

Dusty VonHandorf added a comment - 2017-08-26 13:39

In the example "s" is abbreviated for standard user, "a" for admin user and "u" for user. I wanted to make sure it is not a regression for a similar bug I saw in JIRA that was already closed.

Dusty VonHandorf added a comment - 2017-08-26 13:39 In the example "s" is abbreviated for standard user, "a" for admin user and "u" for user. I wanted to make sure it is not a regression for a similar bug I saw in JIRA that was already closed.

Elena Stepanova made changes - 2017-08-26 17:26

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova added a comment - 2017-08-26 17:50

Thanks for the report.

The culprit is the limited permissions to the database given to role s. Without it (or without giving this role to a), of if the limited permissions are granted to s after ALL is granted to a, it works all right.

We had a somewhat similar bug report ~~MDEV-5771~~, apparently it wasn't fixed for all cases. I checked 10.0.10 to ensure that it's not a later regression.

Elena Stepanova added a comment - 2017-08-26 17:50 Thanks for the report. The culprit is the limited permissions to the database given to role s . Without it (or without giving this role to a ), of if the limited permissions are granted to s after ALL is granted to a , it works all right. We had a somewhat similar bug report MDEV-5771 , apparently it wasn't fixed for all cases. I checked 10.0.10 to ensure that it's not a later regression.

Elena Stepanova made changes - 2017-08-26 17:50

Fix Version/s		10.0 [ 16000 ]
Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.1 [ 16100 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.1 [ 16100 ]
Assignee		Vicentiu Ciorbaru [ cvicentiu ]

Sergei Golubchik made changes - 2017-12-12 19:32

Sprint

10.1.30 [ 215 ]

Vicențiu Ciorbaru made changes - 2017-12-16 16:21

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Vicențiu Ciorbaru added a comment - 2017-12-20 12:41

Fixed with:

https://github.com/MariaDB/server/commit/2fced9e7b643d0c101d0ffde33f51e0a38f7017e

Vicențiu Ciorbaru added a comment - 2017-12-20 12:41 Fixed with: https://github.com/MariaDB/server/commit/2fced9e7b643d0c101d0ffde33f51e0a38f7017e

Vicențiu Ciorbaru made changes - 2017-12-20 12:41

Fix Version/s		10.0.34 [ 22613 ]
Fix Version/s		10.1.30 [ 22637 ]
Fix Version/s		10.2.12 [ 22810 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.0 [ 16000 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:45

Workflow

MariaDB v3 [ 82274 ]

MariaDB v4 [ 152710 ]

People

Assignee:: Vicențiu Ciorbaru

Reporter:: Dusty VonHandorf

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2017-08-26 13:35

Updated:: 2017-12-20 12:41

Resolved:: 2017-12-20 12:41

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration