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

Comments

Reporting Database

[WreckDiver]

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

[jedidiah]

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

[Musrum]

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

Re:Reporting Database

[hackstraw]

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

You just violated the MS-SQL license.

Re:Reporting Database

[Anonymous Coward]

That kind of locking issues does not happen in Oracle due to its multi-version concurrency control strategy and locking implementation. MS SQL place locks on every row a SQL statement touches for the duration of that particular statement in order to prevent changes to those rows while the statement is running. Oracle avoid this by making "backups" (rollback/undo) of any changed rows and reads from those backups instead if needed. Locks are still used but they don't block writers. Also, MS SQL uses a "lock manager" process which keeps track of every lock in the entire database. Statements and transactions touching many rows will therfore cause the lock manager to allocate a lot of memory in order to keep track of all locks. Then it uses lock escalation techniques, i.e. changing the granularity of the locks so fewer locks are used at the expense of larger locked areas, to keep the memory usage down. Oracle does not have a lock manager (except when dealing with distributed transactions and two phase commits (2PC) across multiple databases. Oracle store the lock information in the header of the rows themselves.

Why?

[iElucidate]

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?

[ZeroConcept]

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

You seem to be the problem

[etymxris]

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

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

Partial data replication

[netsavior]

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.

Use a read only replica

[Giant Electronic Bra]

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.

Suggestion

[ggvaidya]

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

Becasue you are stupid is a valid reason

[geekoid]

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

[sirket]

Did you even bother to read the original post? The person said their client wanted read-only access. The problem isn't the client messing up data- it's the client bringing the server to its knees with an abysmally inefficient query. Oracle has some features to limit the damage a user could do- but the only truly safe option is a read-only replica.

The person also wrote that the customer did not want 24 hour old data (you really didn't bother to read the post did you?) so your cube is a useless idea.

If you think it's a good idea to give clients direct access to your production database then please send me over your resume so I can make sure it goes on our "Never, ever hire this person" board.

You have some other problems...

[teknopurge]

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,

Re:A simple suggestion

[TheRealMindChild]

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.

Sheesh, have a reason, at least

[Reality Master 101]

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

ummm....

[Anonymous Coward]

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

Re:A simple suggestion

[x00101010x]

You could scare management by explaining to them that allowing direct access will disclose your database schema to the customer which will allow them to reverse engineer some of your service's design and possibly allow them to make their own (eliminating their need to continue working with your company).

What's the customer's name?

[tjstork]

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.

Hey, I have a consulting firm that would be willing to work with the client to ensure they have that database access. :-)

What we could do is give them the query access via their own public synonym space, and build it into our SLA that we are not responsible for downtime due to their querying. We would also bundle some support costs into the agreement.

Re:What's the customer's name?

[2short]

Seriously. If our sales guys had the clients name, we'd eat his lunch.

He seems to think 2-8 GB is a big database. If the customer wants some custom report, he thinks emailing someone who writes custom SQL and sends them an excel spreadsheet the next day is a process that "ticks along happily". If your customer is asking for direct SQL access so they can bypass you and do stuff themselves, your process is not ticking along happily.

Re:A simple suggestion

[CastrTroy]

Well, you could always ship the information to another machine, and have them access it from there. You could easily do it so that it's only a couple minutes behind real time. If they really think they need the information that badly, they will pay for the cost of the extra machine. If the really can't wait for the CSV files to come in at night, then it must be pretty important for them to have the data right now, or it must be really difficult for them to manager their own copy based on the CSV files. Shipping it to another machine would allow them to do stupid queries without compromising the performance of the main system.

Re:A simple suggestion

[BitZtream]

With Oracle, all you would demo is that you aren't a very good oracle DBA.

Oracle has plenty of security and control mechanisms to ensure that a user can't starve the system of resources if you know how to use them.

Say Yes.

[Angostura]

"Absolutely, we would be delighted to provide you with this high-value ad-hoc access system. In order to protect our valuable operational infrastructure this will require the installation of a separate datawarehouse. Provisioning this system will cost $X, the monthly charge for maintenance of the system, the population of the datawarehouse and the provision of secure access will cost $Y"

The advantage of this approach:

1. It makes you look helpful and willing to accomodate your customers
2. It makes it clear what some of the issues are
3. If you set the values of $X and $Y at the correct values you can generate significant additional revenue for your business
4. If you set the values of $X and $Y just a little higher, the answer equates to "No".

Win-Win.

Re:A simple suggestion

[tinkerghost]

too simple,
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

[an.echte.trilingue]

How you phrase it is everything. "No" will never stick, especially if the customer can easily migrate elsewhere. As a computer guy/dept, management/the customer sees you as somebody who just makes the mysterious boxes do what they want, no matter how asinine you know that request to be. Once you start throwing barriers between the manager/customer and what he thinks he wants, you will soon be replaced by somebody who who doesn't.

The key is to try to steer the customer to another direction. Often they want silly things like this because they don't know the alternatives. Engage the customer and find out what they are doing, and toss out a better solution. In the end, you will both be happier.

If you do end up having to give them RO access, I would be sure to write some method into their user interface that restricts wildcards. You don't want somebody doing the oracle equivalent of
echo "select * from huge_table" | cat > querry.sql; mysql -u user -p huge_db < querry.sql | grep value

Sounds silly but I saw a colleague write a script that did something about like that.

Re:A simple suggestion

[Em Adespoton]

This is definitely something that should be stressed. What I provide my customers is a front end with standard queries, PLUS the ability to trigger a backup (either daily snapshot or cumulative snapshot... which takes some time to transfer; our databases are generally around 2-8GB of data) of the database that they can then access and manipulate to their heart's content. At no point do they gain access to live data, but they can take snapshots whenever they want.

I have been toying with the idea of a shadow database that they can have live access to but which is only updated, never queried, by the main system. This is another possibility for your customer, and provides fresh income for you and your team as you develop this "new product".

Re:A simple suggestion

[phallstrom]

Additionally, your customer will now begin to rely on your schema and when you decide to change it they will be upset. In OO terms you just gave them access to all your private methods :(

This happened to me. No choice in the matter. 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.

Re:A simple suggestion

[cayenne8]

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

I don't see what the problem is....just set up a role with select privs. only on that customer's table(s). If you have all the customers' data mixed in the same tables, then create a view on their data and grant select only on that. Or...maybe look into Oracle's granular level permissions you can set up?

Re:A simple suggestion

[PRMan]

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

[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

[lakeland]

Of course Oracle has permissions. That area of Oracle is substantially more sophisticated than MySQL's, not that surprisingly really - large enterprise access is bread and butter for oracle. Oracle's permissions are so fine grained that most people haven't heard of half of them... has a very nice permission set called 'roles' which allows you to carefully work out a set of common permissions and easily grant them all to a bunch of users. One area Oracle is missing for some reason is grant permissions at a schema level (which MySQL would call a database) rather than the object level - it is something that comes up a lot in practice.

However, when you start talking about load issues, that's where things that are feasible in MySQL just aren't in Oracle. Presuming this DBA is running Oracle EE, he'll be paying $40k/CPU (or technically, $40k/2 cores). That means for him to replicate onto another box for load issues will cost him an extra $40k just for a simple dual-core machine. Or $45k say, hardware isn't completely free.

If he wants that load balancing to happen automatically rather than telling clients which machine to log into, then Oracle has a much better product than MySQL's cluster. Unfortunately, MySQL's cluster is virtually free, while Oracle RAC is over $500k. At the same price, I would have chosen RAC over Cluster, but with that kind of price difference...

So, I think it basically comes down to load issues. Scaling up an Oracle install is unaffordable without a great business case and expecting random clients to not bring the server to its needs (granting them unlimited CPU) won't work - especially on a server which no doubt has limited cores - while not granting unlimited CPU will lead to all sorts of confused issues logged about queries failing.

There are plenty of solutions. Replicate onto Postgres (it supports Oracle's syntax so would be a better choice then MySQL). Create some nice star schemas and export via Discover or similar, replicate onto a machine that the client supplies and pays for licencing of, etc. Ditching Oracle EE and going SE might be enough too, the EE features are nice but not when they prevent business growth. Writing a custom SQL Server integration and syncing daily is probably only a few hours work and good enough for a DB up to about a TB if daily sync is fresh enough. That's just off the top of my head, I'm sure there are more options.

Re:A simple suggestion

[sumdumass]

There's too much of an instinct in IT to think of the systems as "yours", and protect your little kingdom. It's a computer, not your children.

The problem is that a lot of times, the buck stops with you and your reputation is on the line when someone else borks something.

I had a system that was breaking about once a week and I was getting some real heat from the managment to keep it running. It was usually on a Saturday when it failed and sometimes on a Thursday night. I eventually started suspecting that someone was messing with it because there were no logs of anything and none of the usual "something isn't acting right" when it would bork. I even started replacing things that I knew couldn't be the cause of the problems but it was about the only thing I haven't done yet. I wiped and reloaded the system 3 times, each time holding off on all the updates in case one of them was causing the problems. After about a month, I changed the passwords and took an IP camera in and set it by the terminal. Turns out that one of the members of the cleaning crew was on site alone during those nights. He would get the password from the sticky note on the wall of the management's office (why it was there, I will never know) and run a counterstrike/half life server from the server. He would then turn all the services off that he thought nobody needed, half ass his work then pull a laptop out and spend the next 4-6 paid hours playing games over it.

After this came to light, I found out that another client I had been attempting to get a contract with talked to the managment of this place and got a bad review specifically because of this server having repeated issues that I couldn't fix. After the real problem was known, the client called me up and gave me the contract I was looking for and specifically mentioned that he was worried because of all the trouble I was having with the servers at the other place.

It isn't just that one time either. I have sites that we totally lock down and reimage the profiles each night so any unapproved changes to the systems is removed at the end of each shift. I find that I am having to look for reasons to show up to those sites and make sure everything it working right. I also have sites where power users are present without any restrictions and I am constantly being called in to fix something. In fact, If I can keep people away from IE and outlook (express), convince them to not install anything not directly related to their work, make sure an up to date anti virus scanner is present, I don't have too many problems outside of hardware failures and stuff outside our control.

We protect the system like they are our children because our reputations are on the line. In many situations, our reputation determines out pay or potential pay. It stops us from doing productive things when we have to fix over people's mess up's that they attempt to hide so they don't look bad. Even if you can always blame it on someone else, you still end up looking bad because your always blaming someone else.