Re: Philosophical question

On Thu, Dec 15, 2011 at 1:49 AM, Andreas <maps(dot)on(at)gmx(dot)net> wrote:
> Am 15.12.2011 05:47, schrieb Darren Duncan:
>
>> Darren Duncan wrote:
>>>
>>> A practice I like that I've seen done for a federal-government scale
>>> database program is to have each person using the application to login to
>>> the database using their own temporary database user.  How it works is that
>>> the database has a users table similar to as if the application was managing
>>> users itself, and when the person logs in they are using credentials defined
>>> in that table.  What actually happens for login is that there is a special
>>> database user which only has privileges to execute a single stored
>>> procedure, and the application's login screen talks to the database with
>>> that special user and invokes the procedure, giving the person-provided user
>>> and pass as procedure arguments.  The stored procedure checks the database
>>> table, and if the credentials are accepted, the procedure then generates a
>>> new database user and password and gives these back to the application,
>>> which then turns around and logs in as the temporary user in order to do all
>>> the normal work of the person.  This generated user only has the privileges
>>> that the person needs.  This approach seems to have security benefits of
>>> some kinds. -- Darren Duncan
>>
>>
>> I should also clarify that this was a decade ago and the context was a
>> desktop application which maintained an open connection for the person's
>> work day. Although I think the same technique was also used there for web
>> applications afterwards, that used the same database, if I don't
>> mis-remember.  I believe this was an Oracle 8 DBMS. -- Darren Duncan
>>
> I see.
> How did they do a "garbage collection" of those temporary users.
> A  desktop app does somehow know when it comes to it's end and could remove
> the temp user
> ... provided it didn't crash.
>
> On the other hand a web app might logically be running over a lot of pages
> but AFAIK in reality it's processing terminates after every page.
> 0)  ...
> 1)  app connects to DB and fetches some stuff
> 2)  app processes stuff and generates some html form
> 3)  app disconnects from DB
> 4)  app sends the form to browser and browser displays that form
> 5)  app terminates while storing some status stuff e.g. in a session
> 6)  browser waits for input and some click event to restart the app
> 7)  browser sends back the data and restarts the app
>
> One could drop the temp-user everytime after step 3 so the DB stays
> uncluttered but the on every reconnect there has to be generated a new
> temp-user and that looks inefficient.

LedgerSMB has to deal with a similar issue regarding discretionary
locks on transactions and open form id's tied to users sessions. In a
web app this can be problematic.

So what we do is have everything set up so when the user's session is
deleted all locking info is also deleted. On each page request, when
we check the session table (which is usually less than one page in
size), we check for old sessions and delete them. If there are no
stale sessions, this is pretty painless. If there are stale sessions,
and the db is large, then this takes the page takes a bit longer to
load but this is a rare case.

Best Wishes,
Chris Travers