Solved

Best Practices for Timestamping DB Records?

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Powershell script 13 101
When should I use a controller? 3 34
Yahoo!'s Common Tags, explain them to me like I'm an infant. 4 36
Grunt script for Build Process 1 27
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
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…

770 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