内容简介：使用审计功能记录错误密码登陆信息
恢复完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
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
首席产品官1 从新手到行家
车马 / 机械工业出版社 / 2018-9-25 / 79
《首席产品官》共2册，旨在为产品新人成长为产品行家，产品白领成长为产品金领，最后成长为首席产品官（CPO）提供产品认知、能力体系、成长方法三个维度的全方位指导。 作者在互联网领域从业近20年，是中国早期的互联网产品经理，曾是周鸿祎旗下“3721”的产品经理，担任CPO和CEO多年。作者将自己多年来的产品经验体系化，锤炼出了“产品人的能力杠铃模型”（简称“杠铃模型”），简洁、直观、兼容性好、实......一起来看看 《首席产品官1 从新手到行家》 这本书的介绍吧!
