登入歷史記錄
loginHistoryIFSAPP.LOGIN_HISTORY
IFSAPP.LOGIN_HISTORY_API
CREATE OR REPLACE VIEW LOGIN_HISTORY AS
SELECT session_id session_id,
username username,
login_time login_time,
logoff_time logoff_time,
osuser osuser,
machine_name machine_name,
ip_address ip_address,
module module,
rowid objid,
ltrim(lpad(to_char(login_time,'YYYYMMDDHH24MISS'),2000)) objversion
FROM login_history_tab
WITH read only;
comment on table LOGIN_HISTORY is 'LU=LoginHistory^PROMPT=Login History^MODULE=NTCH^';
comment on column LOGIN_HISTORY.SESSION_ID is 'FLAGS=PMI-L^DATATYPE=NUMBER^PROMPT=Session Id^';
comment on column LOGIN_HISTORY.USERNAME is 'FLAGS=KMIUL^DATATYPE=STRING(100)^PROMPT=Username^';
comment on column LOGIN_HISTORY.LOGIN_TIME is 'FLAGS=AMIUL^DATATYPE=DATE/DATE^PROMPT=Login Time^';
comment on column LOGIN_HISTORY.LOGOFF_TIME is 'FLAGS=AMIUL^DATATYPE=DATE/DATE^PROMPT=Logoff Time^';
comment on column LOGIN_HISTORY.OSUSER is 'FLAGS=AMIUL^DATATYPE=STRING(100)^PROMPT=Osuser^';
comment on column LOGIN_HISTORY.MACHINE_NAME is 'FLAGS=AMIUL^DATATYPE=STRING(100)^PROMPT=Machine Name^';
comment on column LOGIN_HISTORY.IP_ADDRESS is 'FLAGS=AMIUL^DATATYPE=STRING(100)^PROMPT=IP Address^';
comment on column LOGIN_HISTORY.MODULE is 'FLAGS=AMIUL^DATATYPE=STRING(100)^PROMPT=Module^';
CREATE OR REPLACE TRIGGER LOGIN_HISTORY_TRG
AFTER LOGON ON DATABASE
DECLARE
machine_name_VARCHAR2(64);
os_userid_ VARCHAR2(30);
program_id_ VARCHAR2(64);
module_ VARCHAR2(48);
logon_time_ date;
machine_count_ number;--longine added
CURSOR c1 IS
SELECT logon_time, osuser, machine, program, module
FROM v$session
WHERE audsid = userenv( 'sessionid' );
BEGIN
OPEN c1;
FETCH c1 INTO logon_time_, os_userid_, machine_name_, program_id_, module_;
CLOSE c1;
--IF USER NOT IN ('IFSAPP','IFSAPPEN','SYS','SYSTEM','IFSAPPZH','FNDEXT') THEN*/
-- IF userenv('sessionid') > 0 THEN*/
--longine added
Select count(machine) INTO machine_count_
From v$session
WhereSYS_CONTEXT('USERENV','HOST') = MACHINE;
IF machine_count_=0 THEN
--IF SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS'THEN
INSERT INTO LOGIN_HISTORY_TAB
( SESSION_ID,
USERNAME,
LOGIN_TIME,
OSUSER,
MACHINE_NAME,
IP_ADDRESS,
MODULE )
VALUES
( SYS_CONTEXT('USERENV', 'SESSIONID'),
FND_SESSION_API.Get_Fnd_User,
logon_time_,
os_userid_,
machine_name_,
SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
module_);
END IF;
END;
/*CREATE OR REPLACE TRIGGER IFSAPP.LOGIN_HISTORY_TRG
AFTER LOGON ON DATABASE
DECLARE
machine_name_ VARCHAR2(64);
os_userid_ VARCHAR2(30);
program_id_ VARCHAR2(64);
module_ VARCHAR2(48);
logon_time_ date;
\*CURSOR c1 IS
SELECT logon_time, osuser, machine, program, module
FROM v$session
WHERE audsid = userenv( 'sessionid' );*\
BEGIN
\* OPEN c1;
FETCH c1 INTO logon_time_, os_userid_, machine_name_, program_id_, module_;
CLOSE c1;
--IF USER NOT IN ('IFSAPP','IFSAPPEN','SYS','SYSTEM','IFSAPPZH','FNDEXT') THEN*\
\*IF userenv('sessionid') > 0 THEN*\
\* INSERT INTO LOGIN_HISTORY_TAB
( SESSION_ID,
USERNAME,
LOGIN_TIME,
OSUSER,
MACHINE_NAME,
IP_ADDRESS,
MODULE )
VALUES
( SYS_CONTEXT('USERENV', 'SESSIONID'),
FND_SESSION_API.Get_Fnd_User,
logon_time_,
os_userid_,
machine_name_,
SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
module_);
END IF;*\
IF SYS_CONTEXT('USERENV','SESSION_USER') <> 'SYS'THEN
INSERT INTO LOGIN_HISTORY_TAB
(SESSION_ID,
LOGIN_TIME,
LOGOFF_TIME,
USERNAME,
OSUSER,
MACHINE_NAME,
IP_ADDRESS,
MODULE)
select AUDSID,sysdate,null,USERNAME,osuser,machine,
SYS_CONTEXT('USERENV','IP_ADDRESS'),program
from v$session
where AUDSID = USERENV('SESSIONID'); --當前SESSION
END IF;
END;*/
頁:
[1]