Security is a slippery slope, one where you have to balance return on
investment with paranoia.
Many developers will design a database system, implement it, then try to
secure it. I will begin by saying that this is the most flawed approach you
can have. When you design a system with security in mind, you approach every
step in the process differently.
So, the first question is, how do I begin ?
Well, connect to your database server as SYSDBA and create a new user
'DEVELOPER' (you can use your own name but for example purposes, developer
will be fine).
Now, connect to the isc4.gdb file with ISQL or some other interactive tool
and grant full security access to 'DEVELOPER'.
Now, disconnect from the server and reconnect as 'DEVELOPER'.
From this point on, never, ever, ever, connect as SYSDBA unless you absolutly
Next step, as DEVELOPER, create your database and create three roles
Now, create a table we will refer to as 'IB_USERS' although the name you use
will not be that.
We also need two other tables refered to as 'IB_GROUPS' and 'IB_RIGHTS'
Together, these three tables will be the backbone for your security system,
they are beginning of a secure database.
Now, create a series of custom exceptions
'CONNECTION FROM UNAUTHORISED PC'
'CONNECTION FROM UNAUTHORISED CLIENT'
'CONNECTION OUTSIDE OF AUTHORISED HOURS'
You can create as many as you can think of.
The concept being, when someone does somthing against the security rules you
setup, an exception is raised, and the current work gets rolled back,
regardless of the client being used. You can get away with a single
exception, but, when it comes down to support and debugging issues, the more
differentiation, the better.
Ok, you have some tables, some exceptions, some roles, what now?
Well, now you need to make sure that only proper users can modify the
database. This includes SYSDBA. You see, once you create a GDB, and you add
a user to the system, that user, who may or may not have any rights to any of
your system tables, that user can create thier own procedures and tables
etc., that can cause problems with your nicely laid out system.
We start this process by creating a stored procedure that for reference, we
will call PRC$SEC_PERMCHECK() and we pass it a unique number that identifies
the item and action we are now doing, more on this later.
PRC$SEC_PERMCHECK() is called by triggers placed on every table in the
database, including system tables. Each of these triggers is given a unique
number that is sent to the procedure when it is called. The stored procedure
can get the current time and current user from the global variables that IB
provides. By cross referencing the user and the unique trigger identifier in
the security tables, you can find out whether you should raise an exception
or not. So, after creating a trigger on the system tables, that calls this
procedure, if any user, including SYSDBA, attempts to create, modify or
delete, any metadata, an exception will be raised, unless that user is
specifically put into the security tables.
But, how do we prevent SYSDBA from reading all of our private
procedure/trigger language source code? We update the system tables,
replacing the source code (not the BLR code) with a blob the only containes
three spaces. The reason we use three spaces instead of a null is because of
a known bug that sometimes will cause a trigger to fire twice if the source
Ok, we have now set it up so that SYSDBA cannot modify the tables, but we
want to make sure they can not even connect, so we create a role called
SYSDBA by directly inserting values into the system tables.
Sysdba now can not even connect to our database. Users who can extract
metadata cannot see the source of the triggers/procedures.
Ok, but how do you prevent a authorised user from connecting to the database
using some tool other than the programs you are providing?
First you never give your users thier real login names or passwords.
Your program goes through a series of steps when loging onto the database.
It prompts the user for a username and password.
It connects to the database as the 'SEC_LOGIN' user.
The SEC_LOGIN user only has the right to run the 'PRC$SEC_LOGINIB_USERS()'
The PRC$SEC_LOGIN() procedure is passed the ip_address of the calling
machine, the user supplied username and the user supplied password.
It then looks up the appropriate information from the IB_USERS table and
calls a UDF that adds a new user to the system with a random user name and
password. The stored procedure grants the appropriate rights to the newly
created user via directly inserting values into the system tables.
The stored procedure then returns to the calling application the real
(random) user name and password, as well as the role for the user to use.
This means that every time the user logs in, they are logging in via a
different user name and password. This means that all triggers and
procedures that rely upon the USER variable, will need to resolve the real
user name from the random name that is returned.
The PRC$SEC_PERMCHECK() that is called for every insert/update/delete checks
to see if the login time has expired for the user and calls a UDF that cleans
up the isc4.gdb and removes the random name from the database (and any other
random users who have not logged out properly).
As each user gets a unique name, even if they think they are logging in with
the same login name. This gives you a unique identifier for this login and
helps with logging and replication.
Since the users inside the system are constantly changing, no other
application except for yours will know how to connect to your database.
You can move the GDB around from machine to machine and still be assured some
On top of the above security measures, you do not give your tables intuitive
names, nor do you make your stored procedures, thier names or thier
parameters should always be kept secret. Any knowledge of your database
structure is a direct tool for cracking your security.
This is a beginning 10000' overview on securing your database.
I am sure I have prompted more questions than I have given answers, so feel
free to ask.