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