Solved

Best Practices for Timestamping DB Records?

Posted on 2011-03-22
4
523 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 33

Accepted Solution

by:
ste5an earned 500 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 33

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Learn by example how to specify CSS selectors for Selenium WebDriver test automation software.
An enjoyable and seamless user experience can go a long way on an eCommerce site. While a cohesive layout and engaging copy play roles in creating a positive user experience, some sites neglect aspects that seem marginal but in actuality prove very …
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now