
For example, this could be used with the PAM authentication plugin:

GRANT USAGE ON *.* TO foo2@test IDENTIFIED VIA pam;

Some authentication plugins allow additional arguments to be specified after a
USING or AS keyword. For example, the PAM authentication plugin accepts a
service name:

GRANT USAGE ON *.* TO foo2@test IDENTIFIED VIA pam USING 'mariadb';

The exact meaning of the additional argument would depend on the specific
authentication plugin.

MariaDB starting with 10.4.0
----------------------------
The USING or AS keyword can also be used to provide a plain-text password to a
plugin if it's provided as an argument to the PASSWORD() function. This is
only valid for authentication plugins that have implemented a hook for the
PASSWORD() function. For example, the ed25519 authentication plugin supports
this:

CREATE USER safe@'%' IDENTIFIED VIA ed25519 
 USING PASSWORD('secret');

MariaDB starting with 10.4.3
----------------------------
One can specify many authentication plugins, they all work as alternatives
ways of authenticating a user:

CREATE USER safe@'%' IDENTIFIED VIA ed25519 
 USING PASSWORD('secret') OR unix_socket;

By default, when you create a user without specifying an authentication
plugin, MariaDB uses the mysql_native_password plugin.

Resource Limit Options
----------------------

It is possible to set per-account limits for certain server resources. The
following table shows the values that can be set per account:

+--------------------------------------+--------------------------------------+
| Limit Type                           | Decription                           |
+--------------------------------------+--------------------------------------+
| MAX_QUERIES_PER_HOUR                 | Number of statements that the        |
|                                      | account can issue per hour           |
|                                      | (including updates)                  |
+--------------------------------------+--------------------------------------+
| MAX_UPDATES_PER_HOUR                 | Number of updates (not queries)      |
|                                      | that the account can issue per hour  |
+--------------------------------------+--------------------------------------+
| MAX_CONNECTIONS_PER_HOUR             | Number of connections that the       |
|                                      | account can start per hour           |
+--------------------------------------+--------------------------------------+
| MAX_USER_CONNECTIONS                 | Number of simultaneous connections   |
|                                      | that can be accepted from the same   |
|                                      | account; if it is 0,                 |
|                                      | max_connections will be used         |
|                                      | instead; if max_connections is 0,    |
|                                      | there is no limit for this           |
|                                      | account's simultaneous connections.  |
+--------------------------------------+--------------------------------------+
| MAX_STATEMENT_TIME                   | Timeout, in seconds, for statements  |
|                                      | executed by the user. See also       |
|                                      | Aborting Statements that Exceed a    |
|                                      | Certain Time to Execute.             |
+--------------------------------------+--------------------------------------+

If any of these limits are set to 0, then there is no limit for that resource
for that user.

To set resource limits for an account, if you do not want to change that
account's privileges, you can issue a GRANT statement with the USAGE
privilege, which has no meaning. The statement can name some or all limit
types, in any order.

Here is an example showing how to set resource limits:

GRANT USAGE ON *.* TO 'someone'@'localhost' WITH
  MAX_USER_CONNECTIONS 0
  MAX_QUERIES_PER_HOUR 200;

The resources are tracked per account, which means 'user'@'server'; not per
user name or per connection.

The count can be reset for all users using FLUSH USER_RESOURCES, FLUSH
PRIVILEGES or mariadb-admin reload.

Users with the CONNECTION ADMIN privilege (in MariaDB 10.5.2 and later) or the
SUPER privilege are not restricted by max_user_connections, max_connections,
or max_password_errors.

Per account resource limits are stored in the user table, in the mysql
database. Columns used for resources limits are named max_questions,
max_updates, max_connections (for MAX_CONNECTIONS_PER_HOUR), and
max_user_connections (for MAX_USER_CONNECTIONS).

TLS Options
-----------

By default, MariaDB transmits data between the server and clients without
encrypting it. This is generally acceptable when the server and client run on
the same host or in networks where security is guaranteed through other means.
However, in cases where the server and client exist on separate networks or
they are in a high-risk network, the lack of encryption does introduce
security concerns as a malicious actor could potentially eavesdrop on the
traffic as it is sent over the network between them.

To mitigate this concern, MariaDB allows you to encrypt data in transit
between the server and clients using the Transport Layer Security (TLS)
protocol. TLS was formerly known as Secure Socket Layer (SSL), but strictly
speaking the SSL protocol is a predecessor to TLS and, that version of the
protocol is now considered insecure. The documentation still uses the term SSL
often and for compatibility reasons TLS-related server system and status
variables still use the prefix ssl_, but internally, MariaDB only supports its
secure successors.

See Secure Connections Overview for more information about how to determine
whether your MariaDB server has TLS support.

You can set certain TLS-related restrictions for specific user accounts. For
instance, you might use this with user accounts that require access to
sensitive data while sending it across networks that you do not control. These
restrictions can be enabled for a user account with the CREATE USER, ALTER
USER, or GRANT statements. The following options are available:

+---------------------------+------------------------------------------------+
| Option                    | Description                                    |
+---------------------------+------------------------------------------------+
| REQUIRE NONE              | TLS is not required for this account, but can  |
|                           | still be used.                                 |
+---------------------------+------------------------------------------------+
| REQUIRE SSL               | The account must use TLS, but no valid X509    |
|                           | certificate is required. This option cannot    |
|                           | be combined with other TLS options.            |
+---------------------------+------------------------------------------------+
| REQUIRE X509              | The account must use TLS and must have a       |
|                           | valid X509 certificate. This option implies    |
|                           | REQUIRE SSL. This option cannot be combined    |
|                           | with other TLS options.                        |
+---------------------------+------------------------------------------------+
| REQUIRE ISSUER 'issuer'   | The account must use TLS and must have a       |
|                           | valid X509 certificate. Also, the Certificate  |
|                           | Authority must be the one specified via the    |
|                           | string issuer. This option implies REQUIRE     |
|                           | X509. This option can be combined with the     |
|                           | SUBJECT, and CIPHER options in any order.      |
+---------------------------+------------------------------------------------+
| REQUIRE SUBJECT 'subject' | The account must use TLS and must have a       |
|                           | valid X509 certificate. Also, the              |
|                           | certificate's Subject must be the one          |
|                           | specified via the string subject. This option  |
|                           | implies REQUIRE X509. This option can be       |
|                           | combined with the ISSUER, and CIPHER options   |
|                           | in any order.                                  |
+---------------------------+------------------------------------------------+
| REQUIRE CIPHER 'cipher'   | The account must use TLS, but no valid X509    |
|                           | certificate is required. Also, the encryption  |
|                           | used for the connection must use a specific    |
|                           | cipher method specified in the string cipher.  |
|                           | This option implies REQUIRE SSL. This option   |
|                           | can be combined with the ISSUER, and SUBJECT   |
|                           | options in any order.                          |
+---------------------------+------------------------------------------------+

The REQUIRE keyword must be used only once for all specified options, and the
AND keyword can be used to separate individual options, but it is not required.

For example, you can create a user account that requires these TLS options
with the following:

GRANT USAGE ON *.* TO 'alice'@'%'
 REQUIRE SUBJECT '/CN=alice/O=My Dom, Inc./C=US/ST=Oregon/L=Portland'
 AND ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some Company/CN=Peter
Parker/emailAddress=p.parker@marvel.com'
 AND CIPHER 'SHA-DES-CBC3-EDH-RSA';

If any of these options are set for a specific user account, then any client
who tries to connect with that user account will have to be configured to
connect with TLS.

See Securing Connections for Client and Server for information on how to
enable TLS on the client and server.

Roles
-----

Syntax
------

GRANT role TO grantee [, grantee ... ]
[ WITH ADMIN OPTION ]

grantee:
  rolename
  username [authentication_option]

The GRANT statement is also used to grant the use of a role to one or more
users or other roles. In order to be able to grant a role, the grantor doing
so must have permission to do so (see WITH ADMIN in the CREATE ROLE article).

Specifying the WITH ADMIN OPTION permits the grantee to in turn grant the role
to another.

For example, the following commands show how to grant the same role to a
couple different users.

GRANT journalist TO hulda;

GRANT journalist TO berengar WITH ADMIN OPTION;

If a user has been granted a role, they do not automatically obtain all
permissions associated with that role. These permissions are only in use when
the user activates the role with the SET ROLE statement.

TO PUBLIC
---------

MariaDB starting with 10.11
---------------------------

Syntax
------

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

GRANT ... TO PUBLIC grants privileges to all users with access to the server.
The privileges also apply to users created after the privileges are granted.
This can be useful when one only wants to state once that all users need to
have a certain set of privileges.

When running SHOW GRANTS, a user will also see all privileges inherited from
PUBLIC. SHOW GRANTS FOR PUBLIC will only show TO PUBLIC grants.

Grant Examples
--------------

Granting Root-like Privileges
-----------------------------

You can create a user that has privileges similar to the default root accounts
by executing the following:

CREATE USER 'alexander'@'localhost';
GRANT ALL PRIVILEGES ON  *.* to 'alexander'@'localhost' WITH GRANT OPTION;

URL: https://mariadb.com/kb/en/grant/