Re: Oracle Applications DBA question

gforestieri9_at_yahoo.com (Greg Forestieri) wrote in message news:<6a8cdd95.0409080432.2e0ab0ac_at_posting.google.com>...
> 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.

Simply say, "If you want to write your own queries without going through our department, we can set you up with a nice datamart. It will cost you X dollars, including duplicating the production box, licensing and... In addition, if you want up-to-the-minute access, it will cost you Y more dollars for us to enhance the production box and set up replication."  

Then offer to set up little db's on linux PC's with extractions for what they want to play with, with whatever the tool-of-the-month is that you want on your resume.

Oh, and hire a business analyst to reconcile those ad-hoc reports. When it comes down to it, those users would rather have someone else write the queries anyways. For "free."

jg

--
@home.com is bogus.
Oops. http://msnbc.msn.com/id/5942268/