doing things in truly haphazard fashion, I have had dealings with a system that has a logon trigger, and it's caused no end of trouble, confusion and unnecessary work. You are quite right to be wary of them, and avoidance is recommended (in my case as the result of bitter experience).
I'm relieved to hear that you have granted users restricted privileges and are achieving most of what you want that way.
However, your response seems a little contradictory. You state
jdenni said:
the users are now free to access/query those same objects we previously provided access to for Discoverer purposes -- all without any constraints.
and yet you claim
jdenni said:
we're already providing users with the minimum possible privlieges
These statements can't be simultaneously true. If I have understood correctly, you grant a role with restricted privileges to users of your discoverer tool, and it limits the carnage they can cause, by dint of 'under the cover'
jdenni said:
configured joins and suppressed fields
. Therefore this application is providing another level of protection to prevent "the query from hell" running and restricting its impact via throttling. Your best solution is to create yet another role, which provides the minimum privileges and permissions, by views or VPD (
Virtual
Private
Database), which would seem an excellent way of achieving what you want. Grant users that role only, and once you've checked that it causes no problems, migrate the discoverer users to that role too. Then remove the too-liberal role.
The notion that you can control applications and/or executables is risible. How do you programatically detect the application? By inspecting the value of PROGRAM in V$SESSION perhaps? Whats to stop a user creating an executable of their own, but causing it to spoof its name to one that you allow in? Take that last as rhetorical. You don't control applications because you can't. If connection details are known, aliens from the planet Zork can connect to your system, with an application written in Zorkish. Ditto a wannabee hacker in China/Russia etc.
If however, said connection details lead to a restricted account, both the aliens and chinese can do no harm.
If applications
could be controlled, there would be no such thing as a hacker.
Dealing with your issue about runaway queries consuming too much resource, Oracle does provide help in this area, by means of profiles.
is a good intro to the subject, but with boring predictability, let me say "read the oracle documentation".
I suggest that you create the fully restricted roles, profiles and/or VPD ASAP. What would happen if management decided to use a different tool, which provided none of the aforementioned protections? The db must be capable of defending itself at all times, that way you can sleep easy.
Regards
T