Subject Re: [IBO] Asta or Direct IP
Author Dalton Calford

Step one, create a whole bunch of users in the isec4.gdb.
Use a nice random number generator combined with some ascii characters to
create the names and passwords.
Record these names and passwords in your database so that they can be
referenced by stored procedures within your database.

Create another user - call them 'GATEWAY' (come up with a different name, I
am only putting forward examples)

Your application, at login, prompts the user for thier user name, password,
and role (just like normal).
Your application then encrypts the username, password, role and logs onto the
server as the 'GATEWAY' user.

The 'GATEWAY' user can only execute one stored procedure, and has no other
rights on the database. (Triggers on system tables ensure this).

The stored procedure takes the username, password and role as parameters and
first decodes them using the md5hash routines. (plus bin_xor) or simple
pattern matching.
(This is also a very detailed subject, for another email)

Then with the decoded information, matches the user supplied values with the
internal users table.
The stored procedure then returns the users real user name, password and role.

The application then reconnects as normal, using the results of the stored
procedure for the connection parameters.
You can also have the application provide IP address, mac address or any
other information you may want to track. You can also have the stored
procedure return special information to the client (database is currently
shutdown, or you are not allowed to access the database after 5pm etc)

The only limits are your imagination.
(I have extended this concept so that the database that the application
initially connects to, is not the operational database, it also provides the
target database and server to the client. This is used for load balancing
and system maintenance. I do this with our own version of replication)

This brings up the question of how do you manage all these random user names
(darn hard to remember and to type when it comes to grant statements)

Although you can not issue GRANT or REVOKE statements within a stored
procedure, you can insert or delete the appropriate values into the system
If you want, you can use this method to set up a client that automatically
randomizes the 'real' user names and passwords, without having to notify a
end user (since the password the end user knows is not the 'real' password

We have a whole series of stored procedures that define groups, add users to
groups. revoke rights etc.

I hope this has given you some guidence.

best regards


On Wednesday 30 May 2001 11:36, you wrote:
> Thank you very much for your response. I like your option (2) as it
> seems to solve the security problem and reduces the overall costs to
> both us and the customer. And Yes, I would appreciate very much any
> additional information or direction on this option. Our application
> includes Point of Sale and has a need for realtime inventory levels
> from a central warehouse so I don't think replication is a good
> option here. Also there is not much of a security issure with
> usernames and passwords internally, execpt for the possible
> interception by internet hackers so this option looks real
> interesting. In your opinion are there any other gotchas using
> interbase this way, as compared to a adding middleware like ASTA. It
> would seem to me that by stripping away the middle layer I would get
> better performance. Thank again.
> --- In IBObjects@y..., Dalton Calford <dcalford@d...> wrote:
> > You have many options in this area.>
> > 2) Do not use real user name/passwords. Have a single stored
> procedure on
> > the server that can only be run under one login(the only thing that
> login can
> > do). That stored procedure grabs the encrypted strings passed to
> it by the
> > client, decrypts them using Gregory Deatzs FreeUDFLib MD5 routines
> (I can give
> > more details if you are interested) and returns the users real
> login name and
> > password (encrypted with the same MD5 routines). That way, the
> users never
> > know thier true username or password except for that one
> application.
> > Meanwhile you get the full benefit of individual user names.
> > (this is heavily summarized, I can go into more detail if you wish)
> Your use of Yahoo! Groups is subject to