Database

3-Aug-05 9:07

Since the date is stored with high precision (i would guess as a function of the exact time the entry was made, like Now()), a general date query is likely to have many misses (the hh:mm:ss parts may get defaulted to 00 and cause a candidate to fail the equality match). I would use a range query instead, (i.e Between "XXX - 12H" and 'XXX + 12h', or present a selection list of candidate dates queried from the database. The string format of the date is also dependant on the locale setting of the computer running you app, so using the general date format is risky dd/mm/yy works for some locales, others expect mm/dd/yy. I would format the date using th 'odbc canonical' format which is locale immune:
"XXXX" = "{ts 'yyyy-MM-dd HH:mm:ss'}".

Even better, use a parameterized query and pass the date value(s) as a parameter of type OLEDATE.

You should also be aware that Access stores the dates as local time relative to the zero date , so changing the time zone can cause the dates to appear differently in different time zones...

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke

tracking actions in sql

WetRivrRat3-Aug-05 5:35

3-Aug-05 5:35

Hey all,
I'm working with a software package that relies on the security of the server to protect the database, meaning the database is entirely in plain english, and there is no double checking or check sum features to verify the data.
The server is accessable by all domain users(go figgure?) and therefore so is the database. The problem I am having is certain users have figgured this out and will go in to the db to change their access level and passwords of others (just for the sake of being malicious)

I have contacted the developers of the package and they basically said, "Sorry, mate, can't help you, we didn't design the system to be used in a hostile environment like yours". So I have no help from them, but still have the problem.

I need a way to track the transactions happening on the access_level column of the users table.

I've tried using the sql profiler, but i only know how to trace current transactions not previous tranactions so if the server gets rebooted, or someone logs into the console they see that its running and shut it off.

Basically, is there anyway to write something that will store the transactions happening on that column? or is there anyway to store the username column that was modified? so if i were user1 and i went in to the db and modified my account access level there would be a log or record of that transaction somewhere else that would store that users name.

I will plan to write any neccessary app in C#, and I would prefer to have the app run on my pc, not the server.
i've thought about just running an app that queries the db every so often (3-5 mins) to see the number of users in each access level, then returns the usernames for each admin access level(the level they are maliciously accessing) but that seems like a awful lot of work on the db, specially since its allready running slow due to the number of hits and request the server allready deals with. (which i can't really change since the server is run and operated by a different team, I just run the software package, there is a server group and a db group)

Is there a better option???
Confused | :confused:

Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."

Why is common sense such an un-common comodity?

Rob Graham3-Aug-05 6:20

3-Aug-05 6:20

What is the underlying database engine? If it is sql server you could add an update trigger on the users table and audit trail the changes to another (new) table. Better yet, the trigger could also reject access level changes by other than specific users, and perhps temporarily disable their access completely...

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke

WetRivrRat3-Aug-05 7:05

3-Aug-05 7:05

yes it is sql server, and i will have to do some research on these triggers you speak of, any direct links, good books, ideas you can share?

Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."

Why is common sense such an un-common comodity?

Rob Graham3-Aug-05 7:15

3-Aug-05 7:15

The most complete documentation would be in SQL Sever Books On Line, which can be downloaded here.[^]

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke

Anonymous3-Aug-05 7:20

Anonymous

3-Aug-05 7:20

Cool, I'll check it out, thanks!

WetRivrRat5-Aug-05 8:40

Re: tracking actions in sql [Edit]

5-Aug-05 8:40

ok, so i've done some reading on triggers, and now i've got some questions!!!

do i want to do this with an instead of, or after statement?
or would it be just best for me to use this: CREATE trigger Nnew_updates on people for insert, update as
if i use this one, will i need to search thru the deleted table to find it, then rollback, or just use the instead of and compare the requested change with the trigger and not let it do it at all? (can i even do that with the instead of)
I found some DB2 info that uses BEFORE, too bad sqlSever does't use that, it'd be nice.
Course, the info that i found showing sql doesn't use it was from 2000, and it said that Microsoft was contemplating the use of BEFORE, do you konw if they have implemented the use of it in sp3 by chance?

My next question then is regardless of what method I go with what should my trigger statement look like? for instance I'm already looking at using what i pasted earlier and i've got the format down, but what should my begin statement look like?

I need to beable to compare the old access_level to the new access_level. so will i need to compare the deleted table with the people table? or is there an easier way to just not allow changes to the access_level column?

Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."

Why is common sense such an un-common comodity?

Rob Graham5-Aug-05 9:22

Re: tracking actions in sql [Edit]

5-Aug-05 9:22

I would use separate triggers for insert and update. The Deleted pseudoTable will not exist in the case of an insert, so the handling will likely need to be different. In both cases the new data can be accessed in the "Inserted" psuedoTable.
The for insert, for update triggers would seem nore sensible here than an after or instead of...

Could be as simple as "if Inserted.Access_level <> Deleted.Access_level then rollback transaction"

For inserts, I suspect you will want to check the user that is inserting the record (not the user being added)
- SELECT user_name() will return this, but this might be probematic if the application is always logged in as sysadmin - dbo - (it shouldn't be, but that is another matter).

Yuo should likely disallow changes to more than on record at a time (rollback if @@rowCount > 1) as this would seem inappropriate in any case, and allows succeeding checks to assume only one row (the current one) in the psuedo tables.

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke

WetRivrRat5-Aug-05 9:55

Re: tracking actions in sql [Edit]

5-Aug-05 9:55

Well, my thinking is that I'm going to be better off by not restricting it to users as there IS one user who has admin access to the software but does not need the access to change others access(i was forced to give him access). so in other words i need to restrict the access to the access_level column entirely, and IF i need to update someones level(only if my boss tells me to) then i disable the trigger to complete what I would need to do. plus the domain levels are not set to change the db level so they can log in with sa level access.
and i think i would need to block any new entries as admin as there is nothing keeping someone from entering a new username and creating it as an admin from the db.(not sure if that was inline or out-of-line with what you were saying about the inserts.)

in your last statement, not sure i fully understand, are you saying i need to block more the possiblity of someone using a query to insert multiple accounts so as to trick the trigger into only stopping the first entry?

and i sould be able to run both the insert and update checks in the same trigger right? by just separating the functions of each rather than running them as a single statement?

Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."

Why is common sense such an un-common comodity?

Rob Graham5-Aug-05 11:45