Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> logon trigger cannot prevent DBA account from logging in databa se
Hi all,
I want to prevent certain OS users from logging in database using certain DB accounts (say: ORAUSR1). The following code works only if ORAUSR1 is NOT a DBA account. Can anybody please help?
We have to grant the DBA role to the schema owner of our ERP system , but the password for this account is well-known and changing it is not advisable as many applications are using this account with hard-code the password.
CREATE OR REPLACE TRIGGER logonauditing AFTER LOGON ON database DECLARE
machinename VARCHAR2(64); osuserid VARCHAR2(30); v_sid NUMBER(10); v_serial NUMBER(10); v_killsession varchar2(500); CURSOR c1 IS SELECT sid, serial#, osuser, machine FROM v$session WHERE audsid = userenv('sessionid'); BEGIN OPEN c1; FETCH c1 INTO v_sid, v_serial, osuserid, machinename; if upper(user) in ('ORAUSER1','ORAUSER2') and osuserid not in ('OSUSER1','OSUSER2') then v_killsession := 'alter system kill session ' ||''''|| v_sid ||','|| v_serial ||''''; execute immediate v_killsession; -- same if I try "raise_application_error( ....)" END IF;
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 04 2006 - 11:34:03 CDT