Providing access to your database can lead to horrible data breaches, such as when China pulled down all federal employee data from the Office of Personnel Management. Regardless of the security you put in place, fundamentally all the information in your database is accessible to the right query, with the correct credentials. Your only protection in this case is insuring only the right people have credentials, and they only use them as authorized.
When security is critical, add a second level of data security. Here’s how it works.
The important piece here is the data provider layer. The data provider is the only application allowed to talk to the database. No other system has the credentials, and the database will only accept requests from the data provider server. At this point we are no more secure; we’ve just moved the location of our weak point.
Now the key to the two-level security is the public API of the data provider. The data provider requires credentials for a request. It provides multiple APIs, one for each set of data that can be returned. You pass to a given API the select for the data you want.
Advantages of Two-Level Data Security
Setting your system up this way gives four major advantages.
- Each API returns an XML dataset of the requested data. So it reads the SQL data and then populates only the values in the XML schema returned by this API call. This eliminates a query for all data and instead returns only the data allowed for this request. If you call the API provided to return health insurance pertinent information in an employee database, there is no way to get security clearance data out of the database.
- Each set of credentials has access to only a specific subset of the APIs. For example, a health insurance provider has access to the health care information APIs but not to the salary information APIs. If someone hacks a set of credentials, they now have access to one subset of the database, but only that subset.
- Each API has an expected signature of number of requests, number of results returned by each request, etc. Say the API for employee salaries generally gets 20 requests/day that usually return 1 record/request, and you unexpectedly get 200 requests during a day and/or requests returning 100+ records. You can then shut down the credentials used until you can verify the abnormal requests.
- You can store allowed IP addresses for each set of credentials. Any use from another IP address is disallowed.
No security system is perfect. But implementing the above is significantly more secure than allowing direct access of the underlying database. Any successful hacks will tend to be small sets of partial data.
Agree/disagree/have something to add? Let me know in the comments. Thanks.
Author: David Thielen
Dave, Windward's founder and CEO, is passionate about building superb software teams from scratch and dramatically improving the productivity of existing software teams. He's really proud that he once created a game so compelling (Enemy Nations) that a now-professional World of Warcraft player lost his job for playing it incessantly on company time. You can read more from Dave on his personal blog, and at Huffington Post.
Other posts by David Thielen