[firebird-support] RE: How can I use/map Active Directory Groups within my Firebird in Trusted Authentication mode?

[FSPAPA INCA Team i...@foodstuffs-si.co.nz [firebird-support]]

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 
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>>
Sent: Friday, 6 March 2020 1:38 AM
To: 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]
Gesendet: Dienstag, 3. März 2020 21:13
An: 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" 

Re: [firebird-support] Re: Cannot delete Firebird database file as it is in use by the application..

[Steve Naidamast blackfalconsoftw...@outlook.com [firebird-support]]

Thank you very much for all your replies.

I will try the "Clear Pools" option with the Firebird ADO.NET provider first.

If that doesn't work, I have come up with the idea to start a small, external 
process that will check for the release of the file by the application and then 
delete it...

Thank you again...  

Steve Naidamast
  Sr. Software Engineer
  blackfalconsoftw...@outlook.com

[firebird-support] Cannot delete Firebird database file as it is in use by the application..

[Elmar Haneke el...@haneke.de [firebird-support]]

> As a result, I have no idea why the Firebird FDB database file is
> still being used by the master application.
>
> Does anyone have any ideas as to how I can get around this so I can
> complete the delete process?


If all connections are properly closed you should be able to drop
database using firebird API, this should be possible even if deleting by
direct filesystem delete is not possible.


Elmar







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Re: Cannot delete Firebird database file as it is in use by the application..

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 2020-03-10 22:04, Steve Naidamast blackfalconsoftw...@outlook.com 
[firebird-support] wrote:
> Hello...
> 
>  I am developing a security extension, which is used as a loaded
> assembly to my main application, all of which use the Firebird
> Embedded Edition 2.59 for my application's database.
> 
>  As part of my security processes, I compress the file upon exiting
> the application.  When the compression process is completed, I want to
> delete the Firebird FDB database file.  However, I cannot do this as
> the Firebird database file is in use by the application.
> 
>  I have checked all of my data access coding and in every case the
> associated database connection is being properly closed upon
> completion of any database access method.
> 
>  As a result, I have no idea why the Firebird FDB database file is
> still being used by the master application.
> 
>  Does anyone have any ideas as to how I can get around this so I can
> complete the delete process?

IIRC, you are programming in C#, right? The Firebird ADO.net provider 
uses a connection pool, so although the logical connection used by your 
application maybe closed, the physical connection is still open. You can 
close unused connections in the pool using FbConnection.ClearAllPools() 
(or alternatively, use the connection property to not pool the 
connections).

Mark

Re: [firebird-support] Firebird client connection timeout

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 2020-03-10 20:14, Andrei Luís compuvale.softw...@gmail.com 
[firebird-support] wrote:
> Hummm, I think Dimitry is right. I made some tests here on client
> side:
> 
> server IP:   10.0.2.2
> path: d:\database.fdb
> 
> Using connection string: 10.0.2.2:d:\database.fdb  Correct IP and
> correct path, the connect is made instantly
> 
> Using connection string: 10..0.2.2:d:\data.fdb  Correct IP and wrong
> path, instantly my application recognizes the error, and a error
> message appears.
> 
>  Using connection string: 10.0.2.21:d:\database.fdb Wrong  IP and
> correct path, the application freezes for about 45 seconds, and just
> after that the error message appears.
> 
> So, even though this is not a Firebird issue, do anyone has a tip to
> solve this delay on windows? Should it be set on the server or on the
> clients?
> 
> Thanks in advance.

Maybe this can help: 
https://serverfault.com/questions/193160/which-is-the-default-tcp-connect-timeout-in-windows

Mark

AW: [firebird-support] RE: How can I use/map Active Directory Groups within my Firebird in Trusted Authentication mode?

['Mathias Pannier (unitel)' pann...@ubsysteme.de [firebird-support]]

> Though it might be helpful if a warning was issued when specifying an unknown 
> plugin name.

This would be a nice feature. A check if the domain/group was correct is also 
desirable.

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Gesendet: Freitag, 6. März 2020 10:24
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] RE: How can I use/map Active Directory Groups 
within my Firebird in Trusted Authentication mode?



On 2020-03-06 04:46, FSPAPA INCA Team 
i...@foodstuffs-si.co.nz
[firebird-support] wrote:
> 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.

Good find! I assume plugin names are not validated because available
plugins depend on configuration and available plugins on connect time,
and for example global mappings don't necessarily know which actual
plugins are available when connecting to a specific database. Though it
might be helpful if a warning was issued when specifying an unknown
plugin name.

Mark

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

[firebird-support] AW: How can I use/map Active Directory Groups within my Firebird in Trusted Authentication mode?

['Mathias Pannier (unitel)' pann...@ubsysteme.de [firebird-support]]

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>>
Sent: Friday, 6 March 2020 1:38 AM
To: 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]
Gesendet: Dienstag, 3. März 2020 21:13
An: 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>>
Sent: Tuesday, 3 March 2020 7:52 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.

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]
Gesendet: Dienstag, 3. März 2020 03:42
An: 
firebird-support@yahoogroups.com>
Betreff: [firebird-support] RE: How can I use/map