Because this paper was originally written for Firebird 1/InterBase 6 servers, here are important notes for users of newer versions:

Security - Enhanced isc4.gdb

Whenever user logs into InterBase database, his/her password is verified against encrypted password stored in special database - isc4.gdb, that is common for all databases on that IB server. I said "special database", but in fact it is ordinary database like any other. It's (simplified) structure looks like this
  CREATE TABLE USERS (
    USER_NAME  VARCHAR(128),
    PASSWD     VARCHAR(32) );

  GRANT SELECT ON USERS TO PUBLIC;

At first glance you can see several drawbacks of such structure, e.g. only SYSDBA can modify USERS table, so users can't modify their own passwords. But since you can connect to isc4.gdb like to any other database and modify its structure, here are some tips how to improve it. You can also look at full scripts to modify ordinary isc4.gdb to its enhanced version. Never forget to do (physical) copy of isc4.gdb before playing with that database.

How to allow users to modify their own passwords

The easiest and well known modification is to grant update on USERS table to PUBLIC, and add trigger that prevents users (except SYSDBA) from modifying somebody else's password. You can look at full script here . Note that if you want to use standard methods for modifying password (like IBConsole or GSEC utility), you need to grant update rights to several fields (not just PASSWD), specifically
  GRANT UPDATE(PASSWD, GROUP_NAME, UID, GID, FIRST_NAME, MIDDLE_NAME, LAST_NAME)
     ON USERS
     TO PUBLIC;
This modification (as well as original isc4.gdb) has still drawback that full list of users and their encrypted passwords is visible to PUBLIC. (And thus it is easier to download list of other users/passwords and try to break them locally by brute force.)


How to hide list of users/passwords

When you rename USERS table and create USERS view instead of it, you will allow users to modify their passwords as well as hide full list of users from PUBLIC. Each user (except SYSDBA) will see only one (its own) record in isc4.gdb! New isc4.gdb will then look like this (simplified version):
  CREATE TABLE USERS2 (
    USER_NAME  VARCHAR(128),
    PASSWD     VARCHAR(32) );

  CREATE VIEW USERS AS
    SELECT *
      FROM USERS2
     WHERE USER = ''
        OR USER = 'SYSDBA'
        OR USER = USER_NAME;

  GRANT SELECT
     ON USERS
     TO PUBLIC;

  GRANT UPDATE(PASSWD, GROUP_NAME, UID, GID, FIRST_NAME, MIDDLE_NAME, LAST_NAME)
     ON USERS
     TO PUBLIC;

Real table USERS2 is visible only to SYSDBA. The condition
  USER = USER_NAME  
ensures that each user sees its own record. The condition
  USER = 'SYSDBA'  
ensures that SYSDBA can see all records. The condition
  USER = ''  
is important because USER variable contains empty string during password verification!
You can look at full script to modify standard isc4.gdb here .


How to log login-attempts

Replacing USERS table in security database by USERS view has one great advantage - it allows us to call stored procedure whenever user tries to login, i.e. whenever IB server executes command
  SELECT PASSWD
    FROM USERS
   WHERE USER_NAME=:usr;
In other words, we can have some kind of select-trigger, or login-trigger. Such procedure can then be used e.g. to refuse login during some part of day, or to log date/time when user tried to login. Note that it is not possible to distinguish whether login was successful, and that it logs only known usernames (i.e. names already stored in USERS table). But even this limited information can be useful, e.g. to see whether somebody logged at unusual time (night), or to reveal suspicious number of logins during short time. To implement this we need Full script is here .

Because log entires are constantly appended to log_table, it is necessary from time to time to delete or rename the external file! After the user name/password is verified, the security database (and thus external file as well) is disconnected/released by the server, so renaming the file should not be problem.


How to slow down intruders

Once we are able to log who/when tried to login to database, we can use this information to further restrict access. It is possible to e.g. count number of login attempts for given username during last minute and refuse connection if this number is too high, thus effectively preventing using brute force to break into database by scanning all possible passwords. So when somebody tries to guess password by trying to login with different password combinations, it will temporarily block that username from login; for this reason time interval and allowed login count should be carefuly chosen to slow down intruder, but still do not restrict regular users too much (e.g. when somebody just make typo in password). Similar system (more sophisticated, of course) is used in OpenVMS OS. The relevant part of code in SP is as simple as this
  DECLARE VARIABLE cnt INTEGER;

  SELECT COUNT(*)
    FROM log_table
   WHERE uname=:un
     AND tstamp>CURRENT_TIMESTAMP-0.0007
    INTO :cnt;

  IF (cnt>=3) THEN EXIT;

where you can change constants 3 (allowed number of mistakes) and 0.0007 (approximately 1 minute). Full script is here. This procedure works (i.e. prevents access) for all users. One possible modification would be to choose one user (different than SYSDBA, because it is the most endangered username) that is not restriced by that procedure, and that owns all databases (and thus has rights to shutdown the database).


Notes for Firebird 2 users

Security database is now called "security2.fdb", and it already implements many concepts from this paper. It contains table RDB$USERS, but users have access to it via view USERS only. Users can now change their own passwords by default. There is built in protection against brute force attack - after a few unsuccessfull attempts to login, the user and IP address will be locked for few seconds. Although it is not possible to directly connect to security database, it is possible to make backup of it using Services API.
See Release notes for more details.


Notes for Firebird 1.5 users


Notes for InterBase 7 users

Security database name is now "admin.ib" by default, and can be changed in "ibconfig" file by
  ADMIN_DB "new_name_of_admin_database"

External files are now allowed in "ext" subdirectory only, or in directories specified in "ibconfig" file by

  EXTERNAL_FILE_DIRECTORY "/ext"

Copyright © 2004, 2005, 2006 Ivan Prenosil