| logon trigger cannot prevent DBA account from logging in database [message #165966] |
Mon, 03 April 2006 14:31  |
Lijie_Tu
Messages: 6
Registered: April 2006
|
Junior Member |
|
|
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?
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;
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
