[MDEV-30451] GRANT permission missing if provided via role Created: 2023-01-23  Updated: 2023-01-24  Resolved: 2023-01-24

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.5.18
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Birk Bohne Assignee: Anel Husakovic
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Kubernetes 1.25.5, Worker Nodes with Flatcar Linux 3227.2.1, Ubuntu 20.04 base image, MariaDB 10.5.18 packages, Galera 26.4.13


 Description   

I have configured a fullaccess role and i have granted it to the root@% user. The default root@localhost has the same privilege but directly granted.

SELECT user,is_role,host,Grant_priv,Super_priv,default_role,plugin FROM mysql.user ORDER BY user;
 
+----------------+---------+-------------+------------+------------+----------------+-----------------------+
 
| User           | is_role | Host        | Grant_priv | Super_priv | default_role   | plugin                |
 
+----------------+---------+-------------+------------+------------+----------------+-----------------------+
 
| fullaccess     | Y       |             | Y          | Y          |                |                       |
 
| mariadb.sys    | N       | localhost   | N          | N          |                | mysql_native_password |
 
| monitor        | N       | %           | N          | N          | mysql_exporter | mysql_native_password |
 
| monitor        | N       | ::1         | N          | N          | mysql_exporter | mysql_native_password |
 
| monitor        | N       | localhost   | N          | N          | mysql_exporter | mysql_native_password |
 
| mysql_exporter | Y       |             | N          | N          |                |                       |
 
| root           | N       | localhost   | Y          | Y          |                |                       |
 
| root           | N       | mariadb-g-0 | Y          | Y          |                |                       |
 
| root           | N       | 127.0.0.1   | Y          | Y          |                |                       |
 
| root           | N       | ::1         | N          | N          | fullaccess     | mysql_native_password |
 
| root           | N       | %           | N          | N          | fullaccess     | mysql_native_password |
 
+----------------+---------+-------------+------------+------------+----------------+-----------------------+
 
11 rows in set (0.001 sec)
 
 
 
SHOW GRANTS FOR fullaccess;
 
+------------------------------------------------------+
 
| Grants for fullaccess                                |
 
+------------------------------------------------------+
 
| GRANT SUPER ON *.* TO `fullaccess` WITH GRANT OPTION |
 
+------------------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
SHOW GRANTS FOR 'root'@'%';
 
+-----------------------------------------------------------------------------------------------------+
 
| Grants for root@%                                                                                   |
 
+-----------------------------------------------------------------------------------------------------+
 
| GRANT `fullaccess` TO `root`@`%` WITH ADMIN OPTION                                                  |
 
| GRANT USAGE ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD 'xyz'                                       |
 
| SET DEFAULT ROLE `fullaccess` FOR `root`@`%`                                                        |
 
+-----------------------------------------------------------------------------------------------------+
 
3 rows in set (0.000 sec)
 
 
 
SHOW GRANTS FOR 'root'@'localhost';
 
+---------------------------------------------------------------------+
 
| Grants for root@localhost                                           |
 
+---------------------------------------------------------------------+
 
| GRANT `fullaccess` TO `root`@`localhost` WITH ADMIN OPTION          |
 
| GRANT `mysql_exporter` TO `root`@`localhost` WITH ADMIN OPTION      |
 
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION |
 
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION       |
 
+---------------------------------------------------------------------+
 
4 rows in set (0.000 sec)

If i'm using the root@% user i'm unable to GRANT another role to another user.

mysql --protocol=tcp --host=mariadb-g-0 --user=${MARIADB_ROOT_USER} --password=${MARIADB_ROOT_PASSWORD}
 
 
 
SELECT CURRENT_USER;
 
+--------------+
 
| CURRENT_USER |
 
+--------------+
 
| root@%       |
 
+--------------+
 
1 row in set (0.000 sec)
 
 
 
SELECT CURRENT_ROLE;
 
+--------------+
 
| CURRENT_ROLE |
 
+--------------+
 
| fullaccess   |
 
+--------------+
 
1 row in set (0.000 sec)
 
 
 
GRANT mysql_exporter TO 'monitor'@'%';
 
ERROR 1698 (28000): Access denied for user 'root'@'%'

The root@localhost user is able to do that.

mysql --protocol=tcp --host=localhost --user=${MARIADB_ROOT_USER}
 
 
 
SELECT CURRENT_USER;
 
+----------------+
 
| CURRENT_USER   |
 
+----------------+
 
| root@localhost |
 
+----------------+
 
1 row in set (0.000 sec)
 
 
 
SELECT CURRENT_ROLE;
 
+--------------+
 
| CURRENT_ROLE |
 
+--------------+
 
| NULL         |
 
+--------------+
 
1 row in set (0.000 sec)
 
 
 
GRANT mysql_exporter TO 'monitor'@'%';
 
Query OK, 0 rows affected (0.005 sec)

Is this the expected behavior or a bug?

regards,
Birk

PS: The same happens if i use 

GRANT ALL PRIVILEGES ON *.* TO `fullaccess` WITH GRANT OPTION

instead of 

GRANT SUPER ON *.* TO `fullaccess` WITH GRANT OPTION



 Comments   
Comment by Birk Bohne [ 2023-01-23 ]

Even with directly granting these permissions

GRANT ALL PRIVILEGES ON *.* TO `root`@`%` WITH GRANT OPTION; 
GRANT PROXY ON ''@'%' TO 'root'@'%' WITH GRANT OPTION;

the problem

ERROR 1698 (28000) at line 16: Access denied for user 'root'@'%'

is not fixed...

Comment by Anel Husakovic [ 2023-01-23 ]

Hi,
this is not a bug. Role should be grantable, when fetched from IS.applicable_roles https://mariadb.com/kb/en/information-schema-applicable_roles-table/
To do so, you have to grant privilege with admin option 

create user a; # this is by default '%'
 
create user b@localhost;
 
create role r1;
 
create role r2;
 
# Note with admin option
 
grant r1 to a with admin option;
 
# visible only for current_connection
 
SELECT * FROM information_schema.APPLICABLE_ROLES;
 
GRANTEE	ROLE_NAME	IS_GRANTABLE	IS_DEFAULT
 
root@localhost	r1	YES	NO
 
root@localhost	r2	YES	NO
 
 
 
# New connection
 
connect c1,127.0.0.1,a,,;
 
SELECT * FROM information_schema.APPLICABLE_ROLES;
 
GRANTEE	ROLE_NAME	IS_GRANTABLE	IS_DEFAULT
 
a@%	r1	YES	NO
 
show grants for current_user;
 
Grants for a@%
 
GRANT `r1` TO `a`@`%` WITH ADMIN OPTION
 
GRANT USAGE ON *.* TO `a`@`%`
 
# r2 is not visible in IS.applicable_roles to grantee `a@'%'` it will not work
 
grant r2 to b;
 
ERROR 28000: Access denied for user 'a'@'%'
 
 
 
# To make it work, add r2 role to be grantable to `a` user
 
connection default;
 
grant r2 to a with admin option;
 
 
 
# Test again
 
connection c1;
 
SELECT * FROM information_schema.APPLICABLE_ROLES;
 
GRANTEE	ROLE_NAME	IS_GRANTABLE	IS_DEFAULT
 
a@%	r1	YES	NO
 
a@%	r2	YES	NO
 
# Now it works
 
grant r2 to b@localhost;
 
 
 
 
 
connection default;
 
# Verify
 
show grants for b@localhost;
 
Grants for b@localhost
 
GRANT `r2` TO `b`@`localhost`
 
GRANT USAGE ON *.* TO `b`@`localhost`
 
disconnect c1;
 
drop user a;
 
drop user b@localhost;
 
drop role r1;
 
drop role r2;
 
main.test                                [ pass ]      8

Comment by Anel Husakovic [ 2023-01-23 ]

I'm curious about your setting with K8s and Galera as described in Environment of this MDEV.
Can you share it (what can be shared) here or privately on Zulip?

Comment by Birk Bohne [ 2023-01-23 ]

Hi Anel,

thanks for the clarification.

regards,
Birk

Comment by Anel Husakovic [ 2023-01-24 ]

Glad to help Birk .
Regards.

Generated at Thu Feb 08 10:16:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.