[MDEV-5669] Possible inconsistencies or lack of documentation in role privilege grants and propogation - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0.8
Fix Version/s: None
Component/s: None
Labels:
None

Description

There are some non-obvious parts in roles behavior. I couldn't find anything definitive in JIRA, KB, or SQL standard, so I'm not sure whether they are bugs or features, but in the latter case they should be documented in more detail somewhere here: https://mariadb.com/kb/en/roles/ .
If you decide that any of those are bugs, I can create separate subtasks to avoid confusion having several unrelated issues under one entry.

Case 1, nested roles

When a role is granted to a user, the user only gets is the ability to SET the role; but it does not get the privileges granted to the role by default, until the role is set. This is certainly intended.

MariaDB [test]> create role r1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> grant all on db.* to r1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> grant r1 to foo@localhost;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select current_user;

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

| current_user  |

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

| foo@localhost |

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

1 row in set (0.00 sec)

MariaDB [test]> show grants;

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

| Grants for foo@localhost                |

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

| GRANT r1 TO 'foo'@'localhost'           |

| GRANT USAGE ON *.* TO 'foo'@'localhost' |

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

2 rows in set (0.00 sec)

MariaDB [test]> set role r1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> show grants;

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

| Grants for foo@localhost                |

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

| GRANT r1 TO 'foo'@'localhost'           |

| GRANT USAGE ON *.* TO 'foo'@'localhost' |

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

| GRANT ALL PRIVILEGES ON `db`.* TO 'r1'  |

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

4 rows in set (0.00 sec)

But if a role is granted to another role, it looks different. As soon as the user sets the other role, it immediately obtains all privileges granted to the first role, but does not obtain the ability to SET the first role.

MariaDB [test]> create role r1, r2;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> grant all on db1.* to r1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> grant all on db2.* to r2;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> grant r1 to r2;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> grant r2 to foo@localhost;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select current_user;

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

| current_user  |

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

| foo@localhost |

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

1 row in set (0.00 sec)

MariaDB [test]> show grants;

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

| Grants for foo@localhost                |

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

| GRANT r2 TO 'foo'@'localhost'           |

| GRANT USAGE ON *.* TO 'foo'@'localhost' |

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

2 rows in set (0.00 sec)

MariaDB [test]> set role r2;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> show grants;

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

| Grants for foo@localhost                |

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

| GRANT r2 TO 'foo'@'localhost'           |

| GRANT USAGE ON *.* TO 'foo'@'localhost' |

| GRANT r1 TO 'r2'                        |

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

| GRANT ALL PRIVILEGES ON `db2`.* TO 'r2' |

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

| GRANT ALL PRIVILEGES ON `db1`.* TO 'r1' |

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

7 rows in set (0.00 sec)

MariaDB [test]> set role r1;

ERROR 1959 (OP000): Invalid role specification `r1`.

It looks illogical or at least inconsistent. On the other hand, it makes configuration and usage of roles easier, so maybe it is intentional and reasonable. Then, it just needs to be explained clearly in documentation.

Case 2, order of granting

Privilege propogation depends on the order in which the privileges were granted.

Here, I first grant the roles, and then grant privileges to the roles:

MariaDB [test]> create role r1, r2;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> grant r1 to r2;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> grant r2 to foo@localhost;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> grant all on db1.* to r1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> grant all on db2.* to r2;

Query OK, 0 rows affected (0.00 sec)

As a result, the user with active role r2 has access to both db1 and db2:

MariaDB [test]> select current_user;

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

| current_user  |

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

| foo@localhost |

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

1 row in set (0.00 sec)

MariaDB [test]> show tables in db1;

ERROR 1044 (42000): Access denied for user 'foo'@'localhost' to database 'db1'

MariaDB [test]> show tables in db2;

ERROR 1044 (42000): Access denied for user 'foo'@'localhost' to database 'db2'

MariaDB [test]> set role r2;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> show tables in db1;

Empty set (0.00 sec)

MariaDB [test]> show tables in db2;

Empty set (0.00 sec)

MariaDB [test]> show grants;

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

| Grants for foo@localhost                |

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

| GRANT r2 TO 'foo'@'localhost'           |

| GRANT USAGE ON *.* TO 'foo'@'localhost' |

| GRANT r1 TO 'r2'                        |

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

| GRANT ALL PRIVILEGES ON `db2`.* TO 'r2' |

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

| GRANT ALL PRIVILEGES ON `db1`.* TO 'r1' |

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

7 rows in set (0.00 sec)

Now, I first grant privileges to the roles, and then grant roles:

MariaDB [test]> create role r1, r2;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> grant all on db1.* to r1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> grant all on db2.* to r2;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> grant r1 to r2;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> grant r2 to foo@localhost;

Query OK, 0 rows affected (0.00 sec)

As a result, the user with active role r2 only has access to db2, but not to db1:

MariaDB [test]> select current_user;

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

| current_user  |

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

| foo@localhost |

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

1 row in set (0.00 sec)

MariaDB [test]> show tables in db1;

ERROR 1044 (42000): Access denied for user 'foo'@'localhost' to database 'db1'

MariaDB [test]> show tables in db2;

ERROR 1044 (42000): Access denied for user 'foo'@'localhost' to database 'db2'

MariaDB [test]> set role r2;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> show tables in db1;

ERROR 1044 (42000): Access denied for user 'foo'@'localhost' to database 'db1'

MariaDB [test]> show tables in db2;

Empty set (0.00 sec)

MariaDB [test]> show grants;

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

| Grants for foo@localhost                |

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

| GRANT r2 TO 'foo'@'localhost'           |

| GRANT USAGE ON *.* TO 'foo'@'localhost' |

| GRANT r1 TO 'r2'                        |

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

| GRANT ALL PRIVILEGES ON `db2`.* TO 'r2' |

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

| GRANT ALL PRIVILEGES ON `db1`.* TO 'r1' |

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

7 rows in set (0.01 sec)

Which outcome is correct and which not, depends on whether case 1 is the bug or not, but I don't think they can be both correct at once, especially since SHOW GRANTS output is identical.

Case 3. Inherited privileges for view definers get lost

If SQL SECURITY for a view is DEFINER, CURRENT_ROLE inside the view is always NULL, and all privileges associated with the role which the definer had during view creation are lost; so, even immediately after creating the view, the definer cannot use it. It might be unavoidable since the role is not stored anywhere in the view definition, but it should be documented very clearly, much like various combinations CURRENT_USER/DEFINER/SQL SECURITY are documented in MySQL manual.

MariaDB [test]> create role r1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> grant all on db1.* to r1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> grant r1 to foo@localhost;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> grant all on db.* to foo@localhost;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select current_user;

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

| current_user  |

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

| foo@localhost |

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

1 row in set (0.00 sec)

MariaDB [test]> set role r1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> create table db1.t1 (i int);

Query OK, 0 rows affected (0.62 sec)

MariaDB [test]> create view db.v1 as select * from db1.t1;

Query OK, 0 rows affected (0.09 sec)

MariaDB [test]> show create view db.v1;

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

| View | Create View                                                                                                                              | character_set_client | collation_connection |

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

| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`foo`@`localhost` SQL SECURITY DEFINER VIEW `db`.`v1` AS select `db1`.`t1`.`i` AS `i` from `db1`.`t1` | utf8                 | utf8_general_ci      |

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

1 row in set (0.00 sec)

MariaDB [test]> select * from db.v1;

ERROR 1356 (HY000): View 'db.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Case 4. Granting multiple roles in single statement

One can create several roles at once:

MariaDB [test]> create role r1, r2;

Query OK, 0 rows affected (0.01 sec)

One can grant multiple privileges to multiple users at once:

MariaDB [test]> grant SELECT,INSERT on db.* to foo@localhost, bar@localhost;

Query OK, 0 rows affected (0.00 sec)

But one cannot grant multiple roles at once:

MariaDB [test]> grant r1, r2 to foo@localhost;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' r2 to foo@localhost' at line 1

SQL standard seems to think that it should work.
It's not a big deal, but if it's an overlook and not an unavoidable limitation, it might make sense to fix.

Attachments

Issue Links

relates to

MDEV-5771 Privileges acquired via roles depend on the order of granting

Closed

MDEV-5772 Granting multiple roles in single statement does not work

Open

MDEV-21000 default behaviour of sql security and should be configurable.

Open

MDEV-4397 Roles

Closed

MDEV-5164 Testing Roles

Closed

Activity

People

Assignee:: Ian Gilfillan

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2014-02-13 11:32

Updated:: 2019-06-19 12:33

Resolved:: 2014-03-05 09:19

Git Integration

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