hello sir,
i want to know more about this issue.can you plz elaborate..

Thank You
Jabbar Memon

Hi, I am Liam Keller a SE student and I wish to work on this project for GSoC 2017. I think this feature is supported in Oracle from my findings and it will be great for MariaDB to have it too. I have been going through the grammar of SQL used by MariaDB especially for CREATE USER ... statement. My initial thoughts on the new syntax should allow us preserve the current syntax but extend it to support enumerating authentication methods. Something like should work for the new syntax.

CREATE [OR REPLACE] USER [IF NOT EXISTS] 

           user_specification [,user_specification] ...

           [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]

           [WITH resource_option [resource_option] ...]

user_specification:

          username [authentication_option]

authentication_option:

           IDENTIFIED BY 'authentication_string' 

           | IDENTIFIED BY PASSWORD 'hash_string'

           | IDENTIFIED {VIA|WITH} authentication_plugin

           | IDENTIFIED {VIA|WITH} authentication_plugin BY 'authentication_string'

           | IDENTIFIED {VIA|WITH} authentication_plugin {USING|AS} 'hash_string'

         *| IDENTIFIED {VIA|WITH} auth_option [auth_option ...]*

tls_option:

        SSL 

       | X509

       | CIPHER 'cipher'

       | ISSUER 'issuer'

       | SUBJECT 'subject'

resource_option:

      MAX_QUERIES_PER_HOUR count

      | MAX_UPDATE_PER_HOUR count

      | MAX_CONNECTIONS_PER_HOUR count

      | MAX_USER_CONNECTIONS count

I will be working on a draft proposal for this project and I will share it with the Mailing list for review.

Good start - submissions are open now on https://summerofcode.withgoogle.com
Try to address and consider the other questions asked too.

Assorted thoughts

1.

We could try to stick everything into the existing API and protocol, by creating AND and OR authentication plugins. Or one META (or, say, MULTI, or POLICY) plugin. Like

CREATE USER foo@bar IDENTIFIED VIA POLICY USING "(unix_socket OR mysql_native_password AS '*F3A2A51A9B0F2BE2468926B4132313728C250DBF') AND pam AS 'mariadb_local'"

This is not particularly readable
It can be stored in the existing mysql.user table
Needs policy-client plugin
Does not change the protocol, at least formally. Practically it introduces sub-protocol that is implemented by a policy plugin, but there’s no way around it

2.

The opposite, fully native built-in support. The syntax can look like

CREATE USER foo@bar IDENTIFIED VIA (unix_socket OR mysql_native_password AS '*F3A2A51A9B0F2BE2468926B4132313728C250DBF') AND pam AS 'mariadb_local'

Or even

CREATE POLICY pol1 USER IDENTIFIED VIA (unix_socket OR mysql_native_password AS '*F3A2A51A9B0F2BE2468926B4132313728C250DBF') AND pam AS 'mariadb_local'

CREATE USER foo@bar IDENTIFIED VIA pol1

Plugins (or policies) are stored in a separate table mysql.policy, and mysql.user just stores a policy id.
Rather more intrusive
Policies can be used also to specify password validation, usage limits (max queries per hour, etc) and so on. Per user settings (e.g. mqh) overwrite policy.
Easier to maintain for setups with many users (just one command to alter mqh for all users).
Changes the protocol, needs client-side changes (but it cannot be helped)
needs to move actual password out of the policy, they still need to be stored somewhere.

3.

Something in the middle, no policies, still stored in a mysql.user table, but a dedicated syntax.

4.

May be, PAM semantics?

CREATE USER foo@bar IDENTIFIED VIA pam AS 'mariadb_local' required, unix_socket sufficient,  mysql_native_password AS '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' sufficient

Not quite the same, AND/OR syntax seems to be more powerful and more natural too.

5.

Our main use case seems to be really just unix_socket OR mysql_native_password, so we could limit the scope and only implement OR, not AND or parentheses. This wouldn’t simplify much and wouldn’t avoid the implementation question (1. Plugins 2. Native 3. Middle ground)

We can consider using existing table mysql.roles_mapping for authentication
E.g. we can have these roles:
authentication_pam_allowed
authentication_unix_socket_allowed
authentication_unix_socket_required
And if user is part of those roles, then he is allowed/required to use particular authentication.

In some cases the client wants to be able to choose. As the extension of the above idea, for a case of IDENTIFIED WITH A OR B OR C a client can choose to force plugin B, and the server will automatically consider A and C as failed.

Because 1) a password shouldn't be part of a policy, but 2) the server doesn't know what plugin parameters are passwords, a possible solution can be

CREATE POLICY pol1 USER IDENTIFIED VIA (unix_socket OR mysql_native_password AS ?) AND pam AS 'mariadb_local';

CREATE USER foo@bar IDENTIFIED VIA pol1('*F3A2A51A9B0F2BE2468926B4132313728C250DBF');

Or even

CREATE POLICY pol1 USER IDENTIFIED VIA (unix_socket OR mysql_native_password AS ?) AND pam AS 'mariadb_local';

CREATE USER foo@bar IDENTIFIED VIA pol1 USING '*F3A2A51A9B0F2BE2468926B4132313728C250DBF';

Note that in both cases 'mariadb_local' was still hard-coded in the policy

This syntax becomes rather problematic if one wants to prepare CREATE POLICY statement. Alternatively, one can do named parameters:

CREATE POLICY pol1(pass) USER IDENTIFIED VIA (unix_socket OR mysql_native_password AS pass) AND pam AS 'mariadb_local';

CREATE USER foo@bar IDENTIFIED VIA pol1('*F3A2A51A9B0F2BE2468926B4132313728C250DBF');

but that looks like an overkill

About protocol changes: current protocol already supports server telling client what plugin to switch to. Perhaps this can be used to avoid any protocol changes?

Protocol also supports client telling server which plugin to use. I do not think it works, but the field is there.

On the other hand, if the policy is strictly a way to simplify user administration, a way to give some name to a set of user settings, then everything a policy can do, should be doable without. In that case, this should work too:

CREATE USER foo@bar IDENTIFIED VIA (unix_socket OR mysql_native_password AS '*F3A2A51A9B0F2BE2468926B4132313728C250DBF') AND pam AS 'mariadb_local'

and the concept of "policy" could be moved to a separate independent project.

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:

• provide possibility to retrieve list of all users who is allowed to connect trough 'pam' and is not allowed to connect trough 'unix_socket' with simple SELECT .. WHERE query (joins allowed, but advanced parsing of content of column(s) is not allowed).
• provide easy way to revoke / add 'policies' (i.e. with simple INSERT / DELETE or UPDATE commands).

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.

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.

commented on the https://github.com/MariaDB/server/commit/1e4d7ba29ed19f8edfdcc97f408ac1d5d07956e6