logon trigger cannot prevent DBA account from logging in databa se
- From: TU Lijie <Lijie.Tu@xxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 4 Apr 2006 12:34:03 -0400
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;
END;
Other related posts:
