Slashdot Mirror
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?"
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.
-1 Uncomfortable Truth
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.
It is not difficult to make this impossible- oracle allows for limiting resource consumption by user among other things.
It's hard to believe that's how Micronians are made. Why don't we see it right now by having you both kiss one another?
...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.
A Pirate and a Puritan look the same on a balance sheet.
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)?
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.
Still, with a plan, you only get the best you can imagine. I'd always hoped for something better than that. -CP
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.
Anthropic principle: We see the universe the way it is because if it were different we would not be here to see it.
This is seriously the best option. Just write a detailed quote for set up of the server, OS installation, DB, replication, licenses, SSL tunnels required, and recurring rack space allocation and power costs that will be required.
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.
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.
You've already got a cross-join (probably by mistake, given that you even bothered to mention left outer as useful). I think that'd be illustration enough :)
(rot13) rpbzbab@tznvy.pbz
If the poster is really anal about having access to 'the' data but not necessarily worried about schema browsing, etc.. your suggestion is really the way to go.
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.
Bye!
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.
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.
Peter predicted that you would "deliberately forget" creation 2000 years ago...