SQLTeam.com | Weblogs | Forums

Monitoring user activity in database


#1

I need to log the activity of all user database accounts so I can review the activity periodically and look out for strange behavior for audit purposes.

And if this sort of logging already exist, is there a table in the database that holds such information?

Unfortunately I'm completely new to SQL Server.

Can anyone assist?


#2

I think the problem with monitoring of "activity" is that you are looking for the proverbial needle-in-haystack. There are tools that will log all activity, but I'm not sure how you would analyse it. Other folk here will have suggestions, but personally I don't do it that way so I'm no help in terms of how you might do that with something that is as-is but which you want to monitor.

We use Audit tables. So for, for example, a table of Customers with have an AuditCustomers table. All the same columns, plus a column for Date/Time and whether the row was updated or delete. We don't store the current row (that's in the table itself), we store the "previous" row when it is changed/deleted.

All our tables have columns for Create/Update Date and UserID, so the Audit table provides a history of who-did-what-and-when.

We delete rows from Audit tables based on date, which may vary table-to-table. When we purge stale rows we retain one row older than the cutoff - so instead of being able to say "This row has not changed in the last 3 months" (which, IME, tends to the question "Are we sure") we can say "This row has not changed in the last 3 months, and the previous change before that was on dd-Mmm-yyyy hh:mm"

Mostly we use this to detect software issues, or accidental human error, but I have been asked to review historical data for fraud too.

Anyone with DBA access to the database can massage the data however they like, so if you have concerns for those employees something more sophisticated would be required.

Obviously "my way" requires that the software is built with this in mind, but the way this is done is using TRIGGERS. A SQL Trigger fires when a row in a table is Inserts, Updated or Deleted, so it would be possible to add triggers to an existing system in order to capture changes to data, even if the system was not designed for that functionality. (There may be some performance considerations, but IME what we do, which logs a HUGE amount of information, is not apparent to users in terms of slow-down)

We also log all Stored Procedures. So if User-A does a Customer Modification, that executes our CustomerSave Stored Procedure. That, in turn, logs that it was called from Session-123 (which in turn translates to User-A), and all the Parameters passed to CustomerSave. That log tells us exactly what the user was doing, what values were involved, and so on. Again, we mostly use that to diagnose software bugs and user training issues, along with "What processes are too slow", but in terms of reverse-engineering what someone did, fraudulently, it gives a very clear view. I can't see that being retro-fitted to an existing system. There are tools (e.g. SQL Profiler) which will record that information, in real time, but I've ad trouble trying to use that to get the same level of detail that we log - e.g. what did one particular person do, and the flow of control and data from parent-procedure to child-procedure calls


#3

Since you're new to the game, I'll tell you that there is very little in the world of SQL Server that cannot be found in some "expert" article if you know what to look for in Google. Usually, it's pretty easy. For example, if you do the following Google search...

https://www.google.com/search?q=audit+all+user+activity+sql+server+2016&oq=audit+all+user+activity+sql+server+2016

... you'll find some really good information on the subject, especially in the first two non-paid listings. Here they are...

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine

Something else you should get into the habit of, especially on this forum, is that your first sentence of every post should state... "I'm using SQL SERVER xxxx yyyyy Edition" and change out the "xxxx" for the version that you are using and the "yyyyy" for the Edition because it makes a huge difference in how to approach all but the most basic problems because of all the improvements, deprecations, and discontinuations that MS has done to SQL Server over the years. .

Getting back to your original question, the subject of auditing is huge and has nearly as many facets to it as SQL Server itself. You first need to define what the real purpose of the auditing is than then define how you would find the data and analyze it. As Kristen stated, it can and frequently is like "looking for the proverbial needle-in-the-haystack". To make matters much worse, you have to remember the old rule of "if you measure it, you change it" and that can be especially true with auditing. You can, indeed, bring SQL Server to it's knees performance wise, memory wise, and disk space wise if you're not careful.

Take a look at the two links I posted and use the "Google Search" I gave you to find more. To be honest and with no slight intended, this isn't something that should be assigned to a beginner alone. BUT, a dedicated beginner can learn a shed-load of info about nearly every aspect of SQL Server by doing such a thing. Don't expect either to be simple or quick.

If you do need something quick, then maybe don't write your own. Instead, buy a product that will do it for you with the understanding that you'll then need to learn how to use THAT product. :wink:

Also, Google for "sp_WhoIsActive" and study it and how to use. It may be the first step for you, it's free, and it's written by one of the leaders of the industry.


#4

If you want to read all the transactional activities of a user database then use fn_dblog() function. know how to use fn_dblog() function to Read SQL Server transaction logs in SQL Server.


#5

In my opinion, data access is terrible from all fronts. Terrible security, terrible auditing, and lack of standards.
Every application developer and/or DBA uses their own personal way that can change from day to day.

I think I solved it ~17 years ago. Solved all data access for all databases from all languages in all situations. So far, no one listens to a word I say on the subject, but it has been running great at Fortune 500 companies for 17 years.

I originally wrote it in Java (J++), then VB6, then C#. Boils ALL data access down to a few methods: Load, Store, & Delete. Security and logging are optional. It makes things so easy, it pushes the boundaries of object-oriented programming itself.


#6

Interesting. Do you have a write up somewhere on what your methods does and how it solves security issues for data access? And I'm also curious about the pushback you've gotten on it... what are they saying about it?


#7

I suck at writing, but I wrote about it here: http://systemdotpersistence.blogspot.com

Every developer who sees it thinks it's too simple. They think it won't handle every situation. Sadly, if they tried it, they'd find out it works for everything.

Then, they'd wonder why we haven't been doing it that way for 15-20 years. It just maps stored procedures to/from an object. It strongly-types the database to the application.


#8

Is Andrew a genius or a mad man?!? very thin line :slight_smile: if you wait for big brother to implement this, well you will be waiting 17 + years. build it and they will come. do you have some example of this implemented in real world scenario ? sounds very interesting indeed!


#9

Here's the source code:

I developed it at Law.com which was purchased by Thomson Reuters. They don't know what they own:
https://www.deposchedule.com/Begin.asp

Not much to see on DepoSchedule, but the underlying architecture is impressive. It is a cloud-type application service. Multiple apps, servers, databases, single-user login, etc. An Azure built in 2001 with VB6. The source code above is C# and includes the data access part only.