SQL Backend - Capture users details

Get help on databases - MySQL, Oracle, Access, etc.
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

SQL Backend - Capture users details

Post by Tribble »

I need to be able to capture the user's details (name) every time they edit a record in a table. I would also like the date and time.

We now have a few users that are deleting information in fields. We need to be able to track who the idiot is and deal with them. The data is stored in SQL tables. Is there a way that I can build it into SQL or do I have to do it through a Query in Access?

This is rather urgent as we need to sort this out asap. Most users are using Windows authentication to log in.
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Backend - Capture users details

Post by RuadRauFlessa »

Tribble wrote:I need to be able to capture the user's details (name) every time they edit a record in a table. I would also like the date and time.

We now have a few users that are deleting information in fields. We need to be able to track who the idiot is and deal with them. The data is stored in SQL tables. Is there a way that I can build it into SQL or do I have to do it through a Query in Access?

This is rather urgent as we need to sort this out asap. Most users are using Windows authentication to log in.
You could turn transaction loggin on but seing as you are using Acces and not SQL Server I doubt it is possible. I would say work it into the queries.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: SQL Backend - Capture users details

Post by Tribble »

I will look into that. Working on the forms rather as queries seem more complex.
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Backend - Capture users details

Post by RuadRauFlessa »

Tribble wrote:I will look into that. Working on the forms rather as queries seem more complex.
:lol: depends on the queries.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: SQL Backend - Capture users details

Post by Tribble »

I have queries based on queries that are based on queries
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Backend - Capture users details

Post by RuadRauFlessa »

Tribble wrote:I have queries based on queries that are based on queries
Then consolidate them. I know it is a big job but the simpler the better.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: SQL Backend - Capture users details

Post by rustypup »

just to toss my dodgy penny into the pot... when users are deleting critical data, this says "design flaw" - time to relook the input approach.

User Level Security - i'm no access fundi, but you should be able to prevent unauthorised deletion ...

after a brief scan, it would appear that user logging and user object references are only available once ULS is installed, not before then.
Most people would sooner die than think; in fact, they do so - Bertrand Russel
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: SQL Backend - Capture users details

Post by Tribble »

The problem is the user we suspect of deleting data is the one who is supposed to have full access rights. She is not deleting complete records - just clearing out fields. She does leave some data but many of the fields are wiped clean. Not sure how she is doing this accidentally as it involves going into each field and pressing delete or space. She then says that as the record was added last year - she could really not have deleted the data. I need to track who was the last to edit a record and the date and time of that edit.
Image
doo_much
Registered User
Posts: 26022
Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:

Re: SQL Backend - Capture users details

Post by doo_much »

OK I am a total SQL n00b so see this as a question!

Is it not possible to create and UPDATE trigger within the SQL table? Code the trigger so that it
  • 1. Creates a 'back-up of the deleted data
    2. Captures the users' info etc?
As I said - question
MOOD - Thirsty

A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Backend - Capture users details

Post by RuadRauFlessa »

doo_much wrote:OK I am a total SQL n00b so see this as a question!

Is it not possible to create and UPDATE trigger within the SQL table? Code the trigger so that it
  • 1. Creates a 'back-up of the deleted data
    2. Captures the users' info etc?
As I said - question
Problem is that when you use a trigger you don't know who the user was that made the change to the record. Also I am not 100% sure if you can add triggers to tables in Access.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
doo_much
Registered User
Posts: 26022
Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:

Re: SQL Backend - Capture users details

Post by doo_much »

RuadRauFlessa wrote:
doo_much wrote:OK I am a total SQL n00b so see this as a question!

Is it not possible to create and UPDATE trigger within the SQL table? Code the trigger so that it
  • 1. Creates a 'back-up of the deleted data
    2. Captures the users' info etc?
As I said - question
Problem is that when you use a trigger you don't know who the user was that made the change to the record. Also I am not 100% sure if you can add triggers to tables in Access.
Tribble wrote:The data is stored in SQL tables.
:D
MOOD - Thirsty

A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
doo_much
Registered User
Posts: 26022
Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:

Re: SQL Backend - Capture users details

Post by doo_much »

MOOD - Thirsty

A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: SQL Backend - Capture users details

Post by Tribble »

Oooh thank you - I will have a look at that.
Image
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: SQL Backend - Capture users details

Post by rustypup »



SQL is a language...

SQL Server is a platform

MS Access, (with its own flavour of SQL), does provide some of this functionality, but only if USL is installed - without it, there is no reference to the editing user, (although i haven't dug to deep)

from my very sketchy knowledge, access does allow triggers, and runs a rollback instance for each session - perhaps have a trigger trap, inspect and rollback the essential fields... :?
Most people would sooner die than think; in fact, they do so - Bertrand Russel
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Backend - Capture users details

Post by RuadRauFlessa »

In that case go and create 4 triggers for each table in your SQL DB
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
mina.magpie
Registered User
Posts: 263
Joined: 12 Jan 2009, 12:36

Re: SQL Backend - Capture users details

Post by mina.magpie »

I coded something similar in SQL Server a few years back in a project. Basically I used the database itself to do user validation so I could track who was doing what, and then mirrored every user action into a log table. IDK if it will translate well to Access since I don't know what its limitations are compared to Server, but if you want I can email you the code and you can see if there's anything in there you could use. I could also probably talk you through it on chat or something, if you want.

Mina.
"Don't waste your whole life trying to get back what was taken away" - The Offspring from "Kristy are you doing okay"
doo_much
Registered User
Posts: 26022
Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:

Re: SQL Backend - Capture users details

Post by doo_much »

rustypup wrote:


SQL is a language...

SQL Server is a platform
Ahh Thanks!

and thank goodness for my n00bness disclaimer!
MOOD - Thirsty

A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: SQL Backend - Capture users details

Post by Tribble »

ROTFL - never.

It is going to be easier to write a function in the forms that captures the date and time and user's name. Besides - my SQL programming knowledge is non existent. I am still struggling with VBA as it is :lol: :lol: :lol:
Image
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: SQL Backend - Capture users details

Post by rustypup »

mina.magpie wrote:I used the database itself to do user validation so I could track who was doing what
SQL is already doing all of this... those monster logs are there for a reason, (SQL actually uses these logs for many rollback actions...)

access is server's 'special' cousin, twice removed...

i still say the design is flawed. a proper, coded, front-end is the preferred approach to data capture. no user should be entering data directly into a production database using forms. ever. it's just begging for precisely this sort of issue...

now all we need to hear is "there are no backups" and i run screaming for the hills... :lol:

@Tribble - have you considered a coded front-end?
Most people would sooner die than think; in fact, they do so - Bertrand Russel
mina.magpie
Registered User
Posts: 263
Joined: 12 Jan 2009, 12:36

Re: SQL Backend - Capture users details

Post by mina.magpie »

Tribble wrote:It is going to be easier to write a function in the forms that captures the date and time and user's name. Besides - my SQL programming knowledge is non existent. I am still struggling with VBA as it is :lol: :lol: :lol:
Cool beans. As long as you can be sure your user-base only has access to the db via your forms application, you could easily just pipe it all into a text file. If you do end up requiring something more robust though, just shout. :)

Mina.
"Don't waste your whole life trying to get back what was taken away" - The Offspring from "Kristy are you doing okay"
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: SQL Backend - Capture users details

Post by rustypup »

it would appear access!=triggers, so there goes that option... time to bite the bullet :twisted:
Most people would sooner die than think; in fact, they do so - Bertrand Russel
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Backend - Capture users details

Post by RuadRauFlessa »

rustypup, Yeah but she only uses Access as a front end with the forms it provides. They use a SQL Server 2005 SP1 for the back end where the data is actually maintained.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: SQL Backend - Capture users details

Post by Tribble »

rustypup, I really have no idea what you mean by a coded front end. I have a users table which controls access and rights to the database. The SQL server also controls who is allowed to access the database and what rights they have. They use their Window's Authentication. I have three people who have to be allowed to add, edit, delete. I am the only one that can create. There are others that have viewing rights only.

The problem is - one of the three people is causing the problems we have. I need to work out which one so that we can prevent further problems.

We do daily backups which are kept for 14 days. We also do monthly backups and those are kept for 3 months. All are offsite.
no user should be entering data directly into a production database using forms. ever. i
What do you mean by this? How can we update the certifications of the brokers if we cannot edit their data? How can we add new employees - check their supervision requirements if we cannot add the required information? You have me very confused puppy.
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Backend - Capture users details

Post by RuadRauFlessa »

Tribble wrote:rustypup, I really have no idea what you mean by a coded front end. I have a users table which controls access and rights to the database. The SQL server also controls who is allowed to access the database and what rights they have. They use their Window's Authentication. I have three people who have to be allowed to add, edit, delete. I am the only one that can create. There are others that have viewing rights only.

The problem is - one of the three people is causing the problems we have. I need to work out which one so that we can prevent further problems.

We do daily backups which are kept for 14 days. We also do monthly backups and those are kept for 3 months. All are offsite.
no user should be entering data directly into a production database using forms. ever. i
What do you mean by this? How can we update the certifications of the brokers if we cannot edit their data? How can we add new employees - check their supervision requirements if we cannot add the required information? You have me very confused puppy.
What he means is that you should have an application which does data integrity checks. Although SQL Server already caters for foreign indexes you should still check weather the data entered is acceptable. Say you have a description field and someone tries to update the said field you have to check that the new data entered for the field is not blank/zero length/spaces
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
Tribble
Registered User
Posts: 88456
Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:

Re: SQL Backend - Capture users details

Post by Tribble »

I have that already - it doesn't help if they accidentally replace data with a space. But we cannot refer to the old data in order to control the new. One chap moves from one Province to the other, to a different region or branch. We need to be able to capture the data - no matter whether it is completely different to what was there before or not.

How do you prevent spaces from being added?
Image
Post Reply