Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.4.0-1
Description
In some cases there is a need to authenticate the same user in different means, more specifically multiple different authentications methods, or IDENTIFIED BY clauses for the same user. The adjusted CREATE USER syntax would allow multiple IDENTIFIED BY sections for one single user.
Questions:
- What logic should be applied? All of authentication methods should succeed? One of them should succeed? Should we allow complex rules like (first_auth OR second_auth) AND third_auth ? Or, instead of AND/OR may be we should go with PAM model or sufficient/required/etc?
- What syntax should be used?
- stuff like SET PASSWORD — will they be not allowed? allowed? if allowed, how will they work?
- where the new authentication rules will be stored, in what table, what columns?
Attachments
Issue Links
- blocks
-
MDEV-8375 Debian: Passwordless mysqld root login via socket auth bugfixing
-
- Closed
-
-
-
- Closed
-
- causes
-
-
- Closed
-
-
MDEV-21929 Enhance ALTER USER for multiple authentication methods
-
- Open
-
- is blocked by
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Stalled
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
Activity
Right, sorry. I had this in mind but forgot to write. If we do this AND/OR syntax (and, perhaps, if we don't) there should be some INFORMATION_SCHEMA tables presenting current authentication rules to users. Nobody should need to parse SHOW CREATE USER to understand how to authentication is configured.
but if you want to suggest some other notation, please, feel free to.
Reading from I_S will solve part of problem, but it still will be problematic to modify methods, e.g. revoke 'unix_socket' from those who can connect trough 'pam'.
Below is one of ways to implement the suggestion with roles_mapping:
# define roles
|
insert into mysql.user(user, plugin, is_role) |
select 'authentication_unix_socket', 'unix_socket', 1, |
union 'authentication_pam', 'pam', 1; |
|
|
# let foo@bar connect trough unix_socket |
insert into mysql.roles_mapping (Host, User, Role) |
select 'bar', 'foo', 'authentication_unix_socket'; |
|
|
# now later revoke such possibility |
delete from mysql.roles_mapping where (host, user, role) in ('bar', 'foo', 'authentication_unix_socket'); |
|
|
# or revoke 'authentication_unix_socket' from those who have 'authentication_pam' |
delete from mysql.roles_mapping a where (host, user, role) in (select host, user, 'authentication_unix_socket' from mysql.roles_mapping where role = 'authentication_pam'); |
Now, to define 'optional vs forced' unix_socket authentication, we can either reuse some column from 'user' table, or add new column to either 'user' or 'roles_mapping'.
If we have strong reasons to not abuse roles with authentication methods, we can probably define new table 'authentication_role' or 'authentication_group' and try to use the same way as suggestion above
I read all this and for me it is still not clear even which syntax to use.
My concern is that it will be quite headache to parse/build these AND / OR expressions, e.g. for external tools or to build some advanced analytics from these expressions. Therefore I'd stick to Codd's rule #1 "All information in a relational data base is represented explicitly at the logical level and in exactly one way – by values in tables." and rule #4 "The data base description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data."
E.g. that should:
Of course one can say that after all that information will be stored in relational tables and tools could use such SQL commands to avoid parsing AND / OR . But that would mean that we will have to support two notations and always try to make sure that these notations interact in consistent way.
Thus I prefer to have single notation where we define / retrieve info with (simple) INSERT / DELETE / UPDATE / SELECT commands and should design syntax basing on that.