Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23959

GSSAPI plugin - support AD for local group name , and SIDs on Windows

Details

    Description

      The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
      Here https://mariadb.com/kb/en/the-community-is-chaining-upns-via-authentication_options-when-using-the-gs/ a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and what he actually needs is a plugin supporting Active Directory group membership. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

      The proposed syntax

      GROUP prefix

      CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators'
      		 
      CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:BUILTIN\\Administrators'

      SID prefix

      CREATE USER root IDENTIFIED VIA gssapi as 'SID:S-1-5-32-544'  # usual SID form)
      		 
      CREATE USER everyone IDENTIFIED VIA gssapi as 'SID:WD'  #well-known SID 2 letter prefix (WD=world, Everyone)

      The lookup by SID was suggested by users in the above mentioned KB discussion, will perform slightly better, performance-wise, and won't need lookups in the AD. SID potentially can also be used with Unix kerberos, in case AD is used (i.e Linux or Mac join Windows domain) , there will be membership data in kerberos ticket in this case.

      The GROUP: or SID: prefixes are case-sensitive, and there should not be any extra spaces in the string.

      Implementation
      The GROUP: prefix will cause need group name to SID transation (LookupAccountName, which can cause roundtrip to AD)
      the SID: prefix will cause string SID to binary SID translation (ConvertSidStringToSid)

      Once the SID is here, server impersonates the SSPI context,checks membership via CheckTokenMembership() API, then impersonation is reverted, and the returns result of the check as CR_OK or CR_ERROR

      Note , that neither GROUP name nor SID need to be actual group in AD or local computer. It can be the name or SID of the user (this is just how CheckTokenMembership() works)

      Note also, that CheckTokenMembership checks for ENABLED flags in current token groups. The practical implication is that to use "GROUP:Administrators in the UAC environment, client process must run elevated ("as Administrator")

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          wlad Vladislav Vaintroub created issue -
          wlad Vladislav Vaintroub made changes -
          Field Original Value New Value
          Status Open [ 1 ] In Progress [ 3 ]
          wlad Vladislav Vaintroub made changes -
          Description The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
          Here a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and he needs plugin supporting AD (or local) Groups. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

          Obviously, this is the SSPI/Windows specific functionality, and not the Unix GSSAPI but still a good and useful functionality, in my opinion.           		The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
          Here a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and he needs plugin supporting AD (or local) Groups. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

          The proposed syntax

          GROUP prefix
          CREATE USER root IDENTIFIED VIA gssapi as "GROUP:Administrators"

          SID prefix
          CREATE USER root IDENTIFIED VIA gssapi as "SID: S-1-5-32-544" (usual SID form)
          CREATE USER everyone IDENTIFIED VIA gssapi as "SID:WO" (well-known SID 2 letter prefix)

          Implementation
          The GROUP: prefix will cause need group name to SID transation (LookupAccountName, which can cause roundtrip to AD)
          the SID: prefix will cause string SID to binary SID translation (ConvertSidStringToSid)

          Once the SID is here, server impersonates the SSPI context,checks membership via CheckMembership() API, then impersonation is reverted, and the returns result of the check as CR_OK or CR_ERROR

          Note , that neither GROUP name nor SID need to be actual group in AD or local computer. It can be the name or SID of the user (this is just how CheckMembership works)

          Note also, that CheckMembership checks for "enabled" flags in current token groups. The practical implication is that to use "GROUP:Administrators", in the UAC environment, the client process must run elevated ("as Administrator")
          wlad Vladislav Vaintroub made changes -
          Description The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
          Here a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and he needs plugin supporting AD (or local) Groups. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

          The proposed syntax

          GROUP prefix
          CREATE USER root IDENTIFIED VIA gssapi as "GROUP:Administrators"

          SID prefix
          CREATE USER root IDENTIFIED VIA gssapi as "SID: S-1-5-32-544" (usual SID form)
          CREATE USER everyone IDENTIFIED VIA gssapi as "SID:WO" (well-known SID 2 letter prefix)

          Implementation
          The GROUP: prefix will cause need group name to SID transation (LookupAccountName, which can cause roundtrip to AD)
          the SID: prefix will cause string SID to binary SID translation (ConvertSidStringToSid)

          Once the SID is here, server impersonates the SSPI context,checks membership via CheckMembership() API, then impersonation is reverted, and the returns result of the check as CR_OK or CR_ERROR

          Note , that neither GROUP name nor SID need to be actual group in AD or local computer. It can be the name or SID of the user (this is just how CheckMembership works)

          Note also, that CheckMembership checks for "enabled" flags in current token groups. The practical implication is that to use "GROUP:Administrators", in the UAC environment, the client process must run elevated ("as Administrator")
          		The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
          Here a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and he needs plugin supporting AD (or local) Groups. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

          The proposed syntax

          *GROUP prefix*

          {code:SQL}
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators'
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:BUILTIN\\Administrators'
          {code}


          SID prefix
          {code:SQL}
          CREATE USER root IDENTIFIED VIA gssapi as 'SID: S-1-5-32-544" # usual SID form)
          CREATE USER everyone IDENTIFIED VIA gssapi as "SID:WO" #well-known SID 2 letter prefix
          {code}

          Implementation
          The GROUP: prefix will cause need group name to SID transation (LookupAccountName, which can cause roundtrip to AD)
          the SID: prefix will cause string SID to binary SID translation (ConvertSidStringToSid)

          Once the SID is here, server impersonates the SSPI context,checks membership via CheckMembership() API, then impersonation is reverted, and the returns result of the check as CR_OK or CR_ERROR

          Note , that neither GROUP name nor SID need to be actual group in AD or local computer. It can be the name or SID of the user (this is just how CheckMembership works)

          Note also, that CheckMembership checks for "enabled" flags in current token groups. The practical implication is that to use "GROUP:Administrators", in the UAC environment, the client process must run elevated ("as Administrator")
          wlad Vladislav Vaintroub made changes -
          Labels Windows
          wlad Vladislav Vaintroub made changes -
          Component/s Platform Windows [ 10138 ]
          wlad Vladislav Vaintroub made changes -
          Labels Windows
          wlad Vladislav Vaintroub made changes -
          Description The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
          Here a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and he needs plugin supporting AD (or local) Groups. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

          The proposed syntax

          *GROUP prefix*

          {code:SQL}
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators'
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:BUILTIN\\Administrators'
          {code}


          SID prefix
          {code:SQL}
          CREATE USER root IDENTIFIED VIA gssapi as 'SID: S-1-5-32-544" # usual SID form)
          CREATE USER everyone IDENTIFIED VIA gssapi as "SID:WO" #well-known SID 2 letter prefix
          {code}

          Implementation
          The GROUP: prefix will cause need group name to SID transation (LookupAccountName, which can cause roundtrip to AD)
          the SID: prefix will cause string SID to binary SID translation (ConvertSidStringToSid)

          Once the SID is here, server impersonates the SSPI context,checks membership via CheckMembership() API, then impersonation is reverted, and the returns result of the check as CR_OK or CR_ERROR

          Note , that neither GROUP name nor SID need to be actual group in AD or local computer. It can be the name or SID of the user (this is just how CheckMembership works)

          Note also, that CheckMembership checks for "enabled" flags in current token groups. The practical implication is that to use "GROUP:Administrators", in the UAC environment, the client process must run elevated ("as Administrator")
          		The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
          Here a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and he needs plugin supporting AD (or local) Groups. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

          The proposed syntax

          *GROUP prefix*

          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators'
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:BUILTIN\\Administrators'
          {code}


          *SID prefix*
          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'SID: S-1-5-32-544' # usual SID form)
          CREATE USER everyone IDENTIFIED VIA gssapi as 'SID:WO' #well-known SID 2 letter prefix
          {code}

          The GROUP: or SID: prefixes are case-sensitive, and there should not be any extra spaces in the string.

          *Implementation*
          The GROUP: prefix will cause need group name to SID transation (LookupAccountName, which can cause roundtrip to AD)
          the SID: prefix will cause string SID to binary SID translation (ConvertSidStringToSid)

          Once the SID is here, server impersonates the SSPI context,checks membership via CheckMembership() API, then impersonation is reverted, and the returns result of the check as CR_OK or CR_ERROR

          Note , that neither GROUP name nor SID need to be actual group in AD or local computer. It can be the name or SID of the user (this is just how CheckMembership works)

          Note also, that CheckMembership checks for "enabled" flags in current token groups. The practical implication is that to use "GROUP:Administrators", in the UAC environment, the client process must run elevated ("as Administrator")
          wlad Vladislav Vaintroub made changes -
          Description The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
          Here a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and he needs plugin supporting AD (or local) Groups. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

          The proposed syntax

          *GROUP prefix*

          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators'
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:BUILTIN\\Administrators'
          {code}


          *SID prefix*
          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'SID: S-1-5-32-544' # usual SID form)
          CREATE USER everyone IDENTIFIED VIA gssapi as 'SID:WO' #well-known SID 2 letter prefix
          {code}

          The GROUP: or SID: prefixes are case-sensitive, and there should not be any extra spaces in the string.

          *Implementation*
          The GROUP: prefix will cause need group name to SID transation (LookupAccountName, which can cause roundtrip to AD)
          the SID: prefix will cause string SID to binary SID translation (ConvertSidStringToSid)

          Once the SID is here, server impersonates the SSPI context,checks membership via CheckMembership() API, then impersonation is reverted, and the returns result of the check as CR_OK or CR_ERROR

          Note , that neither GROUP name nor SID need to be actual group in AD or local computer. It can be the name or SID of the user (this is just how CheckMembership works)

          Note also, that CheckMembership checks for "enabled" flags in current token groups. The practical implication is that to use "GROUP:Administrators", in the UAC environment, the client process must run elevated ("as Administrator")
          		The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
          Here a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and he needs plugin supporting AD (or local) Groups. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

          The proposed syntax

          *GROUP prefix*

          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators'
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:BUILTIN\\Administrators'
          {code}


          *SID prefix*
          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'SID: S-1-5-32-544' # usual SID form)
          CREATE USER everyone IDENTIFIED VIA gssapi as 'SID:WO' #well-known SID 2 letter prefix
          {code}

          The GROUP: or SID: prefixes are case-sensitive, and there should not be any extra spaces in the string.

          *Implementation*
          The GROUP: prefix will cause need group name to SID transation (_LookupAccountName_, which can cause roundtrip to AD)
          the SID: prefix will cause string SID to binary SID translation (_ConvertSidStringToSid_)

          Once the SID is here, server impersonates the SSPI context,checks membership via _CheckMembership_() API, then impersonation is reverted, and the returns result of the check as CR_OK or CR_ERROR

          Note , that neither GROUP name nor SID need to be actual group in AD or local computer. It can be the name or SID of the user (this is just how _CheckMembership _works)

          Note also, that CheckMembership checks for "enabled" flags in current token groups. The practical implication is that to use "GROUP:Administrators", in the UAC environment, the client process must run elevated ("as Administrator")
          wlad Vladislav Vaintroub made changes -
          Description The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
          Here a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and he needs plugin supporting AD (or local) Groups. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

          The proposed syntax

          *GROUP prefix*

          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators'
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:BUILTIN\\Administrators'
          {code}


          *SID prefix*
          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'SID: S-1-5-32-544' # usual SID form)
          CREATE USER everyone IDENTIFIED VIA gssapi as 'SID:WO' #well-known SID 2 letter prefix
          {code}

          The GROUP: or SID: prefixes are case-sensitive, and there should not be any extra spaces in the string.

          *Implementation*
          The GROUP: prefix will cause need group name to SID transation (_LookupAccountName_, which can cause roundtrip to AD)
          the SID: prefix will cause string SID to binary SID translation (_ConvertSidStringToSid_)

          Once the SID is here, server impersonates the SSPI context,checks membership via _CheckMembership_() API, then impersonation is reverted, and the returns result of the check as CR_OK or CR_ERROR

          Note , that neither GROUP name nor SID need to be actual group in AD or local computer. It can be the name or SID of the user (this is just how _CheckMembership _works)

          Note also, that CheckMembership checks for "enabled" flags in current token groups. The practical implication is that to use "GROUP:Administrators", in the UAC environment, the client process must run elevated ("as Administrator")
          		The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
          Here a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and he needs plugin supporting AD (or local) Groups. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

          The proposed syntax

          *GROUP prefix*

          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators'
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:BUILTIN\\Administrators'
          {code}


          *SID prefix*
          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'SID:S-1-5-32-544' # usual SID form)
          CREATE USER everyone IDENTIFIED VIA gssapi as 'SID:WO' #well-known SID 2 letter prefix
          {code}

          The GROUP: or SID: prefixes are case-sensitive, and there should not be any extra spaces in the string.

          *Implementation*
          The GROUP: prefix will cause need group name to SID transation (_LookupAccountName_, which can cause roundtrip to AD)
          the SID: prefix will cause string SID to binary SID translation (_ConvertSidStringToSid_)

          Once the SID is here, server impersonates the SSPI context,checks membership via _CheckMembership_() API, then impersonation is reverted, and the returns result of the check as CR_OK or CR_ERROR

          Note , that neither GROUP name nor SID need to be actual group in AD or local computer. It can be the name or SID of the user (this is just how _CheckMembership _works)

          Note also, that CheckMembership checks for "enabled" flags in current token groups. The practical implication is that to use "GROUP:Administrators", in the UAC environment, the client process must run elevated ("as Administrator")
          wlad Vladislav Vaintroub made changes -
          Description The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
          Here a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and he needs plugin supporting AD (or local) Groups. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

          The proposed syntax

          *GROUP prefix*

          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators'
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:BUILTIN\\Administrators'
          {code}


          *SID prefix*
          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'SID:S-1-5-32-544' # usual SID form)
          CREATE USER everyone IDENTIFIED VIA gssapi as 'SID:WO' #well-known SID 2 letter prefix
          {code}

          The GROUP: or SID: prefixes are case-sensitive, and there should not be any extra spaces in the string.

          *Implementation*
          The GROUP: prefix will cause need group name to SID transation (_LookupAccountName_, which can cause roundtrip to AD)
          the SID: prefix will cause string SID to binary SID translation (_ConvertSidStringToSid_)

          Once the SID is here, server impersonates the SSPI context,checks membership via _CheckMembership_() API, then impersonation is reverted, and the returns result of the check as CR_OK or CR_ERROR

          Note , that neither GROUP name nor SID need to be actual group in AD or local computer. It can be the name or SID of the user (this is just how _CheckMembership _works)

          Note also, that CheckMembership checks for "enabled" flags in current token groups. The practical implication is that to use "GROUP:Administrators", in the UAC environment, the client process must run elevated ("as Administrator")
          		The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
          Here https://mariadb.com/kb/en/the-community-is-chaining-upns-via-authentication_options-when-using-the-gs/ a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and what he actually needs is a plugin supporting Active Directory group membership. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

          The proposed syntax

          *GROUP prefix*

          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators'
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:BUILTIN\\Administrators'
          {code}


          *SID prefix*
          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'SID:S-1-5-32-544' # usual SID form)
          CREATE USER everyone IDENTIFIED VIA gssapi as 'SID:WO' #well-known SID 2 letter prefix
          {code}

          The lookup by SID was suggested by users in the above mentioned KB discussion, will perform slightly better, performance-wise, and won't need lookups in the AD. SID potentially can also be used with Unix kerberos, in case AD is used (i.e Linux or Mac join Windows domain) , there will be membership data in kerberos ticket in this case.

          The GROUP: or SID: prefixes are case-sensitive, and there should not be any extra spaces in the string.

          *Implementation*
          The GROUP: prefix will cause need group name to SID transation (_LookupAccountName_, which can cause roundtrip to AD)
          the SID: prefix will cause string SID to binary SID translation (_ConvertSidStringToSid_)

          Once the SID is here, server impersonates the SSPI context,checks membership via _CheckMembership_() API, then impersonation is reverted, and the returns result of the check as CR_OK or CR_ERROR

          Note , that neither GROUP name nor SID need to be actual group in AD or local computer. It can be the name or SID of the user (this is just how _CheckMembership _works)

          Note also, that CheckMembership checks for ENABLED flags in current token groups. The practical implication is that to use "GROUP:Administrators in the UAC environment, client process must run elevated ("as Administrator")
          wlad Vladislav Vaintroub made changes -
          Summary GSSAPI plugin - support AD or local group name on Windows GSSAPI plugin - support AD or local group name , and SIDs on Windows
          wlad Vladislav Vaintroub made changes -
          Description The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
          Here https://mariadb.com/kb/en/the-community-is-chaining-upns-via-authentication_options-when-using-the-gs/ a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and what he actually needs is a plugin supporting Active Directory group membership. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

          The proposed syntax

          *GROUP prefix*

          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators'
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:BUILTIN\\Administrators'
          {code}


          *SID prefix*
          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'SID:S-1-5-32-544' # usual SID form)
          CREATE USER everyone IDENTIFIED VIA gssapi as 'SID:WO' #well-known SID 2 letter prefix
          {code}

          The lookup by SID was suggested by users in the above mentioned KB discussion, will perform slightly better, performance-wise, and won't need lookups in the AD. SID potentially can also be used with Unix kerberos, in case AD is used (i.e Linux or Mac join Windows domain) , there will be membership data in kerberos ticket in this case.

          The GROUP: or SID: prefixes are case-sensitive, and there should not be any extra spaces in the string.

          *Implementation*
          The GROUP: prefix will cause need group name to SID transation (_LookupAccountName_, which can cause roundtrip to AD)
          the SID: prefix will cause string SID to binary SID translation (_ConvertSidStringToSid_)

          Once the SID is here, server impersonates the SSPI context,checks membership via _CheckMembership_() API, then impersonation is reverted, and the returns result of the check as CR_OK or CR_ERROR

          Note , that neither GROUP name nor SID need to be actual group in AD or local computer. It can be the name or SID of the user (this is just how _CheckMembership _works)

          Note also, that CheckMembership checks for ENABLED flags in current token groups. The practical implication is that to use "GROUP:Administrators in the UAC environment, client process must run elevated ("as Administrator")
          		The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
          Here https://mariadb.com/kb/en/the-community-is-chaining-upns-via-authentication_options-when-using-the-gs/ a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and what he actually needs is a plugin supporting Active Directory group membership. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

          The proposed syntax

          *GROUP prefix*

          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators'
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:BUILTIN\\Administrators'
          {code}


          *SID prefix*
          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'SID:S-1-5-32-544' # usual SID form)
          CREATE USER everyone IDENTIFIED VIA gssapi as 'SID:WD' #well-known SID 2 letter prefix (WD=world, Everyone)
          {code}

          The lookup by SID was suggested by users in the above mentioned KB discussion, will perform slightly better, performance-wise, and won't need lookups in the AD. SID potentially can also be used with Unix kerberos, in case AD is used (i.e Linux or Mac join Windows domain) , there will be membership data in kerberos ticket in this case.

          The GROUP: or SID: prefixes are case-sensitive, and there should not be any extra spaces in the string.

          *Implementation*
          The GROUP: prefix will cause need group name to SID transation (_LookupAccountName_, which can cause roundtrip to AD)
          the SID: prefix will cause string SID to binary SID translation (_ConvertSidStringToSid_)

          Once the SID is here, server impersonates the SSPI context,checks membership via _CheckMembership_() API, then impersonation is reverted, and the returns result of the check as CR_OK or CR_ERROR

          Note , that neither GROUP name nor SID need to be actual group in AD or local computer. It can be the name or SID of the user (this is just how _CheckMembership _works)

          Note also, that CheckMembership checks for ENABLED flags in current token groups. The practical implication is that to use "GROUP:Administrators in the UAC environment, client process must run elevated ("as Administrator")
          wlad Vladislav Vaintroub made changes -
          Description The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
          Here https://mariadb.com/kb/en/the-community-is-chaining-upns-via-authentication_options-when-using-the-gs/ a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and what he actually needs is a plugin supporting Active Directory group membership. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

          The proposed syntax

          *GROUP prefix*

          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators'
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:BUILTIN\\Administrators'
          {code}


          *SID prefix*
          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'SID:S-1-5-32-544' # usual SID form)
          CREATE USER everyone IDENTIFIED VIA gssapi as 'SID:WD' #well-known SID 2 letter prefix (WD=world, Everyone)
          {code}

          The lookup by SID was suggested by users in the above mentioned KB discussion, will perform slightly better, performance-wise, and won't need lookups in the AD. SID potentially can also be used with Unix kerberos, in case AD is used (i.e Linux or Mac join Windows domain) , there will be membership data in kerberos ticket in this case.

          The GROUP: or SID: prefixes are case-sensitive, and there should not be any extra spaces in the string.

          *Implementation*
          The GROUP: prefix will cause need group name to SID transation (_LookupAccountName_, which can cause roundtrip to AD)
          the SID: prefix will cause string SID to binary SID translation (_ConvertSidStringToSid_)

          Once the SID is here, server impersonates the SSPI context,checks membership via _CheckMembership_() API, then impersonation is reverted, and the returns result of the check as CR_OK or CR_ERROR

          Note , that neither GROUP name nor SID need to be actual group in AD or local computer. It can be the name or SID of the user (this is just how _CheckMembership _works)

          Note also, that CheckMembership checks for ENABLED flags in current token groups. The practical implication is that to use "GROUP:Administrators in the UAC environment, client process must run elevated ("as Administrator")
          		The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
          Here https://mariadb.com/kb/en/the-community-is-chaining-upns-via-authentication_options-when-using-the-gs/ a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and what he actually needs is a plugin supporting Active Directory group membership. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

          The proposed syntax

          *GROUP prefix*

          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators'
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:BUILTIN\\Administrators'
          {code}


          *SID prefix*
          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'SID:S-1-5-32-544' # usual SID form)
          CREATE USER everyone IDENTIFIED VIA gssapi as 'SID:WD' #well-known SID 2 letter prefix (WD=world, Everyone)
          {code}

          The lookup by SID was suggested by users in the above mentioned KB discussion, will perform slightly better, performance-wise, and won't need lookups in the AD. SID potentially can also be used with Unix kerberos, in case AD is used (i.e Linux or Mac join Windows domain) , there will be membership data in kerberos ticket in this case.

          The GROUP: or SID: prefixes are case-sensitive, and there should not be any extra spaces in the string.

          *Implementation*
          The GROUP: prefix will cause need group name to SID transation (_LookupAccountName_, which can cause roundtrip to AD)
          the SID: prefix will cause string SID to binary SID translation (_ConvertSidStringToSid_)

          Once the SID is here, server impersonates the SSPI context,checks membership via _CheckTokenMembership_() API, then impersonation is reverted, and the returns result of the check as CR_OK or CR_ERROR

          Note , that neither GROUP name nor SID need to be actual group in AD or local computer. It can be the name or SID of the user (this is just how _CheckTokenMembership _works)

          Note also, that CheckTokenMembership checks for ENABLED flags in current token groups. The practical implication is that to use "GROUP:Administrators in the UAC environment, client process must run elevated ("as Administrator")
          wlad Vladislav Vaintroub made changes -
          Description The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
          Here https://mariadb.com/kb/en/the-community-is-chaining-upns-via-authentication_options-when-using-the-gs/ a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and what he actually needs is a plugin supporting Active Directory group membership. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

          The proposed syntax

          *GROUP prefix*

          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators'
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:BUILTIN\\Administrators'
          {code}


          *SID prefix*
          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'SID:S-1-5-32-544' # usual SID form)
          CREATE USER everyone IDENTIFIED VIA gssapi as 'SID:WD' #well-known SID 2 letter prefix (WD=world, Everyone)
          {code}

          The lookup by SID was suggested by users in the above mentioned KB discussion, will perform slightly better, performance-wise, and won't need lookups in the AD. SID potentially can also be used with Unix kerberos, in case AD is used (i.e Linux or Mac join Windows domain) , there will be membership data in kerberos ticket in this case.

          The GROUP: or SID: prefixes are case-sensitive, and there should not be any extra spaces in the string.

          *Implementation*
          The GROUP: prefix will cause need group name to SID transation (_LookupAccountName_, which can cause roundtrip to AD)
          the SID: prefix will cause string SID to binary SID translation (_ConvertSidStringToSid_)

          Once the SID is here, server impersonates the SSPI context,checks membership via _CheckTokenMembership_() API, then impersonation is reverted, and the returns result of the check as CR_OK or CR_ERROR

          Note , that neither GROUP name nor SID need to be actual group in AD or local computer. It can be the name or SID of the user (this is just how _CheckTokenMembership _works)

          Note also, that CheckTokenMembership checks for ENABLED flags in current token groups. The practical implication is that to use "GROUP:Administrators in the UAC environment, client process must run elevated ("as Administrator")
          		The GSSAPI plugin only accepts user if username matches, which for some use might be too restrictive.
          Here https://mariadb.com/kb/en/the-community-is-chaining-upns-via-authentication_options-when-using-the-gs/ a user struggles to move from MySQL Windows authentication (which is GSSAPI, under the hood, mostly), and what he actually needs is a plugin supporting Active Directory group membership. This is easily done since on the server side we will get a user's token, and group membership is easily derived from the token.

          The proposed syntax

          *GROUP prefix*

          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators'
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:BUILTIN\\Administrators'
          {code}


          *SID prefix*
          {code:sql}
          CREATE USER root IDENTIFIED VIA gssapi as 'SID:S-1-5-32-544' # usual SID form)
          CREATE USER everyone IDENTIFIED VIA gssapi as 'SID:WD' #well-known SID 2 letter prefix (WD=world, Everyone)
          {code}

          The lookup by SID was suggested by users in the above mentioned KB discussion, will perform slightly better, performance-wise, and won't need lookups in the AD. SID potentially can also be used with Unix kerberos, in case AD is used (i.e Linux or Mac join Windows domain) , there will be membership data in kerberos ticket in this case.

          The GROUP: or SID: prefixes are case-sensitive, and there should not be any extra spaces in the string.

          *Implementation*
          The GROUP: prefix will cause need group name to SID transation (_LookupAccountName_, which can cause roundtrip to AD)
          the SID: prefix will cause string SID to binary SID translation (_ConvertSidStringToSid_)

          Once the SID is here, server impersonates the SSPI context,checks membership via _CheckTokenMembership()_ API, then impersonation is reverted, and the returns result of the check as CR_OK or CR_ERROR

          Note , that neither GROUP name nor SID need to be actual group in AD or local computer. It can be the name or SID of the user (this is just how _CheckTokenMembership()_ works)

          Note also, that _CheckTokenMembership_ checks for ENABLED flags in current token groups. The practical implication is that to use "GROUP:Administrators in the UAC environment, client process must run elevated ("as Administrator")
          wlad Vladislav Vaintroub added a comment -

          @serg , I understand that most of the Windows internal stuff might be uninteresting, but I'd like to have a feedback on functionality, as well as on suggested syntax with GROUP: and SID: prefixes.

          wlad Vladislav Vaintroub added a comment - @serg , I understand that most of the Windows internal stuff might be uninteresting, but I'd like to have a feedback on functionality, as well as on suggested syntax with GROUP: and SID: prefixes.
          wlad Vladislav Vaintroub made changes -
          Assignee Vladislav Vaintroub [ wlad ] Sergei Golubchik [ serg ]
          Status In Progress [ 3 ] In Review [ 10002 ]
          Kunde Johannes added a comment - - edited

          Hi @vlad, I think it would also be useful, if one could authenticate with more than one group, like for instance:

          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators' OR gssapi as 'GROUP:OtherGroup'
          CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators,GROUP:OtherGroup'

          I definitely could make use of this – just as additional input/idea. I know that MySQL supports defining multiple groups this way.

          Kunde Johannes added a comment - - edited Hi @vlad, I think it would also be useful, if one could authenticate with more than one group, like for instance: CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators' OR gssapi as 'GROUP:OtherGroup' CREATE USER root IDENTIFIED VIA gssapi as 'GROUP:Administrators,GROUP:OtherGroup' I definitely could make use of this – just as additional input/idea. I know that MySQL supports defining multiple groups this way.
          wlad Vladislav Vaintroub added a comment -

          Kunde I thought the first of your proposals would already work , or would not it? With 2 Kerberos handshakes, admittedly, this would be somewhat slower.

          The second of your proposals, would not it be simple/preferable to handle things on the AD site, e.g create a group contains both Administrators and OtherGroup, call it MariaDBRootGroup?

          I mean, now you're satisfied with groups separated by "," meaning OR, but tomorrow you might want an AND (member of both groups), and next day NOT (member of one group, but not another one), so we could end up creating own full-featured domain specific language just for "IDENTIFIED VIA gssapi AS"

          wlad Vladislav Vaintroub added a comment - Kunde I thought the first of your proposals would already work , or would not it? With 2 Kerberos handshakes, admittedly, this would be somewhat slower. The second of your proposals, would not it be simple/preferable to handle things on the AD site, e.g create a group contains both Administrators and OtherGroup, call it MariaDBRootGroup? I mean, now you're satisfied with groups separated by "," meaning OR, but tomorrow you might want an AND (member of both groups), and next day NOT (member of one group, but not another one), so we could end up creating own full-featured domain specific language just for "IDENTIFIED VIA gssapi AS"
          Kunde Johannes added a comment -

          The first proposal (see it as two syntax options) already works with UPNs, correct – I only wanted to mention it here as possible use case. It can be useful if two user-groups should access one service, but in general you're right that this can be done on AD site as well.

          From a logical perspective it always means OR, of course neither AND nor NOT – I get your point.

          Kunde Johannes added a comment - The first proposal (see it as two syntax options) already works with UPNs, correct – I only wanted to mention it here as possible use case. It can be useful if two user-groups should access one service, but in general you're right that this can be done on AD site as well. From a logical perspective it always means OR, of course neither AND nor NOT – I get your point.
          serg Sergei Golubchik made changes -
          Fix Version/s 10.6.0 [ 24431 ]
          Fix Version/s 10.6 [ 24028 ]
          Assignee Sergei Golubchik [ serg ] Vladislav Vaintroub [ wlad ]
          Resolution Fixed [ 1 ]
          Status In Review [ 10002 ] Closed [ 6 ]
          wlad Vladislav Vaintroub made changes -
          Summary GSSAPI plugin - support AD or local group name , and SIDs on Windows GSSAPI plugin - support AD for local group name , and SIDs on Windows
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 114605 ] MariaDB v4 [ 134350 ]

          People

            wlad Vladislav Vaintroub
            wlad Vladislav Vaintroub
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.