?
Solved

Best Practices for Timestamping DB Records?

Posted on 2011-03-22
4
Medium Priority
?
544 Views
Last Modified: 2012-05-11
We are readying ourselves for PCI compliance and are trying to determine best practices for obtaining time stamps to use on database records stored by our web apps:

Currently we use built-in date/time/timestamp functions in our server scripts that derive the time from the host server and use that to timestamp the saved records.
Our host server time is set to a stratum 1 SNTP server on an biweekly basis.
One group here is recommending purchasing a commercial app that would frequently set the host server time via SNTP (RFC-2030) and record a log of all corrections; our server scripts would continue to use their built-in date/time/timestamp functions and would require no modification.
Another group is recommending that we get an accurate time from within the server scripts via SNTP rather than the server; this would require modification of existing server scripts and each script would perform time queries each time they are executed.
The goal is to put into practice what will best satisfy the requirements of forensic record analysis and PCI audits.  We would appreciate any input on this.  Thank you.

0
Comment
Question by:NovoDev
  • 2
  • 2
4 Comments
 
LVL 36

Accepted Solution

by:
ste5an earned 2000 total points
ID: 35196965
hi,

first of all: you need at least a database side correct time stamp, because you may have latencies between your web apps and the database itself.These time stamps must have the highest accuracy in such a scenario.

You may add an additional time stamp in the web apps, but the necessity depends on your actual requirements and the design of these apps.

Then I think you need to go for NTP and not for SNTP, cause NTP has the higher accuracy.

Besides that, you can only go and buy an NTP appliance (not an application) which can guarantee the required accuracy. I would suspect that your NTP time source must be a stratum 1 server itself, thus it must get its time by DCF77 and/or WWVB  and/or GPS, and additional from an external stratum 1 server.

You can't get accurate time stamps in your server scripts, cause permanent high frequent querying of external stratum 1 server is not recommended and may be blocked by these servers.
0
 

Author Comment

by:NovoDev
ID: 35200948
"first of all: you need at least a database side correct time stamp..."
Thanks - so for MySQL we could use something like:

CREATE TABLE test.table (
`row_inserted` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`row_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

When the record is created, "row_inserted" would contain the creation date and time, and "row_updated" would show all subsequent updates.

So that puts the burden of time accuracy upon the database server - if we can find an appliance that can maintain the database server time, and that keeps a log of all corrections, should that meet PCI compliance requirements?  If there is a PCI audit or a credit card fraud forensic analysis, I would imagine having a correction log would be a requirement.  The PCI "Requirements and Security Assessment Procedures" do not give specifics on time accuracy; only that any stored credit card data records log the date and time.

We are increasing the points on this question since it is getting rather involved.

We again appreciate any further input on this - thank you.
0
 
LVL 36

Expert Comment

by:ste5an
ID: 35201374
hi,

There is normally another requirement: You need to not only to maintain a time stamp, you also need to ensure order on the records. So you need also a monotonic increasing counter row counter or row id.
0
 

Author Comment

by:NovoDev
ID: 35201831
"...You need to not only to maintain a time stamp, you also need to ensure order on the records..."

We do that already with an auto-incrementing UID as the first column of every table containing CC data.  Thanks for the heads up on that.

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
The viewer will learn how to count occurrences of each item in an array.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question