SQL Backend - Capture users details
- Tribble
- Registered User
- Posts: 88465
- 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
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.
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.
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Backend - Capture users details
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.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.
- Spoiler (show)
- Tribble
- Registered User
- Posts: 88465
- 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
I will look into that. Working on the forms rather as queries seem more complex.
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Backend - Capture users details
depends on the queries.Tribble wrote:I will look into that. Working on the forms rather as queries seem more complex.
- Spoiler (show)
- Tribble
- Registered User
- Posts: 88465
- 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
I have queries based on queries that are based on queries
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Backend - Capture users details
Then consolidate them. I know it is a big job but the simpler the better.Tribble wrote:I have queries based on queries that are based on queries
- Spoiler (show)
- rustypup
- Registered User
- Posts: 8872
- Joined: 13 Dec 2004, 02:00
- Location: nullus pixius demonica
- Contact:
Re: SQL Backend - Capture users details
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.
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
- Tribble
- Registered User
- Posts: 88465
- 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
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.
-
- Registered User
- Posts: 26022
- Joined: 13 May 2004, 02:00
- Location: Getting there...
- Contact:
Re: SQL Backend - Capture users details
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
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?
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
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
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Backend - Capture users details
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.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 itAs I said - question
- 1. Creates a 'back-up of the deleted data
2. Captures the users' info etc?
- Spoiler (show)
-
- Registered User
- Posts: 26022
- Joined: 13 May 2004, 02:00
- Location: Getting there...
- Contact:
Re: SQL Backend - Capture users details
RuadRauFlessa wrote: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.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 itAs I said - question
- 1. Creates a 'back-up of the deleted data
2. Captures the users' info etc?
Tribble wrote:The data is stored in SQL tables.
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
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
-
- Registered User
- Posts: 26022
- Joined: 13 May 2004, 02:00
- Location: Getting there...
- Contact:
Re: SQL Backend - Capture users details
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
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
- Tribble
- Registered User
- Posts: 88465
- 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
Oooh thank you - I will have a look at that.
- rustypup
- Registered User
- Posts: 8872
- Joined: 13 Dec 2004, 02:00
- Location: nullus pixius demonica
- Contact:
Re: SQL Backend - Capture users details
doo_much wrote:It seems this CAN be done!
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
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Backend - Capture users details
In that case go and create 4 triggers for each table in your SQL DB
- Spoiler (show)
-
- Registered User
- Posts: 263
- Joined: 12 Jan 2009, 12:36
Re: SQL Backend - Capture users details
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.
Mina.
"Don't waste your whole life trying to get back what was taken away" - The Offspring from "Kristy are you doing okay"
-
- Registered User
- Posts: 26022
- Joined: 13 May 2004, 02:00
- Location: Getting there...
- Contact:
Re: SQL Backend - Capture users details
Ahh Thanks!rustypup wrote:doo_much wrote:It seems this CAN be done!
SQL is a language...
SQL Server is a platform
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
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
- Tribble
- Registered User
- Posts: 88465
- 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
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
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
- rustypup
- Registered User
- Posts: 8872
- Joined: 13 Dec 2004, 02:00
- Location: nullus pixius demonica
- Contact:
Re: SQL Backend - Capture users details
SQL is already doing all of this... those monster logs are there for a reason, (SQL actually uses these logs for many rollback actions...)mina.magpie wrote:I used the database itself to do user validation so I could track who was doing what
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...
@Tribble - have you considered a coded front-end?
Most people would sooner die than think; in fact, they do so - Bertrand Russel
-
- Registered User
- Posts: 263
- Joined: 12 Jan 2009, 12:36
Re: SQL Backend - Capture users details
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.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
Mina.
"Don't waste your whole life trying to get back what was taken away" - The Offspring from "Kristy are you doing okay"
- rustypup
- Registered User
- Posts: 8872
- Joined: 13 Dec 2004, 02:00
- Location: nullus pixius demonica
- Contact:
Re: SQL Backend - Capture users details
it would appear access!=triggers, so there goes that option... time to bite the bullet
Most people would sooner die than think; in fact, they do so - Bertrand Russel
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Backend - Capture users details
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.
- Spoiler (show)
- Tribble
- Registered User
- Posts: 88465
- 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
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.
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.
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.no user should be entering data directly into a production database using forms. ever. i
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Backend - Capture users details
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/spacesTribble 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.
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.no user should be entering data directly into a production database using forms. ever. i
- Spoiler (show)
- Tribble
- Registered User
- Posts: 88465
- 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
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?
How do you prevent spaces from being added?