Database User Accounts

Each database service you deploy on MariaDB SkySQL has its own set of user accounts. These accounts are used to access and administer the database server. Database user accounts are managed by connecting to a database service using a client with an account holding sufficient privileges.

Accounts to access the SkySQL Portal are separate from database user accounts.

User Accounts

User accounts consist of a user and the host from which the user connects. If a user wishes to connect from a new location, a different user account must be created for that location or a wild card used in the hostname.

Multi-node services on MariaDB SkySQL leverage MariaDB MaxScale for load balancing, high availability, and HTAP replication.

MaxScale authenticates users based on the connecting user's IP address.

MaxScale User Accounts

This section applies to:

  • MariaDB Platform for Transactions services in HA topology launched before 2021-08-09

  • MariaDB Platform for Transactions services in Galera topology launched before 2021-08-09

  • MariaDB Platform for Analytics services launched before 2021-08-09

  • HTAP services launched before 2021-08-09

  • MariaDB Platform for Distributed SQL services that use MariaDB MaxScale

MaxScale authenticates users using the IP address from which the user connects, but when MaxScale routes the connection to an Enterprise Server or Xpand node, the node authenticates the user using the internal MaxScale IP address.

When using these services, for each user you must create a second user with the internal MaxScale host (10.%) and the same password. When managing privileges or removing users, you must also apply the appropriate changes to the user with the MaxScale host.

For example:

CREATE USER "db_user"@"10.%" IDENTIFIED BY "passwd";

Create User Accounts

User accounts are composed of a user name and the host from which the user connects. You can additionally specify a password or authentication plugin.

CREATE USER "db_user"@"192.0.2.1" IDENTIFIED BY "passwd";

Note that MariaDB Platform on MariaDB SkySQL is configured by default to validate password strength. Additional information is available in the Password Strength section.

Remove User Accounts

DROP USER 'db_user'@'192.0.2.1';

Note that you can also remove IP addresses from the whitelist, blocking their access to all services.

Privileges

Creating a user account allows it to connect to your database service. To do anything more than connect, you need to grant the user account privileges to run commands or access resources. Only grant those privileges that the user needs to function on the database service.

Distributed SQL

This instruction applies to MariaDB Platform for Distributed SQL.

GRANT CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES,
    DROP, LOCK TABLES, ALTER, CREATE VIEW, DELETE,
    INDEX, INSERT, SELECT, SHOW VIEW, TRIGGER,
    UPDATE, ALTER ROUTINE, EXECUTE
ON accounts.* TO 'db_user'@'192.0.2.1';

This allows the user to perform read, write, and administer the accounts database.

All Other Services

This instruction applies to all services except MariaDB Platform for Distributed SQL.

GRANT CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES,
   DROP, EVENT, LOCK TABLES, ALTER, CREATE VIEW, DELETE,
   DELETE HISTORY, INDEX, INSERT, SELECT, SHOW VIEW, TRIGGER,
   UPDATE, ALTER ROUTINE, EXECUTE
ON accounts.* TO 'db_user'@'192.0.2.1';

This allows the user to perform read, write, and administer the accounts database.

MariaDB SkySQL users are not granted SUPER privilege. However, current control design does not prevent the escalation of privileges through modification of global_priv table data. Changes to the global_priv table are unsupported.

Updating Passwords

Distributed SQL

This instruction applies to MariaDB Platform for Distributed SQL.

ALTER USER 'DB00000001'@'%' IDENTIFIED BY "passwd";

All Other Services

If a user needs to change their password, update it using the SET PASSWORD statement:

To update the password for a user account, use the SET PASSWORD statement:

SET PASSWORD FOR 'db_user'@'192.0.2.1' = PASSWORD('passwd');

Remember to use the PASSWORD() function to hash the password in the database.

Note that services are configured by default to validate password strength. Additional information is available in the Password Strength section.

Password Strength

Services are configured by default to use the Simple Password Check plugin to validate password strength. Statements that set passwords that do not meet the configured requirements return an error:

CREATE USER "db_user"@"192.0.2.1" IDENTIFIED BY "mypasswd";
Error 1819 (HY000): Your password does not satisfy the current policy requirements
SHOW VARIABLES LIKE 'simple_password_check%';

Requirements are configurable using Configuration Manager.

Use a password that contains the correct requirements.

LDAP Authentication

LDAP authentication via PAM is supported on the Transactional Standalone topology of MariaDB Platform for Transactions.

Create a support case to request LDAP authentication setup.

Anonymous LDAP bind is not currently supported. Bind DN and password are required.

Once configured, users are created as follows:

CREATE USER '<USERNAME>'@'%' IDENTIFIED VIA 'pam' USING 'mariadb';

Two-Factor Authentication

Two-Factor Authentication (2FA or MFA) via PAM is supported on the Transactional Standalone topology of MariaDB Platform for Transactions on GCP.

Create a support case to request Two-Factor Authentication (2FA) setup.

Once configured, users are created as follows:

CREATE USER '<USERNAME>'@'%' IDENTIFIED VIA 'pam' USING 'mariadb2fa';