How to log user access to sql when all users share common access name and password?

Angelstar

Currently working on a project.
User information is stored using session.
All connections made to SQL server is through a common default user name and password.
How should I go about logging the actions of the user?

A. Script every button the user might click on to log user actions.
Can be very tedious.

B. Use trigger to log all access made to SQL tables.
How to determine which user triggered the log?

Both approach pose a problem. Is there any simpler method?

JDcrack

have you considered using a solution with cookies?

There are also a lot of opensource PHP scripts out there that have already been written, that can handle what you are looking for:

http://www.hotscripts.com/PHP/Scripts_and_Programs/Click_Tracking/

Angelstar

I need to log the changes made to the SQL tables if any, and not just the name of the button the user clicked on.

P.S. I am using MSSQL 7

JDcrack

You could add a 2 additional columns to any given table,
user
date_modified

Of course in this case 'user' could contain the users IP address or session ID.
Then change any INSERT or UPDATE SQL statements to add that info to the DB..

Just a thought 🙂

Angelstar

Good suggestion....however, client request that in the case that record was modified, I need to keep a copy of the old data.
This would cause the size of the database to more than double.

Considering the current daabase contains records all the way from 1956, this is not very effective.

JDcrack

Yes I see what mean..

However, I dont see a way around not doubling your DB size,
that is, if you intend to keep old entries and new entries.

On second thought.....
You could create a seperate table,
we will call edited_data
and has the following columns:
id (this is a unique primary id row for this table)
entry_id (this is the unique identifier for the original row that was modified)
orig_vals (all of the original, unedited data)
new_vals (the new edited data)
date modified
user

this table could be backed up and emptied as often as you need, so your DB size would not necessarily have to grow to double its size

Angelstar

How do I pass in the user id? Currently all users log in based on a common db user name and password.

Does it mean to set Trigger for the tables I want to monitor?

JDcrack

Hmmm, to be perfectly honest, I have never worked with MSSQL 7
I am really familiar with mySQL, and stored procedures and triggers have not yet been implemented for mySQL.

I was just trying to help you with general ideas, that will apply to any DB

As for adding user data....
Have you considered using session IDs or storing IP addresses?

Also for as much of an undertaking as this would be to your project, have you considered also adding a user authentication script? so that users have to login with a unique userid/password?

Angelstar

I've been using session variable to store the user details. The users log into the web server (Apache) using unique user name and passwprd. However, they use a common user name and password to log into the SQl server.

JDcrack

I believe that is a typical setup for a DB driven website.

You could use the unique username/password from the login to Apache to store in the DB.

Sxooter

Originally posted by Angelstar
I've been using session variable to store the user details. The users log into the web server (Apache) using unique user name and passwprd. However, they use a common user name and password to log into the SQl server.

Is it possible to set your database server to accept trusted connections (i.e. name passing only, no password required) then setup the database so they each have an "account". Since it's a trusted connection, there's no passwords to setup / keep up to date.

The only issue there is the maintenance of all those user accounts.