Friday, April 25, 2008

Integrated auth in Firebird 2.1

Just a quick sample of integrated windows auth in Firebird 2.1
Logon to my Firebird server as a user with administrative privileges on the machine:

C:\Programmi\Firebird\Firebird_2_1\bin>isql localhost:employee
Database: localhost:employee
SQL>

So I'm logged. let's check who am I for Firebird:

SQL> select current_user from rdb$database;

USER

===============================================================================

SYSDBA


SQL>

That's right, as an administrator on the machine, I'm SYSDBA on the database, now let's try with an unprivileged user I have on this machine:
I'll open another command line client as user postgres (guess what other database is installed on this machine?)

C:\Programmi\Firebird\Firebird_2_1\bin>runas /user:postgres cmd
Immettere la password per postgres:
Tentativo di avvio di cmd come utente "AB-2346789223445\postgres" ...

C:\Programmi\Firebird\Firebird_2_1\bin>

Now I have another command line window open

C:\Programmi\Firebird\Firebird_2_1\bin>isql localhost:employee
Database: localhost:employee
SQL> select current_user from rdb$database;

USER

===============================================================================

AB-2346789223445\POSTGRES


SQL>

Ok, I'm in as postgres, let's try a select

SQL> select * from country;

COUNTRY CURRENCY
=============== ==========
USA Dollar
England Pound
Canada CdnDlr
Switzerland SFranc
Japan Yen
Italy Lira
France FFranc
Germany D-Mark
Australia ADollar
Hong Kong HKDollar
Netherlands Guilder
Belgium BFranc
Austria Schilling
Fiji FDollar

SQL>

This works as expected, table country is available for SYSDBA and for PUBLIC, my current user is in PUBLIC by default.
Let's check a different table, on which PUBLIC has no grants

SQL> select * from tbl_stock_warehouse;
Statement failed, SQLCODE = -551
no permission for read/select access to TABLE TBL_STOCK_WAREHOUSE
SQL>

My request has been rejected as expected, great!
If this is not enough for you a lot of improvements in the user management area are currently in the works for Firebird 2.5 (Core 696 and CORE 1660) and in derived databases like RedSoft's one which will hopefully be merged in the official Firebird server.

2 comments:

José Torres said...

This is what I expect from any decent database. Is there any way of granting at field level?

pabloj said...

Field level user rights management is only partially supported in Firebird (as of 2.1), see http://www.firebirdfaq.org/faq94/