Re: Oracle admin

Audun wrote:

> Can anyone give me some tips regarding user admin. The question is:
>
> How do you prevent a end-user using "direct-access"-tools
> (SQL-Plus...) from damaging tables to which they have privileges via
> user applications (build in PowerBuilder, VB ....)?
>
> If anyone could tell me about their solution to this area, I would be
> very grateful.
>

How about this(this assumes you have access to all the source).

1. using dbms_application.set_client_info to set some text string for each app.
2. in a trigger for each table, do a check against a security table that holds the legal values of the text string(trigger before insert,update,delete). if it matches, do nothing, if it is not a legal value, then raise_application_error(-20001,'Unregistered application, changes not authorized);
3. I implemented just that and it keeps, sqlplus, PB, Infomaker users from making dml changes to tables. no one needs select on the security table except the owner of the triggers for the table.

joe

--
Joseph S. Testa, Oracle Database Administrator, mailto:jtesta_at_scioto.net
(home)
Vice-President Ohio Oracle Users Group,
See the Oracle FAQ at http://www.orafaq.org
Try http://web.scioto.net/jtesta that is also the place to find the FREE,
drop column script
ICQ UIN:  2832230(&oracle, home).