-----Original Message----- From: Sergei Golubchik [mailto:serg@askmonty.org] Sent: Samstag, 31. Dezember 2011 11:47 To: Vladislav Vaintroub Cc: maria-developers@lists.launchpad.net Subject: Re: win auth plugin

Hi Sergei,

...

Hmm:) Supplying user name and verifying is sort of duplicating information. Server knows exactly who's connecting anyway, after the handshake is performed. So - is that providing user name really required?

Username is part of MySQL client-server protocol. When somebody connects he does it under a specific user name, that is, one claims to be a user XXX. And a password was used to verify this claim. This is called authentication. Now a plugin can verify this claim without a password too.

Simply replacing user name is not a good idea as it'll allow basically anyone to connect without any user name verificaton.

From practical point of view an example: I write an ASP.NET application and use SQLServer. I use "IntergratedSecurity=SSPI" in the connection string. I do not specify username of password. This way, I do not have to hardcode usernames into

In the case given , SSPI delivers the real (windows) identity of user without any username and password. I mean plugin can check that user is user without password, it also can check who is that user without username. I can connect, I should not claim to be under specific user name, I let DBMS find out what my username is. the aspx source files and my application remains portable and does not reveal any details about the database. And it is not like SQLServer would somehow get windows username from the environment, and embed this into the login message, no. It just lets Kerberos or NTLM find out who the user is, verifies that user can connect (has a "login" created with CREATE LOGIN), finds out the corresponding user name, and authentication is done at this point.

I can do that in 5.5 - where a new user name is verified against the proxy table. Or fix 5.2 to verify the new user name against the user table. I'd rather wait for 5.5.

Ok.

...

I would understand providing extra name information if it would help to resolve some ambiguity. For example, it addition to user name it could be a group name. Given a token, checking that supplied string is either user name or one of the groups he belongs to, is simple.. From my point of view, allowing groups could make it more useful in practice ,allowing many different users/applications act as the single MySQL user, reducing the need of one-to-one mapping for every single OS user on MySQL level.

Yes, I thought of it, but slightly differently:

* I don't quite like the idea of using user name or group name, whatever works. I'd rather specify what to use - user or group name - in the CREATE USER:

CREATE USER xxx IDENTIFIED VIA windows USING 'GROUP';

where GROUP is not a group name, but literally a string "GROUP", meaning that the plugin should look at the group name instead of the user name.

* I'd like to do this at the same time for all plugin where it make sense - for socket_peercred, for pam, and for windows auth plugins. So I see it as a separate feature, common for many plugins, not specific to windows plugin. Which means - done in a separate changeset.

Ok, sounds good to me.

...

I'm afraid .Net connector does not work without mapping, or are things have changed in the last months? That would be pity, and greatly reduce the utility . Does any connector apart from .NET support authentication yet?

I didn't try any. What do you mean ".Net connector does not work without mapping" ?

Connector/NET, at least at the time I left , used predefined username, proxy and long mapping string for the users. "Integrated Security=SSPI" was mapped to user hardcoded "windows_authentication" or similar, to hardcoded plugin name given in client authentication packet, and would not work if authentication is enforced by the server (i.e connector connects without "IntergratedSecurity=SSPI", but serve decides out of a sudden than this user has to use Windows authentication). Client decides/enforces when Windows authentication is performed in this model, not the server. For that to work, there must be a mapping between catch-all "windows_authentication" to real DBMS user.

...

Ok, to the itself. I do not think comparing usernames as strings is the correct way to go + if (!client_sid.is_valid() || + !client_sid.make_username(buf, sizeof(buf)) || + stricmp(info->user_name, buf))

Yes, you said that already. Sorry for this. I didn't do any comparison originally, and just returned 'buf' as the user name. Then I added the comparison, almost right before pushing.

I'll fix it.

...

The problem with it is that usernames can be really specified in multitude of different ways (name, .\name, machine\name are different names for the same local user for example). A more robust comparison would be retrieving SID for info->user_name, and comparing it with client_sid using e.g EqualSid().

For comparison - yes. But if I simply want to return a user name?

If we want or to normalize name in some standard format, It is possible to impersonate user, use GetUserNameEx then revert impersonation (maybe there is a better method without involving impersonation, but I this is the first one that comes to mind) If we do not want to normalize, LookupAccountSid and then concatenate domain, '\' and username. I do not know if it returns FQDN for domain or not.. Or, we can omit domain and \ provided user is local. We need to tell domain user from local user with the same name, which means we cannot simply ignore the domain part as it is done currently .

...

Could you understand a strange dance around 254 bytes in Rafals code (Handshake_client::write_packet)? I do not get it. Did he fix a real problem, or problem that would not exist, or did he redefine protocol in his own way ?

There is a real problem - the length of the plugin data in the first client response packet is sent in one byte. So it's limited to 255. Rafal fixed this in a plugin, by sending second packet with the rest of the data.

Which is a bad solution, this should've been fixed in a protocol by storing plugin data length in more bytes.

I didn't do that for compatibility reasons (with .Net, for example).

Hmm, .Net client did not implement any special logic for fist that packet sent(I should know, I wrote this part back in Oracle). It sent all SSPI messages wrapped in usual packets, 3 bytes length plus one byte sequence number. I do not remember finding anything in the protocol that would require a workaround.

On the other hand... I'd better fix the protocol and the plugin. But in the server part of the plugin I keep the support for Rafal's protocol workaround. I all this is possiblle, that is. If not - I'll keep everything as is.

Ok.

Regards, Sergei