Home » RDBMS Server » Security » Trigger to prevent any user to login
Trigger to prevent any user to login [message #161172] Thu, 02 March 2006 06:56 Go to next message
orajamzs
Messages: 110
Registered: February 2006
Location: hyderabad
Senior Member
How to create a trigger on any user to prevent login on a particular time or day.
Re: Trigger to prevent any user to login [message #161173 is a reply to message #161172] Thu, 02 March 2006 06:59 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
Probably you are looking for a logon trigger on database.
In that case this article may help you:
http://www.unix.org.ua/orelly/oracle/guide8i/ch06_02.htm

--Girish
Re: Trigger to prevent any user to login [message #161390 is a reply to message #161172] Fri, 03 March 2006 12:05 Go to previous messageGo to next message
rkl1
Messages: 97
Registered: June 2005
Member
Try something like this. I wonder, you may not restrict a dba to prevent him from loggging. However you could play around to harass other users :

create or replace trigger trig_log_user
AFTER LOGON ON DATABASE
WHEN (USER ='HR')
declare
v_hr varchar2(20);
begin
select to_char(sysdate, 'HH24') into v_hr from dual;
--after 10AM,does not allow connection.
if v_hr >=10 then
raise_application_error (-100023, 'go home');
end if;
end;
/
Re: Trigger to prevent any user to login [message #165967 is a reply to message #161390] Mon, 03 April 2006 14:34 Go to previous messageGo to next message
Lijie_Tu
Messages: 6
Registered: April 2006
Junior Member
I've created something similar. But I want to prevent ANY users (including DBA users) from logging in. I'll really appreciate it if someone can find a workaround for this.
Re: Trigger to prevent any user to login [message #166035 is a reply to message #165967] Tue, 04 April 2006 03:01 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
What happens if something goes wrong with the db? Presumably you're going to let at least 1 person be able to log in 24/7. If not, you're as well 'pulling the plug'
Jim
Re: Trigger to prevent any user to login [message #166043 is a reply to message #166035] Tue, 04 April 2006 04:19 Go to previous messageGo to next message
Maaher
Messages: 7064
Registered: December 2001
Senior Member
Shut it down. That will prevent any user from connecting.

MHE
Re: Trigger to prevent any user to login [message #166045 is a reply to message #166043] Tue, 04 April 2006 04:23 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Time for public enemy:
"I shut it down, shut it down, shut it shut it down ...."
Smile Cool Laughing
Re: Trigger to prevent any user to login [message #166048 is a reply to message #166045] Tue, 04 April 2006 04:33 Go to previous messageGo to next message
Maaher
Messages: 7064
Registered: December 2001
Senior Member
Razz I know, we once had a DBA singing this song regularly. But seriously: if you don't want your database to be used at a given time, shut it down.

MHE
Re: Trigger to prevent any user to login [message #166050 is a reply to message #166048] Tue, 04 April 2006 04:38 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
No arguments from this side of the fence Smile

[Updated on: Tue, 04 April 2006 04:50]

Report message to a moderator

Re: Trigger to prevent any user to login [message #166105 is a reply to message #166035] Tue, 04 April 2006 09:33 Go to previous messageGo to next message
Lijie_Tu
Messages: 6
Registered: April 2006
Junior Member
Actually, the goal is to allow only certain OS users to use DBA accounts. The database owner of our ERP system is granted a DBA role, we only want certain users to use it. (its password is well known and hard-coded in many applications). Here's my code, it only works for non-DBA users:

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: Trigger to prevent any user to login [message #166113 is a reply to message #166105] Tue, 04 April 2006 10:08 Go to previous messageGo to next message
Maaher
Messages: 7064
Registered: December 2001
Senior Member
Lijie_Tu wrote on Tue, 04 April 2006 16:33

(its password is well known and hard-coded in many applications).
This is when i get my coat and walk out. A hard coded system password...*leaves building in disbelief*

MHE
Re: Trigger to prevent any user to login [message #166114 is a reply to message #166113] Tue, 04 April 2006 10:09 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
My own reaction was "Oh good grief"
Re: Trigger to prevent any user to login [message #166119 is a reply to message #166114] Tue, 04 April 2006 10:14 Go to previous messageGo to next message
Maaher
Messages: 7064
Registered: December 2001
Senior Member
This is not security, it is the opposite: what if one of your users one day DOES change the password?

MHE
Re: Trigger to prevent any user to login [message #166128 is a reply to message #166035] Tue, 04 April 2006 12:47 Go to previous messageGo to next message
Lijie_Tu
Messages: 6
Registered: April 2006
Junior Member
Well, in that case, Oracle should only prevent the logon trigger from killing sys/system session, while still allow the killing of other sessions.
Re: Trigger to prevent any user to login [message #166197 is a reply to message #166128] Wed, 05 April 2006 02:24 Go to previous messageGo to next message
Maaher
Messages: 7064
Registered: December 2001
Senior Member
SYS and SYSTEM are just names (albeit fixed by Oracle and unchangeable) but it is their SYSDBA role that makes them powerful.

Lijie_Tu 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.
Ok, get on the phone with Oracle and tell them they have to change their security features because you hard coded the DBA's password in your application Wink.

Why on earth does an application be granted the DBA role? Revoke it from the application. And a tip: I think you should look into OS authentication. The Oracle Database Administrator's Guide explains this feature quite well. I believe it is what you were looking for.

MHE
Re: Trigger to prevent any user to login [message #166741 is a reply to message #161173] Sat, 08 April 2006 00:42 Go to previous message
Mohannad
Messages: 47
Registered: January 2006
Location: palestine
Member

is it work in oracle9i the trigger which work on 8i
all the best
Previous Topic: logon trigger cannot prevent DBA account from logging in database
Next Topic: sys and system
Goto Forum:
  


Current Time: Mon Dec 06 20:38:35 CST 2021