MariaDB User Management
CREATE [OR REPLACE] USER [IF NOT EXISTS]
user_specification [,user_specification] …
[ REQUIRE {NONE | tls_option [[AND] tls_option] …} ]
[ WITH resource_option [resource_option] … ] ;
user_specification:
username [authentication_option]
authentication_option:
IDENTIFIED BY ‘authentication_string’
| IDENTIFIED BY PASSWORD ‘hash_string’
| IDENTIFIED {VIA|WITH} authentication_plugin
| IDENTIFIED {VIA|WITH} authentication_plugin BY ‘authentication_string’
| IDENTIFIED {VIA|WITH} authentication_plugin {USING|AS} ‘hash_string’
tls_option:
SSL | X509 | CIPHER ‘cipher’ | ISSUER ‘issuer’ | SUBJECT ‘subject‘
resource_option:
MAX_QUERIES_PER_HOUR count
| MAX_UPDATE_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
Note : These last two options (tls & resource) are only available through create command starting from 10.2.
However, you can use them in GRANT command from starting 10.0.5
For more details, refer at : https://mariadb.com/kb/en/mariadb/create-user/
| Hostname | Description |
| % | All hosts (Default wildcard if you don’t specify anything ) |
| localhost | Only local client connections |
|
Access to all IP series starting 192.16.xxx.xxx |
| _ | Underscore wildcards are also allowed |
DROP USER user_name [, user_name] …
DROP USER [IF EXISTS] user_name [, user_name] … ( >= MariaDB 10.1.3 )
SET PASSWORD [FOR user] =
{
PASSWORD(‘some password’)
| ‘encrypted password’
}
GRANT
priv_type [(column_list)] [, priv_type [(column_list)]] …
ON [object_type] priv_level
TO user [IDENTIFIED [BY [PASSWORD] ‘password’]
|{VIA|WITH} plugin_name [{USING|AS} ‘plugin_option’]]
[, user [IDENTIFIED [BY [PASSWORD] ‘password’]
|{VIA|WITH} plugin_name]
[{USING|AS} ‘plugin_option’]] user_options…
GRANT PROXY ON user_specification TO user_specification [, user_specification] … [WITH GRANT OPTION]
priv_level: * | *.* | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_name
object_type: TABLE | FUNCTION | PROCEDURE
with_option: GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
user_options: [REQUIRE {NONE | tls_option [[AND] tls_option] …}] [WITH with_option [with_option] …]
tls_option: SSL | X509 | CIPHER ‘cipher‘ | ISSUER ‘issuer‘ | SUBJECT ‘subject’
REVOKE
priv_type [(column_list)] [, priv_type [(column_list)]] …
ON [object_type] priv_level
FROM user [, user] …
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] …
MariaDB> REVOKE
->
-> UPDATE,DELETE,INSERT
->
-> ON mydb.mytbl
-> FROM ‘user’@’localhost’ ;
Query OK, 0 rows affected (0.00 sec
MariaDB> REVOKE ALL PRIVILEGES ON mydb.mytbl FROM ‘user’@’localhost’;
Query OK, 0 rows affected (0.00 sec)
CREATE [OR REPLACE] ROLE [IF NOT EXISTS] role
[WITH ADMIN
{CURRENT_USER | CURRENT_ROLE | user | role}]
CREATE ROLE dba;
CREATE ROLE admin WITH ADMIN sa_admin;
DROP ROLE [IF EXISTS] role_name [,role_name …]
DROP ROLE dba;
DROP ROLE IF EXISTS admin;
CREATE ROLE audit_bean_counters;
GRANT SELECT ON accounts.* to audit_bean_counters;
GRANT audit_bean_counters to ceo;