Search code examples
delphiauthenticationdelphi-xe2firebirddatasnap

DataSnap and Database Connection / Login


I am trying to work out the "right" way to establish a connection to my database from the server of my DataSnap application.

Each (most) of my tables in the database have fields (whose values are set via a trigger on insert and update) called Updated and Created (this is the current time stamp when the record is written) and updated_by and created_by which (should) contain the currently logged in user.

I want the user to 'login' from the client side such that these fields reflect the user who has logged in (and by extension, I'll get user authentication from the database, not just from the server). I can handle the authentication to the server itself from the client ok handling the OnUserAuthenticate and OnUserAuthorize events on the server. I am trying to then pass the credentials to my database so that the triggers can set the fields stated above correctly.

So what is the way to approach this scenario? I am wonder if the DSAuthProxyUser and DSAuthProxyPassword from the client can be used but I can't find much (any) documentation on how I would use that. Do I establish a new connection for every user who connects? This seems most logical to me. I'm not going to have a lot of concurrent users. Tops 30. Most likely 5-10. But what is the "normal" way this is done? I don't want to (hope I don't have to) pass in the username to each of my insert/updates to set the values in the tables.

I hope I have explained my situation clearly.

Thanks


Solution

  • I haven't used it yet, but it looks to me that the RDB$SET_CONTEXT() and RDB$GET_CONTEXT() introduced in Firebird 2 are what you need. Using these functions, you can set (and get) additional information specific to the user session (namespace USER_SESSION) or the current transaction (namespace USER_TRANSACTION). You can also retrieve additional system information for the current session (namespace SYSTEM), but that is probably not relevant for your case.

    What you would need to do is call the RDB$SET_CONTEXT() method in that OnUserAuthorize event, eg using (as a query):

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

    Here 'actualuser' is the context variable we use. In your triggers you can then retrieve the name (assuming PSQL, with a declared variable actualuser)

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

    You can then use actualuser in the rest of your trigger. Just make sure you also account for the case where the context variable is not set (eg an administrator making direct changes to the database or something like that).