loginHistory
IFSAPP.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
- Where SYS_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;*/
複製代碼 |