e granter has the PROXY privilege for the
''@'%' anonymous user account, like this:
 
GRANT PROXY ON ''@'%' TO 'dba'@'localhost' WITH
GRANT OPTION;
 
For example, the following example succeeds because the user
can grant the PROXY privilege for any other user account:
 
SELECT USER(), CURRENT_USER();
+-----------------+-----------------+
| USER() | CURRENT_USER() |
+-----------------+-----------------+
| alice@localhost | alice@localhost |
+-----------------+-----------------+
 
SHOW GRANTS;
 
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for alice@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost'
IDENTIFIED BY PASSWORD
'*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' WITH GRANT
OPTION |
| GRANT PROXY ON ''@'%' TO 'alice'@'localhost' WITH
GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
 
GRANT PROXY ON 'app1_dba'@'localhost' TO
'bob'@'localhost';
 
Query OK, 0 rows affected (0.004 sec)
 
GRANT PROXY ON 'app2_dba'@'localhost' TO
'carol'@'localhost';
 
Query OK, 0 rows affected (0.004 sec)
 
The default root user accounts created by mysql_install_db
have this privilege. For example:
 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH
GRANT OPTION;
 
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH
GRANT OPTION;
 
This allows the default root user accounts to grant the
PROXY privilege for any other user account, and it also
allows the default root user accounts to grant others the
privilege to do the same.
 
Authentication Options
 
The authentication options for the GRANT statement are the
same as those for the CREATE USER statement.
 
IDENTIFIED BY 'password'
 
The optional IDENTIFIED BY clause can be used to provide an
account with a password. The password should be specified in
plain text. It will be hashed by the PASSWORD function prior
to being stored to the mysql.user table.
 
For example, if our password is mariadb, then we can create
the user with:
 
GRANT USAGE ON *.* TO foo2@test IDENTIFIED BY 'mariadb';
 
If you do not specify a password with the IDENTIFIED BY
clause, the user
will be able to connect without a password. A blank password
is not a wildcard
to match any password. The user must connect without
providing a password if no
password is set.
 
If the user account already exists and if you provide the
IDENTIFIED BY clause, then the user's password will be
changed. You must have the privileges needed for the SET
PASSWORD
statement to change a user's password with GRANT.
 
The only authentication plugins that this clause supports
are mysql_native_password and mysql_old_password.
 
IDENTIFIED BY PASSWORD 'password_hash'
 
The optional IDENTIFIED BY PASSWORD clause can be used to
provide an account with a password that has already been
hashed. The password should be specified as a hash that was
provided by the PASSWORD function. It will be stored to the
mysql.user table as-is.
 
For example, if our password is mariadb, then we can find
the hash with:
 
SELECT PASSWORD('mariadb');
+-------------------------------------------+
| PASSWORD('mariadb') |
+-------------------------------------------+
| *54958E764CE10E50764C2EECBB71D01F08549980 |
+-------------------------------------------+
1 row in set (0.00 sec)
 
And then we can create a user with the hash:
 
GRANT USAGE ON *.* TO foo2@test IDENTIFIED BY PASSWORD
'*54958E764CE10E50764C2EECBB71D01F08549980';
 
If you do not specify a password with the IDENTIFIED BY
clause, the user
will be able to connect without a password. A blank password
is not a wildcard
to match any password. The user must connect without
providing a password if no
password is set.
 
If the user account already exists and if you provide the
IDENTIFIED BY clause, then the user's password will be
changed. You must have the privileges needed for the SET
PASSWORD
statement to change a user's password with GRANT.
 
The only authentication plugins that this clause supports
are mysql_native_password and mysql_old_password.
 
IDENTIFIED {VIA|WITH} authentication_plugin
 
The optional IDENTIFIED VIA authentication_plugin allows you
to specify that the account should be authenticated by a
specific authentication plugin. The plugin name must be an
active authentication plugin as per SHOW PLUGINS. If it
doesn't show up in that output, then you will need to
install it with INSTALL PLUGIN or INSTALL SONAME.
 
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.
 
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');
 
One can specify many authentication plugins, they all works
as alternatives ways of authenticating a user:
 
CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING
PASSWORD('secret') OR unix_socket;
 
Resource Limit Options
 
MariaDB 10.2.0 introduced a number of 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 mysqladmin reload.
 
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 one of the methods
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 'TLSv1.2';
 
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
 
Roles were introduced in MariaDB 10.0.5.
 
Syntax
------ 
GRANT role TO grantee [, grantee2 ... ]
[ WITH ADMIN OPTION ]
 
The GRANT statement is also used to grant the use 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.
 
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/