Re: select only user causing locks?

On 4/29/05, Mladen Gogala <gogala_at_sbcglobal.net> wrote:
> Oh my gawd! This reinforces my beliefs that guns should be kept locked in=
 a safe place!
> If you give a gun to a childish person, it will shoot itself in a foot, a=
t best. This

Indeed.

> First, you don't ever give end-lusers a tool that can issue ad-hoc querie=
s. You make darned

As soon as I'm golfing buddies with the CIO... (jeez, I should know better than to expect a serious answer on a Friday afternoon.)

Anyway, I finally found this in the docs: Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-02

The following table privileges authorize operations on a table. Any one of following object privileges allows the grantee to lock the table in any lock mode with the LOCK TABLE statement. [table with: DEBUG, DELETE, INSERT, REFERENCES, SELECT, UNDER, UPDATE]

How many people have taken the above information and realized the query account the project manager demands for, say, auditing purposes can then lock up the entire application? I've been scouring the 'net and haven't found any warnings to that affect.

This should be just an academic point since any power user sitting at a SQL prompt that issues a "select for update" or "lock table" command will be shot. Unfortunately the power users have moved on to Toad and it is pretty easy to unwittingly have Toad do a "select for update".

Given that a query (audit) account is a requirement and that I can't control what tool they will use, anyone have ideas on how to stop the locks?

Thanks,
Steven

--
http://www.freelists.org/webpage/oracle-l