ORACLE records the IP address of the connected user and the IP address of the user who failed to log on

The main function of the realization is that Oracle Log on successfully logged in user IP address, Logon failure log logon failed IP Address

1, you need to set up a trigger to record the logon success of the client user'sIPAddress

We all know that the name of the client machine is recorded in the v$session, but there is no IP, what if the record clinet IP? Run the Dbms_session process package registration, and then execute the stored procedure On_logon_trigger, so that when the client logs on, the v$ The Client_info column of the session records its corresponding IP information.

Using the Dbms_session process package, first execute the

BEGIN

Dbms_session.set_identifier (Sys_context (' USERENV ', ' ip_address '));

END ;

Execute the trigger again trigger

Create or Replace Trigger On_logon_trigger

After logon on Database

Begin

Dbms_application_info.set_client_info (Sys_context ( ' Userenv ', ' ip_address ' ) );

End ;

executing these process package triggers requires DBA permissions.

 -------------------------------------------------------------------------------------------------- --------------
< Copyright, the article allows reprint, but must be linked to the source address, otherwise investigate legal liability !>
Original Blog Address: http://blog.csdn.net/mchdba/article/details/45749131
Hara Douglas fir (MCHDBA)
--------------------------------------------------------------------------------------------------------------- -

2, and then use the Super administrator toPlsqllog In, you can view the connectionOracleof theIPInformation:

Execute Query SQL:

Select username,program,machine,client_info,sys_context (' Userenv ',' ip_address ' ) as ipadd

from v$session s

where username is notnull

order by Username,program,machine;

The information is as follows:

3, the log information is logged when a trigger is created to fail the login:

Write a trigger, the information of the trigger is recorded in the alert log, and the user information of the login failure is obtained by viewing the alert log.

The triggers are as follows:

CREATE OR REPLACE Triggerlogon_denied_to_alert

After Servererror on DATABASE

DECLARE

Message VARCHAR2 (168);

IP VARCHAR2 (15);

V_os_user VARCHAR2 (80);

V_module VARCHAR2 (50);

V_action VARCHAR2 (50);

V_pid VARCHAR2 (10);

V_SID number;

V_program VARCHAR2 (48);

BEGIN

IF (Ora_is_servererror (1017)) Then

--Get IP for remote connections:

IF Upper (Sys_context (' Userenv ', ' network_protocol ')) = ' TCP ' Then

IP: = Sys_context (' Userenv ', ' ip_address ');

END IF;

SELECT sid into V_sid from Sys.v_$mystat WHERE rownum < 2;

SELECT P.spid, V.program

Into V_pid, V_program

From V$process p, V$session v

WHERE p.addr = v.paddr

and v.sid = V_sid;

V_os_user: = Sys_context (' Userenv ', ' os_user ');

Dbms_application_info.read_module (V_module, v_action);

Message: = To_char (sysdate, ' YYYYMMDD hh24miss ') | |

' logon denied from ' | | NVL (IP, ' localhost ') | | "| |

V_pid | | "| | V_os_user | | ' With ' | | V_program | | ' – ' | |

V_module | | "| | V_action;

SYS.DBMS_SYSTEM.KSDWRT (2, message);

ENDIF;

END;

/

Execution Error:

Compilation errors for Triggerpowerdesk. Logon_denied_to_alert

Error:pls-00201:identifier ' SYS. Dbms_system ' must be declared

Line:35

TEXT:SYS.DBMS_SYSTEM.KSDWRT (2, message);

Error:pl/sql:statement ignored

Line:35

TEXT:SYS.DBMS_SYSTEM.KSDWRT (2, message);

Need to give permission

Grant execute on Sys.dbms_system Topowerdesk;

Then the execution succeeded.

4, Logon failure viewAlertInformation

PSLQL log in as shown in:

To view the alert log in the background, you will see the failure information record:

Fri May 15 19:11:09 2015

20150515 191109 logon denied from192.168.120.169 20934 Administrator with Plsqldev.exe? Plsqldev.exe

20150515 191109 logon denied from192.168.120.169 20934 Administrator with Plsqldev.exe? Plsqldev.exe

Fri May 15 19:11:18 2015

20150515 191118 logon denied from192.168.120.169 20958 Administrator with Plsqldev.exe? Plsqldev.exe

20150515 191118 logon denied from 192.168.120.16920958 Administrator with Plsqldev.exe? Plsqldev.exe

ORACLE records the IP address of the connected user and the IP address of the user who failed to log on