Oracle Database View User login information for client including IP

By creating a trigger to log user login, audit the logged-in user, record the login user, host, system users and IP address:

1. Create a table in SYS mode that records the details of the client's login to the database server.

 create table login_history    (     username    VARCHAR2,   --user name       machine     VARCHAR2,   --machine name       login_time date,           --Login Time       ip          VARCHAR2 (   --ip address     );  or  sql > create table LOGON_TABLE as       select  Sysdate logon_time,           sys_context (' USERENV ', ' session_user ')  username,            sys_ Context (' USERENV ', ' HOST ')  host,             Sys_context (' USERENV ', ' Os_user')  os_user,            sys_context (' USERENV ', ' IP_Address ')  ip_address      from dual    /table  created.

2. Create a trigger in SYS mode.

Create or replace trigger login_log     after logon on  database   begin     insert into login_history        select username, machine, sysdate, sys_context (' Userenv ',  ' ip_address ')          from v$session         where audsid = userenv (' SessionID ');      commit;   end;    or  SQL> create or  replace trigger tri_logon after logon on database      begin         insert into logon_table                     ( logon_ Time,    &nbsP;                username,                       host,                      os_user,                      ip_address                    )               values                     ( sysdate,                     sys_context (' USERENV ',  ' session_user '),                     sys_context (' USERENV ', ' HOST '),                     sys_context (' USERENV ', ' os_user '),                     sys_ Context (' USERENV ', ' ip_address '));     end;    /trigger created.

3. Some instances of viewing session count (log in with SYS user)

--View login_history Table select * from Login_history;    --View all current session Count Select COUNT (*) from v$session;    --View the current client computer with all sessions named Rhel select COUNT (*) from v$session where machine = ' RHEL '; --View the current number of connections for each client computer select COUNT (*), machine from V$session Group by machine;

4. View the IP of the current session

Select Sys_context (' Userenv ', ' ip_address ') from dual;

This article is from the "Night" blog, please be sure to keep this source http://liuqun.blog.51cto.com/3544993/1984200

Oracle Database View User login information for client including IP