Home » RDBMS Server » Security » logon trigger cannot prevent DBA account from logging in database
logon trigger cannot prevent DBA account from logging in database [message #165966] Mon, 03 April 2006 14:31 Go to next message
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;
Re: logon trigger cannot prevent DBA account from logging in database [message #166044 is a reply to message #165966] Tue, 04 April 2006 04:20 Go to previous messageGo to next message
Maaher
Messages: 7064
Registered: December 2001
Senior Member
No, you cannot prevent DBA's from connecting to their database and I don't see the point of preventing that. Just revoke the DBA from the users you don't want to be a DBA.

MHE
Re: logon trigger cannot prevent DBA account from logging in database [message #166106 is a reply to message #166044] Tue, 04 April 2006 09:40 Go to previous messageGo to next message
Lijie_Tu
Messages: 6
Registered: April 2006
Junior Member
I'm not talking about sys or system accounts. The database owner of our ERP system is granted a DBA role,its password is well known and hard-coded in many applications. Changing password is not advisable, so an alternative is to allow only certain OS users to use it.


Re: logon trigger cannot prevent DBA account from logging in database [message #166116 is a reply to message #166106] Tue, 04 April 2006 10:10 Go to previous message
Maaher
Messages: 7064
Registered: December 2001
Senior Member
Let's continue in your other thread.

http://www.orafaq.com/forum/fa/448/0/

Topic Closed

MHE
Previous Topic: forget my sys password
Next Topic: Trigger to prevent any user to login
Goto Forum:
  


Current Time: Sat Dec 04 02:25:45 CST 2021