Detailed information on Security Setup

Bill,

Sorry, I am with you on the Security v. access.  I was having brain spasms this morning.

While there are SOP in place for security(via login, roles, view, etc), this requirement is resulting from 2 guys on a golf course stating I want secure data and Yes.  I will ensure your data is secure.  I know Ill encrypt your data.  After this sweeping statement, they go drink and then someone has to figure it out.

This is were things get sticky (and vague).  Apparently, the client wants all things to be protected from everyone.  For example, there are the obvious columns (client info, bank accounts, PO numbers, transaction amounts etc)  but this client wants every single order and the contents of that order(items, addresses, dates, times, quantities, etc) to be protected.  Apparently, the standard use of logins, roles, and views is NOT enough.  They dont want anyone to be able to see anything.  While this shouldnt happen based on roles, views, and other measures, this guy wants the data encrypted.  As a result, I must now potentially protect all columns in all tables (i.e. Sales, Orders, PreOrders, etc).  I am trying to stop this but some people are just silly.

I have warned them about the strain that they will put on resources but they dont seem to care.  Honestly, I dont know who is worse.  The guy asking the request or the sales guy saying yes but thats my problem.  Anyhow,  I am still working the lets just go through the database and each of the tables and encrypt specific columns.  In my head, I just see them tagging every column for encryption or a very large number of columns.  Therefore, I was wondering.  Is there a point at which one just does the whole database?
This would be for sanity as well as maintenance.

While this is not the actual case, I want to try to make an analogy and I think this will work.  Think of an advertising company that has Coke and Pepsi as clients.  While standard access restrictions are in place (certain IPs for firewall, secure logins, roles, view, other in-house methods, and etc), the Pepsi guys wants to ensure that none of the Coke users can see ANY of his data.  Well they cant but he is paranoid and wants the data encrypted.  I guess to be fair.  The guys at Coke do have access and could hire a hackers etc etc


knel

You have my sympathies.  If you want some revenge, or at least to teach the salesman not to make promises like that again, put together a proposal for funding really making a secure system.  That includes: buying new disk arrays that support encryption, upgrading to ASE 15 so that you can do semantic partitioning (have to purchase that option too), implementing auditing, two factor authentication, a huge new machine to handle the (in fact) awful performance hit encrypting every column will be, all the development, testing and OA&M work to make that all happen along with anything else you can think of in terms of security.  (You can never be too secure ;-)  Then widely distribute your request for funding making sure everyone knows that this is to fulfill a "requirement" from this salesman.

O.K., now that we have collectively vented...

I really don't think the column-at-a-time encryption is going to work for you.  Probably not for the reason you are thinking though.

I would right a code generator to handle all of the column changes and run it out of an Excel spreadsheet so the maintenance is trivial.  The real problem is performance.  You are going to eat up a huge amount of CPU doing anything.  To say you will take a performance hit is about like saying a semi hitting a mini is going to dent it.  In addition to the per field overhead, when you encrypt index columns, assuming you stick with simple encryption (no padding, etc), they are really only good for exact match queries and joins.  Encrypted column indexes don't work for range scans, like clauses, sorting, max/min, etc.

It has been awhile since I look at the encryption stuff.  A quick review of the doc seems to indicate that the level of granularity for decryption is the column, not the row.  In the case of Coke and Pepsi sharing a server, you are still hosed as you are still reliant on traditional row level security to isolate the users/data.

If you extend the logic to hacking, then there is no solution.  Even if you have separate database servers for the two entities, a hacker can still attack the other guys system.

I would be tempted to tell my boss that you need to isolate the "encryption" guy on his own server.  That is really not that much of a stretch when you consider that you are going to have to buy that much hardware if you enable encryption on every column anyway.  At least this way, only "encryption" guy takes the hit; not the rest of your customers.

Best of luck.

Bill