|
|
Re: Trigger to prevent any user to login [message #161390 is a reply to message #161172] |
Fri, 03 March 2006 12:05 |
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 #166105 is a reply to message #166035] |
Tue, 04 April 2006 09:33 |
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;
|
|
|
|
|
|
|
|
|