Database User Accounts

Overview

A separate set of database user accounts and privileges applies to each SkySQL service:

  • Database user accounts are used to access and administer the database server

  • Database user accounts are managed by connecting to the database service with an account holding sufficient privileges

Database user accounts are not used to access the SkySQL Portal. For information on access to the SkySQL Portal, see "Portal 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:

  • Replicated Transactions services launched before 2021-08-09

  • Analytics services launched before 2021-08-09

  • Distributed Transactions 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";

root and SUPER

  • The database username cannot be root.

  • The default DB######### database user account should be used as your equivalent to root.

  • MariaDB SkySQL users are not granted SUPER privilege.

  • While possible for the default user, changes to the global_priv table are unsupported.

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.

To add a user account, use the CREATE USER statement:

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

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

Remove User Accounts

To remove user accounts, use the DROP USER statement:

DROP USER 'db_user'@'192.0.2.1';

Note that you can also remove IP addresses from the allowlist, 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 Transactions

This instruction applies to Distributed Transactions services on SkySQL.

To grant privileges to a user account, use the GRANT statement:

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 Distributed Transactions.

To grant privileges to a user account, use the GRANT statement:

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.

Updating Passwords

Distributed Transactions

This instruction applies to Distributed Transactions services on MariaDB SkySQL.

To update the password for a user account, use the ALTER USER statement:

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

By default, services that use MariaDB Enterprise Server are configured 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

To list the Simple Password Check requirements, use SHOW VARIABLES to check its configuration:

SHOW VARIABLES LIKE 'simple_password_check%';

Requirements are configurable using Configuration Manager.

Use a password that contains the correct requirements.

LDAP Authentication

By default, database users are authenticated against the database. MariaDB SkySQL supports database account LDAP authentication as an option.

Two-Factor Authentication

By default, database users are authenticated by single factor, password. MariaDB SkySQL supports database account 2FA (two-factor authentication) as an option.