Re: logon trigger cannot prevent DBA account from logging in data ba se

Couldn't agree more, but I've also inherited a similar situation; short-term solution was a logon trigger but not logon on database but logon on schema.
Something like this worked for us :

create or replace trigger sys.blablabla
 after
  logon
 on "ORAUSER1".schema
declare
  os_user varchar2(30);
begin
  select sys_context('USERENV','OS_USER') into os_user from dual;

  if upper(os_user) not in ('OSUSER1', 'OSUSER1') then     raise_application_error(-20001, 'blablabla');   end if;
end;

mvg/regards

Jo

                                                                           
             "Jared Still"                                                 
             <jkstill_at_gmail.co                                             
             m>                                                         To 
             Sent by:                  Lijie.Tu_at_comaupico.com              
             oracle-l-bounce_at_f                                          cc 
             reelists.org              "David Sharples"                    
                                       <davidsharples_at_gmail.com>,          
                                       oracle-l_at_freelists.org              
             05-04-06 02:34                                        Subject 
                                       Re: logon trigger cannot prevent    
                                       DBA account from logging in data ba 
             Please respond to         se                                  
             jkstill_at_gmail.com                                             
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           

Create a new role for the user, similar to the DBA role if that is what it requires.

Exclude the ADMINSTER DATABASE TRIGGER privilege from the role, revoke DBA from the user and grant the new role to the user.

Any user with the ADMINSTER DATABASE TRIGGER either directly or indirectly through a role cannot be prevented from logging in through the use of a trigger.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

On 4/4/06, TU Lijie <Lijie.Tu_at_comaupico.com> wrote:

      Well, in that case, Oracle should only prevent the logon trigger from
      killing sys/system session, while still allow the killing of other
      sessions.


      Anyway, logon trigger does not seem to get what I want, just
      wondering if there is a workaround to this.



      -----Original Message-----
      From: David Sharples [mailto:davidsharples_at_gmail.com]
      Sent: Tuesday, April 04, 2006 12:42 PM
      To: Lijie.Tu_at_comaupico.com
      Cc: oracle-l_at_freelists.org
      Subject: Re: logon trigger cannot prevent DBA account from logging in
      databa se





      you cant stop dba accounts from logging into the database.  The
      reason being is that if you wrote a login trigger that didnt work
      then no-one

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