Geek Answers Handbook

DataSnap and database connection / login

I am trying to develop the “right” way to connect to my database from the server of my DataSnap application.

Each (most) of my tables in the database has fields (the values ​​of which are set using the trigger when inserting and updating) under the name "Updated and created" (this is the current time stamp when writing a record) and updated_by and created_by, which (should) contain current user.

I want the user to "log in" from the client side, so that these fields reflect the user who is logged in (and, in addition, I will get user authentication from the database, and not just from the server). I can handle authentication on the server itself from the client, supporting the processing of OnUserAuthenticate and OnUserAuthorize events on the server. I then try to transfer the credentials to my database so that the triggers can correctly set the fields mentioned above.

So what is the way to approach this scenario? I am wondering if DSAuthProxyUser and DSAuthProxyPassword can be used on the client, but I can not find much (any) documentation on how I will use this. Create a new connection for each user that connects? This seems to me the most logical. I will not have many concurrent users. Tops 30. Most likely 5-10. But what is the “normal” way? I do not want (I hope that I do not have to) pass the username to each of my inserts / updates in order to set the values ​​in the tables.

I hope I clearly explained my situation.

thanks

+4
source share
2 answers

I haven't used it yet, but it seems to me that RDB$SET_CONTEXT() and RDB$GET_CONTEXT() , introduced in Firebird 2, is what you need. Using this, you can set (and get) additional information related to a user session ( USER_SESSION namespace) or the current transaction ( USER_TRANSACTION namespace). You can also get additional system information for the current session ( SYSTEM namespace), but this is probably not the case.

What you need to do is call the RDB$SET_CONTEXT() method in this OnUserAuthorize event, for example using (as a request):

 SELECT RDB$SET_CONTEXT('USER_SESSION', 'actualuser', '<name of user') FROM RDB$DATABASE

Here 'actualuser' uses a context variable. In your triggers you can get a name (suppose PSQL with a declared variable actualuser )

 actualuser = RDB$GET_CONTEXT('USER_SESSION', 'actualuser');

Then you can use actualuser in the rest of your trigger. Just make sure that you also consider the case where the context variable is not set (for example, the administrator makes direct changes to the database or something like that).

+2
source

Firebird has the keyword CURRENT_USER, which can be used in SQL.

The example below is based on http://www.firebirdsql.org/refdocs/langrefupd15-current_user.html

 create trigger bi_customers for customers before insert as begin New.created_by = CURRENT_USER; end

To have a username for updates, simply declare a trigger before the update, for example

 create trigger bi_customers for customers before update as begin New.updated_by = CURRENT_USER; end

This solution requires mapping 1: 1 database users to external users. In the simplest implementation, this means that the DataSnap session authenticates the user credentials for the database.

In your description, however, this is two-step authentication (first against the DataSnap level, and then from the database). I’m not sure how this can be done with regard to secure password processing, and if you plan to have a separate user / password table only for the first stage of authentication (DataSnap) and matching user login from DataSnap to the database as a kind of "decoupling".

+1
source

More articles:


All Articles