GRANT TO PUBLIC in MariaDB

Written by Written by Vicențiu Ciorbaru

MariaDB 10.11.0, our latest preview release, features quite a number of improvements. The one we’ll talk about today is GRANT ... TO PUBLIC.

Background

MariaDB has quite a complex privilege system. Most of it is based on the SQL Standard spec; however we do have some specific MariaDB extensions. GRANT ... TO PUBLIC (MDEV-5215) is a standard feature that is now available as a preview in MariaDB 10.11.0. It is related to ROLES and DEFAULT ROLE, but it covers a different use case.

ROLES are effectively “privilege packages” that you can enable and disable as a user. One can also set which “privilege package” will be enabled at connect time by setting a DEFAULT ROLE per user. This is all quite useful, however it is missing one key feature. For a DBA, it would be quite useful to state only once that all users need to have a certain set of privileges. This is where GRANT ... TO PUBLIC comes in.

Usage

GRANT <privilege> ON <database>.<object> TO PUBLIC;
REVOKE <privilege> ON <database>.<object> FROM PUBLIC;

Granting a privilege to public works just like granting a privilege to a regular role. The only difference is that these privileges apply to all users that have login access to the server. This holds true even for users created after PUBLIC grants have been modified.

When running SHOW GRANTS, a user will also see the rights inherited from PUBLIC. And one can also run SHOW GRANTS FOR PUBLIC, which will only show PUBLIC’s grants.

... (connect as root) ...
MariaDB [(none)]> create user developer;
MariaDB [(none)]> create database dev_db;
MariaDB [(none)]> grant all on dev_db.* to public;
MariaDB [(none)]> grant all on mysql.* to developer;

... (connect as developer) ...
MariaDB [(none)]> show grants;
+-------------------------------------------------+
| Grants for developer@%                          |
+-------------------------------------------------+
| GRANT USAGE ON . TO developer@%                 |
| GRANT ALL PRIVILEGES ON mysql.* TO developer@%  |
| GRANT ALL PRIVILEGES ON dev_db.* TO PUBLIC      |
+-------------------------------------------------+

MariaDB [(none)]> show grants for public;
+------------------------------------------------+
| Grants for PUBLIC                              |
+------------------------------------------------+
| GRANT ALL PRIVILEGES ON `dev_db`.* TO `PUBLIC` |
+------------------------------------------------+

However there is currently a bug (MDEV-29752) where one can’t run SHOW GRANTS FOR PUBLIC unless one has SELECT rights on the mysql database. Expect this to be fixed before the feature becomes GA.

How to try out this feature

The fastest way to take MariaDB 10.11 for a spin is to run it in docker / podman with the following commands:

To start a 10.11.0 MariaDB container:

docker run --env MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=True --name mariadb-preview -it quay.io/mariadb-foundation/mariadb-devel:10.11-preview

After that, you can connect in another terminal with:

docker exec -it mariadb-preview mysql -u root

If you’re running podman, just replace docker with podman.
Alternatively, you can download the binary directly from mariadb.org/download