0

I am looking for a clean way to do audit trails in MS SQL Server, for compliance reasons, preferably completely on the database side without involving the web application.

When talking about audit trails I mean a complete log of the changes to the database at the data level. So, who changed what and when?

I have a setup with active directory as authentication backend and I need to have a Java based web application developed.

So in order to get a valid audit trail I would need to have the information about who changed what and when. AFAIK there is a feature called SQL Server Audit which can provide exactly that.

My question is about the user management and authentication on the MS SQL side of things. The web application should use the AD users and then access the database. I would thus concur that the AD users need to be relayed to the MS SQL DB by the web application in order to show up in the audit trail.

This seems to be confirmed by this MS doc: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/getting-started-with-database-engine-permissions?view=sql-server-2017

Which suggests that in a typical scenario one would try to manage users and groups via AD and have MS SQL just map these groups to roles on the database. These roles would in turn be granted privileges.

On the other hand some of my colleagues are saying that we should use one shared application account for database access and build the audit trail ourselves in the application. They are citing vague security reasons.

What is the right thing (tm) to do?

Felix Ruzzoli
  • 1
  • 2
  • What are you auditing? Schema changes, permission grants, and failed logins to SQL are easy enough to audit. Data level changes significant to the application is something custom to write in your app. – John Mahowald Jul 17 '19 at 12:56
  • I am looking for a solution to capture Data level changes. So why should one implement that on the application side of things when the database we are paying for has that already built in? See: https://docs.microsoft.com/de-de/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-2017 – Felix Ruzzoli Jul 18 '19 at 07:16
  • There are use cases for both. – Greg Askew Jul 18 '19 at 11:47

1 Answers1

0

So you're planning to give a whole load of users access to your database, merely not to have an audit trail where it actually should be? It's not "vague security concerns" that your colleagues have, but plain common sense. A database audit trail shows things like "this user updated/inserted/deleted that record". It doesn't say what actually happened, like "this person asked for a day of leave on the 20th" (to use a leave management system as an example). The database audit should be there, to see who made manual changes to the data. The web application should have its own audit trail (which it could sore in the database) for events happening in it (the web app).

SeverityOne
  • 101
  • 2
  • No. Not at all. The plan would be to use the DB permission and privilege system in order to create roles, add users to them and then grant DB permissions based on roles. As is suggested as recommended practice by the database vendor. I take it you did not read the docs? – Felix Ruzzoli Jul 17 '19 at 06:29
  • I read through it, and I think you misunderstood it. It specifically talks about giving *persons* access to the database - not applications. If a person has access to a web app and you propagate the authentication/authorisation to the database server, it means that any user can directly connect to the database and change any data. You don't want that to happen. Moreover, logging data level changes isn't very useful for the web app. A data level change is akin to "my car drove 10 km", but you don't specify *where* it went, or *what for*. – SeverityOne Jul 18 '19 at 10:59