|
Home / Article archives / Programming
Security - Enhanced isc4.gdb
01/17/2003 | Views: 7343
|
Source: Ivan Prenosil's InterBase site
Some tips to improve security of isc4.gdb database.
Author: Ivan Prenosil. Original text
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
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.
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 as well)
is visible to PUBLIC.
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 .
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 FilesBorlandInterBaseisc4.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 appended to log_table,
it is necessary from time to time to delete or rename external file!
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 whould 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).
Copyright © 2001 Ivan Prenosil
|
Back to the news section
|
|
 |
|
Home 
InterBase 
Firebird 
Community 
About us
Search 
Help
English 
Russian
Login / Register
