Hi Everyone,

I have been thinking about IB and security, and various methods of implementation and
would like to share my thoughts on the subject.

Security is a strange and complicated subject.

Some users do not care about security and it is more of a hassle then benefit when
trying to get multiple users to have proper rights to a database.

Others want a very basic authentication mechinism such as what we have today, still
others want a authentication and verification system more complex than anything we can
imagine.

What I have been thinking about is some way of giving everyone the security system they
want without making the system so complex that no one wants to use it (or code it).

The biggest problem with any change to the security methodology is keeping
compatibility with older implementations.

Before I suggest a solution to the problem and open it up for debate, I want to explain
the problems with the current security implementation I want to address with this
method.

First the qualifiers

(a) Any implementation needs to maintain compatibility with the existing code base.

(b) Any implementation needs to maintain compatibility with current SQL standards
(c) The implementation has to be flexible enough to support as many needs as
possible
(d) Any new security system must be able to NOT be implemented (for those who do
not care and don't want the extra complexity)
(e) Any database can have security added later without changing the origional
design.

Now for the different issues.

(a) Standard private desktop database use prepared by a end user with no real
concern for security.
(b) Small manager creating a small app to cover his/her department (current method
is perfect)
(c) Developer creates a complex database and although he/she does not care about
end user access, they do not want thier source code/schema design to be given out.
(d) Developer who wants the use of Date/Time/Total Accesses/Station Accessed
From/Role/User or any variation thereof for verification.
(e) Developer who wants to restrict the data in a table depending on the data from
the table or another table (record level security)
(f) Developer who wants to make sure that only the application he/she wrote is the
only way to access the database.
(g) Developer who wants to use a alternitive encryption/length of
username/password than what IB currently uses.
(h) Developer who wants to make sure that the database is secure even if the
attacker has physical access to the database.

I am sure I have forgotten or missed alot of other restrictions/needs that I have seen
on the newsgroups over the years. In fact, the reason I have been looking at the new
method I am thinking of is to allow the system to be flexible enough to handle security
matters I have never heard/thought of.

I will list the change, and the reason for the change.

(A) No system wide security authentication database - All security information is
held within the GDB itself. ISC4.GDB still exists.

The Developer who creates the database, sends a username and password.
That user name is verified against ISC4.GDB (this prevents just anyone from creating a
db on the server)
In the new database, the Developers name and password are automatically copied from
ISC4.GDB into the databases own security table.
In the new database, the SYSDBA's name and password are automatically copied from
ISC4.GDB into the databases own security table.
From that point on, all security information for a database is verified from within
that very database with no more reference to ISEC4.GDB

(B) New Triggers
On_Connect
On_DisConnect
On_Select
On_RowSelect
When a User connects, the On_Connect trigger fires. A standard fully disclosed
(Developer knows the source of the standard trigger) bit of SP/Trigger code fires and
checks the users table within the database. If the Developer wants, he/she can change
the Trigger code to implement any form of security they want for the database,
including logging to external tables.
When a new table is created, standard On_select/before insert/before update/before
delete trigger is created that checks the user rights table. The Develper can alter
these triggers to implement any form of security they wish.
The On_RowSelect trigger fires before any row on the current table gets returned to the
client. At this point, the row can be skipped, or, different values returned to the
client. By default, no standard trigger exists for this, but, the Developer can add
them as needed.
The On_DisConnect can be used to do cleanup code /logging and fires as the closing
point of the thread on the server so that even a improper disconnect can have this code
fired (but still within the transaction context of the client so only external tables
can be altered)

(C) New Global variables (like the USER variable) need to be surfaced to the SP
language.
ROLE
REMOTE_MACHINE (Ip address/netbios address/etc)

(D) Plain USER/PASSWORD passing to the server - a new api or a modification of the
current api to allow the Developer to create thier own encryption of this data and have
it passed unchanged to the database for processing. By creative use of this api, the
Developer can ensure that only his/her applications can access the database.

You will note that I have not suggested encrypting the database. You can do this on a
OS level and would not give much benefit but would slow down the database without
adding functionality.

The fact that you can define your own security at all levels allows the Developer to
get around any security holes created by getting local access by root or SYSDBA access
(in fact this type of system would allow SYSDBA to be removed from a specific
database).

There are alot of implications to any change of handling security and I am sure that
there are alot of improvments to the model I propose.

Since the source is not yet released, It would be nice to hear from the current
engineers about how hard/easy it would be to implement the different parts of this
suggestion. (specific details not needed, just a overview so that alternitives can be
put forward if a simpler method is needed)

Best regards

Dalton