Account Locking and Password Expiration Overview

Robert Bîndar

Software Developer @MariaDB Foundation

MariaDB Unconference�New York 23-24 February 2019

2

#define

Account Locking:

Offers the ability to mark an account locked and deny any subsequent connection requests for that account during the authentication stage.

Password Expiration:

A new connection for an account with an expired password is either denied or only allowed to execute SET PASSWORD to change the password (sandbox mode), depending on various server and client settings.

The feature supports expiring passwords with immediate effect, per-account automatic expiration as well as global policies for automatic expiration.

Account Locking and Password Expiration Overview

3

• Compliance with latest security standards checklists

​

• Satisfy use cases where the minimum privilege package = no client connection at all

​

• Offer DBAs the possibility of using some integrated, straightforward security solutions (that they were already using anyway with various home built hacks).

​

• Keep up with MySQL

Account Locking and Password Expiration Overview

​

Motivation

4

• Disable by default any part of these features that can be annoying or intrusive for our users

​

• Make use of the new JSON user table

​

• But also have the features fully compatible with MySQL 5.7

​

• Preserve compatibility with previous MariaDB versions

Account Locking and Password Expiration Overview

​

Initial Requirements

5

Account Locking

Account Locking and Password Expiration Overview

​

6

• Creates a user account that is locked

Account Locking and Password Expiration Overview

​

Account Locking

​

MariaDB [(none)]> CREATE USER user@localhost ACCOUNT LOCK;

Query OK, 0 rows affected (0.00 sec)�

​

7

• SHOW CREATE USER displays the locking status of an account

Account Locking and Password Expiration Overview

​

Account Locking

​

MariaDB [(none)]> SHOW CREATE USER user@localhost;

+---------------------------------------------+

| CREATE USER for user@localhost |

+---------------------------------------------+

| CREATE USER 'user'@'localhost' ACCOUNT LOCK |

+---------------------------------------------+

1 row in set (0.000 sec)

8

• Altering an existing account to lock/unlock

Account Locking and Password Expiration Overview

​

Account Locking

​

MariaDB [(none)]> ALTER USER user@localhost ACCOUNT UNLOCK;

Query OK, 0 rows affected (0.00 sec)

​

MariaDB [(none)]> ALTER USER user@localhost ACCOUNT LOCK;

Query OK, 0 rows affected (0.00 sec)

​

9

• Attempting a connection using a locked account returns ER_ACCOUNT_HAS_BEEN_LOCKED

Account Locking and Password Expiration Overview

​

Account Locking

bindar@computer:~/MariaDB/server$ ./client/mysql -uuser

ERROR 4151 (HY000): Access denied, this account is locked�

​

10

Whether an account is locked or not is checked during the authentication phase (including COM_CHANGE_USER).

Locking an account does not affect existing connections.

Account Locking and Password Expiration Overview

​

Account Locking

11

Password Expiration

Account Locking and Password Expiration Overview

​

12

• Creates a new account and expire the password with immediate effect

Account Locking and Password Expiration Overview

​

Password Expiration

​

MariaDB [(none)]> CREATE USER user@localhost PASSWORD EXPIRE;

Query OK, 0 rows affected (0.00 sec)�

​

13

Password Expiration

Disconnect Mode:

In this mode, any new connections for accounts with expired passwords are refused.

Sandbox Mode:

A new connection for an account with the password expired is only allowed to execute SET PASSWORD to change the account password, attempts to execute any other statements are rejected.

Account Locking and Password Expiration Overview

14

• disconnect_on_expired_password system var (default OFF) controls how

clients unaware of the sandbox mode are treated

​

• But --connect-expired-password arg passed to the client takes precedence and the server knows to put the connection in sandbox mode

​

• Also interactive client connections are always put in sandbox mode

​

• In the MariaDB C Connector, MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS can be passed to mysql_options to achieve a similar behavior

Account Locking and Password Expiration Overview

​

Password Expiration

15

• The client is still able to connect to the server, but only the SET PASSWORD

statement is allowed for changing the account password

• Executing any other statement returns ER_MUST_CHANGE_PASSWORD

Account Locking and Password Expiration Overview

​

Password Expiration

​

$ mysql -u user

Welcome to the MariaDB monitor.

MariaDB [(none)]> SELECT CURRENT_USER;

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

MariaDB [(none)]> SET PASSWORD= PASSWORD(‘abc’);

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT CURRENT_USER;

+-----------------+

| CURRENT_USER |

+-----------------+

| user@localhost |

+-----------------+

1 row in set (0.00 sec)

​

16

• Trying to connect using an expired password account returns ER_MUST_CHANGE_PASSWORD_LOGIN

Account Locking and Password Expiration Overview

​

Password Expiration

​

$ mysql -u user

ERROR 1862 (HY000): Your password has expired. To log in you must change

it using a client that supports expired passwords

​

​

​

​

17

• default_password_lifetime (default 0) controls the global automatic password expiration policy

​

• Can be set at runtime using SET GLOBAL, specified in the config file or as server arg (--default-pasword-lifetime=90)

​

• default_password_lifetime=0 means passwords never expire

​

• default_password_lifetime=90 means passwords expire every 90 days

​

• But per-account expiration policies override the global policies

Account Locking and Password Expiration Overview

​

Password Expiration

18

• The password of this account will never expire regardless of what global policies say

Account Locking and Password Expiration Overview

​

Password Expiration

MariaDB [(none)]> ALTER USER user@localhost PASSWORD EXPIRE NEVER;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW CREATE USER user@localhost;

+-------------------------------------------------------+

| CREATE USER for user@localhost |

+-------------------------------------------------------+

| CREATE USER 'user'@'localhost' PASSWORD EXPIRE NEVER |

+-------------------------------------------------------+

1 row in set (0.00 sec)

​

�

​

19

• default_password_lifetime is overriden and for this account the password will expire every 30 days

Account Locking and Password Expiration Overview

​

Password Expiration

MariaDB [(none)]> ALTER USER user@localhost PASSWORD EXPIRE INTERVAL 30 DAY;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW CREATE USER user@localhost;

+-----------------------------------------------------------------+

| CREATE USER for user@localhost |

+-----------------------------------------------------------------+

| CREATE USER 'user'@'localhost' PASSWORD EXPIRE INTERVAL 30 DAY |

+-----------------------------------------------------------------+

1 row in set (0.00 sec)

​

20

• By specifying DEFAULT as per-account policy, the value in the default_password_lifetime sys var will be used.

Account Locking and Password Expiration Overview

​

Password Expiration

MariaDB [(none)]> ALTER USER user@localhost PASSWORD EXPIRE DEFAULT;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW CREATE USER user@localhost;

+---------------------------------+

| CREATE USER for user@localhost |

+---------------------------------+

| CREATE USER 'user'@'localhost' |

+---------------------------------+

1 row in set (0.00 sec)

​

21

Title of the presentation, if necessary shortened

Sponsors

22

Thank You!

​

Contact details:

robert@mariadb.org

​

About:

mariadb.org/about/staff/robert-bindar/

Account Locking and Password Expiration Overview

​