Re: Security Problem

Insoo Kang wrote:
>
> We have an application that allows you to delete and update records in
> a table. The problem is that a same user can login using sqlplus and
> delete the entire records in a table. In order to prevent this, I
> came up with the following scheme: revoke table access privilege if
> a user is connecting via sqlplus or any other applications.
>
> I can write a trigger that sends a message to a pro*c application that
> revokes table access privilege, but the problem is that v$session is a
> view not a table so I can't write a trigger based on v$session view.
> Does anybody have a suggestion how I can accomplish this whether it be
> through a trigger or some other means?
>
> --Insoo

Why not have two roles for the user, say, XXX_POWER_USER and XXX_SELECT_ONLY_USER. The user has both roles granted to them but only has the "SELECT_ONLY" enabled by default. So when they come in thru SQLPlus (or any other tool) they can only select.

When they log on to the application, it just enables the role "POWER_USER" and off they go...Once they log out (ie disconnect) they will automatically resume just the "SELECT_ONLY" ability...

The only real hole in this is if your users know how to issue their own "set role" commands .... which ours don't...

Cheers

-- 
Connor McDonald
Systems Administrator-Unix/Oracle
BHP Iron Ore
"You're not drunk if you can lie on the floor without holding on"
            - Dean Martin