Subject Re: [IB-Architect] Re: Some thoughts on IB and security
Author dcalford
Hi Claudio,

"Claudio Valderrama C." wrote:

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

This is where proper backups are important. If the database is corrupted to the point
that a table within the database is no longer readable, then you can not be sure of
any of the data in the database. At that point, you would need some low level tools
to access the file structure (which you would need anyway for that level of
corruption) so the Interbase server and standard client tools are of no value at that

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

I am thinking that certain common values such as the decrypted users password would be
available only during these triggers. This would allow the Developer to perform the
neccessary checks without the end user being able to get this information.

> Otherwise, I still
> opt for my idea of BeforeFirstConnect/AfterLastDisconnect, because I want
> some housekeeping tasks to be carried when such conditions arise.

I actually like the idea of a First Connect Trigger and Final Disconnect Trigger.
They have value beyond security needs and should not be difficult to implement.

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

If I did not know better, I would say you have been reading a discussion I have been
having on another thread.

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

No, the information would only be available to triggers (not to standard SQL or Stored
procedures, unless the trigger passes the info off to another table or Stored
The concept is that instead of having the Interbase Client Library encrypt the user
name and password to be transmitted to the server, the Developer would encrypt it
using his/her own method and the IB client would just pass the final result to the
server. The On Connect trigger could then use a UDF or some fancy SP to decrypt and
authenticate the user.

Thanks for the feedback