Board index » delphi » Making DB Connections from UDFs

Making DB Connections from UDFs

Hi all,

I have just read this really excellent web page on enhancing Interbase's
user management features:
http://www.volny.cz/iprenosil/interbase/ip_ib_isc4.htm.  It describes
techniques to log login attempts.  It does so using an external file.  
However, I have also read that:

(a) Interbase does not handle realy large external files very well and
(b) You cannot run DELETE statements of records in external files
(therefore you cannot reduce the file size via SQL)
and
(c) You cannot edit external files with other applications, since Interbase
locks the file.

This means that the file will grow bigger and bigger, and there is no way
(other than shutting down the database) to do anything about that.

So, I'm wondering about using a UDF instead.  The UDF would connect back to
the security database, and write a record into a table.  I know that,
usually, making database connections from a UDF is a bad thing, since UDF's
are not under transaction control.  However, in this case, that is exactly
what I want.  I want the UDF to create the log record, regardless of
whether the original transaction commits or rolls back.  (See the link
above for the reason why).

So, would it be OK to use a UDF like this?  Or are there other reasons why
people recommend against creating database connections in UDFs?

John

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.co/m2/

 

Re:Making DB Connections from UDFs


Quote
> I have just read this really excellent web page on enhancing Interbase's
> user management features:
> http://www.volny.cz/iprenosil/interbase/ip_ib_isc4.htm.  It describes
> techniques to log login attempts.  It does so using an external file.
> However, I have also read that:

> (a) Interbase does not handle realy large external files very well and
> (b) You cannot run DELETE statements of records in external files
> (therefore you cannot reduce the file size via SQL)
> and
> (c) You cannot edit external files with other applications, since Interbase
> locks the file.

Right, the only operations allowed on external tables are SELECT and INSERT.

Quote
> This means that the file will grow bigger and bigger, and there is no way
> (other than shutting down the database) to do anything about that.

The file used to store data of external table is locked when you use
that table in a statement (e.g. SELECT * FROM ext_tab;),
and is released when all connections to the database are closed.
Under normal circumstances you do not connect to the security database directly,
and InterBase does not hold permanent connection either - after verifying
user name/password it disconnects immediately and thus releases
the external table/file too (unless there is a change in IB7; I did not try this version yet).
This means that it should not be a problem to rename the file when it is too large,
and InterBase will create new file then. (If renaming fails, just try it again;
the file is really locked only for a short time during creating new connection.)

--
Ivan
http://www.volny.cz/iprenosil/interbase

Re:Making DB Connections from UDFs


Quote
> The file used to store data of external table is locked when you use
> that table in a statement (e.g. SELECT * FROM ext_tab;),
> and is released when all connections to the database are closed.

Thanks Ivan, I must have misinterpreted some of the other messages that I
read about external files.

One other thing that I'm thinking of is actually arranging things so that I
can detect failed logins. If I could use a UDF, then it could work
something like this:

(a) Intercept the login process as you describe on your site, but use a UDF
to log to a "real" table, instead of logging to an external one.  This puts
a record into the table for each login attempt, both those that succeed and
those that fail (due to bad passwords).
(b) After each successful logon, the application updates the record that
was just added to the log, setting a "successful" flag to true.
(c) Then I can find failed login attempts by looking for all records where
the sucess flag is not set.

So, if anyone can answer my original question about UDFs, I'd appreciate
it.  But, if I can't use UDFs then at least I know I can delete or rename
the file as you describe.

Thanks for the great tips on your site, they are very helpful!

John

Quote
> --
> Ivan
> http://www.volny.cz/iprenosil/interbase

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

Re:Making DB Connections from UDFs


Hi,

Making an extra connection to admin.ib from inside a UDF? I think it is not
logical.
1. connecting to admin.ib during user authentication is an internal process
and only READS this database, so you can't use a UDF there even if you
define one. There is no insert or update, how can you intervene with a
trigger or something? (Even if IB manager writes logged in users somewhere,
instead of keeping them in memory, I have no knowledge of it.)
2. what use will it have? The person is already connected to the db file he
wants to connect.

If what you want to do is to track successful logins, you would rather
1. define a DB_USERS file inside your own database as a master table
2. define a DB_LOGINS file as its detail
3. call a P_LOGIN stored procedure as soon as your user has logged in (at
the very start of your program execution) and insert a record there
containing user, login timestamp and an empty logout timestamp
4. update this last logged record with a timestamp when your user logs out

DB_LOGINS table will also show you unintended breaks from your database
then, as their logout timestamps will be empty. You may even make use of it
to see who is still logged in. I used this method from Delphi before IB
manager API components were introduced.

If what you want is to track unwanted/unsuccessful login attempts, you
should rather intervene BEFORE login process has started. And this is when
the user program asks access to port 3050 with tcp protocol. If you are
using Linux, see tcpwrappers and xinetd subjects in man pages. (and get
stuck like me ...)

Good programming days ..

Dr.Cem

"John Rusk" <john.r...@compudigm.noSpamPlease.co.nz>, iletide sunu yazdi
news:oprot25twuksrffj@newsgroups.borland.com...

Quote
> > The file used to store data of external table is locked when you use
> > that table in a statement (e.g. SELECT * FROM ext_tab;),
> > and is released when all connections to the database are closed.

> Thanks Ivan, I must have misinterpreted some of the other messages that I
> read about external files.

> One other thing that I'm thinking of is actually arranging things so that
I
> can detect failed logins. If I could use a UDF, then it could work
> something like this:

> (a) Intercept the login process as you describe on your site, but use a
UDF
> to log to a "real" table, instead of logging to an external one.  This
puts
> a record into the table for each login attempt, both those that succeed
and
> those that fail (due to bad passwords).
> (b) After each successful logon, the application updates the record that
> was just added to the log, setting a "successful" flag to true.
> (c) Then I can find failed login attempts by looking for all records where
> the sucess flag is not set.

> So, if anyone can answer my original question about UDFs, I'd appreciate
> it.  But, if I can't use UDFs then at least I know I can delete or rename
> the file as you describe.

> Thanks for the great tips on your site, they are very helpful!

> John

> > --
> > Ivan
> > http://www.volny.cz/iprenosil/interbase

> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

Re:Making DB Connections from UDFs


"John Rusk" <john.r...@compudigm.noSpamPlease.co.nz> wrote

Quote
> > The file used to store data of external table is locked when you use
> > that table in a statement (e.g. SELECT * FROM ext_tab;),
> > and is released when all connections to the database are closed.

> Thanks Ivan, I must have misinterpreted some of the other messages that I
> read about external files.

> One other thing that I'm thinking of is actually arranging things so that I
> can detect failed logins. If I could use a UDF, then it could work
> something like this:

> (a) Intercept the login process as you describe on your site, but use a UDF
> to log to a "real" table, instead of logging to an external one.  This puts
> a record into the table for each login attempt, both those that succeed and
> those that fail (due to bad passwords).
> (b) After each successful logon, the application updates the record that
> was just added to the log, setting a "successful" flag to true.
> (c) Then I can find failed login attempts by looking for all records where
> the sucess flag is not set.

> So, if anyone can answer my original question about UDFs, I'd appreciate
> it.  But, if I can't use UDFs then at least I know I can delete or rename
> the file as you describe.

The simple logging described on my site works always,
while setting "successful" flag would depend on well behaving
of the application, i.e. whether it obey the rule of always updating the record
(e.g. IBConsole will never change the flag automatically).

Another small problem is how to find the right record to update
(e.g. when two users are connecting with the same username
at the same time). If you do not need to log other informations
(like name of database), then it should be enough to update
the latest record with right username that has not set the flag yet.

I would avoid making connection from inside UDF - it can be slow,
and you would have to solve some potential update conflicts.

What about using external files both in security database
(filled automatically) and in each database (filled by application).
Then just merge/compare the data from these tables when necessary
(I do not expect you need to do that often?).

Quote
> Thanks for the great tips on your site, they are very helpful!

You are welcome.

--
Ivan
http://www.volny.cz/iprenosil/interbase

Re:Making DB Connections from UDFs


Quote
> What about using external files both in security database
> (filled automatically) and in each database (filled by application).
> Then just merge/compare the data from these tables when necessary
> (I do not expect you need to do that often?).

I wondered about that.  The catch is that I do need to detect login
failures fairly promptly.

I'm considering writing a little server of my own, that just sits there,
running, on the machine all the time.  I'd follow your approach in the
security database, but instead of writing to an external file, I'd call a
UDF that would send a message to my special server.  (Probably via a COM
call).  Then, when the application connects successfully to the main
database, it would invoke another UDF.  This one would send another message
to the same server.  Both messages would contain the username - the first
would signal "login attempt in progress", and the second would signal
"login attempt successful".

All the server needs to do is keep track of the messages it receives.  If
it ever recieves a "login" message that is _not_ closely followed by a
"success" message for the same username, then there has been a login
failure.

John

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

Other Threads