Subject RE: [IB-Architect] Re: Some thoughts on IB and security
Author Claudio Valderrama C.
> -----Original Message-----
> From: dcalford [mailto:dcalford@...]
> 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

Because we are humans, we have to deal with risks: actually, if for some
reason, isc4.gdb gets corrupted, you replace it. If you store the user/pw
tuples in each db, if the db gets corrupted, how do you repair it if you
can't be authenticated, for example? I don't know how MsSql and other
products deal with that problem. For example, in MsSql you define global
users in the master database (isc4.gdb on steroids) but must add them in
each specific db you want to grant rights to them.

> (B) New Triggers
> On_Connect
> On_DisConnect
> On_Select
> On_RowSelect

I'm willing to think this suffices if you make sure OnConnect/OnDisconnect
will have some parameter on the current logged in users. Otherwise, I still
opt for my idea of BeforeFirstConnect/AfterLastDisconnect, because I want
some housekeeping tasks to be carried when such conditions arise.

> 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.

Sure, so you won't get any penalty if you don't require such granularity

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

I cannot agree more. Also, I've seen requests to know the name of the
originator table when a trigger is activated, so you can pass it from
several triggers to a general procedure that does a common task in behalf of
all of them.

> (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.

Can you explain more, Dalton? Do you mean it would be possible to issue
select password from rdb$database
and return this information in a normal data packet, hence defeating my
security implementation that took care to encrypt and protect the pw at
login time with some paranoid security standard?

> 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.

I agree completely. Otherwise, I would want to see the list of db products
that currently scramble themselves the database file.

Claudio Valderrama C.
Ingeniero en Informática - Consultor independiente