[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.

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

Status

Open [ 1 ]

Confirmed [ 10101 ]

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 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