Oracle中如何记录和查看用户的登录记录

您所在的位置：网站首页 › oracle11g开启审计 › Oracle中如何记录和查看用户的登录记录

Oracle中如何记录和查看用户的登录记录

2023-07-19 20:41| 来源: 网络整理| 查看: 265

【引言】

来了一需求，项目组想查看一段时间内都有哪些连接访问oracle数据库，并需要记录相关信息在日志文件中。有什么方法可以搞下这个问题。今天念叨下：

文章大纲：有哪些审计的方法？如何实现上述审计？审计建议

有哪些审计的方法？上述引言的问题，说的再通俗点就是：数据库用户的登录记录，如何查看？

一般有三种方法：

使用trigger定制化用系统触发器来记录用户登录开启库audit审计功能用oracle库本身的审记功能来记录用户的操作信息使用logmnr进行挖取日志分析从logminner的文件中抓取出用户登录信息

如何实现上述审计？

方法一使用trigger定制化

具体操作如下：

1. 创建表空间

CREATE TABLESPACE logInfo DATAFILE '/oradata/group/logInfo_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON; /

2. 创建用户登录表，并制定表空间为logInfo

CREATE TABLE LOGINFO_LOG ( SESSION_ID NUMBER(8,0) NOT NULL, LOGIN_ON_TIME DATE, LOGIN_OFF_TIME DATE, USER_IN_DB VARCHAR2(50), MACHINE VARCHAR2(50), IP_ADDRESS VARCHAR2(20), RUN_PROGRAM VARCHAR2(50) ) PCTFREE 10 PCTUSED 40 MAXTRANS 255 TABLESPACE logInfo STORAGE(INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) NOCACHE LOGGING /

3. 创建用户登录触发器

CREATE OR REPLACE TRIGGER sys.LOGIN_ON_INFO AFTER LOGON ON DATABASE BEGIN INSERT into LOGINFO_LOG(session_id,login_on_time,login_off_time,user_in_db,machine,ip_address,run_program) SELECT AUDSID,sysdate,null,sys.login_user,machine,SYS_CONTEXT('USERENV','IP_ADDRESS'),program FROM v$session WHERE AUDSID=USERENV('SESSIONID'); END; /

4. 创建登出信息触发器

CREATE OR REPLACE TRIGGER SYS.LOGIN_OFF_INFO BEFORE LOGOFF ON DATABASE Begin update sys.LOGINFO_LOG set login_off_time=sysdate where session_id=USERENV('SESSIONID'); exception when others then null; END; /

5. 查看登陆信息：

Select * from loginfo_log order by 2 desc;

另，可在$ORACLE_HOME/network/admin/sqlnet.ora 中设置访问限制：

--启动检查 tcp.validate_checking=yes ---允许访问 tcp.invited_nodes=(IP1,IP2,….) --- 拒绝访问 tcp.excluded_nodes=(IP1,IP2,…..)

此外，因为应用程序中的delete、drop、update操作也需要记录，如下以drop审计进行举例，说明使用触发器如何进行用户操作的留痕。

1. 创建drop操作的用户留痕表

CREATE TABLE DROP_LOG ( SESSION_ID INT NOT NULL, DROP_TIME DATE, IP_ADDRESS VARCHAR2 (20), OBJECT_OWNER VARCHAR2 (30), OBJECT_NAME VARCHAR2 (30), OBJECT_TYPE VARCHAR2 (20), DROP_BY_USER VARCHAR2 (30) );

2. 创建删除表操作的触发器

CREATE OR REPLACE TRIGGER DROP_INFO AFTER DROP ON username.schema BEGIN INSERT INTO DROP_LOG (SESSION_ID, DROP_TIME, IP_ADDRESS, OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE, DROP_BY_USER) VALUES(userenv('sessionid'), SYSDATE, sys_context('userenv','ip_address'), SYS.DICTIONARY_OBJ_OWNER, SYS.DICTIONARY_OBJ_NAME, SYS.DICTIONARY_OBJ_TYPE, SYS.LOGIN_USER);? end; / 方法二开启库audit审计记录登录功能

简单介绍下Oracle的审计功能

审计（Audit)用于监视用户所执行的数据库操作，审计记录可存在数据字典表（称为审计记录:存储在system表空间中的SYS.AUD表中，可通过视图dba_audit_trail查看）或操作系统审计记录中(默认位置为ORACLE_BASE/admin/$ORACLE_SID/adump/)，默认情况下审计是不开启。

和审计相关的两个参数说明

参数1 ：audit_sys_operations

默认为false，当设置为true时，所有sys用户（包括以sysdba, sysoper身份登录的用户）的操作都会被记录，audit trail不会写在aud$表中，linux/unix平台中则会记录在audit_file_dest参数指定的文件中。

参数2：audit_trail：

None：是默认值，不做任何审计；

DB：审计的结果只有连接信息，记录在数据库的审计表aud$；

DB,Extended：这样审计结果里面除了连接信息还包含了当时执行的具体语句；

OS：将audit trail 记录在操作系统文件中，文件名由参数audit_file_dest指定位置；

XML：10g里新增的。

注意：

上述两个参数是static参数，需重启数据库方可生效。

1. 检查审计功能是否开启

SQLPLUS> connect / AS SYSDBA SQLPLUS> select * from sys.aud$; --没有记录返回 SQLPLUS> select * from dba_audit_trail; - 没有记录返回

如上述查询发现表不存在，说明审计相关的表还没有安装，需要安装。

安装方式如下：

SQLPLUS> connect / as sysdba SQLPLUS> @$ORACLE_HOME/rdbms/admin/cataudit.sql

注意：

审计表默认安装在SYSTEM表空间。所以要确保SYSTEM表空间充足。

开启审计功能

设置审计参数

SQL> alter system set audit_sys_operations=TRUE scope=spfile; --如果要审计管理用户(以sysdba/sysoper角色登陆)，需要开此参数。 SQL> alter system set audit_trail=db scope=spfile;

重新启动数据库

以SYSTEM帐号登录数据库执行下列语句，设置CONNECT审计并检查是否设置成功：

SQL> connect system/password --审计登陆用户 SQL> audit connect 如下是常用的几个审计举例 --审计所有对表的操作 SQL> audit all on table; --审计用户test对表的所有操作 sql> audit table by test; --审计任何用户删除用户test表的操作 SQL> AUDIT DELETE ANY test.TABLE; --审计任何用户删除失败的情况 SQL> AUDIT DELETE ANY TABLE WHENEVER NOT SUCCESSFUL; --只审计删除成功的情况 SQL> AUDIT DELETE ANY TABLE WHENEVER SUCCESSFUL; --审计SYSTEM用户对表user.table的delete,update,insert操作 SQL> AUDIT DELETE,UPDATE,INSERT ON user.table by SYSTEM;

以SYSTEM帐号登录数据库执行下列语句，设置CONNECT审计并检查是否设置成功：

SQL> col user_name format a20 SQL> col audit_option format a20 SQL> set linesize 999 SQL> select user_name, audit_option, success, failure from sys.dba_stmt_audit_opts; USER_NAME AUDIT_OPTION SUCCESS FAILURE ----------- -------------------- ------------------------------ ------------------------------ ALTER SYSTEM BY ACCESS BY ACCESS SYSTEM AUDIT BY ACCESS BY ACCESS CREATE SESSION BY ACCESS BY ACCESS CREATE USER BY ACCESS BY ACCESS ALTER USER BY ACCESS BY ACCESS DROP USER BY ACCESS BY ACCESS PUBLIC SYNONYM BY ACCESS BY ACCESS DATABASE LINK BY ACCESS BY ACCESS ROLE BY ACCESS BY ACCESS PROFILE BY ACCESS BY ACCESS CREATE ANY TABLE BY ACCESS BY ACCESS 查询 AUD$表就可以查看到审计结果了 SQL> select sessionid, to_char(timestamp#,'DD-MON-YY:HH24:MI:SS') \ login,userid, to_char(logoff$time,'DD-MON-YY:HH24:MI:SS') logoff \ from sys.aud$ where userid='USERNAME'; 关闭审计的方法，直接在审计语句前加no，如 SQL> noaudit connect Sql> noaudit all on t_test;

最后注意：

将审计相关的表移动到其他表空间由于AUD表等审计相关的表存放在SYSTEM表空间，因此为了不影响系统性能，保护SYSTEM表空间不被占用，最好把AUD移动到其他的表空间上，使用下面语句移动：

sql>connect / as sysdba; sql>alter table aud$ move tablespace ; sql>alter index I_aud1 rebuild online tablespace ; SQL> alter table audit$ move tablespace ; SQL> alter index i_audit rebuild online tablespace ; SQL> alter table audit_actions move tablespace ; SQL> alter index i_audit_actions rebuild online tablespace ; 方法三使用logmnr进行挖取日志分析

操作步骤如下：

sys用户登陆，安装LogMiner工具 SQL>@$ORACLE_HOME\rdbms\admin\dbmslm.sql; SQL>@ $ORACLE_HOME\rdbms\admin\dbmslmd.sql;

脚本1 用来创建DBMS_LOGMNR包，该包用来分析日志文件。

脚本2用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。

修改初始参数文件 UTL_FILE_DIR = ($ORACLE_HOME\logs)

重新启动数据库，使新加的参数生效。

创建数据字典文件： SQL> connect /as sysdbaSQL> execute dbms_logmnr_d.build(dictionary_filename => 'dict.ora',dictionary_location => '/oracle/logs'); 创建分析列表，添加分析日志： SQL> execute dbms_logmnr.add_logfile(LogFileName => '/ORACLE/ORADATA/ORADBSP/REDO01.LOG',Options => dbms_logmnr.new); 添加分析日志（建议一次添加1个）： SQL> execute dbms_logmnr.add_logfile(LogFileName => '/ORACLE/ORADATA/ORADBSP/REDO02.LOG',Options => dbms_logmnr.ADDFILE); logMiner日志分析

无限制条件

SQL> execute dbms_logmnr.start_logmnr(DictFileName => '/oracle/logs'dict.ora');

带限制条件：

基于时间段的分析

SQL> execute dbms_logmnr.start_logmnr(startTime => to_date('20191112100000','yyyy-mm-dd hh24:mi:ss'),endTime => to_date('20191112110000','yyyy-mm-dd hh24:mi:ss'),DictFileName => '/oracle/logs'dict.ora'); 分析后释放内存： SQL> execute dbms_logmnr.end_logmnr; 删除日志分析文件： SQL>exec dbms_logmnr.add_logfile('/ORACLE/ORADATA/ORADBSP/REDO02.LOG',dbms_logmnr.removefile); 查看LogMiner工具分析结果， --所有与logmnr相关的视图 SQL> select * from dict t where t.table_name like '%LOGMNR%';

上述语句中，分析结果存在GV$LOGMNR_LOGS 分析日志列表视图，使用如下语句查询：

SQL>create table logmnr_tmp as select * from GV$GV$LOGMNR_LOGS; SQL>SELECT scn, timestamp, log_id, seg_owner, seg_type, table_space, data_blk#, data_obj#, data_objd#, session#, serial#, username, session_info, sql_redo, sql_undo FROM logmnr_tmp t WHERE t.sql_redo LIKE 'delete%';

关于LogMiner的更详细的使用，请看本公众号之前的一篇推文：

《Logminer：oracle人为误操作之恢复神器》

【结语】

1.本文讲述三种查看用户登录信息的方法：触发器、audit审计、LogMiner方式。

使用 Oracle Audit 函数来记录用户登录信息，但开放了 Audit 函数将会使Oracle 性能下降，甚至导致 Oracle 崩溃；故推荐使用方法1触发器的方式，不占用系统资源，方便灵活。

【参考】

http://blog.itpub.net/26736162/viewspace-2140644/

【参考】

https://www.cnblogs.com/aocle/archive/2012/09/19/2694126.html

【本文地址】

Oracle中如何记录和查看用户的登录记录

Oracle中如何记录和查看用户的登录记录

今日新闻

推荐新闻