0

We have data stored in a data warehouse as follows:

Price

Date

Product Name (varchar(25))

We currently only have seven products. Once every business day, seven new data points are added representing the day's price for each product.

On the website, a user sees this information on a page load. Analytics shows that the amount of page loads is about 100 per day.

The website server is an external server to our data center. The data warehouse server is internal to our data center and has other critical data (in different tables) not related and should not be accessible to the website.

The following approaches have been considered:

1) The data warehouse could daily push (SFTP) a CSV file containing the daily data to the web server. The web server would have a process running on a crontab every 15 minutes. It would check if the file had changed. If so, it would update its database with the data. Then, on page loads, the web server would query its database to get the data to display on the web page.

Usually, the push would only be once a day, but more than one push could be possible to communicate (infrequent) price corrections. Even in the price correction scenario, all data would be delivered in the file. The polling process would pick up the change and overwrite the data in the database.

2) The web server could request data from the data warehouse using JDBC or similar SQL connection technology.

However, a security concerns have been voiced. The concern is that by allowing the web server access to our data warehouse, SQL injection attack or some other external attack through the website could compromise the data warehouse. Measures could be put in place to reduce the risk, but the easiest and safest approach has been suggested to simply not allow any public facing system to directly access the data warehouse. In other words, the data warehouse can establish a communication with other servers (say to SFTP the file), but no server can initiate a connection with the data warehouse. Do these concerns seem reasonable and hard to mitigate?

3) A web service could be built, and the web server could call the web service that is hosted in our internal data center.

4) A process hosted in our internal data center could call the web server when it knows the data warehouse has data available. If so, how should this be done? HTTPS with some way to prevent other unauthorized clients from making the same call?

Which of the above approaches is best or is there a better approach than listed above? What are the pros and cons to an approach?

0

When you enter the prices each day in the data warehouse why not just put them in a database specific for the webserver that just holds "today's" prices? Does a static list of prices need to be in a database? You can't create a daily price file or are you planning on using it for other data purposes.

SQL databases are used all the time to provide data for websites, but you have to know how to avoid injection attacks. Not that this database for the website is as critical as your data warehouse, but someone in your company better learn how to handle the security.

  • Thanks JeffO. To answer your questions: 1)Approach 1 would have the data stored in a database. But it would get that data from a flat file. 2) The "static" list of prices do not need to be in a database although for security purposes, I think storing the data in the websever database might be better. – James Jan 10 '14 at 15:39
  • You can have files for data source purposes that are secured. – JeffO Jan 10 '14 at 23:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.