[MDEV-8383] "GRANT role TO user" does not replicate Created: 2015-06-25  Updated: 2016-08-16  Resolved: 2015-07-11

Status: Closed
Project: MariaDB Server
Component/s: Admin statements, wsrep
Affects Version/s: 10.0.17-galera
Fix Version/s: 10.0.21-galera

Type: Bug Priority: Major
Reporter: Cosmin Stahie Assignee: Nirbhay Choubey (Inactive)
Resolution: Fixed Votes: 0
Labels: galera, replication, roles
Environment:

Ubuntu TT, Galera 10.0.17, AWS EC2 instances


 Description   

Hello,

We have encountered a bug trying to build our Galera cluster.

Due to our app requirements (B2B apps) we need to create different mysql users for each customer, and restrict those users to only some filtered views with check option on to avoid customer X to be able to access data of customer Y (row level security).

This task can be trivial to manage through ROLES, by creating a role which has access to those views only and then assign that role to each mysql user of each customer.

It was ok for us to execute "SET ROLE X" at the beginning of each connection (until latest Galera with DEFAULT ROLE per user will become a stable release), but now we've hit another wall: The statement GRANT role TO userx@'%' does not replicate between GALERA nodes. Which means that some customers will end up with permissions (role granted) only on one node, unless we manually patch things out (which is a nightmare to maintain atm).

Does anybody know about this bug and are there plans to be solved?

Thank you
Cosmin

 Comments   
Comment by Nirbhay Choubey (Inactive) [ 2015-07-11 ]

https://github.com/MariaDB/server/commit/6771b818a38a72e89d318eb9f1f3a2b8a64f0d5f

Comment by aftab khan [ 2016-08-16 ]

Looks like this fix has applied to enterprise binaries only, this issue still exists in Mariadb 10.0.21 (community binaries)

Comment by Nirbhay Choubey (Inactive) [ 2016-08-16 ]

aftab.khan No, the fix was pushed to MariaDB Galera 10.0.21. What exact commands are failing for you?

Comment by aftab khan [ 2016-08-16 ]

Here are the steps to reproduce this issue:

Assuming you have at least 2 nodes mariadb 10.0.21 galera cluster.

#
 
# # Create ROLE on one of the cluster member:
 
#
 
MariaDB [(none)]> create role ifactory_dev;
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES;
 
+----------+--------------+--------------+
 
| GRANTEE  | ROLE_NAME    | IS_GRANTABLE |
 
+----------+--------------+--------------+
 
| dba@10.% | ifactory_dev | YES          |
 
+----------+--------------+--------------+
 
1 row in set (0.01 sec)
 
 
 
#
 
# Log in to other cluster member
 
# 
 
 
MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES;
 
Empty set (0.00 sec)
 
 
 
MariaDB [(none)]> select version();
 
+-------------------------------------+
 
| version()                           |
 
+-------------------------------------+
 
| 10.0.21-MariaDB-1~precise-wsrep-log |
 
+-------------------------------------+
 
1 row in set (0.02 sec)

Comment by Nirbhay Choubey (Inactive) [ 2016-08-16 ]

What do you get for "select * from roles_mapping"?

Comment by Nirbhay Choubey (Inactive) [ 2016-08-16 ]

MDEV-10566

Generated at Thu Feb 08 07:26:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.