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
How to hide list of users/passwords
How to log login-attempts
How to slow down intruders

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

External table, because transaction used by IB server to verify username/password is not committed, but rolled back.
```
  CREATE TABLE log_table
    EXTERNAL FILE 'C:\Program Files\Borland\InterBase\isc4.log'
    ( tstamp TIMESTAMP,
      uname  CHAR(31) ); 
```
If you want log table to be readable as text file, you can add two more fields: one CHAR(1) to separate tstamp and uname fields, one CHAR(2) to be filled with CR+LF codes by stored procedure, and use CHAR(20) instead of TIMESTAMP.
Stored procdure (select type, to be callable from view), that is able to return information whether user is allowed to see specific row. In the method I chose the procedure either returns a row (i.e. SUSPEND is called) to indicate success, or does not return any row (i.e. EXIT is called) to indicate that row is forbidden. Output parameter is just formal, it's value is ignored.
```
  CREATE PROCEDURE log_proc
    (un VARCHAR(31))
  RETURNS
    (x CHAR(1))
  AS
  BEGIN
    IF (USER='')
    THEN
      INSERT INTO log_table (TSTAMP, UNAME)
             VALUES ( CURRENT_TIMESTAMP, :un);

    IF (USER='' OR USER='SYSDBA' OR USER=:un)
    THEN
      SUSPEND;
  END 
```
Notice the test (USER=''); when IB server verifies password, USER variable is empty! It helps distinguish whether password is being verified by IB server, or whether user is directly connected to isc4.gdb.
Rename table USERS. It can be easily done by creating new table, copying existing records, and dropping original table.

Create view that will be used instead of original USERS table and that calls our select stored procedure.

  CREATE VIEW USERS (USER_NAME) AS
    SELECT *
      FROM users2
     WHERE EXISTS (SELECT * FROM log_proc(users2.user_name));

Grant appropriate rights, i.e.

  GRANT SELECT ON USERS TO PUBLIC;

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

  GRANT INSERT   ON log_table           TO PROCEDURE log_proc;
  GRANT EXECUTE  ON PROCEDURE log_proc  TO PUBLIC;

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

Security database name is now "security.fdb" by default, and can be changed in "aliases.conf" file by
```
  C:\Program Files\Firebird\security.fdb = C:\Program Files\Firebird\My_Security_Db.fdb 
```
(Server restart is not necessary when changing aliases.conf.)
The transaction used to check password in security database is now Read Only, which means that it is no more possible to insert any data into external log table. Thus, scripts used in How to log login-attempts and How to slow down intruders sub-chapters are no longer applicable to Firebird 1.5. The only workaround is to replace the functionality of external tables by UDF functions.
In older IB and FB versions, database server first connected to security database, retrieved necessary informations, and then disconnected.
In Firebird 1.5, however, once the connection to security database is established, it is kept until Firebird server is shutdown. It means that changes to security database must be done on its copy, i.e.
1. make backup of security.fdb
2. restore it under different name
3. make changes in that copy (i.e. run the script)
4. shut down Firebird server
5. swap old and new versions of security.fdb
External files are disabled by default now, and can be enabled in "firebird.conf" file by
```
  ExternalFileAccess = Full 
```
or
```
  ExternalFileAccess = Restrict dir1;dir2;... 
```
(But this change is not important for modified security database because of limitation imposed by Read Only transaction.)

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"