使用审计功能记录错误密码登陆信息

栏目: 数据库 · Oracle · 发布时间: 7年前

内容简介:使用审计功能记录错误密码登陆信息

恢复完UAT环境,发现业务用户总被锁定,问谁都说自己的程序密码是对的,本来想写个触发器记录是谁总用错误的密码登陆数据库,发现这个数据库的审计没有关闭(11g默认审计功能是开启的),是打开的。数据库版本11.2.0.4。

1 SQL> show parameter audit
2
3 NAME                                 TYPE        VALUE
4 ------------------------------------ ----------- ------------------------------
5 audit_file_dest                      string      /u01/app/oracle/admin/PROD/adu
6                                                 mp
7 audit_sys_operations                 boolean     FALSE
8 audit_syslog_level                   string
9 audit_trail                          string      DB

在11g如果没有关闭掉审计的功能,默认是可以记录错误密码登陆信息的,很幸运的是,这个功能并没有被禁掉。那么查询AUD$表就能查询到错误密码登陆信息。以下是在没有做过任何设置的11.2.0.4.0版本的数据库中做的测试,先使用错误密码登陆数据库。

01 [oracle@secdb1 admin]$ sqlplus dbdream/oracle@localhost/PROD
02
03 SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 16 11:48:17 2015
04
05 Copyright (c) 1982, 2013, Oracle.  All rights reserved.
06
07 ERROR:
08 ORA-01017: invalid username/password; logon denied
09
10 Enter user-name:
11 ERROR:
12 ORA-01017: invalid username/password; logon denied
13
14 Enter user-name:
15 ERROR:
16 ORA-01017: invalid username/password; logon denied
17
18 SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

查询AUD$表,其中returncode字段记录的就是用户登录信息,1017位密码错误,登录失败,0为正常登录数据库。

1 SQL> select userid,userhost,terminal,returncode,spare1 from aud$;
2
3 USERID     USERHOST   TERMINAL   RETURNCODE SPARE1
4 ---------- ---------- ---------- ---------- ----------
5 DBDREAM    secdb1     pts/1            1017 oracle

11g默认不止开启了错误密码登陆的审计,正常登录到数据库的连接也会被审计到,下面先通过正确的密码登陆数据库。

01 [oracle@secdb1 ~]$ sqlplus dbdream/dbdream@localhost/PROD
02
03 SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 16 11:54:21 2015
04
05 Copyright (c) 1982, 2013, Oracle.  All rights reserved.
06
07 Connected to:
08 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
09 With the Partitioning, OLAP, Data Mining and Real Application Testing options
10
11 SQL>

查询AUD$表会发现,这个连接也被记录了。

1 SQL> select userid,userhost,terminal,returncode,spare1 from aud$;
2
3 USERID     USERHOST   TERMINAL   RETURNCODE SPARE1
4 ---------- ---------- ---------- ---------- ----------
5 DBDREAM    secdb1     pts/1            1017 oracle
6 DBDREAM    secdb1     pts/1               0 oracle

在10g版本,审计默认是关闭的,下面是10.2.0.1.0版本的数据库,审计默认关闭。

01 SYS@EMREP> select * from v$version;
02
03 BANNER
04 ----------------------------------------------------------------
05 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
06 PL/SQL Release 10.2.0.1.0 - Production
07 CORE    10.2.0.1.0      Production
08 TNS for Linux: Version 10.2.0.1.0 - Production
09 NLSRTL Version 10.2.0.1.0 - Production
10
11 SYS@EMREP> show parameter audit
12
13 NAME                                 TYPE        VALUE
14 ------------------------------------ ----------- ------------------------------
15 audit_file_dest                      string      /u01/app/oracle/admin/EMREP/ad
16                                                 ump
17 audit_sys_operations                 boolean     FALSE
18 audit_syslog_level                   string
19 audit_trail                          string      NONE

打开审计功能,看看是否可以审计到用户登录信息。

01 YS@EMREP> alter system set audit_trail=db scope=spfile;
02
03 System altered.
04
05 SYS@EMREP> startup force
06 ORACLE instance started.
07
08 Total System Global Area  587202560 bytes
09 Fixed Size                  1220724 bytes
10 Variable Size             188747660 bytes
11 Database Buffers          394264576 bytes
12 Redo Buffers                2969600 bytes
13 Database mounted.
14 Database opened.
15 SYS@EMREP> show parameter audit
16
17 NAME                                 TYPE        VALUE
18 ------------------------------------ ----------- ------------------------------
19 audit_file_dest                      string      /u01/app/oracle/admin/EMREP/ad
20                                                 ump
21 audit_sys_operations                 boolean     FALSE
22 audit_syslog_level                   string
23 audit_trail                          string      DB

audit_trail是静态参数,修改后需要重启数据库才能生效。使用错误的密码登陆数据库,看看是否会被审计到。

01 [oracle@dbdream admin]$ sqlplus dbdream/oracle@localhost/EMREP
02
03 SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 16 13:13:57 2015
04
05 Copyright (c) 1982, 2005, Oracle.  All rights reserved.
06
07 ERROR:
08 ORA-01017: invalid username/password; logon denied
09
10 Enter user-name:
11 ERROR:
12 ORA-01017: invalid username/password; logon denied
13
14 Enter user-name:
15 ERROR:
16 ORA-01017: invalid username/password; logon denied
17
18 SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

查询AUD$表,看看是否记录错误密码登陆的信息。

1 SYS@EMREP> select userid,userhost,terminal,returncode,spare1 from aud$;
2
3 no rows selected

10g版本的审计默认是不记录错误密码登陆的信息,需要手动设置。

1 SYS@EMREP> audit session whenever not successful;
2
3 Audit succeeded.

再次使用错误密码登陆数据库,此时就会被记录下来。

01 Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
02 With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
03 [oracle@dbdream admin]$ sqlplus dbdream/oracle@localhost/EMREP
04
05 SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 16 13:15:04 2015
06
07 Copyright (c) 1982, 2005, Oracle.  All rights reserved.
08
09 ERROR:
10 ORA-01017: invalid username/password; logon denied
11
12 Enter user-name:
13 ERROR:
14 ORA-01017: invalid username/password; logon denied
15
16 Enter user-name:
17 ERROR:
18 ORA-01017: invalid username/password; logon denied
19
20 SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
21
22 SYS@EMREP> select userid,userhost,terminal,returncode,spare1 from aud$;
23
24 USERID     USERHOST   TERMINAL   RETURNCODE SPARE1
25 ---------- ---------- ---------- ---------- ----------
26 DBDREAM    dbdream    pts/2            1017 oracle

那么正常登录到数据库是否会被审计记录下来呢?下面使用正确的密码登陆数据库。

01 [oracle@dbdream admin]$ sqlplus dbdream/dbdream@localhost/EMREP
02
03 SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 16 13:16:07 2015
04
05 Copyright (c) 1982, 2005, Oracle.  All rights reserved.
06
07 Connected to:
08 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
09 With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
10
11 DBDREAM@localhost/EMREP>
12
13 SYS@EMREP> select userid,userhost,terminal,returncode,spare1 from aud$;
14
15 USERID     USERHOST   TERMINAL   RETURNCODE SPARE1
16 ---------- ---------- ---------- ---------- ----------
17 DBDREAM    dbdream    pts/2            1017 oracle

查询发现正常登录数据库的操作并没有被记录下来,要想记录正常登录的信息,也需要手动配置。

01 SYS@EMREP> audit session whenever successful;
02
03 Audit succeeded.
04
05 SYS@EMREP> !
06 [oracle@dbdream admin]$ sqlplus dbdream/dbdream@localhost/EMREP
07
08 SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 16 13:17:51 2015
09
10 Copyright (c) 1982, 2005, Oracle.  All rights reserved.
11
12 Connected to:
13 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
14 With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
15
16 DBDREAM@localhost/EMREP> exit
17 Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
18 With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
19 [oracle@dbdream admin]$ exit
20 exit
21
22 SYS@EMREP> select userid,userhost,terminal,returncode,spare1 from aud$;
23
24 USERID     USERHOST   TERMINAL   RETURNCODE SPARE1
25 ---------- ---------- ---------- ---------- ----------
26 DBDREAM    dbdream    pts/2            1017 oracle
27 DBDREAM    dbdream    pts/2               0 oracle

11g简化了审计的配置,但是AUD$表会越来越大,需要定期清理,而很多人是不会注意这些的,就会导致system表空间使用率很高。


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

The Algorithm Design Manual

The Algorithm Design Manual

Steven S Skiena / Springer / 2011-11-14 / GBP 55.07

....The most comprehensive guide to designing practical and efficient algorithms.... Written by a well-known algorithms researcher who received the IEEE Computer Science and Engineering Teaching Aw......一起来看看 《The Algorithm Design Manual》 这本书的介绍吧!

MD5 加密
MD5 加密

MD5 加密工具

Markdown 在线编辑器
Markdown 在线编辑器

Markdown 在线编辑器

HSV CMYK 转换工具
HSV CMYK 转换工具

HSV CMYK互换工具