Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Learn account management SQL statements for MariaDB Server. This section covers commands like CREATE USER, GRANT, and REVOKE to securely manage user access and privileges within your database.
Remove a role from the system. Learn the syntax to delete defined roles and revoke them from any users or roles that currently hold them.
The DROP ROLE statement removes one or more MariaDB . To use this statement, you must have the global privilege or the privilege for the mysql database.
DROP ROLE does not disable roles for connections which selected them with . If a role has previously been set as a , DROP ROLE does not remove the record of the default role from the table. If the role is subsequently recreated and granted, it will again be the user's default. Use to explicitly remove this.
If any of the specified user accounts do not exist, ERROR 1396 (HY000)results. If an error occurs, DROP ROLE will still drop the roles that do not result in an error. Only one error is produced for all roles which have not been dropped:
Failed CREATE or DROP operations, for both users and roles, produce the same error code.
If the IF EXISTS clause is used, MariaDB will return a warning instead of an error if the role does not exist.
The same thing using the optional IF EXISTS clause:
This page is licensed: CC BY-SA / Gnu FDL
DROP ROLE [IF EXISTS] role_name [,role_name ...]CREATE ROLE creates a new row in the is_role field set to Y. It also creates a record in the If any of the specified roles already exist, ERROR 1396 (HY000) results. If an error occurs, CREATE ROLE will still create the roles that do not result in an error. The maximum length for a role is 128 characters. Role names can be quoted, as explained in the Identifier names page. Only one error is produced for all roles which have not been created:
Failed CREATE or DROP operations, for both users and roles, produce the same error code.
PUBLIC and NONE are reserved, and cannot be used as role names. NONE is used to unset a role and PUBLIC has a special use in other systems, such as Oracle, so is reserved for compatibility purposes.
For valid identifiers to use as role names, see Identifier Names.
The optional WITH ADMIN clause determines whether the current user, the current role or another user or role has use of the newly created role. If the clause is omitted, WITH ADMIN CURRENT_USER is treated as the default, which means that the current user will be able to GRANT this role to users.
If the optional OR REPLACE clause is used, it acts as a shortcut for:
When the IF NOT EXISTS clause is used, MariaDB will return a warning instead of an error if the specified role already exists. Cannot be used together with the OR REPLACE clause.
Granting the role to another user. Only user lorinda@localhost has permission to grant the developer role:
The OR REPLACE and IF NOT EXISTS clauses. The journalist role already exists:
This page is licensed: CC BY-SA / Gnu FDL
Only one role can be current at a time. Executing SET ROLE replaces the current role; it does not add to a list of current roles. This is SQL Standard compliant behavior which differs from MySQL, where you may have several current roles at a time.
The SET ROLE statement switches the current role for the session, enabling its associated permissions. To have no current role, set NONE.
If a role that doesn't exist, or to which the user has not been assigned, is specified, an ERROR 1959 (OP000): Invalid role specification error occurs.
An automatic SET ROLE is implicitly performed when a user connects if that user has been assigned a default role. See SET DEFAULT ROLE.
This page is licensed: CC BY-SA / Gnu FDL
SET ROLE { role | NONE }--Checking the current role status
SELECT CURRENT_ROLE;To be able to set a role as a default, the role must already have been granted to that user, and one needs the privileges to enable this role (if you cannot do SET ROLE X, you won't be able to do SET DEFAULT ROLE X). To set a default role for another user one needs to have write access to the mysql database.
To remove a user's default role, use SET DEFAULT ROLE NONE [ FOR user@host ]. The record of the default role is not removed if the role is dropped or revoked, so if the role is subsequently re-created or granted, it will again be the user's default role.
The default role is stored in the default_role column in the mysql.user table/view, as well as in the Information Schema APPLICABLE_ROLES table, so these can be viewed to see which role has been assigned to a user as the default.
Setting a default role for the current user:
Removing a default role from the current user:
Setting a default role for another user. The role has to have been granted to the user before it can be set as default:
Viewing mysql.user:
Removing a default role for another user
This page is licensed: CC BY-SA / Gnu FDL
Rename existing database accounts. This guide explains how to change a user's name while preserving their current privileges and properties.
RENAME USER old_user TO new_user
[, old_user TO new_user] ...The RENAME USER statement renames existing MariaDB accounts. To use it, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database. Each account is named using the same format as for the statement; for example, 'jeffrey'@'localhost'.
If you specify only the user name part of the account name, a host name part of '%' is used.
If any of the old user accounts do not exist or any of the new user accounts already exist, ERROR 1396 (HY000) results. If an error occurs, RENAME USERwill still rename the accounts that do not result in an error.
For modifying an existing account, see .
Renaming the host component of a user:
This page is licensed: GPLv2, originally from
Impersonate another user for the current session. Learn how to assume the identity and privileges of another account for testing or administration.
Certain users can perform server actions as another user:
This is implemented through the SET SESSION AUTHORIZATION statement.
This permits everything that can be done in a with an arbitrary definer.
In particular, this bypasses , , authentication, REQUIRE SSL checks, and so on.
Users are required to have the privilege.
Does not work inside , prepared statements and .
CREATE [OR REPLACE] ROLE [IF NOT EXISTS] role
[WITH ADMIN
{CURRENT_USER | CURRENT_ROLE | user | role}]ERROR 1396 (HY000): Operation CREATE ROLE failed for 'a','b','c'DROP ROLE IF EXISTS name;
CREATE ROLE name ...;CREATE ROLE journalist;
CREATE ROLE developer WITH ADMIN lorinda@localhost;SELECT USER();+-------------------+
| USER() |
+-------------------+
| henning@localhost |
+-------------------+
...GRANT developer TO ian@localhost;Access denied for user 'henning'@'localhost'SELECT USER();+-------------------+
| USER() |
+-------------------+
| lorinda@localhost |
+-------------------+GRANT m_role TO ian@localhost;CREATE ROLE journalist;
ERROR 1396 (HY000): Operation CREATE ROLE failed for 'journalist'
CREATE OR REPLACE ROLE journalist;
Query OK, 0 rows affected (0.00 sec)
CREATE ROLE IF NOT EXISTS journalist;
Query OK, 0 rows affected, 1 warning (0.00 sec)SHOW WARNINGS;+-------+------+---------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------+
| Note | 1975 | Can't create role 'journalist'; it already exists |
+-------+------+---------------------------------------------------++--------------+
| CURRENT_ROLE |
+--------------+
| NULL |
+--------------+--Setting the staff role, and verifying the switch
SET ROLE staff;
SELECT CURRENT_ROLE;+--------------+
| CURRENT_ROLE |
+--------------+
| staff |
+--------------+--switching to 'admin' role, and verifying the switch
SET ROLE admin;
SELECT CURRENT_ROLE;+--------------+
| CURRENT_ROLE |
+--------------+
| admin |
+--------------+--Removing the active role
SET ROLE NONE;
SELECT CURRENT_ROLE();+----------------+
| CURRENT_ROLE() |
+----------------+
| NULL |
+----------------+SET DEFAULT ROLE { role | NONE } [ FOR user@host ]SET DEFAULT ROLE journalist;SET DEFAULT ROLE NONE;CREATE ROLE journalist;
CREATE USER taniel;
SET DEFAULT ROLE journalist FOR taniel;
ERROR 1959 (OP000): Invalid role specification `journalist`
GRANT journalist TO taniel;
SET DEFAULT ROLE journalist FOR taniel;SELECT * FROM mysql.user WHERE user='taniel'\G*************************** 1. row ***************************
Host: %
User: taniel
...
is_role: N
default_role: journalist
...SET DEFAULT ROLE NONE FOR taniel;ERROR 1396 (HY000): Operation DROP ROLE failed for 'a','b','c'DROP ROLE journalist;DROP ROLE journalist;
ERROR 1396 (HY000): Operation DROP ROLE failed for 'journalist'
DROP ROLE IF EXISTS journalist;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Note (Code 1975): Can't drop role 'journalist'; it doesn't existRENAME USER 'foo'@'1.2.3.4' TO 'foo'@'10.20.30.40';SELECT USER(), CURRENT_USER(), DATABASE();CREATE USER 'donald', 'mickey';
RENAME USER 'donald' TO 'duck'@'localhost', 'mickey' TO 'mouse'@'localhost';+--------------------+--------------------+------------+
| user() | current_user() | database() |
+--------------------+--------------------+------------+
| msandbox@localhost | msandbox@localhost | test |
+--------------------+--------------------+------------+
1 row in set (0.000 sec)SET SESSION AUTHORIZATION foo@localhost;
SELECT USER(), CURRENT_USER(), DATABASE();+---------------+----------------+------------+
| user() | current_user() | database() |
+---------------+----------------+------------+
| foo@localhost | foo@% | NULL |
+---------------+----------------+------------+CREATE USERstatement; for example, 'jeffrey'@'localhost'. If you specify only the user name part of the account name, a host name part of '%' is used. For additional information about specifying account names, see From MariaDB 12.1, by default, if you specify an account that is currently connected, it will not be deleted until the connection is closed. The connection will not automatically be closed. The statement will complete, and a warning, "Dropped users 'user'@'host[,...]' have active connections. Use KILL CONNECTION if they should not be used anymore" issued.
In , if a user is connected, the DROP USER statement will fail with an error "Operation DROP USER failed for 'foo'@'localhost'".
If you specify an account that is currently connected, it will not be deleted until the connection is closed. The connection will not automatically be closed.
If any of the specified user accounts do not exist, ERROR 1396 (HY000)results. If an error occurs, DROP USER will still drop the accounts that do not result in an error. Only one error is produced for all users which have not been dropped:
Failed CREATE or DROP operations, for both users and roles, produce the same error code.
If the IF EXISTS clause is used, MariaDB will return a note instead of an error if the user does not exist.
The CREATE USER statement creates new MariaDB accounts. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database.
If any of the specified accounts, or any permissions f
IF EXISTS:
This page is licensed: GPLv2, originally from fill_help_tables.sql
Set or change a user's password. This guide covers the syntax for updating authentication credentials for yourself or other accounts.
The SET PASSWORD statement assigns a password to an existing MariaDB user account.
If the password is specified using the or function, the literal text of the password should be given. If the password is specified without using either function, the password should be the already-encrypted password value as returned by .
With no FOR clause, this statement sets the password for the current user. Any client that has connected to the server using a non-anonymous account can change the password for that account.
With a FOR clause, this statement sets the password for a specific account on the current server host. Only clients that have the UPDATE privilege for the mysql database can do this. The user value should be given in user_name@host_name format, where user_name and host_name are exactly as they are listed in the User and Host columns of the table (or view in current versions) entry.
The argument to and the password given to MariaDB clients can be of arbitrary length.
SET PASSWORD (with or without PASSWORD()) works for accounts authenticated via any that supports passwords stored in the table.
The , , and authentication plugins store passwords in the table.
If you run SET PASSWORD on an account that authenticates with one of these authentication plugins that stores passwords in the table, then the PASSWORD() function is evaluated by the specific authentication plugin used by the account. The authentication plugin hashes the password with a method that is compatible with that specific authentication plugin.
The , , , and authentication plugins do not store passwords in the table. These authentication plugins rely on other methods to authenticate the user.
If you attempt to run SET PASSWORD on an account that authenticates with one of these authentication plugins that doesn't store a password in the table, then MariaDB Server will issue an error like the following:
User accounts do not always require passwords to login.
The , and authentication plugins do not require a password to authenticate the user.
The authentication plugin may or may not require a password to authenticate the user, depending on the specific configuration.
The and authentication plugins require passwords for authentication, but the password can be blank. In that case, no password is required.
If you provide a password while attempting to log into the server as an account that doesn't require a password, then MariaDB server will simply ignore the password.
A user account can be defined to use multiple authentication plugins in a specific order of preference. This specific scenario may be more noticeable in these versions, since an account could be associated with some authentication plugins that require a password, and some that do not.
For example, if you had an entry with User and Host column values of 'bob' and '%.loc.gov', you would write the statement like this:
If you want to delete a password for a user, you would do:
- permits the setting of basic criteria for passwords
This page is licensed: GPLv2, originally from
DROP USER [IF EXISTS] user_name [, user_name] ...ERROR 1396 (HY000): Operation DROP USER failed for 'u1'@'%','u2'@'%'DROP USER bob;
DROP USER foo2@localhost,foo2@'127.%';DROP USER bob;
ERROR 1396 (HY000): Operation DROP USER failed for 'bob'@'%'
DROP USER IF EXISTS bob;
Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+-------+------+---------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------+
| Note | 1974 | Can't drop user 'bob'@'%'; it doesn't exist |
+-------+------+---------------------------------------------+SET PASSWORD [FOR user] =
{
PASSWORD('some password')
| OLD_PASSWORD('some password')
| 'encrypted password'
}The REVOKE statement enables system administrators to revoke privileges (or roles - see section below) from MariaDB accounts. Each account is named using the same format as for the GRANT statement; for example, 'jeffrey'@'localhost'. If you specify only the user name part of the account name, a host name part of '%' is used. For details on the levels at which privileges exist, the allowablepriv_type and priv_level values, and the syntax for specifying users and passwords, see GRANT.
To use the first REVOKE syntax, you must have theGRANT OPTION privilege, and you must have the privileges that you are revoking.
To revoke all privileges, use the second syntax, which drops all global, database, table, column, and routine privileges for the named user or users:
To use this REVOKE syntax, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database. See GRANT.
REVOKE is also used to remove a role from a user or another role that it's previously been assigned to. If a role has previously been set as a default role, REVOKE does not remove the record of the default role from the mysql.user table. If the role is subsequently granted again, it will again be the user's default. Use SET DEFAULT ROLE NONE to explicitly remove this.
REVOKE role is also permitted in prepared statements.
REVOKE role is not permitted in prepared statements.
The REVOKE PROXY syntax removes the ability for one user to proxy as another.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SET PASSWORD IS ignored FOR users authenticating via unix_socket pluginSET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');SET PASSWORD FOR 'bob'@localhost = PASSWORD("");/* 1. Revoking Privileges */
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM account_or_role [, account_or_role] ...
/* 2. Revoking All Privileges */
REVOKE ALL [PRIVILEGES], GRANT OPTION
FROM account_or_role [, account_or_role] ...
/* 3. Revoking Proxy Access */
REVOKE PROXY ON user_or_role
FROM account_or_role [, account_or_role] ...
/* 4. Revoking Roles */
REVOKE role [, role] ...
FROM account_or_role [, account_or_role] ...
/* 5. Revoking Admin Option for Roles */
REVOKE ADMIN OPTION FOR role [, role] ...
FROM account_or_role [, account_or_role] ...
/* Variable Definitions */
account_or_role:
username
| role
| PUBLIC
object_type:
TABLE
| FUNCTION
| PROCEDURE
| PACKAGE
| PACKAGE BODY
priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_nameREVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...REVOKE SUPER ON *.* FROM 'alexander'@'localhost';REVOKE role [, role ...]
FROM grantee [, grantee2 ... ]
REVOKE ADMIN OPTION FOR role FROM grantee [, grantee2]REVOKE journalist FROM huldaREVOKE PROXY ON 'dba_user'@'localhost' FROM 'app_user'@'localhost';If any of the specified user accounts do not yet exist, an error results. If an error occurs, ALTER USER will still modify the accounts that do not result in an error. Only one error is produced for all users which have not been modified.
For renaming an existing account (user name and/or host), see RENAME USER.
When the IF EXISTS clause is used, MariaDB will return a warning instead of an error for each specified user that does not exist.
For ALTER USER statements, account names are specified as the username argument in the same way as they are for CREATE USER statements. See account names from the CREATE USER page for details on how account names are specified.
CURRENT_USER or CURRENT_USER() can also be used to alter the account logged into the current session. For example, to change the current user's password to mariadb:
From MariaDB 10.4, it is possible to use more than one authentication plugin for each user account. For example, this can be useful to slowly migrate users to the more secure ed25519 authentication plugin over time, while allowing the old mysql_native_password authentication plugin as an alternative for the transitional period. See Authentication from MariaDB 10.4 for more.
When running ALTER USER, not specifying an authentication option in the IDENTIFIED VIA clause will remove that authentication method. (However this was not the case before , see MDEV-21928)
For example, a user is created with the ability to authenticate via both a password and unix_socket:
If the user's password is updated, but unix_socket authentication is not specified in the IDENTIFIED VIA clause, unix_socket authentication will no longer be permitted.
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 in the mysql.user view.
For example, if our password is mariadb, then we can set the account's password with:
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.
The only authentication plugins that this clause supports are mysql_native_password and mysql_old_password.
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 in the mysql.user view as-is.
For example, if our password is mariadb, then we can find the hash with:
And then we can set an account's password with the hash:
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.
The only authentication plugins that this clause supports are mysql_native_password and mysql_old_password.
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:
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:
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:
The USING or AS keyword cannot be used to provide a plain-text password to a plugin if it's provided as an argument to the PASSWORD() function.
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:
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 alter a user account to require these TLS options with the following:
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.
It is possible to set per-account limits for certain server resources. The following table shows the values that can be set per account:
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 .
If any of these limits are set to 0, then there is no limit for that resource for that user.
Here is an example showing how to set an account's resource limits:
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).
Besides automatic password expiry, as determined by default_password_lifetime, password expiry times can be set on an individual user basis, overriding the global setting, for example:
See User Password Expiry for more details.
Account locking permits privileged administrators to lock/unlock user accounts. No new client connections will be permitted if an account is locked (existing connections are not affected). For example:
See Account Locking for more details.
The lock_option and password_option clauses can occur in either order.
The lock_option must be placed before the password_option.
- permits the setting of basic criteria for passwords
- allow various authentication methods to be used, and new ones to be developed.
This page is licensed: CC BY-SA / Gnu FDL
ALTER USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret');ALTER USER [IF EXISTS]
user_specification [,user_specification] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH resource_option [resource_option] ...]
[lock_option] [password_option]
user_specification:
username [authentication_option]
authentication_option:
IDENTIFIED BY 'password'
| IDENTIFIED BY PASSWORD 'password_hash'
| IDENTIFIED {VIA|WITH} authentication_rule [OR authentication_rule] ...
authentication_rule:
authentication_plugin
| authentication_plugin {USING|AS} 'authentication_string'
| authentication_plugin {USING|AS} PASSWORD('password')
tls_option
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
resource_option
MAX_QUERIES_PER_HOUR COUNT
| MAX_UPDATES_PER_HOUR COUNT
| MAX_CONNECTIONS_PER_HOUR COUNT
| MAX_USER_CONNECTIONS COUNT
| MAX_STATEMENT_TIME TIME
password_option:
PASSWORD EXPIRE
| PASSWORD EXPIRE DEFAULT
| PASSWORD EXPIRE NEVER
| PASSWORD EXPIRE INTERVAL N DAY
lock_option:
ACCOUNT LOCK
| ACCOUNT UNLOCK
}ALTER USER CURRENT_USER() IDENTIFIED BY 'mariadb';CREATE USER 'bob'@'localhost'
IDENTIFIED VIA mysql_native_password USING PASSWORD('pwd')
OR unix_socket;
SHOW CREATE USER 'bob'@'localhost'\G*************************** 1. row ***************************
CREATE USER for bob@localhost: CREATE USER `bob`@`localhost`
IDENTIFIED VIA mysql_native_password
USING '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD'
OR unix_socketALTER USER 'bob'@'localhost' IDENTIFIED VIA mysql_native_password
USING PASSWORD('pwd2');
SHOW CREATE USER 'bob'@'localhost'\G*************************** 1. row ***************************
CREATE USER for bob@localhost: CREATE USER `bob`@`localhost`
IDENTIFIED BY PASSWORD '*38366FDA01695B6A5A9DD4E428D9FB8F7EB75512'ALTER USER foo2@test IDENTIFIED BY 'mariadb';SELECT PASSWORD('mariadb');+-------------------------------------------+
| PASSWORD('mariadb') |
+-------------------------------------------+
| *54958E764CE10E50764C2EECBB71D01F08549980 |
+-------------------------------------------+ALTER USER foo2@test
IDENTIFIED BY PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980';ALTER USER foo2@test IDENTIFIED VIA pam;ALTER USER foo2@test IDENTIFIED VIA pam USING 'mariadb';ALTER USER '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';ALTER USER 'someone'@'localhost' WITH
MAX_USER_CONNECTIONS 10
MAX_QUERIES_PER_HOUR 200;ALTER USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY;
ALTER USER 'monty'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'monty'@'localhost' PASSWORD EXPIRE DEFAULT;ALTER USER 'marijn'@'localhost' ACCOUNT LOCK;Create new database accounts. This guide covers the syntax for defining users, setting authentication methods, and establishing initial resource limits.
The CREATE USER statement creates new MariaDB accounts. To use it, you must have the global privilege or the privilege for the database.
For each account, CREATE USER creates a new row in the view (and the underlying table) that has no privileges.
For each account, CREATE USER creates a new row in table that has no privileges.
If any of the specified accounts, or any permissions for the specified accounts, already exist, then the server returns ERROR 1396 (HY000). If an error occurs, CREATE USER will still create the accounts that do not result in an error. Only one error is produced for all users which have not been created:
CREATE USER, , , and all produce the same error code when they fail.
See below for details on how account names are specified.
One can also create users with if does not have set. NO_AUTO_CREATE_USER is set by default.
If the optional OR REPLACE clause is used, it is basically a shortcut for:
For example:
When the IF NOT EXISTS clause is used, MariaDB will return a warning instead of an error if the specified user already exists.
For example:
If more than one authentication mechanism is declared using the OR keyword, the mechanisms are attempted in the order they are declared in the CREATE USER statement. As soon as one of the authentication mechanisms is successful, authentication is complete. If none of them is successful, the authentication has failed.
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 function prior to being stored in the / table.
For example, if our password is mariadb, then we can create the user with:
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.
The only that this clause supports are and .
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 function. It will be stored in the / table as-is.
For example, if our password is mariadb, then we can find the hash with:
And then we can create a user with the hash:
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.
The only that this clause supports are and .
The optional IDENTIFIED VIA authentication_plugin allows you to specify that the account should be authenticated by a specific . The plugin name must be an active authentication plugin as per . If it doesn't show up in that output, then you will need to install it with or .
VIA and WITH are synonyms.
For example, this could be used with the :
Some authentication plugins allow additional arguments to be specified after a USING or AS keyword. For example, the accepts a :
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 function. This is only valid for that have implemented a hook for the function. For example, the authentication plugin supports this:
One can specify many authentication plugins, they all work as alternatives ways of authenticating a user:
By default, when you create a user without specifying an authentication plugin, MariaDB uses the plugin.
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.
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 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 , , or statements. The following options are available:
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:
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 for information on how to enable TLS on the client and server.
It is possible to set per-account limits for certain server resources. The following table shows the values that can be set per account:
If any of these limits are set to 0, then there is no limit for that resource for that user.
Here is an example showing how to create a user with resource limits:
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 , or .
Per account resource limits are stored in the table, in the 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).
Account names have both a user name component and a host name component, and are specified as 'user_name'@'host_name'.
The user name and host name may be unquoted, quoted as strings using double quotes (") or
single quotes ('), or quoted as identifiers using backticks (```). You must use quotes
when using special characters (such as a hyphen) or wildcard characters. If you quote, you
must quote the user name and host name separately (for example 'user_name'@'host_name').
If the host name is not provided, it is assumed to be '%'.
Host names may contain the wildcard characters % and _. They are matched as if by
the clause. If you need to use a wildcard character literally (for example, to
match a domain name with an underscore), prefix the character with a backslash. See LIKE
for more information on escaping wildcard characters.
Host name matches are case-insensitive. Host names can match either domain names or IP
addresses. Use 'localhost' as the host name to allow only local client connections. On Linux, the loopback interface (127.0.0.1) will not match 'localhost' as it is not considered a local connection: this means that only connections via UNIX-domain sockets will match 'localhost'.
You can use a netmask to match a range of IP addresses using 'base_ip/netmask' as the
host name. A user with an IP address ip_addr will be allowed to connect if the following
condition is true:
For example, given a user:
the IP addresses satisfying this condition range from 247.150.130.0 to 247.150.130.255.
Using 255.255.255.255 is equivalent to not using a netmask at all. Netmasks cannot be used for IPv6 addresses.
Note that the credentials added when creating a user with the '%' wildcard host will not grant access in all cases. For example, some systems come with an anonymous localhost user, and when connecting from localhost this will take precedence.
Before , the host name component could be up to 60 characters in length. Starting from , it can be up to 255 characters.
User names must match exactly, including case. A user name that is empty is known as an anonymous account and is allowed to match a login attempt with any user name component. These are described more in the next section.
For valid identifiers to use as user names, see .
It is possible for more than one account to match when a user connects. MariaDB selects the first matching account after sorting according to the following criteria:
Accounts with an exact host name are sorted before accounts using a wildcard in the host name. Host names using a netmask are considered to be exact for sorting.
Accounts with a wildcard in the host name are sorted according to the position of the first wildcard character. Those with a wildcard character later in the host name sort before those with a wildcard character earlier in the host name.
Accounts with a non-empty user name sort before accounts with an empty user name.
Accounts with an empty user name are sorted last. As mentioned previously, these are known as anonymous accounts. These are described more in the next section.
The following table shows a list of example account as sorted by these criteria:
Once connected, you only have the privileges granted to the account that matched, not all accounts that could have matched. For example, consider the following commands:
If you connect as joffrey from 192.168.0.3, you will have the SELECT
privilege on the table test.t1, but not on the table test.t2. If you connect as joffrey from any other IP address, you will have the SELECT privilege on the table test.t2, but not
on the table test.t1.
Usernames can be up to 80 characters long before 10.6 and starting from 10.6 it can be 128 characters long.
Anonymous accounts are accounts where the user name portion of the account name is empty. These accounts act as special catch-all accounts. If a user attempts to log into the system from a host, and an anonymous account exists with a host name portion that matches the user's host, then the user will log in as the anonymous account if there is no more specific account match for the user name that the user entered.
For example, here are some anonymous accounts:
On some systems, the table has some entries for the ''@'%' anonymous account by default. Unfortunately, there is no matching entry in the / table, which means that this anonymous account doesn't exactly exist, but it does have privileges--usually on the default test database created by . These account-less privileges are a legacy that is leftover from a time when MySQL's privilege system was less advanced.
This situation means that you will run into errors if you try to create a ''@'%' account. For example:
The fix is to the row in the table and then execute :
Note that FLUSH PRIVILEGES is only needed if one modifies the mysql tables directly. It is not needed when using CREATE USER, DROP USER, GRANT etc.
And then the account can be created:
See for more information.
Besides automatic password expiry, as determined by , password expiry times can be set on an individual user basis, overriding the global setting, for example:
See for more details.
Account locking permits privileged administrators to lock/unlock user accounts. No new client connections will be permitted if an account is locked (existing connections are not affected). For example:
See for more details.
The lock_option and password_option clauses can occur in either order.
Prior to and , the lock_option must be placed before the password_option.
From and , the lock_option and password_option clauses can occur in either order.
This page is licensed: GPLv2, originally from
CREATE [OR REPLACE] USER [IF NOT EXISTS]
user_specification [,user_specification ...]
[REQUIRE {NONE | tls_option [[AND] tls_option ...] }]
[WITH resource_option [resource_option ...] ]
[lock_option] [password_option]
user_specification:
username [authentication_option]
authentication_option:
IDENTIFIED BY 'password'
| IDENTIFIED BY PASSWORD 'password_hash'
| IDENTIFIED {VIA|WITH} authentication_rule [OR authentication_rule ...]
authentication_rule:
authentication_plugin
| authentication_plugin {USING|AS} 'authentication_string'
| authentication_plugin {USING|AS} PASSWORD('password')
tls_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
resource_option:
MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
| MAX_STATEMENT_TIME time
password_option:
PASSWORD EXPIRE
| PASSWORD EXPIRE DEFAULT
| PASSWORD EXPIRE NEVER
| PASSWORD EXPIRE INTERVAL N DAY
lock_option:
ACCOUNT LOCK
| ACCOUNT UNLOCK
}Password Validation Plugins - permits the setting of basic criteria for passwords
Authentication Plugins - allow various authentication methods to be used, and new ones to be developed.
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.
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.
ERROR 1396 (HY000):
Operation CREATE USER failed for 'u1'@'%','u2'@'%'DROP USER IF EXISTS name;
CREATE USER name ...;CREATE USER foo2@test IDENTIFIED BY 'password';
ERROR 1396 (HY000): Operation CREATE USER failed for 'foo2'@'test'
CREATE OR REPLACE USER foo2@test IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)CREATE USER foo2@test IDENTIFIED BY 'password';
ERROR 1396 (HY000): Operation CREATE USER failed for 'foo2'@'test'
CREATE USER IF NOT EXISTS foo2@test IDENTIFIED BY 'password';
Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+-------+------+----------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------+
| Note | 1973 | Can't create user 'foo2'@'test'; it already exists |
+-------+------+----------------------------------------------------+CREATE USER foo2@test IDENTIFIED BY 'mariadb';SELECT PASSWORD('mariadb');
+-------------------------------------------+
| PASSWORD('mariadb') |
+-------------------------------------------+
| *54958E764CE10E50764C2EECBB71D01F08549980 |
+-------------------------------------------+
1 row in set (0.00 sec)CREATE USER foo2@test IDENTIFIED BY PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980';CREATE USER foo2@test IDENTIFIED VIA pam;CREATE USER foo2@test IDENTIFIED VIA pam USING 'mariadb';CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret');CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret') OR unix_socket;CREATE USER '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';CREATE USER 'someone'@'localhost' WITH
MAX_USER_CONNECTIONS 10
MAX_QUERIES_PER_HOUR 200;ip_addr & netmask = base_ipCREATE USER 'maria'@'247.150.130.0/255.255.255.0';+---------+-------------+
| User | Host |
+---------+-------------+
| joffrey | 192.168.0.3 |
| | 192.168.0.% |
| joffrey | 192.168.% |
| | 192.168.% |
+---------+-------------+CREATE USER 'joffrey'@'192.168.0.3';
CREATE USER 'joffrey'@'%';
GRANT SELECT ON test.t1 TO 'joffrey'@'192.168.0.3';
GRANT SELECT ON test.t2 TO 'joffrey'@'%';CREATE USER ''@'localhost';
CREATE USER ''@'192.168.0.3';CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;CREATE USER ''@'%';
Query OK, 0 rows affected (0.01 sec)CREATE USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY;CREATE USER 'marijn'@'localhost' ACCOUNT LOCK;Assign privileges and roles. Learn the syntax to give users or roles permission to access databases, tables, and execute specific commands.
The GRANT statement allows you to grant privileges or to accounts. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting.
Use the statement to revoke privileges granted with the GRANT statement.
Use the statement to determine what privileges an account has.
For GRANT statements, account names are specified as the username argument in the same way as they are for statements. See from the CREATE USER page for details on how account names are specified.
The GRANT statement also allows you to implicitly create accounts in some cases.
If the account does not yet exist, then GRANT can implicitly create it. To implicitly create an account with GRANT, a user is required to have the same privileges that would be required to explicitly create the account with the CREATE USER statement.
If the NO_AUTO_CREATE_USER is set, then accounts can only be created if authentication information is specified, or with a statement. If no authentication information is provided, GRANT will produce an error when the specified account does not exist, for example:
Privileges can be set globally, for an entire database, for a table or routine, or for individual columns in a table. Certain privileges can only be set at certain levels.
Global privileges do not take effect immediately and are only applied to connections created after the GRANT statement was executed.
are granted using *.* for priv_level. Global privileges include privileges to administer the database and manage user accounts, as well as privileges for all tables, functions, and procedures. Global privileges are stored in .
are granted using db_name.* for priv_level, or using just * to use the . Database privileges include privileges to create tables and functions, as well as privileges for all tables, functions, and procedures in the database. Database privileges are stored in the .
USAGE PrivilegeThe USAGE privilege grants no real privileges. The statement will show a global USAGE privilege for a newly-created user. You can use USAGE with the GRANT statement to change options like GRANT OPTIONand MAX_USER_CONNECTIONS without changing any account privileges.
ALL PRIVILEGES PrivilegeThe ALL PRIVILEGES privilege grants all available privileges. Granting all privileges only affects the given privilege level. For example, granting all privileges on a table does not grant any privileges on the database or globally.
Using ALL PRIVILEGES does not grant the special GRANT OPTION privilege.
You can use ALL instead of ALL PRIVILEGES.
GRANT OPTION PrivilegeUse the WITH GRANT OPTION clause to give users the ability to grant privileges to other users at the given privilege level. Users with the GRANT OPTION privilege can only grant privileges they have. They cannot grant privileges at a higher privilege level than they have the GRANT OPTION privilege.
The GRANT OPTION privilege cannot be set for individual columns. If you use WITH GRANT OPTION when specifying , the GRANT OPTION privilege will be granted for the entire table.
Using the WITH GRANT OPTION clause is equivalent to listing GRANT OPTION as a privilege.
The following table lists the privileges that can be granted globally. You can also grant all database, table, and function privileges globally. When granted globally, these privileges apply to all databases, tables, or functions, including those created later.
To set a global privilege, use *.* for priv_level.
Enables administration of the , including the statement and setting the system variables:
New name for . REPLICATION CLIENT can still be used, though.
Use instead. isn't included in this privilege, and is required.
Permits running SHOW commands related to the , in particular the and statements.
Enables replaying the binary log with the statement (generated by ), executing when is set to replication, and setting the session values of system variables usually included in BINLOG output, in particular:
Enables administering connection resource limit options. This includes ignoring the limits specified by and , and allowing one extra connection over
The statements specified in are not executed, owned by other users is permitted. The following connection-related system variables can be changed:
Create a user using the statement, or implicitly create a user with the GRANT statement.
Execute , , and statements.
FEDERATED ADMIN is not available.
Read and write files on the server, using statements like or functions like . Also needed to create outward tables. MariaDB server must have the permissions to access those files.
Grant global privileges. You can only grant privileges that you have.
Show information about the active processes, for example via or . If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MariaDB account that you are using).
User ignores the system variable, and can perform write operations even when the read_only option is active.
A user with that privilege can also change the (global) value of read_only.
The READ_ONLY ADMIN privilege has been removed from . The benefit of this is that one can remove the READ_ONLY ADMIN privilege from all users and ensure that no one can make any changes on any non-temporary tables. This is useful on replicas when one wants to ensure that the replica is kept identical to the primary.
User ignores the system variable, and can perform write operations even when the read_only option is active.
A user with that privilege can also change the (global) value of
Execute statements or equivalent commands.
Execute and informative statements. Renamed to (but still supported as an alias for compatibility reasons).
Execute and informative statements. is part of .
Execute and informative statements. Using instead is still supported as an alias.
Execute and informative statements. Renamed to in (but still supported as an alias for compatibility reasons). was part of prior to .
Permits administration of primary servers, including the statement, and setting the , , and system variables.
REPLICATION MASTER ADMIN is not available.
Permit and .
See Reasoning tab as to why this was implemented.
When a user would upgrade from an older major release to a minor release prior to , certain user accounts would lose capabilities. For example, a user account that had the REPLICATION CLIENT privilege in older major releases could run , but after upgrading to a minor release prior to , they could no longer run , because that statement was changed to require the REPLICATION REPLICA ADMIN privilege.
This issue is fixed in with this new privilege, which now grants the user the ability to execute SHOW [ALL] (SLAVE | REPLICA) STATUS.
When a database is upgraded from an older major release to MariaDB Server 10.5.9 or later, any user accounts with the REPLICATION CLIENT or REPLICATION SLAVE
Synonym for .
REPLICATION REPLICA is not available.
Accounts used by replica servers on the primary need this privilege. This is needed to get the updates made on the master. is an alias for REPLICATION SLAVE.
Accounts used by replica servers on the primary need this privilege. This is needed to get the updates made on the master.
Permits administering replica servers, including , , , , statements, replaying the binary log with the statement (generated by ), and setting the system variables:
Enables setting the DEFINER when creating , , and .
SET USER isn't available.
List all databases using the statement. Without the SHOW DATABASES privilege, you can still issue the SHOW DATABASES statement, but it will only list databases containing tables on which you have privileges.
Shut down the server using or the command.
Execute superuser statements: , (users who do not have this privilege can only KILL their own threads), , , or the command. Also, this permission allows the user to write data even if the startup option is set, enable or disable logging, enable or disable replication on replica, specify a DEFINER for statements that support that clause, connect once reaching the MAX_CONNECTIONS. If a statement has been specified for the option, that command will not be executed when a user with SUPER privileges connects to the server.
The SUPER privilege has been split into multiple smaller privileges to allow for more fine-grained privileges (). The privileges are:
The following table lists the privileges that can be granted at the database level. You can also grant all table and function privileges at the database level. Table and function privileges on a database apply to all tables or functions in that database, including those created later.
To set a privilege for a database, specify the database usingdb_name.* for priv_level, or just use * to specify the current. database.
Some table privileges can be set for individual columns of a table. To use column privileges, specify the table explicitly and provide a list of column names after the privilege type. For example, the following statement would allow the user to read the names and positions of employees, but not other information from the same table, such as salaries.
The PROXY privilege allows one user to proxy as another user, which means their privileges change to that of the proxy user, and the function returns the user name of the proxy user.
The PROXY privilege only works with authentication plugins that support it. The default authentication plugin does not support proxy users.
The authentication plugin is the only plugin included with MariaDB that currently supports proxy users. The PROXY privilege is commonly used with the authentication plugin to enable .
For example, to grant the PROXY privilege to an that authenticates with the authentication plugin, you could execute the following:
A user account can only grant the PROXY privilege for a specific user account if the granter also has the PROXY privilege for that specific user account, and if that privilege is defined WITH GRANT OPTION. For example, the following example fails because the granter does not have the PROXY privilege for that specific user account at all:
And the following example fails because the granter does have the PROXY privilege for that specific user account, but it is not defined WITH GRANT OPTION:
But the following example succeeds because the granter does have the PROXY privilege for that specific user account, and it is defined WITH GRANT OPTION:
A user account can grant the PROXY privilege for any other user account if the granter has the PROXY privilege for the ''@'%' anonymous user account, like this:
For example, the following example succeeds because the user can grant the PROXY privilege for any other user account:
The default root user accounts created by have this privilege. For example:
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.
The authentication options for the GRANT statement are the same as those for the statement.
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 function prior to being stored.
For example, if our password is mariadb, then we can create the user with:
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 statement to change a user's password with GRANT.
The only that this clause supports are and .
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 function. It will be stored as-is.
For example, if our password is mariadb, then we can find the hash with:
And then we can create a user with the hash:
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 tastatement to change a user's password with GRANT.
The only that this clause supports are and .
The optional IDENTIFIED VIA authentication_plugin allows you to specify that the account should be authenticated by a specific . The plugin name must be an active authentication plugin as per . If it doesn't show up in that output, then you will need to install it with or .
For example, this could be used with the :
Some authentication plugins allow additional arguments to be specified after a USING or AS keyword. For example, the accepts a :
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 function. This is only valid for that have implemented a hook for the function. For example, the authentication plugin supports this:
One can specify many authentication plugins, they all work as alternative ways of authenticating a user:
By default, when you create a user without specifying an authentication plugin, MariaDB uses the plugin.
It is possible to set per-account limits for certain server resources. The following table shows the values that can be set per account:
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:
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 , or .
Users with the CONNECTION ADMIN privilege or the SUPER privilege are not restricted by max_user_connections or max_password_errors , and they are allowed one additional connection when max_connections is reached.
Users with the CONNECTION ADMIN privilege or the SUPER privilege are restricted by max_user_connections or max_password_errors , and they are not allowed one additional connection when max_connections
Per account resource limits are stored in the table, in the 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).
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 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 , , or statements. The following options are available:
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:
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 for information on how to enable TLS on the client and server.
The GRANT statement is also used to grant the use of a 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 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.
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 statement.
Be careful to avoid conflicting role and user names. In case of a conflict, the role name takes precedence, as shown in the following example. The GRANT statement assigns privileges to the role, not to the user:
TO PUBLIC is unavailable.
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 , a user will also see all privileges inherited from PUBLIC. will only show TO PUBLIC grants.
You can create a user that has privileges similar to the default root accounts by executing the following:
allows you to start MariaDB without GRANT. This is useful if you lost your root password.
/* 1. Granting Privileges */
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO account_or_role [, account_or_role] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH grant_option_list]
/* 2. Granting Proxy Access */
GRANT PROXY ON user_or_role
TO account_or_role [, account_or_role] ...
[WITH GRANT OPTION]
/* 3. Granting Roles */
GRANT role [, role] ...
TO account_or_role [, account_or_role] ...
[WITH ADMIN OPTION]
/* Variable Definitions */
account_or_role:
username [authentication_option]
| role
| PUBLIC
authentication_option:
IDENTIFIED BY 'password'
| IDENTIFIED BY PASSWORD 'password_hash'
| IDENTIFIED {VIA | WITH} authentication_rule [OR authentication_rule ...]
authentication_rule:
authentication_plugin
| authentication_plugin {USING | AS} 'authentication_string'
| authentication_plugin {USING | AS} PASSWORD('password')
priv_type:
ALL [PRIVILEGES]
| ALTER | ALTER ROUTINE | BINLOG ADMIN | BINLOG MONITOR | BINLOG REPLAY
| CONNECTION ADMIN | CREATE | CREATE ROUTINE | CREATE TABLESPACE
| CREATE TEMPORARY TABLES | CREATE USER | CREATE VIEW
| DELETE | DELETE HISTORY | DROP | EVENT | EXECUTE | FEDERATED ADMIN
| FILE | GRANT OPTION | INDEX | INSERT | LOCK TABLES | PROCESS
| READ ONLY ADMIN | RELOAD | REPLICATION CLIENT | REPLICATION MASTER ADMIN
| REPLICATION SLAVE | REPLICATION SLAVE ADMIN | REFERENCES
| SELECT | SET USER | SHOW CREATE ROUTINE | SHOW DATABASES | SHOW VIEW
| SHUTDOWN | SLAVE MONITOR | SUPER | TRIGGER | UPDATE | USAGE
object_type:
TABLE
| FUNCTION
| PROCEDURE
| PACKAGE
| PACKAGE BODY
priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
grant_option_list:
grant_option [grant_option] ...
grant_option:
GRANT OPTION
| resource_option
resource_option:
MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
| MAX_STATEMENT_TIME time
tls_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'db_name.tbl_namefor priv_level, or using just tbl_name to specify a table in the current database. The TABLE keyword is optional. Table privileges include the ability to select and change data in the table. Certain table privileges can be granted for individual columns.Column privileges priv_type are granted by specifying a table for priv_level and providing a column list after the privilege type. They allow you to control exactly which columns in a table users can select and change.
Function privileges priv_type are granted using FUNCTION db_name.routine_name for priv_level, or using just FUNCTION routine_name to specify a function in the current database.
Procedure privileges priv_type are granted using PROCEDURE db_name.routine_name for priv_level, or using just PROCEDURE routine_name to specify a procedure in the current database.
sql_log_bin and
BINLOG ADMIN isn't available.
BINLOG REPLAY isn't available.
thread_pool_size, and
read_onlyThe READ_ONLY ADMIN privilege is included in SUPER.
READ\_ONLY ADMIN isn't available.
REPLICA MONITORHowever, when a database is upgraded from an early 10.5 minor release to 10.5.9 and later, the user will have to fix any user account privileges manually.
REPLICA MONITOR is not available.
sync_relay_log, and
REPLICATION SLAVE ADMIN is not available.
These grants are no longer a part of SUPER and need to be granted separately.
The READ_ONLY ADMIN privilege has been removed from SUPER. The benefit of this is that one can remove the READ_ONLY ADMIN privilege from all users and ensure that no one can make any changes on any non-temporary tables. This is useful on replicas when one wants to ensure that the replica is kept identical to the primary (MDEV-29596).
The SUPER privilege has been split into multiple smaller privileges to allow for more fine-grained privileges (MDEV-21743). The privileges are:
These grants are part of SUPER and don't need to be granted separately.
Use the SUPER privilege.
LOCK TABLES
Acquire explicit locks using the statement; you also need to have the SELECT privilege on a table, in order to lock it.
SHOW CREATE ROUTINE
Permit viewing the SHOW CREATE definition statement of a routine, for example , even if not the routine owner. From .
GRANT OPTION
Grant table privileges. You can only grant privileges that you have.
INDEX
Create an index on a table using the statement. Without the INDEX privilege, you can still create indexes when creating a table using the statement if the you have the CREATE privilege, and you can create indexes using the statement if you have the ALTER privilege.
INSERT
Add rows to a table using the statement. The INSERT privilege can also be set on individual columns; see below for details.
REFERENCES
Unused.
SELECT
Read data from a table using the statement. The SELECT privilege can also be set on individual columns; see below for details.
SHOW VIEW
Show the statement to create a view using the statement.
TRIGGER
Required to run the , , and statements. When another user activates a trigger (running INSERT, UPDATE, or DELETE statements on the associated table), for the trigger to execute, the user that defined the trigger should have the TRIGGER privilege for the table. The user running the INSERT, UPDATE, or DELETE statements on the table is not required to have the TRIGGER privilege.
UPDATE
Update existing rows in a table using the statement. UPDATE statements usually include a WHERE clause to update only certain rows. You must have SELECT privileges on the table or the appropriate columns for the WHERE clause. The UPDATE privilege can also be set on individual columns; see below for details.
Password Validation Plugins - permits the setting of basic criteria for passwords
Authentication Plugins - allow various authentication methods to be used, and new ones to be developed.
CREATE
Create a database using the CREATE DATABASE statement, when the privilege is granted for a database. You can grant the CREATE privilege on databases that do not yet exist. This also grants the CREATE privilege on all tables in the database.
CREATE ROUTINE
Create Stored Programs using the CREATE PROCEDURE and CREATE FUNCTION statements.
CREATE TEMPORARY TABLES
Create temporary tables with the CREATE TEMPORARY TABLE statement. This privilege enable writing and dropping those temporary tables
DROP
Drop a database using the DROP DATABASE statement, when the privilege is granted for a database. This also grants the DROP privilege on all tables in the database.
EVENT
Create, drop and alter EVENTs.
GRANT OPTION
Grant database privileges. You can only grant privileges that you have.
ALTER
Change the structure of an existing table using the ALTER TABLE statement.
CREATE
Create a table using the CREATE TABLE statement. You can grant the CREATE privilege on tables that do not yet exist.
CREATE VIEW
Create a view using the CREATE_VIEW statement.
DELETE
Remove rows from a table using the DELETE statement.
DELETE HISTORY
Remove historical rows from a table using the DELETE HISTORY statement. Displays as DELETE VERSIONING ROWS when running SHOW PRIVILEGES until (MDEV-20382). If a user has the SUPER privilege but not this privilege, running mariadb-upgrade will grant this privilege as well.
DROP
Drop a table using the DROP TABLE statement or a view using the DROP VIEW statement. Also required to execute the TRUNCATE TABLE statement.
INSERT (column_list)
Add rows specifying values in columns using the INSERT statement. If you only have column-level INSERT privileges, you must specify the columns you are setting in the INSERT statement. All other columns will be set to their default values, or NULL.
REFERENCES (column_list)
Unused.
SELECT (column_list)
Read values in columns using the SELECT statement. You cannot access or query any columns for which you do not have SELECT privileges, including in WHERE, ON, GROUP BY, and ORDER BY clauses.
UPDATE (column_list)
Update values in columns of existing rows using the UPDATE statement. UPDATE statements usually include a WHERE clause to update only certain rows. You must have SELECT privileges on the table or the appropriate columns for the WHERE clause.
ALTER ROUTINE
Change the characteristics of a stored function using the ALTER FUNCTION statement.
EXECUTE
Use a stored function. You need SELECT privileges for any tables or columns accessed by the function.
GRANT OPTION
Grant function privileges. You can only grant privileges that you have.
ALTER ROUTINE
Change the characteristics of a stored procedure using the ALTER PROCEDURE statement.
EXECUTE
Execute a stored procedure using the CALL statement. The privilege to call a procedure may allow you to perform actions you wouldn't otherwise be able to do, such as insert rows into a table.
GRANT OPTION
Grant procedure privileges. You can only grant privileges that you have.
ALTER ROUTINE
Change the characteristics of a stored package.
EXECUTE
Execute a stored package or package body.
GRANT OPTION
Grant package privileges. You can only grant privileges that you have.
PROXY
Permits one user to be a proxy for another.
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.
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.
SHOW VARIABLES LIKE '%sql_mode%' ;+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+GRANT USAGE ON *.* TO 'user123'@'%' IDENTIFIED BY '';ERROR 1133 (28000): Can't find any matching row in the user tableGRANT USAGE ON *.* TO 'user123'@'%'
IDENTIFIED VIA PAM using 'mariadb' require ssl ;
Query OK, 0 rows affected (0.00 sec)SELECT host, user FROM mysql.user WHERE user='user123' ;+------+----------+
| host | user |
+------+----------+
| % | user123 |
+------+----------+GRANT SELECT (name, position) ON Employee TO 'jeffrey'@'localhost';GRANT EXECUTE ON PROCEDURE mysql.create_db TO maintainer;CREATE USER 'dba'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' ;
CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb';
GRANT PROXY ON 'dba'@'%' TO ''@'%';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' |
+-----------------------------------------------------------------------------------------------------------------------+GRANT PROXY ON 'dba'@'localhost' TO 'bob'@'localhost';
ERROR 1698 (28000): Access denied for user 'alice'@'localhost'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' |
| GRANT PROXY ON 'dba'@'localhost' TO 'alice'@'localhost' |
+-----------------------------------------------------------------------------------------------------------------------+GRANT PROXY ON 'dba'@'localhost' TO 'bob'@'localhost';
ERROR 1698 (28000): Access denied for user 'alice'@'localhost'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 'dba'@'localhost' TO 'alice'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+GRANT PROXY ON 'dba'@'localhost' TO 'bob'@'localhost';GRANT PROXY ON ''@'%' TO 'dba'@'localhost' WITH GRANT OPTION;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)GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION;GRANT USAGE ON *.* TO foo2@test IDENTIFIED BY 'mariadb';SELECT PASSWORD('mariadb');+-------------------------------------------+
| PASSWORD('mariadb') |
+-------------------------------------------+
| *54958E764CE10E50764C2EECBB71D01F08549980 |
+-------------------------------------------+
1 row in set (0.00 sec)GRANT USAGE ON *.* TO foo2@test IDENTIFIED BY
PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980';GRANT USAGE ON *.* TO foo2@test IDENTIFIED VIA pam;GRANT USAGE ON *.* TO foo2@test IDENTIFIED VIA pam USING 'mariadb';CREATE USER safe@'%' IDENTIFIED VIA ed25519
USING PASSWORD('secret');CREATE USER safe@'%' IDENTIFIED VIA ed25519
USING PASSWORD('secret') OR unix_socket;GRANT USAGE ON *.* TO 'someone'@'localhost' WITH
MAX_USER_CONNECTIONS 0
MAX_QUERIES_PER_HOUR 200;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';GRANT role TO grantee [, grantee ... ]
[ WITH ADMIN OPTION ]
grantee:
rolename
username [authentication_option]GRANT journalist TO hulda;
GRANT journalist TO berengar WITH ADMIN OPTION;CREATE USER alice IDENTIFIED BY 'password';
CREATE ROLE alice;
GRANT select, insert on db.* TO alice;GRANT <privilege> ON <DATABASE>.<object> TO PUBLIC;
REVOKE <privilege> ON <DATABASE>.<object> FROM PUBLIC;CREATE USER 'alexander'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'alexander'@'localhost' WITH GRANT OPTION;This page is licensed: GPLv2, originally from fill_help_tables.sql