记录这些信息有很几种,有审计、使用触发器、还有logminor。公司业务同事只是针对某个用户做简单的记录,所以选择比较简单的触发器。
为减小影响,使用单独的表空间和用户
1)创建表空间、用户并授权
create tablespace audi datafile '/oradata/aud01.dbf' SIZE 2G;
create user audi identified by audi default tablespace audi quota unlimited on audi;
grant connect,resource,create table,create sequence,create trigger to audi;
grant select on v_$session to audi;
2)建表
<pre name="code" class="sql">create table AUDI.T_GMUSE_LOGIN_LOG
(
id NUMBER(10) ,
username VARCHAR2(30),
logontime DATE,
terminal VARCHAR2(50),
ipadress VARCHAR2(20),
osuser VARCHAR2(30),
machine VARCHAR2(64),
program VARCHAR2(64),
sid NUMBER,
serial# NUMBER,
ausid NUMBER,
primary key (id)
);
3)创建序列
create sequence audi.seq_gmuse_login
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
cache 20;
4)创建触发器
CREATE OR REPLACE TRIGGER AUDI.TR_GMUSER_LOGIN
AFTER LOGON ON DATABASE
DECLARE
BEGIN
INSERT INTO AUDI.T_GMUSE_LOGIN_LOG
SELECT SEQ_GMUSE_LOGIN.NEXTVAL id,
USERNAME ,
LOGON_TIME,
TERMINAL,
SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
OSUSER,
MACHINE,
PROGRAM,
SID,
SERIAL#,
USERENV('SESSIONID')
FROM V$SESSION T1
WHERE USERNAME = 'GMUSER'
AND NOT EXISTS
(SELECT * FROM AUDI.T_GMUSE_LOGIN_LOG T2 WHERE T1.LOGON_TIME = T2.LOGONTIME);
END;