Authenticate through Active Directory

[Vincenzo Cali]

We are trying to use our Active Directory to authorize users on our Firebird Servers.

I know "Trusted User" can be used to authenticate Windows users, but is there a way to determine the SQL Privileges based on AD groups?

[Dimitry Sibiryakov]

'Vincenzo Cali' via firebird-support wrote 17.07.2024 13:05:
> I know "Trusted User" can be used to authenticate Windows users, but is there a
> way to determine the SQL Privileges based on AD groups?

Yes:
https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref50/fblangref50-security-mapping.html#fblangref50-security-mapping-create

Also dos/sql.extensions/README.mapping.html

> Map windows group to trusted firebird role:
>
> CREATE MAPPING WINGROUP1 USING PLUGIN WIN_SSPI FROM GROUP GROUP_NAME TO ROLE ROLE_NAME;


--
WBR, SD.

[Mark Rotteveel]

Interestingly enough, that specific example was not included in the
Language Reference. Does it actually work?

Mark
--
Mark Rotteveel

[Vincenzo Cali]

This is my failed attempt :
I have no permission to make a select on existing table. The connection to the database through trusted authentification is ok.

I used the security.db from firebird to grant rights.

Can someone help me with this problem?

cd C:\Programme\dbms\Firebird\Firebird_3_0 

isql 

CONNECT "C:\Programme\dbms\Firebird\Firebird_3_0\security3.fdb" 

CON> user 'SYSDBA' password 'XYZ'; 

SQL> show role;
       DM_OKULARDB_DDL_ROLE

SQL> show grants;

...

GRANT CREATE TABLE TO ROLE DM_OKULARDB_DDL_ROLE
GRANT ALTER ANY TABLE TO ROLE DM_OKULARDB_DDL_ROLE
GRANT DROP ANY TABLE TO ROLE DM_OKULARDB_DDL_ROLE
GRANT CREATE VIEW TO ROLE DM_OKULARDB_DDL_ROLE
GRANT DROP ANY VIEW TO ROLE DM_OKULARDB_DDL_ROLE

SQL> show mapping;
MAP_DDL_ROLE USING PLUGIN WIN_SSPI FROM GROUP parcit\dm_okulardb_ddl_firebird TO ROLE DM_OKULARDB_DDL_ROLE
TRUSTED_USERS USING PLUGIN WIN_SSPI FROM ANY USER TO USER

*** Global mapping ***
MAP_DDL_ROLE USING PLUGIN WIN_SSPI FROM GROUP parcit\dm_okulardb_ddl_firebird TO ROLE DM_OKULARDB_DDL_ROLE
TRUSTED_USERS USING PLUGIN WIN_SSPI FROM ANY USER TO USER

[Dimitry Sibiryakov]

'Vincenzo Cali' via firebird-support wrote 02.09.2024 14:31:
> I have no permission to make a select on existing table. The connection to the
> database through trusted authentification is ok.
> I used the security.db from firebird to grant rights.
> Can someone help me with this problem?

Try to remove role from connection credentials. Then use "select
current_user, current_role from rdb$database" to see the effective user name and
role.
I suspect you didn't grant the role to the user which is required if you use
an explicit role during connection.

--
WBR, SD.

[Vincenzo Cali]

Hello, 
Something's going wrong. 

It takes the Windows user PARCIT\VC and not the one from the AD group PARCIT\DM_OKULARDB_DDL_FIREBIRD
PARCIT\VC is definitely wrong

>  I suspect you didn't grant the role to the user which is required if you use
an explicit role during connection.

I thought it should work with a mapping:

CREATE MAPPING MAP_DDL_ROLE USING PLUGIN WIN_SSPI FROM GROUP PARCIT\DM_OKULARDB_DDL_FIREBIRD TO ROLE DM_OKULARDB_DDL_ROLE

You have an idea?

--
You received this message because you are subscribed to a topic in the Google Groups "firebird-support" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/firebird-support/1861DbxzAxk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/781bb7fe-6786-4f5a-b7b2-b2fd839af24c%40ibphoenix.com.

[Dimitry Sibiryakov]

'Vincenzo Cali' via firebird-support wrote 02.09.2024 15:16:
> It takes the Windows user PARCIT\VC and not the one from the AD group
> PARCIT\DM_OKULARDB_DDL_FIREBIRD
> PARCIT\VC is definitely wrong

It looks like you are logged in as the local user VC. You must log into
Windows using AD account to make AD auth work.

--
WBR, SD.

[Vincenzo Cali]

OK I´am logged into Windows using AD account. (User: VC)

We have an AD-Group : DM-OKULARDB_DDL_FIREBIRD and my Windows user VC is member of this group. It´s the same user from Windows AD account.
In the security DB from Firebird I create a ROLE: DM_OKULARDB_ROLE wich mapps the AD Group

CREATE MAPPING MAP_DDL_ROLE USING PLUGIN WIN_SSPI FROM GROUP PARCIT\DM_OKULARDB_DDL_FIREBIRD TO ROLE DM_OKULARDB_DDL_ROLE

TRUSTED_USERS USING PLUGIN WIN_SSPI FROM ANY USER TO USER

And I granted the role like this:
GRANT DM_OKULARDB_DDL_ROLE TO CREATE_DATABASE WITH ADMIN OPTION
GRANT DM_OKULARDB_DDL_ROLE TO DROP_DATABASE WITH ADMIN OPTION

GRANT CREATE TABLE TO ROLE DM_OKULARDB_DDL_ROLE

GRANT CREATE VIEW TO ROLE DM_OKULARDB_DDL_ROLE

GRANT ALTER ANY TABLE TO ROLE DM_OKULARDB_DDL_ROLE

GRANT ALTER ANY VIEW TO ROLE DM_OKULARDB_DDL_ROLE

GRANT DROP ANY TABLE TO ROLE DM_OKULARDB_DDL_ROLE

GRANT DROP ANY VIEW TO ROLE DM_OKULARDB_DDL_ROLE

what can I do next to resolve the problem?

--
You received this message because you are subscribed to a topic in the Google Groups "firebird-support" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/firebird-support/1861DbxzAxk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/c7ee3f2d-d214-43d4-8bde-3cd7ab74b7d7%40ibphoenix.com.

[Vincenzo Cali]

The Role ist none!!

Am Mo., 2. Sept. 2024 um 15:19 Uhr schrieb 'Dimitry Sibiryakov' via
firebird-support <firebird...@googlegroups.com>:
>

> --
> You received this message because you are subscribed to a topic in the Google Groups "firebird-support" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/firebird-support/1861DbxzAxk/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to firebird-suppo...@googlegroups.com.

> To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/c7ee3f2d-d214-43d4-8bde-3cd7ab74b7d7%40ibphoenix.com.

[Dimitry Sibiryakov]

'Vincenzo Cali' via firebird-support wrote 02.09.2024 16:32:
>
> We have an AD-Group : DM-OKULARDB_DDL_FIREBIRD and my Windows user VC is member
> of this group. It´s the same user from Windows AD account.
> In the security DB from Firebird I create a ROLE: DM_OKULARDB_ROLE wich mapps
> the AD Group

What shows commands `whoami` and `whoami /groups` in Windows?
What shows query "select current_user, current_role from rdb$database"?

--
WBR, SD.

[Vincenzo Cali]

Buongiorno Dimitry Sibiryakov,

whoami /groups` in Windows was a very good hint. I restarted the server and the AD group also appeared.

Connecting now with flameRobin shows the correct USER and ROLE

Unfortunately, I still don't have the rights to read the contents of a table

Here my configuration in the security.db of firebird:

Microsoft Windows [Version 10.0.17763.6189]
(c) 2018 Microsoft Corporation. Alle Rechte vorbehalten.

C:\Users\vc>cd C:\Programme_parcIT\dbms\Firebird\Firebird_3_0
C:\Programme_parcIT\dbms\Firebird\Firebird_3_0>isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> CONNECT "C:\Programme_parcIT\dbms\Firebird\Firebird_3_0\security3.fdb"
CON> user 'SYSDBA' password 'xyz';

Database: "C:\Programme_parcIT\dbms\Firebird\Firebird_3_0\security3.fdb", User: SYSDBA

SQL> select CURRENT_USER from rdb$database;
USER
===============================
SYSDBA

SQL> show role;
       DM_OKULARDB_DDL_ROLE

SQL> show mapping;

MAP_DDL_ROLE USING PLUGIN WIN_SSPI FROM GROUP PARCIT\DM_OKULARDB_DDL_FIREBIRD TO ROLE DM_OKULARDB_DDL_ROLE
TRUSTED_USERS USING PLUGIN WIN_SSPI FROM ANY USER TO USER

*** Global mapping ***

MAP_DDL_ROLE USING PLUGIN WIN_SSPI FROM GROUP PARCIT\DM_OKULARDB_DDL_FIREBIRD TO ROLE DM_OKULARDB_DDL_ROLE
TRUSTED_USERS USING PLUGIN WIN_SSPI FROM ANY USER TO USER

SQL> show grants;
/* Grant permissions for this database */
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON PLG$SRP TO VIEW PLG$SRP_VIEW
GRANT SELECT, UPDATE (PLG$ATTRIBUTES) ON PLG$SRP_VIEW TO PUBLIC
GRANT UPDATE (PLG$COMMENT) ON PLG$SRP_VIEW TO PUBLIC
GRANT UPDATE (PLG$FIRST) ON PLG$SRP_VIEW TO PUBLIC
GRANT UPDATE (PLG$LAST) ON PLG$SRP_VIEW TO PUBLIC
GRANT UPDATE (PLG$MIDDLE) ON PLG$SRP_VIEW TO PUBLIC
GRANT UPDATE (PLG$SALT) ON PLG$SRP_VIEW TO PUBLIC
GRANT UPDATE (PLG$VERIFIER) ON PLG$SRP_VIEW TO PUBLIC
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON PLG$USERS TO VIEW PLG$VIEW_USERS
GRANT SELECT, UPDATE (PLG$FIRST_NAME) ON PLG$VIEW_USERS TO PUBLIC
GRANT UPDATE (PLG$GID) ON PLG$VIEW_USERS TO PUBLIC
GRANT UPDATE (PLG$GROUP_NAME) ON PLG$VIEW_USERS TO PUBLIC
GRANT UPDATE (PLG$LAST_NAME) ON PLG$VIEW_USERS TO PUBLIC
GRANT UPDATE (PLG$MIDDLE_NAME) ON PLG$VIEW_USERS TO PUBLIC
GRANT UPDATE (PLG$PASSWD) ON PLG$VIEW_USERS TO PUBLIC
GRANT UPDATE (PLG$UID) ON PLG$VIEW_USERS TO PUBLIC

GRANT DM_OKULARDB_DDL_ROLE TO CREATE_DATABASE WITH ADMIN OPTION
GRANT DM_OKULARDB_DDL_ROLE TO DROP_DATABASE WITH ADMIN OPTION

GRANT CREATE TABLE TO ROLE DM_OKULARDB_DDL_ROLE

GRANT ALTER ANY TABLE TO ROLE DM_OKULARDB_DDL_ROLE

GRANT DROP ANY TABLE TO ROLE DM_OKULARDB_DDL_ROLE

GRANT CREATE VIEW TO ROLE DM_OKULARDB_DDL_ROLE

GRANT ALTER ANY VIEW TO ROLE DM_OKULARDB_DDL_ROLE

GRANT DROP ANY VIEW TO ROLE DM_OKULARDB_DDL_ROLE

--
You received this message because you are subscribed to a topic in the Google Groups "firebird-support" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/firebird-support/1861DbxzAxk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4bad7c59-2722-4025-98d7-c061f81eebd7%40ibphoenix.com.

[Vincenzo Cali]

Here is the correct screenshot of the missing rights

[Dimitry Sibiryakov]

'Vincenzo Cali' via firebird-support wrote 03.09.2024 9:06:
> whoami /groups` in Windows was a very good hint.

Now do the same WITHOUT `/group`. IT still look like you are logged locally
and not into domain.

> Unfortunately, I still don't have the rights to read the contents of a table

It is quite natural because result `SHOW GRANTS` below has no mention of
"ifbini" table (or procedure).

Please, stop posting screenshots where text copy-paste is enough.

--
WBR, SD.

[Vincenzo Cali]

> Now do the same WITHOUT `/group`. IT still look like you are logged locally and not into domain.

C:\Users\vc>whoami
parcit\vc
I´am definetly logged into domain.

> It is quite natural because result `SHOW GRANTS` below has no mention of "ifbini" table (or procedure).

Should I realy grant every table of all databases? We have about 2600
tables per Database and about 700 Firebird databases.
My goal was to give the role all read and write permissions so that I
don't have to authorize every table

>Please, stop posting screenshots where text copy-paste is enough.

Sorry about the screenshots but sometimes a picture says more. But I
won't post unnecessary screenshots anymore.

Am Di., 3. Sept. 2024 um 11:29 Uhr schrieb 'Dimitry Sibiryakov' via
firebird-support <firebird...@googlegroups.com>:
>

> --
> You received this message because you are subscribed to a topic in the Google Groups "firebird-support" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/firebird-support/1861DbxzAxk/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to firebird-suppo...@googlegroups.com.

> To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/daa6f49f-70fd-422d-ad66-57c24128305c%40ibphoenix.com.

[Dimitry Sibiryakov]

'Vincenzo Cali' via firebird-support wrote 03.09.2024 12:57:
> parcit\vc
> I´am definetly logged into domain.

Only if your domain is called "parcit".

https://learn.microsoft.com/en-us/windows-server/administration/windows-commands/whoami

> Should I realy grant every table of all databases?

There is a system privilege SELECT_ANY_OBJECT_IN_DATABASE but not in Firebird
3.0 so yes, you have to grant users a rights for every database object they must
access individually. That's how SQL security works: user must have minimal
necessary access to minimal list of objects.
Otherwise you can grant everything to PUBLIC and have no security at all.

https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref50/fblangref50-security.html#fblangref50-security

--
WBR, SD.