【实操篇】_Oracle中如何记录访问数据库的登陆信息
【引⾔】
来了⼀需求，项⽬组想查看⼀段时间内都有哪些连接访问oracle数据库，并需要记录相关信息在⽇志⽂件中。有什么⽅法可以搞下这个问题。今天念叨下：
⽂章⼤纲：
1. 有哪些审计的⽅法？
2. 如何实现上述审计？
3. 审计建议
有哪些审计的⽅法？
上述引⾔的问题，说的再通俗点就是：数据库⽤户的登录记录，如何查看？
⼀般有三种⽅法：
1. 使⽤trigger定制化⽤系统触发器来记录⽤户登录
2. 开启库audit审计功能⽤oracle库本⾝的审记功能来记录⽤户的操作信息
3. 使⽤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/a 中设置访问限制：
–启动检查
tcp.validate_checking=yes
—允许访问
tcp.invited_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 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
表中，可通过视图dba udit rail 查看）或操作系统审计记录中(默认位置为
a t
SQLPLUS> @$ORACLE_HOME/rdbms/admin/cataudit.sql
注意：
审计表默认安装在SYSTEM表空间。所以要确保SYSTEM表空间充⾜。
2. 开启审计功能
3. 设置审计参数
SQL> alter system set audit_sys_operations=TRUE scope=spfile;
–如果要审计管理⽤户(以sysdba/sysoper⾓⾊登陆)，需要开此参数。
SQL> alter system set audit_trail=db scope=spfile;
2. 重新启动数据库
3. 以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
4. 查询 AUD$表就可以查看到审计结果了
SQL> select sessionid, to_char(timestamp#,‘DD-MON-YY:HH24:MI:SS’)
login,userid, to_char(logoffKaTeX parse error: Undefined control sequence: \ at position 37: …MI:SS') logoff \f rom sys.aud where userid=‘USERNAME’;
5. 关闭审计的⽅法，直接在审计语句前加no，如
SQL> noaudit connect
Sql> noaudit all on t_test;
最后注意：
将审计相关的表移动到其他表空间由于AUD
表等审计相关的表存放在SY STEM表空间，因此为了不影响系统性能，保护SY STEM表空间不被占⽤，最好把AUD 移动到其他的表空间上，使⽤下⾯语句移动：如何连接oracle数据库
sql>connect / as sysdba;
sql>alter table aud$ move tablespace <new tablespace>;
sql>alter index I_aud1 rebuild online tablespace <new tablespace>;
SQL> alter table audit$ move tablespace <new tablespace>;
SQL> alter index i_audit rebuild online tablespace <new tablespace>;
SQL> alter table audit_actions move tablespace <new tablespace>;
SQL> alter index i_audit_actions rebuild online tablespace <new tablespace>;
⽅法三 使⽤logmnr进⾏挖取⽇志分析
操作步骤如下：
1. sys⽤户登陆，安装LogMiner⼯具
SQL>@$ORACLE_HOME\rdbms\admin\dbmslm.sql;
SQL>@ $ORACLE_HOME\rdbms\admin\dbmslmd.sql;
脚本1 ⽤来创建DBMS_LOGMNR包，该包⽤来分析⽇志⽂件。
脚本2⽤来创建DBMS_LOGMNR_D包，该包⽤来创建数据字典⽂件。
2. 修改初始参数⽂件
UTL_FILE_DIR = ($ORACLE_HOME\logs)
重新启动数据库，使新加的参数⽣效。
3. 创建数据字典⽂件：
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论