Show User and Grants in MariaDB: A Beginner's Guide

MariaDB is known for its complex privilege system, which can be overwhelming for beginners. In this article, we'll explore how to use `SHOW USER` and `GRANTS FOR PUBLIC` effectively.

Currently, there is a bug (MDEV-29752) that prevents users from running `SHOW GRANTS FOR PUBLIC` unless they have SELECT rights on the mysql database. However, this issue is expected to be fixed before the feature becomes Generally Available (GA).

To grant privileges to public, you can use the following commands:

```

GRANT ALL PRIVILEGES ON `dev_db`.* TO `PUBLIC`;

GRANT ALL PRIVILEGES ON dev_db.* TO PUBLIC;

GRANT ALL PRIVILEGES ON mysql.* TO developer;

```

When running `SHOW GRANTS FOR PUBLIC`, you'll only see public's grants. However, when a user runs `SHOW GRANTS`, they will also see the rights inherited from PUBLIC. This is true even for users created after public grants have been modified.

One of the benefits of using public as a role is that granting a privilege to public works just like granting a privilege to a regular role. For example, you can set up public to have a certain set of privileges and apply them to all users who have login access to the server.

In addition to setting up public roles, MariaDB also offers default roles for users. This feature allows you to set which 'privilege package' will be enabled at connect time by setting a DEFAULT ROLE per user. ROLES are effectively 'privilege packages' that you can enable and disable as a user.

To take advantage of this feature, you can use the `TO PUBLIC` command (MDEV-5215), which is available in MariaDB 10.11.0 and later releases. While most of the functionality is based on the SQL Standard specification, MariaDB has also added some specific extensions to enhance its privilege system.

Overall, MariaDB's complex privilege system can be daunting for beginners, but with a little practice and experimentation, you'll be able to master `SHOW USER` and `GRANTS FOR PUBLIC` in no time.

Mariadb show user