Tell me more ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.
up vote 5 down vote favorite

Our database does not have any audit functionality.

  • It does not records who inserts the record or who who changes it
  • It does not keep a history of the changes made
  • Nothing can be restored if something goes wrong
  • There are many other short comings for example the database does not have any data integrity, anything an be thrown in, no check on different fields, duplicates etc

My question is, who's job it is to put this functionality in? An ASP Developer who knows basics of SQL and who interacts with the database regularly. Or an SQL Administrator who's sole job is to work with the databases, optimize it and maintain it?

At this point we do not have an SQL Administrator, but can hire one. How big of an undertaking this should be for a developer to fix the above issues?

share|improve this question

4 Answers

up vote 5 down vote accepted

This is not the job of an ASP developer or the SQL Admnin (DBA). It is the job of a database developer or database architect. If you have to you can hire a DBA to do it, but really they are generally too busy to do design.

Note it is much harder to put things like referential integrity and data checks into place once there is data in the database, this is why you should never design a database without an expert. ANd of course audit functionality will be limited to only those changes going forward, no one can recreate what happened in the past that was not recorded.

share|improve this answer
up vote 3 down vote

Actually SQL Server has auditing built into the product. The easiest way to insure that all the things you describe are done is to enable C2 audit tracing. In SQL Server management studio right click n your server and select properties. C2 audit tracing is under security.

Here is a blog post with video showing how to do it.

To answer your question "who's job it is to put this functionality in?" nobody's, Microsoft has already built it into the product.

It should be the job of your internal auditors to insure that it is turned on and running properly.

share|improve this answer
This auditing is not sufficient for most auditing scenarios. – HLGEM Feb 20 '12 at 21:32
This can be extremely helpful to me! – Noname Feb 20 '12 at 21:35
up vote 1 down vote
  • It does not records who inserts the record or who changes it

This is a database design responsibility.

  • It does not keep a history of the changes made

This is a database design responsibility. A work around could exist by the DBA but the question here how do you use this history and how often?

  • Nothing can be restored if something goes wrong

This is the responsibility of the DBA and the project manager who allowed this system to go to live.

  • There are many other short comings for example the database does not have any data integrity, anything an be thrown in, no check on different fields, duplicates etc

Data integrity is a complex issue. Generally, the referential integrity is tackled by the database designer/modeller. Row duplication should not be allowed of course however, if you have two rows like this for (id, name, salary):

1 Joe 100

2 joe 100

most database system will consider these as 2 different rows even though the 'business data' is identical. This is a requirement and database design issue.

share|improve this answer
I am actually not so familiar with this. Since my application is an ASP.NET with users and different roles. The roles indeed come from ASP.NET application, is it still the designer responsibility to implement the audit and data integrity. Because I would think, most of the stuff can be (and should be) implemented in the application itself. This will open up a new world for me if it can be done outside the application. – Noname Feb 20 '12 at 22:10
1  
In a database application, there should be roles for people who work on this project. Programmers should not do all the work. The database designer and the DBA should coordinate certain tasks and identify the best way to enforce some of the issues you mentioned. The code that does the audit (say the code that sets the userid for whom last changed a row) can be implemented in the database level or in the application level. Personally I would let the application handle business requirements and leave work that can best be done by the database outside of the application code when possible. – Emmad Kareem Feb 20 '12 at 22:16
up vote 0 down vote

99% of this really depends upon the answer to the question "if I updated the database, what part of the application would know who I am?"

Typical web applications connect as a single user, the database server has no clue who is operating the keyboard and whom to assign the actions to.

On the other hand, many desktop apps connect using credentials per-user. In this case they know who is doing what so you can ride infrastructure into an great audit trail.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.