DBTechCon – Spring 2011
Are you ready for DBTechCon 2011. We have over 70 sessions taught by some of the best in the industry. All full members of SSWUG.ORG receive a deep discount, so you may want to consider signing up for full membership. What better way to stretch your training budget; no hotels, no travel, just great content presented over the internet.
Go to the Registration page to register and get more information about the conference.
$$SWYNK$$
Featured Article(s)
Configure Simple Database Mirroring
You have talked about Database Mirroring, researched it, now we need to turn it on.
Featured White Paper(s)
Essential Performance Tools for SQL Server DBAS
Optimizing SQL Server performance can be a daunting task. Especially so for an increasing number of reluctant DBAs faced with… (read more)
Locking Down Your Database Feedback
Lots of feedback from yesterdays newsletter about locking down a database. For recap, the goal is for a vendor selling their product to be able to use SQL Server as their data store, and restrict access to that data store exclusively to their application.
Brendan:
Not sure if I am missing the point here but the eaiest way is to do all database work through stored procedures then the application would be given access to execute those stored procs only. This would be done by giving the application access through a domain account and the account would then have execute. This would prevent users from executing there own code.
Brian:
In your description of the requirements for locking down a database, it is unclear whether only the objects/fields should be inaccessible, or the data as well…
If there was SA/admin access to the database, and the fields were obfuscated, some of the data would be useable if it was human readable (not encrypted or otherwise manipulated, calculated etc.) – things like addresses, names and so forth would be obvious. Currency values would be less obvious, key values probably useless.
Also, if going the permissions/credentials route – and it was its own instance, then wouldn’t the application have to handle ALL functions? (backups, maintenance, etc) – meaning the people running and hosting the database would be unable to manage the instance (other than possibly the MS SQL services?)
And, if the objects (and/or data) were obfuscated, but there was access to the application database was limited , you could still query sysobjects and such to get info on it correct ? (not sure)…
Probably the only way to make this feasible and somewhat easy to maintain (consider just dealing with schema changes!) is to NOT use SQL Server and use a standalone/proprietary/embedded database, spending the time and effort to “obfuscate” what it is using (database engine, files, services etc) rather than trying to fit a square peg into a round hole.
MUCH easier path I would think…. But, without any benefits of using SQL server that may not be present in the db solution used.
Option #2, which is highly dependent on the nature of the application, is use a “cloud” service database – either their own hosted solution or Amazon or….
Option #3, provide the app purely as a SaaS solution… again, highly dependent on many many variable.
But, I assume SQL Server was the only option so….
Marc:
I’m assuming the person who posted the question has built an application and database that is to be installed outside the domain of the application vendor, at a customer’s site.
Even in this scenario, where the database is installed at a customer’s location, I think it would be a rare case, if ever, where you would want to completely prevent access to your database. It can negatively affect customer relationships in several ways and attempts to use security features for a purpose other than a true concern for the security of the customer’s data.
Do you not want your customers to see your bad database design? Are you trying to ensure that you are the only one who can help them when things run slowly? Are you trying to prevent your customers from writing their own queries or reports? Do you think they’re not smart enough to write good SQL? (Maybe they’re not, but is restricting database access really how you would solve that problem?) You don’t want to insult your customers; you want to empower them and work with them, ultimately improving their success.
Except for the tiniest of databases or those of the simplest design, there is no scenario where you want to cut off complete access to a database, because every database requires maintenance and if it’s located at a customer’s site, then they’ll need access to the database for maintenance. This can be done by the SA account, but should also be allowed by the dbo – the person(s) responsible for maintaining that database – under least-privilege best practice.
If your application is smart enough to replace a DBA by monitoring and performing necessary maintenance on the database, then you’re smart enough to make and use your own proprietary database management software.
If the real concern is about customers reading your “proprietary” data and not about them seeing the design of the database, then consider using password-protected encryption within your application – read and write only encrypted data.
If the concern is about reading the logic (the SQL statements) within a stored procedure or user-defined function, then consider using the CLR equivalent and obfuscate the .NET code. But that’s only make it difficult, not impossible, to read.
Alex:
I have been looking for an answer for this for a year now, and didn’t have any real succes in finding a good help.
Michael:
I am very interested in this topic. I’m currently working on a project where we are deploying a SQL Server 2008 database with about 150 tables in the first drop. There are 12 drops. In addition to designing the database and the stored procedures, I’ve been tasked to keep the database from prying eyes and am looking for obfuscation/encryption/restriction techniques. I do not want to go down the path of Great Plains by modifying all my field names. Years ago I worked on a project that interfaced with Great Plains and I still remember more than I care to about the funky field names.