create or replace trigger marca_logon
after logon on database
begin
insert into vigia
values
(User || ' entrou no systema' ||
to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
COMMIT;
END;
The first thing to do, in my point of view, it would be to put an exception on your trigger, to see if it does not shoot or, shoot and generate an error:
create or replace trigger marca_logon
after logon on database
begin
insert into vigia
values
(User || ' entrou no systema' ||
to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'Erro ao logar:'||sqlerrm);
END;
CREATE OR REPLACE TRIGGER sys.security_check
AFTER LOGON ON DATABASE
DECLARE
v_sid NUMBER;
v_isdba VARCHAR2(10);
v_program VARCHAR2(30);
v_osuser VARCHAR2(30);
v_username VARCHAR2(30);
BEGIN
select distinct sid
INTO v_sid
from sys.v_$mystat;
select program, osuser, username
INTO v_program, v_osuser, v_username
from sys.v_$session
where sid = v_sid;
SELECT sys_context ('userenv','ISDBA')
INTO v_isdba
FROM DUAL;
IF (UPPER(v_osuser) != UPPER(v_username))
THEN
RAISE_APPLICATION_ERROR
(-20001,'You cant access the database!',TRUE);
END IF;
END;
/
I created the same trigger, with the user sys and even so nothing was inserted in the table ...
He compiled normally, but when I saw the trigger the same was With an error, I had recompile and the error was from the watch table, which did not exist.
I created the same and the trigger compiled normally. I closed the session and I regained myself with the user sys and again nothing was add in the table.
create or replace trigger sys.marca_logon
after logon on database
begin
insert into vigia
values
(User || ' entrou no sistema ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'Erro ao logar:'||sqlerrm);
END;
Children ...
What is the bank version ??? This is very important can be a bug.
Another thing ...
has already tried "before logon" instead of after ...
compiled and is invalid ... would have to have given error
Gives a "show errors" after cimpile in SQLPlus ... Show something?
In case, bankruptcy, I believe it can not interfere with after-logon trigger, but ...
Another thing, can not be (I believe) of Before-Logon, because "before" to enter? How to get this information? I talked to the DBA here and said it has to be with the after.
Errors arose, he informed me that no.
Another thing I confirmed, very important:
Create with sys or, the user who is creating, has explicit permission on objects.
Another thing, did you take a look at the bank's alert to see if there is any information regarding this attempt? Talking here with the DBA, just passed me to be careful with this trigger, because you may not be able to log in.
--1) tabela
CREATE TABLE auditoria (usuario VARCHAR2(30),
data DATE);
--2) trigger
CREATE OR REPLACE TRIGGER log_auditoria AFTER LOGON ON DATABASE
DECLARE
BEGIN
INSERT INTO auditoria (usuario,
data)
VALUES (USER,
SYSDATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'Erro logando no banco:'||sqlerrm);
END;
-- depois de logado com Sys e Scott
SELECT *
FROM auditoria;
-- resultado
SCOTT 27-fev-2008 10:36:40
SYS 27-fev-2008 10:37:03
