← Back to team overview

maria-developers team mailing list archive

Re: win auth plugin


> -----Original Message-----
> From: Sergei Golubchik [mailto:serg@xxxxxxxxxxxx]
> Sent: Samstag, 31. Dezember 2011 11:47
> To: Vladislav Vaintroub
> Cc: maria-developers@xxxxxxxxxxxxxxxxxxx
> 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
> 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.

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.

>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
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

> 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.


> > 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
>   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
> > 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
> > 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.


> Regards,
> Sergei