Haha, no problem, glad I could help :) I have noticed a couple of gotchas to watch out for with this feature though…
You can have different groups/users mapped to different roles, but if a user matches multiple mappings the connection will fail: PS C:\Users\PGMRSD1> c:\apps\firebird\isql.exe localhost/3051:inca Statement failed, SQLSTATE = 08004 Multiple maps found for FOODSTUFF\APP_INCA_SupportINCA Use CONNECT or CREATE DATABASE to specify a database SQL> It seems to me like it would be all too easy to accidentally lock out users belonging to multiple groups this way. Specifying the desired role name when connecting doesn't help. It also doesn't help if one of the mapped roles doesn't exist in the database you're connecting to (ie different roles for same user in different dbs). Also, we have a system here that regularly backs up databases then restores the backup to live (I think this was recommended practice way back in the days of InterBase 5 or 6). The mappings in our database survive one cycle of this but disappear on the second cycle (tested using gbak from Firebird 3.0.5). Changing the backup procedure only hides this issue (if you actually have to restore a backup then you're halfway there…) So for now my team is considering these restrictions: 1. Only map users, not groups, so as to make mapping conflicts easier to avoid. A convention that sets a unique mapping name for each user could help. 2. Make all mappings global, so they are not part of db backups. The security db could be backed up as is rather than using gbak, and it's also feasible to store the entire security db setup as an sql script. Especially mappings, which don't contain passwords. From: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com> Sent: Wednesday, 11 March 2020 9:05 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] AW: How can I use/map Active Directory Groups within my Firebird in Trusted Authentication mode? Thank You very very much and sorry for this stupid mistake. Now it works as expected. Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Freitag, 6. März 2020 04:46 An: firebird-support@yahoogroups.com Betreff: [firebird-support] RE: How can I use/map Active Directory Groups within my Firebird in Trusted Authentication mode? Aha, you've got a typo! In your create mapping statement (and verified by the output of show mapping), you have "WINSSPI" instead of "WIN_SSPI". I encountered the same issue when I accidentally typed "WIN_SPPI".. It seems that the plugin name is not validated against those currently available (possibly for good reason) and the mapping is happily created and then ignored. Regards Steve From: firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com> <firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com>> Sent: Friday, 6 March 2020 1:38 AM To: firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com> Subject: [firebird-support] AW: How can I use/map Active Directory Groups within my Firebird in Trusted Authentication mode? I can’t get it to work… The user “MYDOMAIN\ADMINISTRATOR” is a member of the AD group “MYDOMAIN\MY_GROUP” ROLE_TEST was created using this statement: CREATE ROLE ROLE_TEST; The mapping was created with the following statement: CREATE MAPPING MY_MAPPING USING PLUGIN WinSSPI FROM GROUP "MYDOMAIN\MY_GROUP" TO ROLE ROLE_TEST; Here is my isql output: C:\Program Files\Firebird\Firebird_3_0>isql.exe localhost:c:\database\test.fdb Database: localhost:c:\database\test.fdb, User: MYDOMAIN\ADMINISTRATOR SQL> show version; ISQL Version: WI-V3.0.5.33220 Firebird 3.0 SQL> select current_user, current_role from rdb$database; USER ROLE =============================== =============================== UNITEL\ADMINISTRATOR NONE SQL> show mapping; MY_MAPPING USING PLUGIN WINSSPI FROM GROUP MYDOMAIN\MY_GROUP TO ROLE ROLE_TEST *** Global mapping *** TRUSTED_AUTH USING PLUGIN WIN_SSPI FROM ANY USER TO USER Any idea? Von: firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com> [mailto:firebird-support@yahoogroups.com] Gesendet: Dienstag, 3. März 2020 21:13 An: firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com> Betreff: [firebird-support] RE: How can I use/map Active Directory Groups within my Firebird in Trusted Authentication mode? Hi Mathias I did not have to recreate my database with FB 3.0.5. The before/after example was around upgrading the server only, with no database changes. (my database was created in FB 3.0.4 / ODS 12 with schema/data migrated across from an ancient InterBase 7.5.1 db, and is still on sql dialect 1) Here's another example with more detail including the mapping statements as requested. Role (pre-existing): create role inca; Mappings: create global mapping trusted_auth using plugin win_sspi from any user to user; create mapping inca_inca using plugin win_sspi from group "foodstuff\APP_INCA_SupportINCA" to role inca; Connection (note that the role is not specified, I'm not sure if it's specified but in practice it seems that if a role is available it will be used): PS C:\Users\PGMRSD1> c:\apps\firebird\isql.exe localhost/3051:inca Database: localhost/3051:inca, User: FOODSTUFF\PGMRSD1, Role: INCA SQL> show mapping; INCA_INCA USING PLUGIN WIN_SSPI FROM GROUP foodstuff\APP_INCA_SupportINCA TO ROLE INCA *** Global mapping *** TRUSTED_AUTH USING PLUGIN WIN_SSPI FROM ANY USER TO USER SQL> select current_user, current_role from rdb$database; USER ROLE =============================== =============================== FOODSTUFF\PGMRSD1 INCA SQL> Regards Steve From: firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com>> <firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com>>> Sent: Tuesday, 3 March 2020 7:52 PM To: firebird-support@yahoogroups.com<mailto:firebird-supp...@yahoogroups..com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-supp...@yahoogroups..com>> Subject: [firebird-support] AW: How can I use/map Active Directory Groups within my Firebird in Trusted Authentication mode? Thank you. Perhaps I have to recreate the Database with FB 3.0.5? Can You show me Your Create Mapping Statement? Does You access your database with Your Windows Account AND the Firebird ROLE? Regards Mathias Von: firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com>> [mailto:firebird-support@yahoogroups.com] Gesendet: Dienstag, 3. März 2020 03:42 An: firebird-support@yahoogroups.com<mailto:firebird-supp...@yahoogroups..com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-supp...@yahoogroups..com>> Betreff: [firebird-support] RE: How can I use/map Active Directory Groups within my Firebird in Trusted Authentication mode? Hi again Mathias – just confirming that upgrading from 3.0..4 to 3.0.5 resolved this issue for me. Before (Firebird 3.0.4): PS C:\Users\PGMRSD1> c:\apps\firebird\isql.exe localhost/3051:foo Database: localhost/3051:foo, User: FOODSTUFF\PGMRSD1 SQL> select current_user, current_role from rdb$database; USER ROLE =============================== =============================== FOODSTUFF\PGMRSD1 NONE After (Firebird 3.0.5): PS C:\Users\PGMRSD1> c:\apps\firebird\isql.exe localhost/3051:foo Database: localhost/3051:foo, User: FOODSTUFF\PGMRSD1, Role: FOO SQL> select current_user, current_role from rdb$database; USER ROLE =============================== =============================== FOODSTUFF\PGMRSD1 FOO From: firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com>>> <firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com>>>> Sent: Monday, 2 March 2020 9:07 AM To: firebird-support@yahoogroups.com<mailto:firebird-supp...@yahoogroups...com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-supp...@yahoogroups..com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-supp...@yahoogroups...com%3cmailto:firebird-support@yahoogroups.com%3cmailto:firebird-supp...@yahoogroups..com>>> Subject: [firebird-support] RE: How can I use/map Active Directory Groups within my Firebird in Trusted Authentication mode? Hi Mathias – I've found a relevant issue… it contains all the keywords you'd think to search for but eventually I found it in the changelog by searching the source for "mapping": http://tracker.firebirdsql.org/browse/CORE-5639 It is marked as resolved in 3.0.5, I'm hoping to test this out today and will let you know how I get on… (see change https://github.com/FirebirdSQL/firebird/commit/e6f525c47a4a60c7881949ffe0138c65f947c470 in 3.0 branch) Regards Steve From: firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com>>> <firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com>>>> Sent: Friday, 28 February 2020 5:14 PM To: firebird-support@yahoogroups.com<mailto:firebird-supp...@yahoogroups...com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-supp...@yahoogroups..com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-supp...@yahoogroups...com%3cmailto:firebird-support@yahoogroups.com%3cmailto:firebird-supp...@yahoogroups..com>>> Subject: [firebird-support] RE: How can I use/map Active Directory Groups within my Firebird in Trusted Authentication mode? Hi – I've got the same question as Mathias. I'm also using Firebird 3 (3.0.4), with a new test database containing its own security database. The setup is similar: create global mapping trusted_auth using plugin win_sspi from any user to user; create role foo; Mapping a single user to the role works: PS C:\Users\adm_sdrake> d:\apps\firebird\isql.exe -u sysdba foo Database: foo, User: SYSDBA SQL> create mapping sjd_foo using plugin win_sspi from user 'foodstuff\adm_sdrake' to role foo; SQL> exit; PS C:\Users\adm_sdrake> d:\apps\firebird\isql.exe localhost/3051:foo Database: localhost/3051:foo, User: FOODSTUFF\ADM_SDRAKE, Role: FOO SQL> quit; But mapping a group does not: PS C:\Users\adm_sdrake> d:\apps\firebird\isql.exe -u sysdba foo Database: foo, User: SYSDBA SQL> drop mapping sjd_foo; SQL> create mapping inca_foo using plugin win_sspi from group 'foodstuff\RBAC_INCA Support' to role foo; SQL> exit; PS C:\Users\adm_sdrake> d:\apps\firebird\isql.exe localhost/3051:foo Database: localhost/3051:foo, User: FOODSTUFF\ADM_SDRAKE SQL> set trusted role; Statement failed, SQLSTATE = 0P000 Your attachment has no trusted role SQL> quit; I've tried multiple groups that I'm a member of, with and without the domain prefix, but no luck. Any clues… is this sort of mapping supported? Regards Steve Drake From: firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com>>> <firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com%3cmailto:firebird-support@yahoogroups.com>>>> Sent: Tuesday, 18 February 2020 10:18 PM To: firebird-support@yahoogroups.com<mailto:firebird-supp...@yahoogroups...com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-supp...@yahoogroups..com<mailto:firebird-support@yahoogroups.com%3cmailto:firebird-supp...@yahoogroups...com%3cmailto:firebird-support@yahoogroups.com%3cmailto:firebird-supp...@yahoogroups..com>>> Subject: [firebird-support] How can I use/map Active Directory Groups within my Firebird in Trusted Authentication mode? Hello, in my actual project I use Trusted User Authentication. As described in the documentation I have created this mapping: CREATE GLOBAL MAPPING TRUSTED_AUTH USING PLUGIN WIN_SSPI FROM ANY USER TO USER; In my opinion this means “all AD users are also Firebird users but without grants to a specific table”. If I grant privileges to a domain user it will work fine: grant all on MY_TABLE to "MYDOMAIN\MYUSERNAME"; In production environment I have about 300 users. All users are in 3 different AD groups. (“basic_user”, “management”, “admins”) – admins are application admins no domain admins. Is it possible to grant to this groups and not to 300 users? Or is it possible to create a mapping (map AD groups to firebird groups)? How? The documentation about mapping is not good. grant all on MY_TABLE to "MYDOMAIN\management"; --> this does not work CREATE MAPPING TEST USING PLUGIN WinSSPI FROM GROUP " management " TO ROLE FIREBIRD_MANAGEMENT; --> this does not work I’m using Firebird 3. Regards Mathias Pannier ub.unitel GmbH, Schulstraße 16, 06792 Sandersdorf-Brehna Geschaeftsfuehrung Klaus Richter, Olaf Meyer Amtsgericht Stendal HRB 26389 FA Bitterfeld Steuernr. 116/107/08597 Ust.identNr. DE815796778 Deutsche Bank IBAN DE53 86070024 0 6143234 00 Kreissparkasse Anhalt-Bitterfeld IBAN DE69 80053722 0 3050326 82 __________________________________________________________ Dieses E-Mail ist nur für den Empfänger bestimmt, an den es gerichtet ist und kann vertrauliches bzw. unter das Berufsgeheimnis fallendes Material enthalten. Jegliche darin enthaltene Ansicht oder Meinungs- äußerung ist die des Autors und stellt nicht notwendigerweise die Ansicht oder Meinung von ub.unitel GmbH dar. Sind Sie nicht der Empfänger, so haben Sie diese E-Mail irrtümlich erhalten und jegliche Verwendung, Veröffentlichung, Weiterleitung, Abschrift oder jeglicher Druck dieser E-Mail ist strengstens untersagt. __________________________________________________________ ---------------------------------------------------------- Note: This e-mail message has been inspected for malicious content. Attention: The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. Please note that the views and opinions expressed in this message may be those of the individual and not necessarily those of Foodstuffs South Island Ltd. Thank you. ---------------------------------------------------------- [Non-text portions of this message have been removed] ---------------------------------------------------------- Note: This e-mail message has been inspected for malicious content. Attention: The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. Please note that the views and opinions expressed in this message may be those of the individual and not necessarily those of Foodstuffs South Island Ltd. Thank you. ---------------------------------------------------------- [Non-text portions of this message have been removed] ---------------------------------------------------------- Note: This e-mail message has been inspected for malicious content. Attention: The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. Please note that the views and opinions expressed in this message may be those of the individual and not necessarily those of Foodstuffs South Island Ltd. Thank you. ---------------------------------------------------------- [Non-text portions of this message have been removed] ub.unitel GmbH, Schulstraße 16, 06792 Sandersdorf-Brehna Geschaeftsfuehrung Klaus Richter, Olaf Meyer Amtsgericht Stendal HRB 26389 FA Bitterfeld Steuernr. 116/107/08597 Ust.identNr. DE815796778 Deutsche Bank IBAN DE53 86070024 0 6143234 00 Kreissparkasse Anhalt-Bitterfeld IBAN DE69 80053722 0 3050326 82 __________________________________________________________ Dieses E-Mail ist nur für den Empfänger bestimmt, an den es gerichtet ist und kann vertrauliches bzw. unter das Berufsgeheimnis fallendes Material enthalten. Jegliche darin enthaltene Ansicht oder Meinungs- äußerung ist die des Autors und stellt nicht notwendigerweise die Ansicht oder Meinung von ub.unitel GmbH dar. Sind Sie nicht der Empfänger, so haben Sie diese E-Mail irrtümlich erhalten und jegliche Verwendung, Veröffentlichung, Weiterleitung, Abschrift oder jeglicher Druck dieser E-Mail ist strengstens untersagt. __________________________________________________________ ---------------------------------------------------------- Note: This e-mail message has been inspected for malicious content. Attention: The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. Please note that the views and opinions expressed in this message may be those of the individual and not necessarily those of Foodstuffs South Island Ltd. Thank you. ---------------------------------------------------------- [Non-text portions of this message have been removed] ub.unitel GmbH, Schulstraße 16, 06792 Sandersdorf-Brehna Geschaeftsfuehrung Klaus Richter, Olaf Meyer Amtsgericht Stendal HRB 26389 FA Bitterfeld Steuernr. 116/107/08597 Ust.identNr. DE815796778 Deutsche Bank IBAN DE53 86070024 0 6143234 00 Kreissparkasse Anhalt-Bitterfeld IBAN DE69 80053722 0 3050326 82 __________________________________________________________ Dieses E-Mail ist nur für den Empfänger bestimmt, an den es gerichtet ist und kann vertrauliches bzw. unter das Berufsgeheimnis fallendes Material enthalten. Jegliche darin enthaltene Ansicht oder Meinungs- äußerung ist die des Autors und stellt nicht notwendigerweise die Ansicht oder Meinung von ub.unitel GmbH dar. Sind Sie nicht der Empfänger, so haben Sie diese E-Mail irrtümlich erhalten und jegliche Verwendung, Veröffentlichung, Weiterleitung, Abschrift oder jeglicher Druck dieser E-Mail ist strengstens untersagt. __________________________________________________________ ---------------------------------------------------------- Note: This e-mail message has been inspected for malicious content. Attention: The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. Please note that the views and opinions expressed in this message may be those of the individual and not necessarily those of Foodstuffs South Island Ltd. Thank you. ---------------------------------------------------------- [Non-text portions of this message have been removed] ub.unitel GmbH, Schulstraße 16, 06792 Sandersdorf-Brehna Geschaeftsfuehrung Klaus Richter, Olaf Meyer Amtsgericht Stendal HRB 26389 FA Bitterfeld Steuernr. 116/107/08597 Ust.identNr. DE815796778 Deutsche Bank IBAN DE53 86070024 0 6143234 00 Kreissparkasse Anhalt-Bitterfeld IBAN DE69 80053722 0 3050326 82 _____________________________________________________________________ Dieses E-Mail ist nur für den Empfänger bestimmt, an den es gerichtet ist und kann vertrauliches bzw. unter das Berufsgeheimnis fallendes Material enthalten. Jegliche darin enthaltene Ansicht oder Meinungs- äußerung ist die des Autors und stellt nicht notwendigerweise die Ansicht oder Meinung von ub.unitel GmbH dar. Sind Sie nicht der Empfänger, so haben Sie diese E-Mail irrtümlich erhalten und jegliche Verwendung, Veröffentlichung, Weiterleitung, Abschrift oder jeglicher Druck dieser E-Mail ist strengstens untersagt. _____________________________________________________________________ ------------------------------------------------------------------------------------------------------------------------ Note: This e-mail message has been inspected for malicious content. Attention: The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. Please note that the views and opinions expressed in this message may be those of the individual and not necessarily those of Foodstuffs South Island Ltd. Thank you. ------------------------------------------------------------------------------------------------------------------------ [Non-text portions of this message have been removed]
