Keeping Customer From Accessing My Database? 567
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:A simple suggestion (Score:5, Informative)
Liability (Score:2, Informative)
Not sure what sort of information they're requesting access to, but if any data at all is of a sensitive nature, by supplying a password to them even in a read-only capacity increases your liability if that information is stolen through unauthorized access. Basically, if a third-party somehow acquires that username/password to steal a dump of every firstName, lastName, DOB, and social security number from the database, you could expose yourself to legal trouble for enabling the access in the first place.
I would err on the side of caution and explain you would rather third-party access not exist at all than open yourself up to the risks of unauthorized use of credentials.
Re:Reporting Database (Score:5, Informative)
Re:A simple suggestion (Score:4, Informative)
Re:Reporting Database (Score:5, Informative)
that the adhoc query account has limited access and limited resource priveleges.
However, the customer is bound to complain when Oracle will refuse to run their
heinous query.
All this is is a basic Oracle question (Score:1, Informative)
Use Oracle RAC and have your application use one server on the node and they can use the other.
Use CPU_PER_CALL to limit their impact.
Honestly this isn't a Slashdot question you need to be on somewhere like http://asktom.oracle.com.
You have many options to allow these users the access they desire but so far no strong reasons to deny them. So go for budget. Write up a RAC or Data Guard proposal, show them the additional initial and then ongoing cost, and let that put them off.
Re:Why? (Score:5, Informative)
2) Who is liable if the customer queries affect the performance of other processes/services (lack of index usage, expensive queries, etc)?
Make a list of bad things that can happen (Score:4, Informative)
Tell you boss how someone with read only access could affect the rest of the users, and you should be on the right track.
Re:Why? (Score:3, Informative)
First, just say "no".
Only if they demand an answer, give them as many reasonable reasons as you can think of, all at once, saying "no external connections; security policy" (only if true; and it should be... just because your DB has passwords doesn't mean it doesn't have vulnerabilities) and/or "potentially random queries against the production database can degrade performance" (again, only if true). Both are strong enough but vague enough that the client should be quiet about it.
Always attempt to couch reasons as near to what the client wants as possible. They want their DB safe from prying eyes, they want their website to run well. Try not to say something like "for the security of our servers" unless you put it at the bottom of the list because they don't care about that, and if they're like most clients, they won't grasp that (yourserver.owned == theirdata.compromised)
If they're still persistent, get another job; sounds like your management doesn't have a backbone
Re:A simple suggestion (Score:5, Informative)
Re:Use a read only replica (Score:3, Informative)
They will either decide not to due to costs (you win) or pony up the money (you win again).
Remember your time is (or at least should be) billable.
Re:A simple suggestion (Score:5, Informative)
select bt.* from big_table bt, bigger_table bbt, biggest_table bbbt where bt.id=bbt.bt_id order by non_indexed_column;
Perhaps a good left outer join tossed in there to really thrash the drives.
Re:A simple suggestion (Score:4, Informative)
Re:A simple suggestion (Score:4, Informative)
Oracle supports standby databases that can be locked into read-only modes. You can even change how often the data is updated, releiving your main access system the burden of direct customer interaction.
Worst case scenario is that the customer chokes the box to its knees and then THEY can't access your data, but it doesn't affect your operations at all.
Since you should most certainly be charging for this service, the profits reaped should more than pay for the small investment in hardware.
Plus, if your main DB does go down in flames, you may be able to requisition the read-only DB as a temporary main of down time becomes excessive.
Re:A simple suggestion (Score:5, Informative)
Re:Reporting Database (Score:5, Informative)
Re:A simple suggestion (Score:5, Informative)
And when it came time to build version 2 and make some internal changes we couldn't because the customers had grown accustomed to the schema being a certain way.
That's what Views are for. They keep using the "old table", which is now a view. You put the changes in the "new table". You don't need to change your code either, except in the instances that required you to change it in the first place.
Re:A simple suggestion (Score:2, Informative)
The customer is giving you a chance to add value, but doing so will take some work on your part to prevent negative performance impact on the production database.
So, you charge them for the work and create a data warehouse for them, perhaps including some analytical tools to help them query/ data mine effectively. The more they do this, the more they will probably want your help to do it better.
Everybody is happy.
Re:A simple suggestion (Score:3, Informative)
Note 67977.1 Oracle Fine Grained Access Control - Working Examples
Note 99250.1 Understanding Fine-Grained Access Control
if that does not help, open a service request and select "SECURITY" as component and get some assistance.