← Back to team overview

maria-developers team mailing list archive

Re: MDEV-12321 authentication plugin: SET PASSWORD support


Hi Sergei!

About the syntax problem, inline:

On 17/10/2018 23:40, Sergei Golubchik wrote:

Now, the syntax problem.

Old MySQL syntax (for the last ~20 years) was

(1)  GRANT ... TO user IDENTIFIED BY 'plain-text password'
(2)  GRANT ... TO user IDENTIFIED BY PASSWORD 'password hash'
(3)  SET PASSWORD = 'password hash'
(4)  SET PASSWORD = PASSWORD('plain-text password')
(5)  SET PASSWORD = OLD_PASSWORD('plain-text password')

here, syntax (1) and (4) were forcing mysql_native_password
authentication, (5) was forcing mysql_old_password, and (2) and (3) were
auto-detecting, based on the hash length.

Later MariaDB and MySQL added support for pluggable authentication with
the syntax

(6)  GRANT ... TO user IDENTIFIED VIA plugin AS 'password hash'

MySQL 5.7 added support for specifying plain-text password for plugins
using the syntax

(7) GRANT ... TO user IDENTIFIED WITH plugin BY 'plain-text password'

I don't quite like it because there's no logical reason why "BY" means a
plain-text password, while "AS" means a hash. Also we support "USING"
instead of "AS", which also means a hash. One can easily get lost in all
these USING/AS/BY and what special semantics each of them has.

I can also never remember what is the difference between BY/AS/USING.

The syntax I've implemented is based on SET PASSWORD:

(8) GRANT ... TO user IDENTIFIED VIA plugin AS PASSWORD('plain-text password')

This is quite intuitive and pretends that there's a sql function
PASSWORD() which returns a hash and it's used as an expression where a
hash is anyway expected. Same works in SET PASSWORD too, obviously.
A PASSWORD() function actually exists and returns the password hash.

The problem here is that PASSWORD() function becomes quite magical. It
returns a different password, depending on what plugin the user is
using. One can still do SELECT PASSWORD("foo"), OLD_PASSWORD("foo"),
but they'll return values for mysql_native_password and
mysql_old_password as before. In the context of SET PASSWORD or GRANT
(or CREATE/ALTER USER) it becomes context dependent, it's a bit
difficult to swallow.

Another approach would be not to pretend it's a function. Say

(9) GRANT ... TO user IDENTIFIED VIA plugin AS PASSWORD 'plain-text password'
     SET PASSWORD = PASSWORD 'plain-text password'

but, unfortunately, it is exactly backwards from the historical behaviour
of (1) and (2).

All in all I'm leaning towards (8), but I'm not quite happy with it :(
One way to solve it could be to extend PASSWORD() function to allow a
second argument, plugin name, like in

   SELECT PASSWORD("foo", "ed25519")

Yet another way could be to remove SQL-level functions PASSWORD() and
OLD_PASSWORD(). That would be my favorite, they always were nothing but
trouble. But I wouldn't risk doing it now :)

I think (8) is ok and intuitively that's what I tried to do when writing test cases for your patch.

I personally would add the extra parameters to PASSWORD. I don't think we should remove PASSWORD(<plain-text>) function, but we should clearly mark it as not-recommended. One might need to figure out what hash a plugin generates for a certain plain-text string and I don't see a way one would do that currently without this extension.