The gsec Utility - Firebird

Firebird provides the gsec utility as a command-line interface for maintaining the security database. It has its own shell for interactive use, or gsec commands can be run directly from an operating system command shell or an executable script (shell script or batch file).

Any authentic user can run gsec, but only the SYSDBA user can modify the user data stored in it. The user name and password will be required unless the environment variables ISC _USER and ISC _PASSWORD are visible to the command shell in which you are running. On POSIX, if you are logged in as root, you can invoke gsec without entering –user and –password parameters.

Starting a gsec Interactive Session

At the command line, at Firebird’s /bin directory prompt, type the following.

For POSIX:

./gsec -user sysdba -password masterkey

For Windows:

gsec -user sysdba -password masterkey

The prompt changes to GSEC>, indicating that you are running gsec in interactive mode.

To end an interactive session, type QUIT at the prompt.

Running gsec As a Remote Client

SYSDBA can use gsec on a client machine to administer user authentication on a remote server. The invocation syntax is different: It needs the –database switch followed by the full network path to the security database. For example (single command), this example shows a POSIX client to Windows server v.1.5:

./gsec -database hotchicken:c:Program FilesFirebirdFirebird_1_5 security.fdb -user sysdba -password masterkey

This example shows a Windows client to POSIX server v.1.0.x:

gsec -database coolduck:/opt/firebird/isc4.gdb -user sysdba -password masterkey

Interactive Commands

The gsec interactive commands are display, add, modify, delete, help, and quit. They are not case sensitive.

  • add, modify, and delete are used for adding and deleting users and for changing passwords. They require the username as a parameter, along with the relevant option switches and arguments.
  • display without the username argument lists all users. Passwords are not shown. displaycan optionally take a username and display the details of that user.
  • help or its alias? displays some help text for the commands.
  • quit ends gsec and closes the shell.

The USERS Table and gsec Options

Table shows the columns in the USERS table, together with their corresponding gsec option switches. Only USER_NAME and PASSWD are required fields when using gsec.

Columns in the USERS Table and gsec Options

Columns in the USERS Table and gsec Options

Columns in the USERS Table and gsec Options

gsec Examples

display

This displays the main columns of the USERS table in the security database. Passwords are never displayed.

user_name uid gid full name ---------------------------------------------- SYSDBA MICKEY 123 345 Mickey Mouse D_DUCK 124 345 Donald Duck JULIUS 125 345 J. Caesar ...

To display the same information for a single row from the USERS table:

GSEC> display username

For example:

GSEC> display julius user_name uid gid full name ---------------------------------------------- JULIUS 125 345 J. Caesar

a[dd]

This adds a user to the USERS table:

a[dd] user_name -pw password [other switches]

where username is a unique, new user name and password is the password associated with that user.

For example:

GSEC> add mmouse -pw veritas

To add authorization for a user named Harry Potter with the user name hpotter and the password noMuggle, enter

GSEC> add hpotter -fname Harry -lname Potter -pw noMuggle

To verify the new entry:

GSEC> display hpotter user_name uid gid full name ---------------------------------------------- HPOTTER Harry Potter

mo[dify]

This is for changing (editing) a column value on an existing USERS record. Supply the user name of the user to change, followed by one or more switches indicating the items to change and the new value for each.

For example, to set the user ID of user mickey to 25, change the first name to Michael and change the password to icecream, enter the following switches:

GSEC> modify mickey -uid 25 -fname Michael -pw icecream

You can’t change a username. Delete the old user and add a new one.

de[lete]

This deletes the user username from the USERS table. Delete takes no other arguments or switches.

de[lete] username

For example:

GSEC> delete mickey

You can use the display command to confirm that the entry has been deleted.

h[elp] or ?

Either of these switches displays a summary of the gsec commands, switches, and syntax.

q[uit]

This ends the interactive session.

Using gsec from a Command Prompt

To use gsec from a command prompt, convert each gsec interactive command to a command switch by prefixing it with a hyphen (–). The option switches are the same.

For example, to add the user Claudio and assign the password dbkeycop, you would enter the following on the command line.

On Windows:

..BIN> gsec -add claudio -pw dbkeycop -user SYSDBA -password masterkey

On POSIX:

bin]$ ./gsec -add claudio -pw dbkeycop -user SYSDBA -password masterkey

To display the contents of the USERS table, enter

> gsec -display

and so on.

gsec Error Messages

gsec Error Messages

gsec Error Messages

gsec Error Messages

SPECIAL TOPIC: Customizing User Security

This section written and printed here with permission by Ivan Prenosil.

Firebird user authentication is—wel l—problematic. The good news is that it’s all going to change for the better in Firebird 2. The bad news is that we have to live with it for some time yet. Ivan Prenosil, a long-time, well-practiced expert in developing with Firebird and its ancestors, developed some techniques for customizing the security database. Ivan agreed to share his tips and scripts with you in this special topic.

The Security Database

When a user logs into a Firebird database, its password is verified against an encrypted password in the security database. On v.1.0.x, the name of the database is isc4.gdb. It is security.fdb on v.1.5. It is an ordinary Firebird database, and the table that’s used for user authentication is USERS. Its (simplified) structure looks like this:

CREATE TABLE USERS ( USER_NAME VARCHAR(128), PASSWD VARCHAR(32) ); /* */ GRANT SELECT ON USERS TO PUBLIC;

On examining the permissions for this table, two major drawbacks are obvious. One is that any user can read the full list of users and encrypted passwords. The other is that only SYSDBA can modify the table—so users cannot change their own passwords.

However, if you are SYSDBA, you can connect to the security database, just as you can to any other database, and modify its structure. That means you can improve it.

Version Differences

In Firebird 1.0.x, when authenticating users, the server connects to the security database, retrieves the necessary information, and then disconnects. Between connections, you can “mess around” with isc4.gdb.

In Firebird 1.5, however, once a connection has been established to the security database, it is retained until the server is shut down. With v.1.5, therefore, you’ll need make all changes to a copy of the security database and install it afterward. Here are the steps:

  1. Make a backup of security.fdb using gbak.
  2. Restore it under a different name.
  3. Run the script(s) on that copy.
  4. Shut down the server.
  5. Swap the old and new versions of security.fdb and rename.
  6. Restart the server.

Allowing Users to Change Their Own Passwords

The easiest and best-known modification is to grant update permissions to give non-SYSDBA users access, GRANT UPDATE ON USERS TO PUBLIC, and add a trigger to prevent any user except SYSDBA from modifying somebody else’s password.

Here is my script:

/* Copyright Ivan Prenosil 2002-2004 */ CONNECT 'C:Program FilesFirebirdFirebird_1_5security.fdb' USER 'SYSDBA' PASSWORD 'masterkey';
CREATE EXCEPTION E_NO_RIGHT 'You have no rights to modify this user.';
SET TERM !!;
CREATE TRIGGER user_name_bu FOR USERS BEFORE UPDATE
AS
BEGIN
IF (NOT (USER='SYSDBA' OR USER=OLD.USER_NAME)) THEN
EXCEPTION E_NO_RIGHT;
END !!
SET TERM ;!!
/** Grants. **/
GRANT UPDATE(PASSWD, GROUP_NAME, UID, GID, FIRST_NAME, MIDDLE_NAME, LAST_NAME)
ON USERS TO PUBLIC;

It’s awkward having to grant permissions on all of the columns when PASSWD is the only one you really want to make accessible. Unfortunately, it is needed if your password application is going to use gsec or the Services API.

This modification doesn’t get around the problem that the full list of users and their encrypted passwords is visible to PUBLIC. It makes it very easy for a user to download a list of the other users’ passwords and try to break them locally by brute force.

How to Hide the Users/Passwords List

If you rename the USERS table and re-create USERS as a view of the renamed table, you can have the best of both worlds. Users will be able to modify their own passwords, and the full list of users and passwords can be hidden from PUBLIC. Each non-SYSDBA user will see only one record in security.fdb (or isc4.gdb, if your server is v.1.0.x). The new structures in security.fdb will be more like the following schema:

/* Copyright Ivan Prenosil 2002-2004 */
CONNECT 'C:Program FilesFirebirdFirebird_1_5security.fdb'
USER 'SYSDBA' PASSWORD 'masterkey';
/** Rename existing USERS table to USERS2. **/
CREATE TABLE USERS2 (
USER_NAME USER_NAME,
SYS_USER_NAME USER_NAME,
GROUP_NAME USER_NAME,
UID UID,
GID GID,
PASSWD PASSWD,
PRIVILEGE PRIVILEGE,
COMMENT COMMENT,
FIRST_NAME NAME_PART,
MIDDLE_NAME NAME_PART,
LAST_NAME NAME_PART,
FULL_NAME COMPUTED BY (first_name || _UNICODE_FSS ' ' || middle_name ||
_UNICODE_FSS ' ' || last_name ));
COMMIT;
INSERT INTO USERS2
(USER_NAME, SYS_USER_NAME, GROUP_NAME,
UID, GID, PASSWD, PRIVILEGE, COMMENT,
FIRST_NAME, MIDDLE_NAME, LAST_NAME)
SELECT
USER_NAME, SYS_USER_NAME, GROUP_NAME,
UID, GID, PASSWD, PRIVILEGE, COMMENT,
FIRST_NAME, MIDDLE_NAME, LAST_NAME
FROM USERS;
COMMIT;
/* */
DROP TABLE USERS;
/* */
CREATE UNIQUE INDEX USER_NAME_INDEX2 ON USERS2(USER_NAME);
/** Create the view that will be used instead of original USERS table. **/
CREATE VIEW USERS AS
SELECT *
FROM USERS2
WHERE USER = ''
OR USER = 'SYSDBA'
OR USER = USER_NAME;
/** Permissions **/
GRANT SELECT ON USERS TO PUBLIC;
GRANT UPDATE(PASSWD, GROUP_NAME, UID, GID, FIRST_NAME, MIDDLE_NAME, LAST_NAME)
ON USERS
TO PUBLIC;

The 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 the USER and CURRENT_USER variables contain empty strings during password verification.

How to Log Login Attempts

Replacing the USERS table in the security database with a USERS view has one great benefit: It allows us to call a stored procedure whenever a user tries to log in. When the
Firebird server executes the statement

SELECT PASSWD
FROM USERS
WHERE USER_NAME =?;

we can execute a procedure that acts like a select trigger or a login trigger. The procedure could be written so that it could, for example, refuse login during some parts of the day, or log a timestamp when users try to log in.

We can log only the names of known users—that is, names already stored in the not USERS table—and it is possible to report whether login was successful. Still, even the limited information we can log can be useful. It will inform us of login attempts at unusual times, and it will reveal suspicious numbers of logins over short periods.

To implement this, we need a table to log into and a stored procedure to do the work.

The Log Table

The log table has to be an external table, because the transaction used by the server to do its authentication is not committed, but rolled back. External tables don’t get “unwritten” by rollback as inserts to a regular table would.

CREATE TABLE log_table EXTERNAL FILE 'C:Program FilesFirebirdFirebird_1_5security.log' ( tstamp TIMESTAMP, uname CHAR(31) );

If you want the log table to be readable as a text file, you can use CHAR(20) instead of TIMESTAMP and cast it, and add two more columns: a single CHAR to separate the tstamp and uname columns and a CHAR(2) to be filled with carriage return and line- feed codes.

The Logging Procedure

The stored procedure is a selectable type, able to be called from the view. When a login succeeds, SUSPEND gets called and a row is written to the log. When the login fails, because the row requested from the view is forbidden, the procedure just terminates without writing anything. The output parameter is just formal; its 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); /* and don't forget to change the fields written if you modify log_table to make it a text file! */ IF (USER = '' OR USER = 'SYSDBA' OR USER = :un) THEN SUSPEND; END

We test (USER = '') because, when Firebird verifies the password, the USER variable is empty. It helps distinguish whether the password is being verified by the server or the user is directly connected to the security database.

Implementing the New Setup

We need to drop the view that is being used in our restructured security database and create a new version that calls the stored procedure:

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

Remember to reinstate the permissions that were lost when the view was dropped:

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

There are some more permissions to add, relating to the stored procedure:

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

Because log entries are constantly appended to log_table, it will be necessary to delete or rename the external file from time to time. The security database and, thus, the external file, are released after the user authentication is finished, so renaming the file should not be a problem.

How to Slow Down Intruders

Once we are able to log the user names that tried to log into the database, and the times they tried, we can use this information to further restrict access.

For example, it would be possible to count the number of login attempts by a given user name during the last minute and, if the number reached some ceiling, to block connection by that user name. It would allow us some protection from brute force attacks, where someone tries to break into the database by repeatedly scanning possible passwords. We can put a temporary block on any user name that displays attacker like behavior.

The time interval and the login count limit need to be chosen carefully, so as to slow down the intruder but not impose penalties on bona fide users who just type badly. Open VMS uses a similar approach.

The relevant piece of code in the stored procedure 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;

You can change the constants, namely 3 (the allowed number of mistakes) and 0.0007 (the interval, approximately 1 minute). This procedure will affect all users. One possible modification would be to choose one non-SYSDBA user (not SYSDBA, because it is the user name most likely to be targeted) and exclude it from the blocking procedure. Make it the user that owns all the databases, thus endowing it with the rights to perform database shutdowns.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics