maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Robert!

On Jan 07, Robert Bindar wrote:
> Hi!
> 
> With this email I intend to kick off a debate on the design of
> password expiration in MariaDB.
> Given this feature may impact the end user experience, I would love if
> the experienced folks here will step in and help us come up with the
> best approach possible. Random thoughts are highly appreciated as
> well.
> 
> *-- Some thoughts*
> 
> I have been trying to wrap my head around how password management
> should look like in MariaDB, my conclusion so far is that the less
> intrusive it is the greater are our chances for the common user to
> like it.
> 
> MySQL released the first version of password expiration in 5.6.6
> (~2012, didn't change much since then). From my research, it seems the
> common user was caught off-guard by this feature, got annoyed by its
> restrictiveness and chose to disable it permanently. I believe we
> should avoid this situation as much as possible.
> I can see the whole story going, nevertheless, in a different
> direction for our business clients.

I suspect the password expiration is more useful in satisfying various
enterprise security checklists and requirements than in actually
improving security.

For example,
https://cryptosmith.com/password-sanity/exp-harmful/
http://blog.axantum.com/2009/12/password-expiration-is-meaningless.html
etc, you can google up as many as you'd like

> **-- How MySQL does it*
> 
> This is a short summary of what's in the MySQL docs about the password
> management system. I'll have it here so people that are unaware of how
> this feature works in MySQL are up to speed.
> 
> MySQL introduced 3 new columns in mysql.user:
> 
> /** *//*password_expired* (Y/N)//
> //    /** If 'Y', the user's password will be considered expired *///
> //** **password_last_changed* (timestamp)//
> //    /** The datetime when the user changed the password last time *///
> //** **password_lifetime* (smallint)//
> //    /** The number of days until the password expires (it's not clear whether//
> //        the days are counted since password_last_changed or since when the//
> //        expiration statement was executed) *//
> 
> Given we support MySQL 5.7, we have to have them as well I believe.
> 
> The /CREATE USER/ and /ALTER USER/ statements were extended as follows:**
> 
> *** /[ALTER|CREATE] USER user@host PASSWORD EXPIRE [option];/
> 
> - If no option is specified, the password is expired with immediate effect.
> - If option is /DEFAULT/, the /*password_lifetime*/ column is set to the 
> number of days specified in the */default_password_lifetime/* system var.
> - If option is /NEVER/, the user password_lifetime column is set to 0, the
> password is never expired for this account.
> - Option can also be /INTERVAL n DAY/, in which case password_lifetime 
> is set to /'n'/ (see column definition above).
> 
> *Password expiration behavior*
>      MySQL controls the effect of an expired password with the
> */disconnect_on_expired_password/* system var.
>      For active sessions, when a password is expired, the server either
> disconnects the client or puts it in a sandbox mode.
>      New client connections can specify an option so the server knows not to
> disconnect them, otherwise the new connection is handled according to the
> system var above.
>      In sandbox mode, the client is able to execute only /SET PASSWORD/,
> /ALTER USER/ and /SET/ statements for vars.
> 
> *Other additions*
> - C library API for specifying disconnect/sandbox option

this should be already done, CONC-314.

> - mysql client new */--connect-expired-password/* option
> *
> **-- Things to improve*
> 
> Below are a few points I found so far that I believe should be discussed
> before moving on to a prototype implementation:
> 
> *1.* Syntax wise
> 
>      Do we want to follow the path of MySQL and extend the /ALTER USER/ and
> /CREATE USER/ statements for password expiration support?
> *Opinion**|* The /ALTER/CREATE USER/ statements seem to be doing quite a lot
> of things already. Introducing a new statement such as
> 
> *    * */PASSWORD EXPIRE user@host [option];/
> 
> may be worth exploring.

I think ALTER/CREATE USER is perfectly expected to set or modify various
properties of an account. So it's quite fitting to use it here.

"PASSWORD EXPIRE" looks strange. First, SQL statements start with a
verb. Second "PASSWORD EXPIRE" (or "EXPIRE PASSWORD", if you'd like)
means to expire it now, not somewhere in the future. And anyway, I don't
see why one would need a new statement to modify just one property of an
account, if all other properties are modified with ALTER USER.

> *2.* Password Expiration behavior
> 
> Is it actually a good idea to overengineer the feature with
> sandbox/disconnect modes?
> *Opinion|* Quite a lot of people are complaining on online platforms
> that their SQL statements stopped working suddenly or that they can't
> connect to the MySQL server anymore. What about we just ask for a new
> password when the next login is attempted? We have ways to disconnect
> a client connection anyway if needed. A good choice for the default
> behavior of this feature might also do the trick.

I'd disable password expiration by default, see urls above :)

> *3.* Having passwords expire on a specific date
> 
> MySQL introduced the concept of expiring the passwords after /N/ days.
> I can see a good use-case in wanting to expire an account's password
> on a specific date/timestamp as well:
> 
> /ALTER USER user1@host PASSWORD EXPIRE DATE tstamp;/
>       or
> /PASSWORD EXPIRE user1@host DATE tstamp;/
> 
> Example use cases:
> * Let's assume company X has a security sanity check on 10th of November
> and wants everybody to update their passwords that day.
> * Company Y wants the password expiration event to happen before the
> working day starts and thus not interrupt their employees during their
> productive hours.

Now, that is overengineering, in my opinion :)
Let's postpone it until users will be requesting it.

Regards,
Sergei
Chief Architect MariaDB
and security@xxxxxxxxxxx