Re: Oracle Applications DBA question

andyho99_at_yahoo.com (Andrew) wrote in message news:<8882aa3c.0409031549.3dd1a9d_at_posting.google.com>...
> Is there any guidelines whether end users allowed to use sql*plus or
> similar tools to query database. We use Oracle Applicatons 11i. End
> users could use Discoverer to create reports and it use same security
> as OA. If end users want to use sql*plus, we need to set up roles or
> something like that to limit their access. I'm just curious what other
> shop do. Should we allow users to use sql*plus? Thanks.

This is a huge question at most apps shops, and I suppose others as well.

We have the problem of multiple users coming at the app instance with Golden, Toad, you name it, with a RO username, writing God-knows what kind of queries.

We are trying now to pull this back away from the users and give them something else that will satisfy their insatiable need to feel more in control. Discoverer *might* be a tool they can use, but then you end up needing purchase/write queries that won't kill your system - and I mean kill. We have had two events in the last 8 months where the system was brought to it's knees by rougue queries. Not a hard thing to do in a development system but lethal in production.

The flip side is how do you know that the queries they are writing are correct? Some accountant writes a query and walks into a managers office - "Look we made a 10 gazillion dollar profit last week!!". Well, is he right? How do you know? Oracle apps has thousands of tables - you think the end users are going to understand that they have to query at least 6 tables - correctly - to see current A/P, depending upon setup? It might be more tables. And their is little referential integrity built into the app, so good luck with your joins. Better hope the TRM is correct.

One-off queries are a great way to make managers think the app is totally screwed up. If they can't balance the ad-hoc uncontrolled queries with internal reports they might just throw the system away. Think about that next time you're bored.

Trust is good, control is much better.

Greg Received on Wed Sep 08 2004 - 07:32:17 CDT