oracle和sqlserver记录客户端ip、用户名、sql

1.oracle

--建立LOGON_TABLE
create table LOGON_TABLE as
select sysdate logon_time,
sys_context('USERENV','SESSION_USER') username,
sys_context('USERENV','IP_ADDRESS') ip_address,
sys_context('USERENV','SESSIONID') session_id
from dual;


--建立触发器
create or replace trigger tri_logon after logon on database
begin
insert into LOGON_TABLE
(
logon_time,
username,
ip_address,
session_id
)
values
(
sysdate,
sys_context('USERENV','SESSION_USER'),
sys_context('USERENV','IP_ADDRESS'),
sys_context('USERENV','SESSIONID')
end;


--客户端IP，数据库用户名，sql
select ip_address,LOGON_TABLE.username,sql_text
from v$session,v$sql,LOGON_TABLE

where v$session.sql_id=v$sql.sql_id and v$session.AUDSID=LOGON_TABLE.session_id;

2.sqlserver

--客户端IP，数据库用户名、sql
select DC.CLIENT_NET_ADDRESS as client_ip,qt.text as sql,SP.loginame as username
from sys.dm_exec_requests r
join SYS.DM_EXEC_CONNECTIONS AS DC
on r.session_id=DC.session_id
join SYS.SYSPROCESSES AS SP
ON SP.SPID = DC.SESSION_ID
cross apply sys.dm_exec_sql_text(SP.sql_handle) as qt

order by r.scheduler_id, r.status, r.session_id;