Re: Oracle admin

On Wed, 19 Nov 1997 21:02:10 -0500, "Joseph S. Testa" <jtesta_at_scioto.net> wrote:

>
>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).
>
 

This prevents only accidental errors. It does not prevent malicious damage.

Could you not just grant to a non-default role?

Even with the method you propose, you should always be aware that you cannot prevent users who have been granted access to tables from changing data in those tables using whatever tool they wish.

This has two implications. First, the database must guarantee the integrity of the data in it - you cannot depend on client level validation checking. Second, you should consider the use of audit triggers to ensure you know which userid changed what. Received on Thu Nov 20 1997 - 00:00:00 CST