How-To? Windows User <-> Firebird User

[Elvis]

Hi DPsen

Bei Firebird 2.1 kann man sich ja als Windows user an die DB anmelden.
Alles schön und gut, aber irgendwie fand' ich im großen Zwischennetz keine Infos dazu wie ich Windows-User zu Firebird-Usern verknüpfe oder Domain-Rollen aus Windows Firebird Rollen zuordne.

Ich würde es schon begrüßen wenn meine Services keine Username/password im ConnectionString bräuchten, sondern ich dem Windows user des Services einfach die nötigen Rechte geben könnte.

Das ganze Sicherheitssystem in Firebird ist allerdings dermaßen anders als in allen DBMS mit denen ich bisher zu tun hatte, so dass ich mir ganz schön verlaufen vorkomme.


btw: Dass ein lokaler Admin als Sysdba ankommt weiß ich schon, aber das wäre nicht akzeptabel...

Edit: Hatte Thread titel nicht wirklich ausgefüllt

[Elvis]

Ein wirkliches Mapping von Firebird Roles zu Domain Groups scheint es leider überhaupt nicht zu geben.
Ebenfalls kann man anscheinend keine Firebird User und Domain User verknüpfen.

Was aber zu gehen scheint ist das Vergeben einer Rolle (oder Berechtigung) an den namen eines Domain Users (Die Quotes sind wichtig!):

SQL-Code:

grant MyRole to "DOMAINNAME\USERNAME";
grant select, insert, update, delete on MyTable to "DOMAINNAME\USERNAME";

[mkinzler]

Sollte automatisch geschehen (FB-User = Windows-Benutzer)

Zitat von Firebird 2.1 Release Notes:

Using Windows Security to Authenticate Users
Alex Peshkov

SQL Privileges
Administrators
Configuration Parameter “Authentication”
Forcing Trusted Authentication

(V.2.1) From Firebird 2.1 onward, Windows “Trusted User” security can be applied for authenticating Firebird users on a Windows host. The Trusted User's security context is passed to the Firebird server and, if it succeeds, it is used to determine the Firebird security user name.

Simply omitting the user and password parameters from the DPB/SPB will automatically cause Windows Trusted User authentication to be applied, in almost all cases. See the Environment section, below, for exceptions.

Illustration

Suppose you have logged in to the Windows server SRV as user 'John'. If you connect to server SRV with isql, without specifying a Firebird user name and password:

isql srv:employee


and do:

SQL> select CURRENT_USER from rdb$database;


you will get something like:

USER
================================================== ==
SRV\John


SQL Privileges

Windows users can be granted rights to access database objects and roles in the same way as regular Firebird users, emulating the capability that has been always been available users of Unix and Linux hosted Firebird databases.
Administrators

If a member of the built-in Domain Admins group connects to Firebird using trusted authentication, he/she will be connected as SYSDBA.
Configuration Parameter “Authentication”

The new parameter Authentication has been added to firebird.conf for configuring the authentication method on Windows. Possible values are.-

Authentication = Native

Provides full compatibility with previous Firebird versions, avoiding trusted authentication.
Authentication = Trusted

The Security database is ignored and only Windows authentication is used. In some respects, on Windows this is more secure than Native, in the sense that it is no less and no more secure than the security of the host operating system.
Authentication = Mixed

This is the default setting.

To retain the legacy behaviour, when the ISC_USER and ISC_PASSWORD variables are set in the environment, they are picked and used instead of trusted authentication.
Note

Trusted authentication can be coerced to override the environment variables if they are set—refer to the notes below.

Forcing Trusted Authentication

For the situation where trusted authentication is needed and there is a likelihood that ISC_USER and ISC_PASSWORD are set, there is a new DPB parameter that you can add to the DPB—isc_dpb_trusted_auth.

Most of the Firebird command-line utilities support parameter by means of the switch -tru[sted] (the abbreviated form is available, according to the usual rules for abbreviating switches).
Note

The qli and nbackup utilities do not follow the pattern: they use single-letter switches that are somewhat arcane. The switch of interest for qli is -K). For nbackup, watch this space. The facility to force trusted authentication is yet to be implemented for it.

Example

C:\Pr~\bin>isql srv:db -- log in using trusted authentication
C:\Pr~\bin>set ISC_USER=user1
C:\Pr~\bin>set ISC_PASSWORD=12345
C:\Pr~\bin>isql srv:db -- log in as 'user1' from environment
C:\Pr~\bin>isql -trust srv:db -- log in using trusted authentication


Important

Windows rules for full domain user names allow names longer than the maximum 31 characters allowed by Firebird for user names. The 31-character limit is enforced and, from V.2.1, logins passing longer names are disabled. This will remain the situation until the mapping of OS objects to database objects is implemented in a later Firebird version.

[Elvis]

Zitat von mkinzler:

Sollte automatisch geschehen (FB-User = Windows-Benutzer)

Thx für die Release notes (ist für andere leser sicherlich interessant! ), aber das steht auch in den Text file im FB root dir.

Was mich interessierte war hauptsächlich das Mapping von der Domain-Group MeineDomäne\BliblablubbUsers zu der FB-Role SomeRole.
So dass man einfach nur den User, in dem mein Service läuft in diese Gruppe stecken muss.
Es ist aber so, dass der Domain User einfach so in Firebird auftaucht, ohne wirklich mit einem richtigen User verknüpft zu sein.
Für meine Zwecke scheint das fast ausreihend zu sein. Wobei ich auch gerne einen Non-Admin User zu einem DB in Firebird gemacht hätte.
Ich weiß nämlich nicht wie ich einer Rolle/User Rechte zum Erzeugen von Tabellen geben kann...

[mkinzler]

Wenn ich dich richtig verstehe, wird dein Wunsch wohl ab FB2.5 erfüllt:

Zitat von FireBird 2.5 Release Notes:

New RDB$ADMIN System Role
Alex Peshkov

A new pre-defined system role RDB$ADMIN has been added for transferring SYSDBA privileges to another user. Any user, when granted the role in a particular database, acquires SYSDBA-like rights when attaching to that database with the RDB$ADMIN role specified.

To assign it, SYSDBA should log in to that database and grant this role to the user, in the same way he would grant any other role to a user.

The following example transfers SYSDBA privileges to users named User1 and Admins\ADMINS. The second user in our example is typical for Windows trusted authentication:

GRANT RDB$ADMIN TO User1;
GRANT RDB$ADMIN TO "Admins\ADMINS";


Note

For Windows trusted authentication, a database can be set up to provide the RDB$ADMIN role to Windows Administrators automatically. This is described in more detail presently.
Windows Domain Administrators

On POSIX hosts, the root user always had SYSDBA privileges, but the same was not possible for a domain administrator on Windows until Firebird 2.1. There, a configuration parameter, Authentication, was introduced whereby a user logged in as a Windows domain administrator could automatically gain server access with SYSDBA privileges through trusted user authentication. The mechanism for achieving that has changed with the introduction of the new system role and associated behaviour in v.2.5.
Automatically Mapping RDB$ADMIN to a Windows User

The situation has not changed for the root user on POSIX but, on Windows, a domain administrator must now be granted the RDB$ADMIN role in order to get SYSDBA access. By default, the SYSDBA must perform this GRANT manually for any user, including a domain administrator. However, the SYSDBA can configure it to happen automatically for Windows Administrators if the Authentication parameter in firebird.conf is 'mixed' or 'trusted'. A new ALTER ROLE syntax is is implemented for this specialised purpose.

Auto-mapping Syntax

To configure a database to auto-grant the RDB$ADMIN role to Administrators, use the following statement:

ALTER ROLE RDB$ADMIN
SET AUTO ADMIN MAPPING;


To revert to the default setting, preventing administrators from getting SYSDBA privileges automatically, issue this statement:

ALTER ROLE RDB$ADMIN
DROP AUTO ADMIN MAPPING;


Escalating RDB$ADMIN Scope

Because security2.fdb is created as (or should be upgraded to) an ODS 11.2 database, it has the pre-defined RDB$ADMIN role, too. SYSDBA can grant RDB$ADMIN in security2.fdb to a user if that user needs the same rights as SYSDBA to administer all other users through gsec or the Services API, i.e., create and drop users or alter any user.

The auto-mapping facility described above is also applicable, if required.
Important

If the user attaches with a user database role passed in the DPB (connection parameters), it will not be replaced with RDB$ADMIN, i.e., he/she will not get SYSDBA rights.

[mkinzler]

http://mariohcornejo.blogspot.com/20...ntication.html