oracle记录某个用户的登录信息

记录这些信息有很几种，有审计、使用触发器、还有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;