Keeping Customer From Accessing My Database?

cyteen02 writes "We run a data processing and tracking system for a customer in the UK. We provide a simple Web site where the customer can display the tracking data held in our Oracle database. From these screens they can query based on a combination of 15 different data fields, so it's pretty flexible. We also provide a csv report overnight of the previous day's data processing, which they can load into their own SQL Server database and produce whatever reports they want. Occasionally they also want one-off specific detailed reports, so we write the SQL for that and send them the results in an Excel format spreadsheet. This all ticks along happily. However they have now asked for direct read-only access to our Oracle database, to be able to run ad-hoc queries without consulting us. As a DBA, my heart sinks at the thought of amateurs pawing through my database. Unfortunately, 'because you are stupid' is not considered a valid business reason to reject their request. So can any Slashdotters assist me in building my case to restrict access? Have you experienced a similar situation? Have you had to support this sort of end user access? How would you advice me to keep my customer away from my precious tables?"

Re:Reporting Database

[hellsDisciple]

Presumably apart from server load it wouldn't be a big deal assuming the users are working on a read-only login?

Oracle Seat License

[SirLanse]

Do they have a seat license to access the data?
No discounts.
You do not answer SQL questions.
Provide 2yr old data dictionary (just like MS does)

Re:Reporting Database

[djones101]

Exactly like WreckDiver said. Create a data warehouse that is populated on a regular basis (nightly seems a good idea), and let them touch the warehouse only. Keeps their paws off of your live database, and keeps your security in place, while giving them what they requested (just in a modified form).

Data Protection Act

[Anonymous Coward]

Say no, it's not permissible under current UK legislation. If one customer accesses another's data, you will be in deep shit.

Play the performance card

[BillBrasky]

Tell your bosses that allowing your customers SQL access to your database could allow them to input inefficient queries that could grind your whole system to a halt. Then none of your customers will have access to the DB.

You could also say that if a security vulnerability is present in your DB vendor's product, it will be much more easily exploitable through direct SQL.

Pay more money!

[RalphTheWonderLlama]

You could give them a database user that is confined to whatever database or tables you wish and can only do SELECTs. Actually you should really say too bad and if you want custom queries, you should pay us to write a front-end for what you need. This solves the access problem and the possible server load problem from whatever cross product queries they might come up with.

Re:You seem to be the problem

[kalirion]

Depends on the query. The right (or wrong) query could take hours to execute on a few hundred row tables.

Pre-process all data requests.

[arthurpaliden]

Just create a pre-processor process that applies a set of rules to the incoming request prior to allowing it through to the database.

Just say no

[sirgoran]

I've run into this myself.

I simply say that "Due to other client data being in the same database I am unable to allow you access. Since doing so would violate the privacy and security of their data, I sure that you would understand why I can't do that. I'm also sure how you would feel if the roles were reversed and how you would feel if another client asked for direct access and could see or read your data."

Usually this takes care of the problem. If not, tell them how much it will cost to set up a stand alone database that only contains their data and then give them some unreasonable amount. If they agree, then you just made your company a nice chunk of change. You then set up the database, and the scripts to replicate the data to back it up (when the client hoses themselves) and move on. When the call comes in that they hosed their database, you charge them for the time to restore from the back-up times a factor of two or three, and again, you've set yourself as the goldenchild for your company by making them money.

-Goran

Re:You seem to be the problem

[recoiledsnake]

How are they going to mess up your database with read-only access? They could run intensive queries, I guess. But unless you've got million+ row tables that are being accessed concurrently by tens of clients, this shouldn't be much of a problem. Anyway, just enable logging and look through what they've been doing in case it's anything stupid. I used to work for a large insurance firm and we'd get a call minutes after doing against the database we shouldn't.

I think the only problem would be that changes to improve the schema design would be more difficult to make because there would be pressure from the client not to break their existing adhoc queries that they already wrote and now run for new data.

Re:Reporting Database

[samwhite_y]

I would second this notion. This is the classic way to solve this problem. Modern databases provide many mechanisms to periodically push over changes to another cloned copy of the database. The advantage of doing this is you can do interesting things to the cloned data that you would be unwilling to do to a live database. For example, you can create temporary reports that get stored in another temporary database table and which in turn allow other reports to be created from this derived table. Oracle lets you do this pretty easily by creating "views". There is a whole industry built around this approach called "data cubing" with specialized tools to let you construct more involved data mining types of queries based on massaging the data in interesting ways.

Re:Why?

[Bill, Shooter of Bul]

So can any Slashdotters assist me in building my case to restrict access?
I think he was asking us to help him flesh out his argument, ie give him some reasons to back up his intuition. Well intentioned or not, your response is like telling someone who's asking for directions that they are lost.

Re:Reporting Database

[Musrum]

Oracle has a different concurrency model to older versions of MS-SQL. There are no read locks.

Oracle Application & Reporting Server

[Swampcritter]

Coming from an ex-Oracle DBA/Administrator background myself, I recommend setting up something like the Oracle Application Server for external users to access the database in a read-only format. That way, through the web interface, you can limit what tables can be reviewed. You could even go as far as creating certain types of search SQL statements that could be run from the portal as well (i.e. via drop downs, buttons, etc). Depending on your Oracle release, you might also want to look into the Oracle Reports server environment as well.

Oracle Application Server Overview: http://www.oracle.com/appserver/appserver_family.html [oracle.com]
Oracle Reports Server Overview: http://www.oracle.com/technology/products/reports/index.html [oracle.com]

-- Michael

Only one safe way to do it

[m.dillon]

And that is to generate a nightly backup of the database (or even a continuous one-way replication of the database), run it in its own virtual machine, and give the customer access to the backup. It's really that simple. The last thing you want to do is to give the customer direct access to your production database system, no matter what kind of security oracle thinks they can provide it would be a big mistake.

If money (in the form of ridiculously expensive oracle licenses) is a concern, then just create a daily backup and run the copy in mysql for the customer.

-Matt

Re:Been there, doing that and more...

[grassy_knoll]

Now the truly scary part is when they want WRITE access to their database.

I've had customers like that as well. Honestly, since they're paying and it's their data I can see where they're coming from.

On the other hand, I usually have someone sign off on a MOU that they understand writing to their database outside of the application is inherently risky, and that they understand there will be additional charges for recovery work.

WHEN a customer mangles their data we get to test our recovery processes. So far, so good.

Also good to have multiple backups using multiple methods. I've had customers mangle multiple tables, so a full database restore with point in time recovery was best while other customers munged up one table, so a restore from export worked out better.

Re:Reporting Database

[nova.alpha]

Some privilege escalation exploits require "just" a read-only login to server, and who knows what kind of vulnerability would black-hat guys discover tomorrow? So I'd recommend author to say NO to his customers.

Re:Just say no

[Improv]

So you suggest lying to clients to satisfy "gut feelings" without any good reasons behind them?

infinite interface

[roaddemon]

My biggest problem with this is that if you give them unfettered access to your database, everything in it becomes an interface you need to support. If they build applications around that data, you can't even change a column name without (potentially) breaking their application. If you give them access to a small subset of tables, there is a smaller interface you need to support.

This is actually my favorite reason for using stored procedures (yes, above performance and security). It's a clear interface definition. Don't break the procedures and you're fine. If there is ad hoc sql running against your database, your interface is basically infinite.

Re:A simple suggestion

[Ash Vince]

Better - show that they would be able to access other customers data and shout "Data Protection Act" as often as possible during demonstration.

The problem with this approach is that you come across as a rubbish DBA. Any DBA worth his salt can set permissions that only allow specific user accounts access to specific tables or views. I have never used Oracle but I do admin several MySQL databases (v4) and even they allow me to limit a particular user in this manner. If you are going to tell me Oracle does not support table level permissions I would be very surprised.

Further down this thread people start mentioning the silly query overloading the server issue. Now this is a real issue but it can be made to work either way you want. We had a similar request from a customer several years ago but we were not so opposed to giving them read only access if it could be done safely. We choose to set up a separate replicated server that they could query directly. If they wipe out the server with a silly query, who cares since it only effects them. The work involved in setting this up, its maintenance and hosting were all chargeable thereby making us more profit. This keeps them happy, the management team happy and me happy since my company operates a profit sharing scheme.

If you still are unable to see the benefit of giving them access then the best bet might be an intellectual property argument. Depending on whether you or they own the IP of the system you provide you may be able to argue that the database structure is a proprietary work and that exposing it would be against company policy in that regard.

Somewhere I used to work had a less than optimal database structure we all inherited from the previous developers who build the system. We knew how bad the design was but changing it was a huge job that we could not make the time to do as we were busy on paid work for other clients. We successfully avoided letting the customer see how awful the design was until the contract ended (it was a fixed term job that could not be extended) by making the IP argument.

Re:A simple suggestion

[Debug0x2a]

As thinkgeek puts it.. SELECT * FROM USERS WHERE CLUE > 0 No rows returned.

Comment removed

[account_deleted]

Comment removed based on user account deletion

Re:A simple suggestion

[Ctrl+V]

creating views limited to their data is a great, easy solution for limiting rows within the same table.

looking at it from a longer term perspective, if you think that this ability is something many of your customers would use, you can look in to what oracle calls Virtual Private Database (VPD). VPD allows you to set rules on tables that, on the server, adds a predicate to all SQL ran against that table. Essestially, it forces a WHERE clause of your choosing for all select, insert, update, delete statements.

if you tie that to a lookup table, say of logged on user's CompanyID#, any statement they send can only ever affect that subset of rows.

'delete * from orders' from Ted@Acme Inc (ComanyID#=5) would turn into: 'delete * from orders where companyid#=5'

fun stuff

Re:A simple suggestion

[afidel]

A well setup database CAN'T be brought down by any type of query, especially when you know ahead of time they might by running bad SQL. In Oracle you can rate limit I/O, cpu, temp, etc per user so just put in reasonable limits to what their user ID can do and put them in the lowest priority queue for each resource and unless your own work is already pushing your database server to its limit then they shouldn't be able to really affect production. We have run into this issue in our own environment where we have about 10% of our user base who have an Excel plugin that allows them to do completely unstructured queries, the user that they proxy through is simply thrown into the lowest priority queue and so while they might use 40% CPU if the box is idle they get almost nothing if it is hammered.