Solved

Best Practices for Timestamping DB Records?

Posted on 2011-03-22
4
516 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 32

Accepted Solution

by:
Stefan Hoffmann earned 500 total points
Comment Utility
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
Comment Utility
"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 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
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
Comment Utility
"...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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…

763 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

8 Experts available now in Live!

Get 1:1 Help Now