×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

Keeping Customer From Accessing My Database?

kdawson posted more than 5 years ago | from the my-precioussss dept.

Databases 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?"

cancel ×
This is a preview of your comment

No Comment Title Entered

Anonymous Coward 1 minute ago

No Comment Entered

567 comments

A simple suggestion (4, Insightful)

suso (153703) | more than 5 years ago | (#23436556)

Just say no and hope that it sticks. Seriously. I find that so many people in the workforce noadays don't know how to say that simple word. No.

Sometimes its hard to make a case for it if management at your company thinks that you are being unreasonable. However if you are a reasonable person and skilled in your profession, management should trust you to do your job. I'm of the opinion that if management can't trust employees in their area of expertise and to give good advice, then it is not a good place to work. My first tech job became this way, the new management that came along had a distrust of us and it made everything sour. Anyways, that's getting away from your question.

But being a sysadmin, I think you have to stand up for your opinion when the time is right to do so. People who aren't in the know always have requests like this to grant more access, make things easier, keep the customer's demands first. Its your job to draw a line in the sand that says you can't go past that point. Some people don't like that, but honestly it doesn't matter. Rules are there for a reason. They are guides to providing good service for all customers, not just one.

Re:A simple suggestion (4, Informative)

ShieldW0lf (601553) | more than 5 years ago | (#23436626)

You could always put together a demonstration, in which you illustrate how easily an unskilled user can issue the wrong query and bring the server to its knees.

Re:A simple suggestion (4, Insightful)

jackharrer (972403) | more than 5 years ago | (#23436772)

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

Re:A simple suggestion (4, Informative)

stoolpigeon (454276) | more than 5 years ago | (#23436816)

It is not difficult to make this impossible- oracle allows for limiting resource consumption by user among other things.

Re:A simple suggestion (5, Insightful)

TheRealMindChild (743925) | more than 5 years ago | (#23436792)

I will agree with this, but add one more note. You are selling them INFORMATION that you compile from YOUR data... you are not selling the data itself. I have had this conversations with clients many times.

Re:A simple suggestion (1)

suso (153703) | more than 5 years ago | (#23436884)

I will agree with this, but add one more note. You are selling them INFORMATION that you compile from YOUR data... you are not selling the data itself. I have had this conversations with clients many times.
Hey that's a good way to put it. Thanks.

Reporting Database (5, Insightful)

WreckDiver (685191) | more than 5 years ago | (#23436584)

The last thing you want is users writing ad-hoc queries against your live data. Replicate the data to a reporting database and let them abuse that.

Re:Reporting Database (3, Interesting)

hellsDisciple (889830) | more than 5 years ago | (#23436608)

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

Re:Reporting Database (1)

getto man d (619850) | more than 5 years ago | (#23436694)

Right. An account without any write-access and a quick script that will take their shell history log and pipe it to you should do the trick. Try something first, limiting absolutely everything you think is critical, but if this fails then you may have to deny them. Hope this helps and good luck.

Re:Reporting Database (4, Informative)

Builder (103701) | more than 5 years ago | (#23436776)

Actually, it can be a huge deal - badly written read queries can bring a database to its knees, slowing it down for the critical business writes.

Re:Reporting Database (5, Informative)

jedidiah (1196) | more than 5 years ago | (#23436860)

...although this is pretty trivial to avoid in Oracle. You just need to make sure
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.

Re:Reporting Database (3, Insightful)

CharlieHedlin (102121) | more than 5 years ago | (#23436802)

Read Only access can still create locks. I haven't worked with Oracle enough (I assume it is MUCH better), but a simple read query can bring our MS Sql database to a grinding halt if it touches tables that are actively updated.

Re:Reporting Database (5, Interesting)

Musrum (779646) | more than 5 years ago | (#23436960)

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

Re:Reporting Database (3, Interesting)

djones101 (1021277) | more than 5 years ago | (#23436636)

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).

Re:Reporting Database (1)

Bryansix (761547) | more than 5 years ago | (#23436708)

Eaxctly. This is what they teach in beginning database classes in college. Always use a data warehouse for data mining that is separate from the live database and never writes back to the live database.

Re:Reporting Database (4, Interesting)

samwhite_y (557562) | more than 5 years ago | (#23436874)

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:Reporting Database (0)

Anonymous Coward | more than 5 years ago | (#23436684)

Exactly.

Sell them on the "Data Mart" idea. A good buzzword will help them accept the proposal.

Promise them read-only access to the Data Mart, which will sync up with your transactional DB every n hours/days/weeks, etc.

Re:Reporting Database (1)

The Blue Meanie (223473) | more than 5 years ago | (#23436770)

Another vote for this one. Replicate the database and give them R/O access to the replicated DB. They can't overload the primary database, and if they manage to screw it up (shouldn't happen with R/O, but you never know), you can just refresh from the master.

Re:Reporting Database (1)

notorious ninja (1137913) | more than 5 years ago | (#23436848)

This is the best solution. Since there's no good reason not to give them access (other than cost?), querying a replication db should be fine. It's not going to mess up your data.

I'd say the same thing (1)

jd (1658) | more than 5 years ago | (#23436856)

Depending on just how heavy the queries are going to be, and how standards-compliant you can require things to get, you may be able to have the database replicated onto a lighter-weight server. This would have the bonus "selling point" of being faster than the main system, and have the "selling point" at your end that a read-only cache is both a standard and secure method of providing the required service.

Re:Reporting Database (1)

sdeering (910208) | more than 5 years ago | (#23436890)

Right, replication is the way to go. Second issue.. make sure they can't see any data they shouldn't, perhaps you have more than one customer in that database.. You may need to put filters on your repl to ensure this.

Why? (5, Insightful)

iElucidate (67873) | more than 5 years ago | (#23436610)

You don't want them "pawing" through your database, but you don't give any reasons why that is a bad idea. If you can't come up with any, you're not going to get very far in your argument. If it is a read-only view of only the data they should be able to see, what is the harm?

No, seriously. Answer that question, and you have a basis for your argument. If you don't have an answer besides "it makes me feel dirty," you've lost.

Re:Why? (4, Interesting)

Bill, Shooter of Bul (629286) | more than 5 years ago | (#23436920)

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:Why? (5, Informative)

ZeroConcept (196261) | more than 5 years ago | (#23436940)

1) Who is responsible to change the customer queries when the schema changes and their reports no longer work?
2) Who is liable if the customer queries affect the performance of other processes/services (lack of index usage, expensive queries, etc)?

Re:Why? (2, Informative)

Culture20 (968837) | more than 5 years ago | (#23437000)

The worst thing you can do with your first "no" is provide a reason. Then they will focus on this reason until they find a chink in your armor, and you'll come up with another reason and they'll get angry.

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

You seem to be the problem (5, Insightful)

etymxris (121288) | more than 5 years ago | (#23436618)

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.

Re:You seem to be the problem (2, Interesting)

kalirion (728907) | more than 5 years ago | (#23436744)

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

Re:You seem to be the problem (5, Interesting)

recoiledsnake (879048) | more than 5 years ago | (#23436820)

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.

Data Protection Act (1)

91degrees (207121) | more than 5 years ago | (#23436632)

It doesn't matter if there's anything in the act that applies. Just lie. Say that uyour legal advisor is worried about breeaches of the act.

Re:Data Protection Act (1, Insightful)

Anonymous Coward | more than 5 years ago | (#23436864)

No need to lie. Giving direct access to the information *sets the company up* for such a violation to occur in the future when new tables/columns are added.

As a DBA, you should express this concern to management IN WRITING as soon as possible, for two reasons:

1. It's your professional responsibility.

2. It's your career; cover your ass!

Oracle Seat License (3, Interesting)

SirLanse (625210) | more than 5 years ago | (#23436634)

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)

Partial data replication (5, Insightful)

netsavior (627338) | more than 5 years ago | (#23436638)

For the love of science do not give them access to your production database, they WILL screw it up, even with just read access.
Here is the psudocode from their SQL:
Select * from everything join everything where non-indexed column like '%'

you need to make them a COPY of the data that they are allowed to access on a seperate database (preferably a seperate server). Most reasonable replication suites allow you to do things like this.

Export it (1)

GWLlosa (800011) | more than 5 years ago | (#23436650)

Have some automated process periodically export the data to a disconnected data source (the exact one is up to you, something like MS Access or an Excel sheet depending on your exact needs) and give the user access to that.

Replication (0)

Anonymous Coward | more than 5 years ago | (#23436652)

If "No" doesn't work, which is my first choice, could you replicate the database and let them work from that? I would avoid letting them hit the live db at all costs.

my solution (1)

Coraon (1080675) | more than 5 years ago | (#23436658)

create a one way mirror of the database, let them access the mirror, that way when they pooch it you can reload...but charge them through the nose to do so, claim is as "you have to recover the data" or some such nonsense.

Like Nancy Reagan Used To Spout; "Just Say No". (4, Insightful)

RCTrucker7 (1049252) | more than 5 years ago | (#23436670)

How about just a simple "No." The database, while containing data pertitnent to your customer, is still your\your companies property. Simply tell them that access to that level, or in fact any level beyond what is alreayd granted to them as a customer, is for you and\or your employees only. Just because he's a customer, doesn't grant him unfettered access to your company or it's property, whether that property is physical or electronic.

Re:Like Nancy Reagan Used To Spout; "Just Say No". (0)

Anonymous Coward | more than 5 years ago | (#23436808)

If you said that to me I would hire another processor. Actually if it is their data and they are *paying* you to build the database isn't still their data?

Re:Like Nancy Reagan Used To Spout; "Just Say No". (1)

Champ (91601) | more than 5 years ago | (#23436942)

Exactly: "No. The database contains our confidential and proprietary information."

End of conversation.

Use a read only replica (5, Insightful)

Giant Electronic Bra (1229876) | more than 5 years ago | (#23436672)

Mirror the database to a 2nd server and provide them read access to that. It has several advantages.

1) You don't have to worry about them causing problems in the production database.

2) You can optimize the replica for read access. A read only database can generally perform MANY times better than one that has to be optimized to support read/write and especially if it is highly transactional.

Granted, it costs you a bit in hardware and setup time, etc. But if you're really nervous about it, then it should do the trick. Given the limited load on the replica and its read only nature it should be able to live on limited hardware, like maybe an older server that you have hanging around. Plus you don't have to worry about reliability either. If the thing blows up no data is lost.

Re:Use a read only replica (4, Insightful)

mcrbids (148650) | more than 5 years ago | (#23436918)


Granted, it costs you a bit in hardware and setup time, etc. But if you're really nervous about it, then it should do the trick. Given the limited load on the replica and its read only nature it should be able to live on limited hardware, like maybe an older server that you have hanging around. Plus you don't have to worry about reliability either. If the thing blows up no data is lost.


Cost? What cost? Oh, you mean the profit that you'll make from charging the end user for time and "overhead" in setting up the replication?

That's only a cost to the requesting end user! It's all profit for you!

Data Protection Act (1, Interesting)

Anonymous Coward | more than 5 years ago | (#23436674)

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

Why not? (3, Insightful)

Hankapobe (1290722) | more than 5 years ago | (#23436682)

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.

Why not set up an account that has read only access? Why not create a view of the table that shows only the columns they need? It'll be good customer service and relations. Just remember, your company can be replaced and if you don't give them the service they want they'll get it somewhere else.

Give them a trial period (0)

glgraca (105308) | more than 5 years ago | (#23436692)

"My precious tables"?

This is valid business mentality?

Why don't you just give them read-only access, and if they do f**k up, then you have a perfectly valid argument. Otherwise, you're just being a jealous geek.

Play the performance card (2, Interesting)

BillBrasky (610875) | more than 5 years ago | (#23436702)

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.

Suggestion (5, Funny)

ggvaidya (747058) | more than 5 years ago | (#23436714)

Don't use your work e-mail address when you call your clients "stupid" in a public forum?

(It's two a.m. here, I bet somebody'll point out some completely idiotic assumption I made in about two seconds. Oh well, so it goes.)

ummm.... (5, Funny)

Anonymous Coward | more than 5 years ago | (#23436910)

Wow, you're right. Next week "Ask Slashdot: How to find a DBA job after being fired from EDS"

Tell them it's not possible (1)

darkfire5252 (760516) | more than 5 years ago | (#23436718)

A simple solution would be to tell them that you do not have the ability to do that. You could say that your database stores data for all of your customers and that it's not possible to give them direct access without compromising the security of the other customers' data.

Re:Tell them it's not possible (0)

Anonymous Coward | more than 5 years ago | (#23436998)

Of course, you then run the risk of them asking someone who actually knows something who will tell them you are incompetent and they ask that you get fired. Don't make things up. If it is performance issues you're worried about, are you sure there's no way to force their requests to run at low priority, for example?

If you have real reasons, state them. If you're just worried they'll see how poorly you've set up the database, then fix it. With read-only, low priority, and a decent database server, what exactly are you worried about?

Becasue you are stupid is a valid reason (5, Insightful)

geekoid (135745) | more than 5 years ago | (#23436722)

just now when said like that.
I am not sure why a DBA doesn't know this, but just create read only views

Seriously - are you really a DBA, or just someone that got stuck DBAing? This situation is dealt with at every place I have ever worked, without exception.

You could also create a Cube. This might be 24 hours old, but I don't know who many transactions we are talking about here.

Be sure you can track all logins, and log what they do.

They are not your tables, get that out of your mind. They are the companies. All you can do is write a report explaining the risks to management, and be sure the users know they are liable when they make a mistake. Then set up views.

Yes, if they screw up you will be the one to fix it, that's your job. At least you can wave off any fault.

Re:Becasue you are stupid is a valid reason (1)

kannibal_klown (531544) | more than 5 years ago | (#23436924)

Well it is the company's server. If you get idiots writing queries they can bog down the server big time. I've heard horror stories of people doing running many concurrent queries every few minutes, and they were poorly written so they'd be constantly putting a drain on the server.

Unfortunately, too many people think that just because they know a few SQL terms that they're DB experts.

Liability (2, Informative)

LuminaireX (949185) | more than 5 years ago | (#23436728)

First of all, I am not a lawyer. YMMV, etc...

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.

Oracle DBA (2, Funny)

MyLongNickName (822545) | more than 5 years ago | (#23436730)

I thought Oracle DBA's were all uber-brilliant? Everyone I have met has told me so.

Anyhow, you never give end users direct access to tables. Never. Users who create nice cross joined tables will bring your system down to a crawl Create data dumps to ancillary database -- one per company in this case. And that is if your management won't back you up in just saying "no". I know this much, and I am NOT a DBA.

Pay more money! (2, Interesting)

RalphTheWonderLlama (927434) | more than 5 years ago | (#23436732)

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.

Build a view? (0)

Anonymous Coward | more than 5 years ago | (#23436736)

You can.. uh.. build them a view.

Maybe you shouldn't be in that job if you are asking.

Due to increased CPU (1)

Greyfire (25489) | more than 5 years ago | (#23436740)

You could go with a denial due to the increased resources that the ADHOC queries will produce. Both CPU and memory used will increase as they do their (most likely) not optimized queries. They won't have access to the rest of the jobs being run on the server so they could be performing a query at a time where usually the resources are free for an existing complicated job. You could even appeal to paranoia with exposing the server odd sql attacks. I'm not sure of the licensing restrictions on Oracle, but maybe they would be violating that if their ADHOC queries were coming from dozens of different users.

Basically they could break the server or increase the licensing costs.

Unless you want an excuse for a beefier server... then this might be worth turning around.

Good luck.

You have some other problems... (5, Insightful)

teknopurge (199509) | more than 5 years ago | (#23436746)

You are supporting them, so make it happen.

Yes, bad queries can run amuck, which is why you give them access to a slaved reporting instance of the DB.

Your tables are not precious, and they're not even yours, they are your customers. Let them run their queries on the reporting database, never the production DB.

Regards,

Offer them what they want (1)

ZeroConcept (196261) | more than 5 years ago | (#23436748)

Make a replica of the database available for this type of queries, you can argue that uncontrolled queries can impact your operations and that an isolated environment is required.

Send them a Statement of Work with this costing scenario as your response.

Don't do it! (0)

Anonymous Coward | more than 5 years ago | (#23436752)

Giving the wrong people access to your database leads to unfortunate questions such as:

"Did you intend to remove/change that data?" (which is never taken well) or "Why is the program/site/etc. no longer working?" and my personal favourite "Why am I still working here?"

At the very least, CYA and say no. If your boss overrides you, then well, you might want to preemptively ask question #3.

Before you give them access (2, Funny)

netsavior (627338) | more than 5 years ago | (#23436758)

Be sure to refactor the tablenames. The customer will catch-on when the "Moron" flag in your users table is set to 1 for their login.

Why not give them access? (1)

vux984 (928602) | more than 5 years ago | (#23436766)

What do they really want it for? What sort of adhoc queries do they imagine they need to run?

Odds are they only need access to a couple of tables... I'd consider creating a couple copies of those tables for them, and set it to update daily from the 'real' ones (with just their data of course), and then give them read only access to that. They are happy, and you are happy, hell if its small enough / simple enough, you could trhow up a mySQL or postgresql server somewhere to host 'their' data for them, and they won't even be able to touch your real server.

Not saying this is a good idea or even feasible... but it may be possible to satisfy their request without opening up your database to them.

Don't do it (4, Insightful)

dstates (629350) | more than 5 years ago | (#23436768)

Agree, just say NO. If they absolutely insist, replicate the tables that they need to see to a second server.

BTW have they offered to pay for all of the consulting time that they are going to request in understanding your schema and formulating their queries? Has management planned on the increase in personnel that your team is going to need to respond to these requests?

Finally, if you expose the schema to outside users, you are effectively making this your API. If you want to change your schema in the future, you are going to be breaking all of the legacy queries that you customers have written.

This is a legitamate request (0)

Anonymous Coward | more than 5 years ago | (#23436778)

... and the proper setup can both satisfy the customer, and keep you happy. It does require work though, as well as investment. Create a datamart. Put it on separate hardware (depending on how much you want to spend, and since you use oracle, you have the option of implementing redundancy). If all the data within the given instance is specific to this one customer, then replication is an option. Otherwise, you'll probably have to create an ETL process (many platforms/apps available for this), unless their data is segragated to a specific set of tables. Create a read-only account on the datamart, punch a hole in your firewall for the specific IPs they'll be accessing it with (depending on how far you want to go, and how sensitive the data is, there are more secure/portable options here, such as VPN).

I realize this is a lot of effort/$$$ to get done, not sure if you can convince the customer to offset, but it's what I would propose. Good luck.

Unless its a question of privacy, just give it to (0)

Anonymous Coward | more than 5 years ago | (#23436780)

I'm not quite sure what you mean by customer, but it shouldn't make too much difference. If there is data they don't need, the best bet is usually confidentiality.

"Unfortunately, due to confidential information, we cannot give you full access to the database"

Your role is probably to serve the customers, so if it's not a privacy concern I doubt there's much you can do (depending on your organization's structure). You can however say "We cannot provide technical support for ad hoc queries, nor can we ensure quality data. If you have any errors/trouble, you will have to submit a full request per current policies". For easy stuff, this will cut down on your workload, while still covering you and your concerns.

SOAP? (-1)

Anonymous Coward | more than 5 years ago | (#23436782)

Use a SOAP interface to expose some predetermined
queries. This will give them access without them potentially killing your database.

Pre-process all data requests. (2, Interesting)

arthurpaliden (939626) | more than 5 years ago | (#23436790)

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 (4, Interesting)

sirgoran (221190) | more than 5 years ago | (#23436796)

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

Microsoft Reporting Services (1)

wzinc (612701) | more than 5 years ago | (#23436798)

Microsoft Reporting Services' Report Designer Ad-Hoc report tool will allow them the access they need, without allowing them to write to the database. Also, you design the model with an Oracle back-end in visual studio, allowing you to have precise control over how they see the database.

http://www.microsoft.com/sql/technologies/reporting/default.mspx [microsoft.com]

http://visualstudiomagazine.com/features/article.aspx?editorialsid=1300 [visualstudiomagazine.com]

Your precious tables? (2, Insightful)

ctdownunder (816383) | more than 5 years ago | (#23436804)

Sounds like the data actually belongs to your customer.

And apart from the replication to another server as mentioned, it sounds like you are being a tad childish. For example: "This is my ball, and I'm going home with it..."

Try user profiles (1)

blueeyedmick (844023) | more than 5 years ago | (#23436806)

Oracle provides user profiles to control virtually everything the user can do within the database, including CPU time and connect time. With it, you should be able to closely control what these ad-hoc queries can do, and avoid problems.

Sheesh, have a reason, at least (5, Insightful)

Reality Master 101 (179095) | more than 5 years ago | (#23436818)

"I don't like them pawing through my database" makes me think that you're embarrassed by the database structure, and don't want people to see how screwed up it is. If that's the reason, then maybe it's time to fix things.

If it's just some weird possessiveness thing, then get over it. It's not your data. It belongs to your company. It's their servers, their programs and their data. If they want to give access, it's their decision, not yours.

Otherwise, a good reason not to allow direct access is performance. Amateurs doing queries against the "real" database can kill the server if they're not doing it correctly. My recommendation is to provision an entirely separate database server with a regularly-updated version of the data (perhaps even a "fixed" version if my first point is in play) and let them go wild on that.

Been there, doing that and more... (1)

Vrallis (33290) | more than 5 years ago | (#23436830)

My company provides SQL-backed software, and we have gone through similar problems. Normally we support Crystal Reports and have someone give them basic training in it. If they cause a problem, we'll cut the access, and they know it.

Now the truly scary part is when they want WRITE access to their database. They are already restricted so they only have access to their own data, so confidentiality of other customers' data isn't an issue. However, they can massively destroy their database under bad circumstances. In one case a customer did a wide-open update to a very large table--they were fortunate that it filled the transaction log and it was rolled back, otherwise their only recovery would have been a database restore. Even then the rollback took hours...

Just say yes (2, Funny)

Anonymous Coward | more than 5 years ago | (#23436832)

What's the worst that could happen if you issue them full read access? I work at a major isp, and I just gave a customer full read access to our client database server (which I am typing this from). As you can see, everything is operating perfe

Don't be a jerk (1, Insightful)

Just Some Guy (3352) | more than 5 years ago | (#23436834)

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?

Let me translate that into non-Oracle DBA terms:

"Whine! Whine! Whine! I hold the key! Me! This is magic! Whine!"

Another translation into Customerese:

"It's my data AND I WANT TO SEE IT, NOW!"

Seriously, what non-ego-driven reason do you possibly have from not allowing your customers to access their property, aka "their data"?

build views (0)

Anonymous Coward | more than 5 years ago | (#23436842)

that limit access to what is visible in the base tables according

API (0)

Anonymous Coward | more than 5 years ago | (#23436844)

Let them know that you can't support their scripts, and if you need to change something internal to the database, it may break what they've put together without your knowledge.

If they're okay with that, then I second the notion of giving them a mirror they can play with - they *are* your customers, after all. Don't promise them anything you can't promise them, but do give them what they want.

If it's their data... (1)

Talsan (515546) | more than 5 years ago | (#23436878)

If it's their data, then why not give them what they want?

If there's data for other customers in the same DB, you could always create a set of views and give them access to them.

In the end, it's read only access that they're looking for. As long as there's an agreement whereby they accept liability if unauthorized access is made using the account you provide, you should be covered.

Not to state the obvious, but... (3, Insightful)

Mike1024 (184871) | more than 5 years ago | (#23436880)

However they have now asked for direct read-only access to our Oracle database, [...] 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?
Not to state the obvious, but perhaps your justification for refusing access could be based on your reasons for refusing access?

If the only reason to refuse them access is that you "don't like the idea", you should come up with a proper reason you feel that way, and if you can't, you should change your opinion - or risk gaining a reputation as an arrogant, arbitrary obstructionist.

The customer can have anything... (2, Insightful)

borkus (179118) | more than 5 years ago | (#23436882)

...that they're willing to pay for.

Pretty clearly, running ad-hoc decision support queries against a transactional database is going to add an undetermined amount of load on that system. So your customer has a few options -

1. Upgrade your systems to support more load. Obviously, they want to still do their data processing in addition to any queries. If they're willing to cover the costs of the upgrade to insure the current level of service, then there shouldn't be a problem.

2. If the data doesn't have to be real time (within a few seconds), you should be able to replicate the data on a separate box for ad-hoc queries while active processing is done on the main database. Again, they need to foot the bill for this replicated server, but it may not need to be as beefy as the production box (depends a great deal on your scale/size).

3. Find a 3rd party to host the data for the customer and have the customer pay the 3rd party directly. Obviously, there may be some development and support cost of maintaining the data feed, but that way the customer understands the actual cost of that capability.

Now, I don't know the competitive and political environment that you're in. Are there competitors that may have a similar product to yours that allow live queries? Sometimes requests like this are simply to provde justification for a switch.

Read Only (1)

AlricTheMad (463234) | more than 5 years ago | (#23436892)

If no is not a sufficient answer and you can not come up with a legitimate business case the a replicated DB would be my choice.
Possible business case reasons:
No way to prevent access to other customers info without significant development time and expense
Server may not be able to handle the expanded load
Not enough licenses for the server

If you have to
Charge for creating the replicated data
charge for development time to provide access
charge for service of maintaining replicated data
write read only views to the data restricting what they actually have access to.

Create a view! (0)

Anonymous Coward | more than 5 years ago | (#23436894)

You could create one or more views that restrict what the user can see. That way you don't have to reveal the structure of the tables.

Create a Copy of the Production DB (0)

Anonymous Coward | more than 5 years ago | (#23436900)

I have been in the position of your users and can relate to their situation, but I am also sensitive to yours.

As others have said, the best option is to create a copy of your production DB and put it on a different server and refresh it weekly or every couple of days (e.g. Sunday and Wednesday). The copy doesn't have to be the entire DB, just a subset. That way, even if they do something stupid (e.g. bad join) they won't crash production.

Higher Authority (0)

Anonymous Coward | more than 5 years ago | (#23436906)

Always try to result to a higher authority like 91 Degrees said and say that you aren't authorized to provide that access. No need to lie about legal stuff though. The initiated will look that up and call you out. Not a good scenario.

The customer needs to cover the cost (2, Insightful)

93 Escort Wagon (326346) | more than 5 years ago | (#23436908)

Explain to the higher-ups, in detail, how this customer request can (and will) impact your other customers. Then tell them the solution is to replicate the database onto another server, which will be the one the customer will be given access to (as others have said). But make sure the customer foots the bill for purchasing and running that new server.

Just make a separate DB for them (0)

Anonymous Coward | more than 5 years ago | (#23436926)

And let them break that, and pay to maintain it as well.

SOX and SAS-70 (1)

slapyslapslap (995769) | more than 5 years ago | (#23436928)

Just make up a bunch of BS about SOX and SAS-70 compliance. Tell them your hands are tied from government regulations. It works every time!

Why not? (1)

bolek_b (246528) | more than 5 years ago | (#23436934)

As long as they ask for a strictly R/O access, why not let them have it? If I hear "my precious tables", I immediately have to think "unprofessional administrator". Do you have to worry about sensitive contents or are you ashamed of the mess, which most production databases converge into?

Easy, straightforward solution (with some implied assumptions, btw): I would create a set of views with SELECT privileges granted to a new role, let's say ADVANCED_CUSTOMER. Then create a new db user, grant him the role and there he goes.

More advanced solution: I would set up a new database, into which the original data would be replicated. Possibly with some time lag, so the advantage of fresh data remains for you.

Anyway, as a customer, I would just state what I want ("this and that set of data"), and the provider's job would be to make it happen. Preferably in some interface neutral way, e.g. SOAP.

All this is is a basic Oracle question (1, Informative)

Anonymous Coward | more than 5 years ago | (#23436936)

Use Oracle Data Guard to replicate the database and let them query on the rerplicated database.

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.

Performance is the only valid reason (0)

Anonymous Coward | more than 5 years ago | (#23436938)

If you're concerned about them writing bad queries and eating up server resources because of it, that is a valid reason. They cannot be reasonably expected to know how to write good SQL. However, that's the only good reason you can give. They are the customer, it's their data, and it's reasonable for them to want access to it.

This is a no-win situation unless you can come up with a replication of the data they can play with as they desire. If you provide the access, then they may wind up writing bad, slow-running SQL. If you don't provide access, then you will wind up getting stuck responding to their every query requests, which presumably has financial implications.

Bottom line: the scope of your relationship is expanding, and perhaps you need a discussion with them about the economic impact (i.e., "this is going to cost you").

Make a list of bad things that can happen (3, Informative)

arnie_apesacrappin (200185) | more than 5 years ago | (#23436962)

If you really want to make a case to management, write out all the worst case scenarios for your management (I know that there should only be one WORST case scenario, but work with me here). If you can list the things that can go wrong, you might be able to help. I once worked in an environment where a developer wrote a query that locked a database for four hours until we killed it. One of the DBAs was able to re-write his query so that it completed in under 10 seconds. His access was then revoked.
Tell you boss how someone with read only access could affect the rest of the users, and you should be on the right track.

use views (1)

bendytendril (1281160) | more than 5 years ago | (#23436964)

I had a similar situation. In my case, I was more concerned they'd see the schema. I created views of denormalized data that they'd be interested in accessing, then granted rights to only these views. They were never the wiser.

Just give it to them (1)

sricetx (806767) | more than 5 years ago | (#23436966)

They are the customer. They paid for the database. Give them full access to it. And when they muck it up, charge them a very high rate to fix things.

Create some views (1)

sugarmotor (621907) | more than 5 years ago | (#23436970)

Just create some views on a copy of the database. They wouldn't know if the view is incomplete.

Let them only read the views created for them.

In general, isn't it funny how views are available for just this reason, and invcorporated into all the big databases, and yet, when it comes time to use the feature, everybody gets nervous.

Similar to using VM appliances instead of adding another package to a machine, another process or service, "just to be safe".

Stephan

I'm a DBA (2, Insightful)

misterjava66 (1265146) | more than 5 years ago | (#23436980)

I'm a DBA
You should not report off of a production Database.
Obviously, you will need to have a feed that looks like a report to the reporting DB but this is NOT a report, it is a feed.
Reporting will harm performance and reliability of the performance of the production db.
Also, reporting off of a simple copy of the production db is generally undesirable, generally you want to have a warehouse/flattenning/sumarization of some kind for reporting.
That's the stock reasons to keep this stuff seperated, its done me well for 20 years.
:-)

Give them acces, charge them to fix it. (1)

Kintanon (65528) | more than 5 years ago | (#23436986)

If they want access, give it to them. Let them know up front that any damage done to the database because of their queries will be billed at 4X the normal rate, or some other suitably large number.

Everyone's happy.

One approach to meet this requirement... (1)

managerialslime (739286) | more than 5 years ago | (#23436992)

Ok, lets assume for just a minute that letting some kind of query capability is appropriate for your customer base and that you already know enough about Oracle views and roles and general security that your concern is improper SQL syntax, run-away queries, etc.


Instead of giving them open access to feed SQL to the database, see if they would be open to using a PHP (or other web) screen where they could check off specified field names, add conditions (like, , and a value, etc.).

You could then construct well-behaved SQL statements and kill requests that your code defines as unreasonable.

If an interactive screen is not acceptable, then consider a facility where they feed you a SQL string and then you use PERL (or other program of choice) to extract simple queries that you can then re-feed to Oracle. Any sufficiently complex or burdensome query could trigger emails to your DBA who could then gently provide guidance to your customer.

Build a web service (0)

Anonymous Coward | more than 5 years ago | (#23436994)

Why don't you ask what sort of queries they want to run and instead build a web service?

They won't have access to the database directly.

Yeah (1)

Colin Smith (2679) | more than 5 years ago | (#23436996)

Tell them to fuck off and get another supplier. I'm sure they aren't worthy of your precious tables.

For crying out loud, what kind of DBA is unable to create views and restrict access to tables? I mean, come on, Oracle can restrict access to specific rows if you need to, that's both it's beauty and hideous ugliness.

 

mo3 0p (-1, Flamebait)

Anonymous Coward | more than 5 years ago | (#23437002)

Briiliant plan not going home
Load More Comments
Slashdot Account

Need an Account?

Forgot your password?

Don't worry, we never post anything without your permission.

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>
Sign up for Slashdot Newsletters
Create a Slashdot Account

Loading...