内容简介：使用审计功能记录错误密码登陆信息
恢复完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表空间使用率很高。
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持 码农网
猜你喜欢:
- 代码审计--源代码审计思路
- Java代码审计丨某开源系统源码审计
- 【代码审计】PHP代码审计之CTF系列(1)
- 【JSP代码审计】某商城几处漏洞审计分析
- 【JSP代码审计】从代码审计的角度看系统接口的安全性
- 通读审计之AACMS
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
Powerful
Patty McCord / Missionday / 2018-1-25
Named by The Washington Post as one of the 11 Leadership Books to Read in 2018 When it comes to recruiting, motivating, and creating great teams, Patty McCord says most companies have it all wrong. Mc......一起来看看 《Powerful》 这本书的介绍吧!
