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

来源：互联网发布：西门子plm软件编辑：程序博客网时间：2024/06/06 03:34

记录这些信息有很几种，有审计、使用触发器、还有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_loginminvalue 1 maxvalue 9999999999 start with 1 increment by 1 cache 20;

4）创建触发器

CREATE OR REPLACE TRIGGER AUDI.TR_GMUSER_LOGIN  AFTER LOGON ON DATABASEDECLAREBEGIN  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;

0 0