maria-developers team mailing list archive

[Vicențiu Ciorbaru <vicentiu@xxxxxxxxxxx>]

Hi Sergei!

About the syntax problem, inline:

On 17/10/2018 23:40, Sergei Golubchik wrote:
> Hi,
>
> 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.

Vicențiu