[MDEV-24345] WITH ADMIN OPTION privilege is missing from SHOW PRIVILEGES Created: 2020-12-03  Updated: 2021-02-12  Resolved: 2021-02-12

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.2.36, 10.3.27, 10.4.17, 10.5.8
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-4397 Roles Closed
Relates
relates to MDEV-24344 BINLOG REPLAY privilege is missing fr... Closed
relates to MDEV-24362 Privilege aliases are missing from SH... Open

 Description   

MDEV-4397 added support for roles. As part of that, the following syntax is valid:

GRANT role_name TO user_name WITH ADMIN OPTION;

For example:

MariaDB [(none)]> CREATE USER my_user@localhost;
 
Query OK, 0 rows affected (0.010 sec)
 
 
 
MariaDB [(none)]> CREATE ROLE my_role;
 
Query OK, 0 rows affected (0.004 sec)
 
 
 
MariaDB [(none)]> GRANT my_role TO my_user@localhost WITH ADMIN OPTION;
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
MariaDB [(none)]> SHOW GRANTS FOR my_user@localhost;
 
+------------------------------------------------------------+
 
| Grants for my_user@localhost                               |
 
+------------------------------------------------------------+
 
| GRANT `my_role` TO `my_user`@`localhost` WITH ADMIN OPTION |
 
| GRANT USAGE ON *.* TO `my_user`@`localhost`                |
 
+------------------------------------------------------------+
 
2 rows in set (0.000 sec)

However, this WITH ADMIN OPTION privilege does not seem to be present in SHOW PRIVILEGES, even though the similar WITH GRANT OPTION privilege is listed:

MariaDB [(none)]> SHOW PRIVILEGES;
 
+--------------------------+---------------------------------------+--------------------------------------------------------------------+
 
| Privilege                | Context                               | Comment                                                            |
 
+--------------------------+---------------------------------------+--------------------------------------------------------------------+
 
| Alter                    | Tables                                | To alter the table                                                 |
 
| Alter routine            | Functions,Procedures                  | To alter or drop stored functions/procedures                       |
 
| Create                   | Databases,Tables,Indexes              | To create new databases and tables                                 |
 
| Create routine           | Databases                             | To use CREATE FUNCTION/PROCEDURE                                   |
 
| Create temporary tables  | Databases                             | To use CREATE TEMPORARY TABLE                                      |
 
| Create view              | Tables                                | To create new views                                                |
 
| Create user              | Server Admin                          | To create new users                                                |
 
| Delete                   | Tables                                | To delete existing rows                                            |
 
| Delete history           | Tables                                | To delete versioning table historical rows                         |
 
| Drop                     | Databases,Tables                      | To drop databases, tables, and views                               |
 
| Event                    | Server Admin                          | To create, alter, drop and execute events                          |
 
| Execute                  | Functions,Procedures                  | To execute stored routines                                         |
 
| File                     | File access on server                 | To read and write files on the server                              |
 
| Grant option             | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess                |
 
| Index                    | Tables                                | To create or drop indexes                                          |
 
| Insert                   | Tables                                | To insert data into tables                                         |
 
| Lock tables              | Databases                             | To use LOCK TABLES (together with SELECT privilege)                |
 
| Process                  | Server Admin                          | To view the plain text of currently executing queries              |
 
| Proxy                    | Server Admin                          | To make proxy user possible                                        |
 
| References               | Databases,Tables                      | To have references on tables                                       |
 
| Reload                   | Server Admin                          | To reload or refresh tables, logs and privileges                   |
 
| Binlog admin             | Server                                | To purge binary logs                                               |
 
| Binlog monitor           | Server                                | To use SHOW BINLOG STATUS and SHOW BINARY LOG                      |
 
| Replication master admin | Server                                | To monitor connected slaves                                        |
 
| Replication slave admin  | Server                                | To start/monitor/stop slave and apply binlog events                |
 
| Replication slave        | Server Admin                          | To read binary log events from the master                          |
 
| Select                   | Tables                                | To retrieve rows from table                                        |
 
| Show databases           | Server Admin                          | To see all databases with SHOW DATABASES                           |
 
| Show view                | Tables                                | To see views with SHOW CREATE VIEW                                 |
 
| Shutdown                 | Server Admin                          | To shut down the server                                            |
 
| Super                    | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.                |
 
| Trigger                  | Tables                                | To use triggers                                                    |
 
| Create tablespace        | Server Admin                          | To create/alter/drop tablespaces                                   |
 
| Update                   | Tables                                | To update existing rows                                            |
 
| Set user                 | Server                                | To create views and stored routines with a different definer       |
 
| Federated admin          | Server                                | To execute the CREATE SERVER, ALTER SERVER, DROP SERVER statements |
 
| Connection admin         | Server                                | To bypass connection limits and kill other users' connections      |
 
| Read_only admin          | Server                                | To perform write operations even if @@read_only=ON                 |
 
| Usage                    | Server Admin                          | No privileges - allow connect only                                 |
 
+--------------------------+---------------------------------------+--------------------------------------------------------------------+
 
39 rows in set (0.008 sec)



 Comments   
Comment by Daniel Black [ 2020-12-05 ]

also commit 0c303b52a96e24c4e59378e3f6e011d67e27b104 includes

CREATE ROLE xxx WITH ADMIN yyy
GRANT xxx TO yyy WITH ADMIN OPTION
REVOKE ADMIN OPTION FOR xxx FROM yyy

Comment by Sergei Golubchik [ 2020-12-08 ]

ADMIN OPTION is not a privilege, it's an option. It's questionable whether SHOW PRIVILEGES should show it.

Note that historically GRANT OPTION is a privilege.

Comment by Daniel Black [ 2021-02-08 ]

When using 'show privileges' programmatically like https://github.com/MariaDB/server/commit/86a7fab1755238710ed301578554d2c7d2d10d13, I wouldn't expect "ADMIN OPTION" to be used in the same way.

If it where included in this list, the context would need to be in "Revoke Role", but even this form of "REVOKE ADMIN OPTION FOR role FROM grantee" is syntactically different from the normal revoke.

As "WITH ADMIN OPTION" is the only GRANT ROLE option I'd suggest we leave this out of "SHOW PRIVILEGES".

Generated at Thu Feb 08 09:29:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.