Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8383

"GRANT role TO user" does not replicate

Details

    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

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          nirbhay_c Nirbhay Choubey (Inactive) added a comment -

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

          nirbhay_c Nirbhay Choubey (Inactive) added a comment - https://github.com/MariaDB/server/commit/6771b818a38a72e89d318eb9f1f3a2b8a64f0d5f
          aftab.khan aftab khan added a comment -

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

          aftab.khan aftab khan added a comment - Looks like this fix has applied to enterprise binaries only, this issue still exists in Mariadb 10.0.21 (community binaries)
          nirbhay_c Nirbhay Choubey (Inactive) added a comment -

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

          nirbhay_c Nirbhay Choubey (Inactive) added a comment - aftab.khan No, the fix was pushed to MariaDB Galera 10.0.21. What exact commands are failing for you?
          aftab.khan aftab khan added a comment - - edited

          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)

          aftab.khan aftab khan added a comment - - edited 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)
          nirbhay_c Nirbhay Choubey (Inactive) added a comment -

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

          nirbhay_c Nirbhay Choubey (Inactive) added a comment - What do you get for "select * from roles_mapping"?
          nirbhay_c Nirbhay Choubey (Inactive) added a comment -

          MDEV-10566

          nirbhay_c Nirbhay Choubey (Inactive) added a comment - MDEV-10566

          People

            nirbhay_c Nirbhay Choubey (Inactive)
            cosmins Cosmin Stahie
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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