Solved

Best Practices for Timestamping DB Records?

Posted on 2011-03-22
4
527 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

749 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