[MDEV-24362] Privilege aliases are missing from SHOW PRIVILEGES Created: 2020-12-07  Updated: 2023-12-04

Status: Open
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.5.8
Fix Version/s: 10.5

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-20601 Make REPLICA a synonym for SLAVE in S... Closed
is caused by MDEV-21743 Split up SUPER privilege to smaller p... Closed
Relates
relates to MDEV-18777 Rename or alias slave-related stateme... Stalled
relates to MDEV-24344 BINLOG REPLAY privilege is missing fr... Closed
relates to MDEV-24345 WITH ADMIN OPTION privilege is missin... Closed

 Description   

MariaDB Server currently supports several privilege aliases:

  • READ ONLY ADMIN is an alias for READ_ONLY ADMIN.
  • REPLICATION REPLICA is an alias for REPLICATION SLAVE.
  • REPLICATION REPLICA ADMIN is an alias for REPLICATION SLAVE ADMIN.
  • REPLICATION CLIENT is an alias for BINLOG MONITOR.

However, these privilege aliases do not seem to be present in SHOW PRIVILEGES:

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 [ 2021-02-08 ]

Also missing as an alias is the obvious old time one of "ALL PRIVILEGES".

Attempting to use the output of SHOW PRIVILEGES programmatically like https://github.com/MariaDB/server/commit/86a7fab1755238710ed301578554d2c7d2d10d13 to ensure that a mariadb-dump --system=users contains a portable set of grants currently requires a special case for "ALL PRIVILEGES".

The other missing aliases would be problematic if using a 10.5 mariadb-dump --system=users with the above commit against an older MariaDB version. In this case the missing grants would be marked as MySQL-8.0+ grants and be ignored on import rather than letting the compatibility later take care of the translation.

As such, I'd like to include these aliases too. ralf.gebhardt@mariadb.com, serg is that ok with you?

Comment by Sergei Golubchik [ 2021-02-08 ]

No, I'd rather not include aliases, but only the default name. READ_ONLY ADMIN is the correct name, The spelling without underscore is a user-friendly alias to account for typical typos.

SLAVE/REPLICA — we should, again, show only one spelling, the default one. The default is supposed to become configurable or change to REPLICATE eventually.

And, again, BINLOG MONITOR is the privilege name and REPLICATION CLIENT is the compatibility alias, that we support, but don't recommend to use.

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