What No Audit trail ?! - Safeguarding

[Geoff]

That may work if you want to know that a change was made by the DB user that everyone uses to make a connection to the SIMS DB. Actual SIMS users are handled by the application, not the database. Also, the request here as I understand it is for logging access, not changes.

 

You can change the trigger to work with selects if you wish. You can also change the @username assignment to use SUSER_SNAME() instead and pull the Windows login info.

[bobsmith]

Not sure I understand that. You are saying you used their audit trail and just truncated it to the last months records?

 

Our experience was that the information was simply not recorded by the audit - it wasn't a problem with the number of records, the information just wasn't there (or if it was, Serco didn't understand how to make any sense of it). I did think of doing something similar to @Geoff's suggestion, but CMIS/ePortal take the same approach as SIMS - the actual application end user is not the DB user and it would have been a lot of effort to close that gap.

 

[ETA - there is also a significant risk that a blunt table trigger approach to audit will play cripple Mr Database at some point (most likely when you least expect it or need it)]

 

CMIS logged changes based on the user logged into the application, not the SQL user.

 

There is a table called AUDITTRAIL which was fairly comprehensive but not perfect. We truncated a month at a time and added it to a new database running on the same server as part of a scripted job out of hours. We could therefore use the built in audit interface within the application to look at anything within the current month, or go to the new SQL database for the older records, all in separate tables by month.

 

If you ever tried to use the audit facility with more than a few months data it would hang the application!

[pcstru]

You can change the trigger to work with selects if you wish.

Can you? There is a clause to create a trigger on select? Where?

 

You can also change the @username assignment to use SUSER_SNAME() instead and pull the Windows login info.

I must be misunderstanding that too then. SUSER_NAME() will return the user in the current security context, which should be the DB Login (for SIMS). That seems to be how it works here so I'd be delighted to hear what I'm doing wrong.

Edited February 5, 2016 by pcstru

[pcstru]

If you ever tried to use the audit facility with more than a few months data it would hang the application!

We used to clear down the table regularly and retain the deleted records but as I said, the times when we actually wanted to know "who did _that_?", the actual information being audited was useless. Perhaps we were just unlucky (or you have been lucky!).

[CyberNerd]

Quick, license and sell a plugin!

Everybody else does as it's the only way to get SIMS to do what you need.

[Geoff]

Can you? There is a clause to create a trigger on select? Where?

 

Sorry, Postgres DB knowledge leaking through. In MS SQL you have to use a Profiler Trace and then create a Collection set.

 

https://msdn.microsoft.com/en-us/library/cc645955.aspx

 

 

I must be misunderstanding that too then. SUSER_NAME() will return the user in the current security context, which should be the DB Login (for SIMS). That seems to be how it works here so I'd be delighted to hear what I'm doing wrong.

 

When I use SUSER_NAME() I get my current Windows domain login username in the old NT \ format. I don't have a Sims server handy as I don't work in Education any more, but as long as you are on the same network and using Integrated Authentication it'll resolve properly.

[pcstru]

Sorry, Postgres DB knowledge leaking through.

Really? I would be kind of gob smacked if it were ever to be a trigger event for a user defined stored procedure on a table in any implementation of a SQL database.

In MS SQL you have to use a Profiler Trace and then create a Collection set.

https://msdn.microsoft.com/en-us/library/cc645955.aspx

In the context of an n-tier architecture, a SIMS customer using SQL profiling (which is aimed at DBA's doing database optimisation) to track SELECTS would be useless. There is no way to distinguish between selects that result in information being presented to the user and selects which are run as a result of enforcing constraints or data aggregation. It would however be a very effective way to cripple your database performance if every single select results in a database write to audit it.

When I use SUSER_NAME() I get my current Windows domain login username in the old NT \ format. I don't have a Sims server handy as I don't work in Education any more, but as long as you are on the same network and using Integrated Authentication it'll resolve properly.

SUSER_NAME() as I said returns the security context. If you log in using windows credentials, then you will get what you say because that is the security context, but many if not most application software effectively uses a single database user and logs in as that while managing the actual application users themselves. That is true for SIMS, CMIS and Bromcom as far as I am aware.

Edited February 5, 2016 by pcstru

[pcstru]

I'm curious as to the actual use of some kind of access log, apologies if someone has covered it. In terms of safeguarding :

 

1. It might flag someone as accessing something they shouldn't. Solution - use the security rights mechanisms provided to prevent such access. Operate good policy and procedure so that access rights are allocated appropriately when staff take on particular roles. There are already a variety of ways of assuring the quality of the implementation of that policy and procedure, which is effectively the use case here.

 

2. You already suspect an individual is accessing information that they should not be but, they either need access to that information for quite legitimate purposes or granularity of rights in the application means effectively that. Solution - this is into disciplinary territory and if you need to monitor what the individual is doing to obtain proof, there are very cheap and effective ways to do that from cameras to screen recording to capturing all their network traffic.

 

Any others?

[matt40k]

@Geoff I should shot you for suggesting adding a trigger on every table. That is a guarantee way to bork your system. Triggers are expensive and should not be used unless absolutely necessary.

 

If you really want to enable auditing, then you need to use the built in audit, it writes to a flat file (you use tsql to read it). It has very little performance impact and can be limited by size etc

 

Its worth noting this wont catch everything. SIMS caches alot of data, so lot will be junk, also means bits will be missed. Also if you have any extracts - both self designed reports or third party products - like groupcall

 

So, how you gonna combine all these logs? If your gonna write these off if shouldnt be too hard for Capita to write a gui for enabling it and a few reports - the gotta might be if express edition doesnt allow auditing

[Geoff]

Really? I would be kind of gob smacked if it were ever to be a trigger event for a user defined stored procedure on a table in any implementation of a SQL database.

 

The postgres way is as follows (but I digress):

 

SQL Fiddle Link

 

BEGIN;

CREATE TABLE foo (id SERIAL PRIMARY KEY, data TEXT);
CREATE TABLE foo_track(tracktime TIMESTAMP DEFAULT now(), foo_row foo);

INSERT INTO foo (data) SELECT 'Some Data'||id FROM generate_series(1,10) AS
id;

CREATE OR REPLACE FUNCTION foo_track_func(foo) RETURNS integer AS
$$
 INSERT INTO foo_track(foo_row) VALUES ($1) RETURNING (foo_row).id
$$
LANGUAGE sql;

CREATE VIEW v_foo AS SELECT foo.*, foo_track_func(foo.*) FROM foo;

SELECT * FROM v_foo;
SELECT * FROM foo_track;

COMMIT;

 

http://www.postgresql.org/docs/9.2/interactive/sql-createrule.html

 

@Geoff I should shot you for suggesting adding a trigger on every table. That is a guarantee way to bork your system. Triggers are expensive and should not be used unless absolutely necessary.

 

I'm used to handling Terrabytes of data on cloud systems these days. So the sentiment is accepted for an on-premises DB.

Edited February 5, 2016 by Geoff

[pcstru]

@Geoff, applause for that. I certainly did learn something but there is a rather limited context where you can pull off that trick. It's not a prospect for the schema of a production system! ... unless I'm missing something really quite big. But it is late and Friday - so I might well be!

[localzuk]

An audit system in SIMS would need to be sensible. Doing it at the SQL level would be a bit overkill IMO. Having it in the client, at a form level would seem to be a better way of handling it.

 

"Fred bloggs searched for Jones"

"Fred Bloggs viewed Tom Jones"

"Fred Bloggs edited Tom Jones fields x y z"

 

Combine that with a "what can Fred Bloggs see" view for each form and it would be a useable and sensible solution. Database overheads would be relatively low also.

 

Would require a bit of additional work on the client but not a huge amount!

[matt40k]

Well for starters Geoff script only trigger on insert, update, delete, not select. So... yer. @localzuk I think you under estimate how much work that would be. Enabling DB auditing is a few hours work - creating reports is harder, but could achieve something basic a few days. So its like 1 months work vs 9 months hard work.

@elsiegee40 - @PhilNeal (well SIMS) falls into the "legacy" category, so they are not legally required to do anything, they'll have a horrid time when they finally release the new SIMS product as they'll find they have to comply. I know we're having some fun with it on our BI platform because effectively anyone could rock up and go hey, what data do you hold on me (data dump - easy) and who's looked at my data and when (not so easy). They might even get slap with the requirement now, depending if the government could define what is "new" is (so is new a major change, so 7.0 to 8.0, or can it be a minor change, like a new feature so 7.0 to 7.1)

 

You are right however, they should be looking at it as a sales gimmick - hey, come use our Azure hosted solution, look at our fancy all-inclusive auditing (only on azure)

[pcstru]

Would require a bit of additional work on the client but not a huge amount!

Would you like to quantify that additional work that is not a "huge amount"? Perhaps avoid the complexity of releasing a coherent 'platform' and just try to get some ballpark figures for the work to the 'forms' by the principle disciplines - the analysts, developers and testers? When you have that, how does the demand stack up against the cost?

 

Your assumptions remind me of the kind of stuff sales people would come back with from customers when I was in that kind of game. Say what? By when? You are ****ing kidding! On the other hand, they got the whacky bonuses - not us grunts who just had to deliver ... something!

Edited February 5, 2016 by pcstru

[localzuk]

Ok, you guys appear to have latched onto a solution and are now working back from there to the problem. That's just not a good way to look at this problem.

 

The solution you have mentioned is nearly useless to a school in fact, and would require a huge amount of work by Capita to provide the data in some form of human readable form for the school. A school doesn't want to know that fred bloggs selected * from the xyz_blah_pupil table. That means nothing to them, and in fact getting that data into a sensible format for the school may be impossible - if those tables are called from different parts of SIMS (which they are), a select on a particular table means nothing to the end user without the context as to what part of the client called it.

 

No, what a school would want is as I listed above, and as I said, is not a complex task to do in a properly built .Net WinForms application. Adding a call to log form access should be relatively simple, just time consuming (remember, complexity and taking time are not the same thing).

 

That data would able to be recalled in reports in a very easy and human readable form also.

@pcstru - you need to work on your manners. There is zero gain to be had from being obnoxious and rude. You appear to be the one making assumptions in fact...

[matt40k]

Still don't agree with you Tony. SQL Audit function IS the answer. Again, using your point at being user driven requirements, they dont want to know tables, they also dont want to know what button they pressed. They want to know what records they (user) looked at and when.

 

The only ui change would be protected records - so parent who works at the school shouldn't be able to see the details of their child. Also staff who were previously students.

[pcstru]

@pcstru - you need to work on your manners. There is zero gain to be had from being obnoxious and rude. You appear to be the one making assumptions in fact...

My apologies if I have offended you. You make a claim that something is a "bit of work but not a huge amount". To me that looks like a figure you have plucked out of thin air. I have some experience (13 years) of exactly the kind of software development involved and my experience tells me you are very wrong. It is potentially a large amount of work - certainly many hundreds of man days would be required over the entire suite to build use logging into the client elements. They would need to be there otherwise it is impossible to distinguish database activity that is significant (presenting information to users) rather than being used to (say) enforce constraints or perform data validation.

[pcstru]

Still don't agree with you Tony. SQL Audit function IS the answer.

I'm presuming you are talking about tracking changes rather than logging what users are doing and trying to infer from that what they are accessing? For change tracking iut seems like a possible approach but how do you track SIMS users at the database level with something that is not application aware when the users are part of the application (i.e. they are just more data in a table)? Part of the problem here would be avoiding false positives - this is potentially evidence that would be used in a disciplinary or follow on employment tribunal or even a criminal case, so it would need to be rock solid. Just assuring that would be a difficult task and any changes SIMS make from one release to the next could break it badly.

[localzuk]

They would need to be there otherwise it is impossible to distinguish database activity that is significant (presenting information to users) rather than being used to (say) enforce constraints or perform data validation.

 

And this is exactly why an SQL auditing approach wouldn't work.

[matt40k]

No, not change tracking or cdc - that is used for loading data into warehouses etc. sql auditing. As in who has selected from table, etc

 

Sql auditing IS the standard - it meets financial, pci, health, military requirements.

[SwedishChef]

@pcstru. I think you miss the point of the thread, whilst some of the points you make are valid.

 

In a nutshell....Big company, large product, seen to be providing the users with a voice (change requests) and not listening or addressing the problem.

Whilst still staying silent and only answering the "corporate tick box" questions. If it's 100's days worth of man hours......then so be it. If Capita had started when the problem was first raised.....they'd be finished by now.

[CAM]

Capita are already focusing on Life without Levels, Progress 8, constant census changes with virtually zero notice from the DfE and the upcoming KS5 changes. That eats a lot of development time, would it make business sense to implement SQL Auditing if they are a legacy application and gain nothing from it's implementation when they are not required to have it?

[pcstru]

@pcstruIn a nutshell....Big company, large product, seen to be providing the users with a voice (change requests) and not listening or addressing the problem. Whilst still staying silent and only answering the "corporate tick box" questions. If it's 100's days worth of man hours......then so be it. If Capita had started when the problem was first raised.....they'd be finished by now.

 

I can't recall anyone seriously asking for read/view logging in an MIS product before and I've yet to see here a real justification for it - but I invite anyone to offer examples (see my post here) where the feature would not be covered by effective alternatives. I do recall people asking for access rights which were sensitive to some data context (i.e you could have rights to view some records in a table but not others), IMO that was equally silly given the expense of both the development and operation of the product and not doing it was a good call. Sometimes it is necessary to save customers from themselves and not give them what they think they want.

 

In terms of logging changes (audit of writes), I think that is probably more important since writes affect data integrity but I'm not clear that it is a kind of "statutory must have" necessitated by policy or legislation around safeguarding. It might benefit the suppliers since it would potentially make it easy to spot hacked DB changes (users hacking the data via the back end or unauthorised 3rd party tools). But it is the kind of feature that if it is not in the architecture/framework, it is very difficult (expensive) to build into a mature product after the fact. From Capita's POV it must be hard to see it as vital since no other product does a good job of it (as far as I know), if it does it at all. It wasn't something that featured on our list of "must have" requirements when we were looking for a product. Perhaps we missed a trick but I don't currently think so.

 

In terms of managing development requests; development is generally resource and time constrained - so you always have a situation where there is more demand on development than can be met for a particular release and many releases are constrained by the timing of statutory requirements so you have limited flexibility on the time available. The availability of development resource has to be split between customer requests and internal development needs (bug fixing and enhancements identified by product managers). The changes being discussed here have the potential to displace all other customer requests for a release cycle (or more than one) as well as internal development requests. Given that, then the popularity of a CR simply can't be decoupled from the cost of it - that would be a very poor way to manage software development. Perhaps MIS suppliers do miss a trick when tracking user CRs, they should publish a cost factor so that users can see requests ranked according to votes and the cost of actually doing them. There could then be an appreciation that in the next release(s) you can have either these 100 popular CR's, or this single equally popular request. Which do you think they should do in that case, the 100 or the 1? Should a CR simply be evaluated on popularity without any reference to the cost?

 

So, yes, perhaps I am missing the point, but if I am, I'm not sure what it is. I'm happy to be educated though so have at it!

[SwedishChef]

@pcstru You make a very eloquent case if you were the one running the business, and can clearly see have an acumen on development/project and change control.

 

I don't think you need educating but again the point of this thread isn't to make a business case or to justify why not to make one. It's to highlight something missing from a product from the point of protecting the students. Step back and think about where we are trying to do here. I also think there are enough replies to this thread to see that people have requested this. I do agree with you though some transparency on decision making from Capita might be beneficial.

[localzuk]

@pcstru, you want some reasons why auditing should be in place?

 

How about being able to determine who has been accessing details in the case of a child protection issue? Eg. A child has been targeted somehow, and there's a hint that it may be a member of staff - being able to look at who accessed that child's records may be of great assistance in any investigation.

 

How about to audit what us IT admins are doing in SIMS? We have unfettered access to the system. Sure, we could poke around in the SQL database itself, but most don't do such things. Having a proper audit would help some way to showing that IT staff don't misuse their privileges.

 

There are others, but those 2 seem reasonable to me, especially the first one.