Embedded User Authentication in InterBase 7.5
Dmitri Kouzmenko
One of the most important new features in InterBase 7.5 is user authentication, which is embedded in the database. Let’s consider the scheme used in the previous version:
Standard scheme
In InterBase, a separate special database isc4.gdb is intended for user list storing. In InterBase 7.0 this database was renamed (admin.ib), and in addition, new ibconfig parameter was added (ADMIN_DB), which allows to specify any name for this database.
In isc4.gdb/admin.ib, there is a basic “USERS” table, which contains username, password, and other parameters. When a client connects to a database:
1. the front-end encrypts the password by DES algorithm with data loss, and then sends the username and encrypted password to the server.
2. the server encrypts the received password once again by the same lossy DES, and then calls isc4/admin in the “USERS” table, finds the necessary user, and then verifies the received password with the stored one.
3. if the passwords are equal, the user connects to the database he/she specified. And if they do not, the user is unable to connect to the database (”wrong user name or password” error reported).
As you can see, to access any database on this server, a user must be specified in isc4/admin only once. In the future, in a particular database, user access is defined by the rights he is granted.
This scheme is insufficient when used in:
- single-user applications. It becomes necessary to deploy both the database and admin.ib.
- deployed or stolen databases. Anyone can “slip” his/her own “admin.ib” with SYSDBA/masterkey to the server, and, as a result, completely control a database.
- systems, in which a user has to connect to only those databases, with which one is allowed to work.
Embedded User Authentication
In InterBase 7.5 you can either refuse using admin.ib (see below), or combine admin.ib with user control in the database. For that purpose, attributes of several system tables were extended, and new SQL-operators were added to manage this functionality (in “gsec” a “user_database” option is added for user management in such databases).
This functionality is supported only for ODS 11.2, i.e. for the databases created or restored from backup in InterBase 7.5. At that, previous versions of InterBase, for example, 7.1 and below, when attempting to connect to such database, will return two types of messages:
- product DATABASE ACCESS is not licensed for databases, in which EUA is enabled or disabled
- internal gds software consistency check (decompression overran buffer (179), file: sqz.c line: 229) for databases, in which EUA was never enabled
In other words, there is no other way to connect to the database, but from InterBase 7.5 specifying a required password (stored in the database) for a specific user (we do not consider the possibility of “hacking” such database, i.e. editing it in HEX-editor).
Enabling EUA
There are two ways of enabling EUA in a database:
1. When creating a database, specify an extra option - WITH ADMIN OPTION - in CREATE DATABASE
2. For any ODS 11.2 database, enter the following operator
ALTER DATABASE ADD ADMIN OPTION
In any case, among ODS 11.2 database system tables there always is the RDB$USERS table. It is an equivalent to the USERS table from admin.ib (RDB$DEFAULT_ROLE, RDB$USER_ACTIVE, and RDB$USER_PRIVILEGE columns are added).
When enabling EUA, it becomes active right away, and in the RDB$USERS table the SYSDBA user standard record appears (the password is encrypted “masterkey”), and with RDB$USER_PRIVILEGE = 1. After that, when connecting to a database, the server ignores presence (or absence) of the user in admin.ib, as well as his/her password. That is to say, when EUA is enabled, one can connect to a database only if username/password combination, stored in rdb$users, is correctly specified.
EUA can be temporarily deactivated by the command
ALTER DATABASE SET ADMIN OPTION INACTIVE
and activated
ALTER DATABASE SET ADMIN OPTION ACTIVE
During deactivation, in RDB$USERS field RDB$USER_ACTIVE is set to ‘N’ for all user records (including SYSDBA). When activating, it is performed conversely: RDB$USER_ACTIVE is entered to ‘Y’ for all users. Doing that, be careful, since if some users were disabled before EUA deactivation, as soon as EUA is activated, all EUA users will be able to access the database (i.e. all EUA accounts will be enabled).
You can completely delete EUA, together with all user records by the command:
ALTER DATABASE DROP ADMIN OPTION
This will clear the RDB$USERS table, and restore functioning of the standard authentication scheme (through admin.ib).
User management
If EUA is enabled, you can manage users:
{CREATE | ALTER} USER SET
option : PASSWORD
[NO] DEFAULT ROLE
[NO] SYSTEM USER NAME
[NO] GROUP NAME
[NO] UID
[NO] GID
[NO] DESCRIPTION
[NO] FIRST NAME
[NO] MIDDLE NAME
[NO] LAST NAME
ACTIVE
INACTIVE
Examples:
CREATE USER TEST SET PASSWORD 'TEST', NO LAST NAME, DEFAULT ROLE ABC
As a result, a user TEST with “TEST” password will be created; the LAST_NAME column will be set NULL, the default role will be “ABC” (and rdb$user_privilege = 0, i.e. “not a database owner”). The same can be performed by the following command set:
CREATE USER TEST SET PASSWORD 'TEST'; ALTER USER TEST SET NO LAST NAME, DEFAULT ROLE ABC;
Draw attention to the fact that one can “enable” and “disable” users by the alter user xxx set inactive/active command. There is no such possibility in the standard admin.ib.
Authentication order
It is important to comment how exactly connections are performed in case EUA is active in a database:
The server opens a specific database.
1. EUA disabled - user authentication is accomplished from admin.ib
2. EUA enabled - user authentication (any user, including SYSDBA), is accomplished from rdb$users of this particular database
That is, when enabling SYSDBA and changing password for SYSDBA, it will be possible to connect to this database under the “SYSDBA” name, only if the user specifies a correct password.
Attention: Admin.ib is mandatory in any case. The server, when trying to connect to a database, requires presence of admin.ib regardless of whether database EUA is enabled or not.
User schemes combining
Thus, in InterBase 7.5 two schemes of user management are supported: standard and EUA. This allows building the following schemes
1. standard: all users included in admin.ib are allowed to access all databases. Access rights to a specific database are defined by grants
2. EUA: the usernames for a particular database are specified in this database only. Accordingly, only these users can connect to it.
3. standard+EUA 1: SYSDBA everwhere is the same (including password), i.e. it administrates all databases on the server. The server databases can be divided into 2 sets: the first set without EUA (public access from admin.ib), and the second set with EUA (only the users specified in this particular database are allowed to access it)
4. standard+EUA 2: All users are common for all databases (if those, for example are copied from one source), but SYSDBA requires different passwords. That is to say, one SYSDBA manages the databases, which do not have EUA, while other SYSDBA controls the databases with EUA enabled.
5. standard+EUA 3: SYSDBA usernames and passwords are different for all databases - with or without EUA. The picture illustrates an example of how two different users connect to different databases.
The picture illustrates an example of how two different users connect to different databases.
User 1 can connect to databases with EUA disabled. To access DB1.IB, it is necessary to create a new user (USER1) in this database, and specify either the same, or a different password (if needed).
User 2 can connect to DB1.IB only. If this user is specified in ADMIN.IB, he/she will be able to work with the databases, in which EUA is disabled.
BACKUP/RESTORE
At the given moment, in InterBase 7.5.0.174 the following behavior is detected (there is no report about fixing that problem in IB 7.5 SP 1):
After restore, the rdb$user_privilege column of the rdb$users table has null value. Even though this is “unimportant” for SYSDBA, in cases when SYSDBA is not the database owner (the owner is, say, the “TEST” user), that particular user, as well as any other users, cannot login to such database.
The situation can be corrected if one logins to this database as SYSDBA, setting “1″ value in the column instead of null for the database owner, and “0″ for all other users. After this procedure, EUA’s workability will be restored.
To date (16.05.2005) in Borland it is considered as bug IB 7.5.0.174.
A workaround:
disable EUA before backup (alter database set admin option inactive); after restore is performed, enable EUA (alter database set admin option active). However, to avoid change of the owner (unless it is sysdba), there should be an owner of the database with EUA in admin.ib.
Other issues
Sometimes, for different purposes, a database can be created by a user other than SYSDBA, for example, in order to use a database owner as a “backup user” (at that, all objects are created and modified on behalf of SYSDBA, and the owner cannot change them). In this case, a user who created the database, is the database owner, and thus can perform backup/restore being an owner not only of the database but also of all objects created by her/him. There are several features of applying such method when EUA is enabled.
1. Create a database not as a “SYSDBA,” but as a “TEST” user. As soon as a “TEST” user is created, it becomes a database owner. At this point, of course, the TEST (with password “test,” for example) user should be specified in admin.ib.
2. Enable EUA in the database.
2. Enable EUA in the database.
In the RDB$USERS table a record about the TEST user with “test” password appears (the password is double-encrypted, as in admin.ib), rdb$user_active = Y and rdb$user_privilege = 1
3. Add a “local” user USR create user USR set password ‘usr’;
4. All this leads to an interesting situation. The “TEST” user can perform backup, but would it be a backup from admin.ib, or from the database? Let’s change the TEST’s password in admin.ib. Let the password be “tttt”.
5. try to backup from TEST user through admin.ib
gbak -b db.ib db.ibk -v -user TEST -pass tttt
does not pass. Try a user from EUA
gbak -b db.ib db.ibk -v -user TEST -pass test
it does pass. That is, only the user specified in eua can do backup (i.e. database owner)
6. So far, it seems like one can delete the “TEST” user in admin.ib, or completely delete admin.ib. But without admin.ib the server will not connect even to the databases with active EUA. In addition, restore should be done by a user specified in admin.ib, since when restoring, it is impossible to find out whether a database has EUA or not.
gbak -c db.ibk 1.db -v user TEST -pass test
does not pass, as it was expected. The TEST user has a different name in admin.ib.
gbak -c db.ibk 1.db -v user TEST -pass tttt
restore is successfully accomplished.
However, as it was already said above, the column rdb$user_privilege = NULL. This makes impossible for any EUA user to connect to the restored database EUA (including the “TEST” user with password “test”).
Connect as TEST/tttt, set “0″ instead of “null” in the rdb$users column of the TEST record, and then disconnect… As a result, EUA resumes work (see above an exam- ple of temporary solution of the problem).
Conclusion
Regarding all that, we can come to several conclusions:
- EUA in this version does not “outlive” backup/restore not only for the owner, but for SYSDBA as well. SYSDBA is “both king and god” for databases and that is why rdb$user_privilege = null is unnoticeable for a developer. This becomes important when one begins to use the database in operating mode.
- When performing restore, there is a need to verify username/password. For regular databases, restoring with other usernames is performed when it is necessary to change the database owner. However, since EUA is enabled for users within the database, restore can be performed in no other way but specifying a new user at gbak -c. Actually, SYSDBA from admin.ib may be a “wrong user” differing from SYSDBA in the database with EUA, if they have different passwords.
- Probably, it would be better to create a flag in Header Page of the database, which would signify presence of EUA. In this case, users would be ignored during restore process. This is up to InterBase 7.5 developers.
- In spite of apparent “autonomy” of EUA, if there is no admin.ib, it would be possible neither to connect to the database, nor perform restoring.
