恢复完UAT环境，发现业务用户总被锁定，问谁都说自己的程序密码是对的，本来想写个触发器记录是谁总用错误的密码登陆数据库，发现这个数据库的审计没有关闭(11g默认审计功能是开启的)，是打开的。数据库版本11.2.0.4。
1 | SQL> show parameter audit |
4 | ------------------------------------ ----------- ------------------------------ |
5 | audit_file_dest string /u01/app/oracle/admin/PROD/adu |
7 | audit_sys_operations boolean FALSE |
8 | audit_syslog_level string |
在11g如果没有关闭掉审计的功能，默认是可以记录错误密码登陆信息的，很幸运的是，这个功能并没有被禁掉。那么查询AUD$表就能查询到错误密码登陆信息。以下是在没有做过任何设置的11.2.0.4.0版本的数据库中做的测试，先使用错误密码登陆数据库。
01 | [oracle@secdb1 admin]$ sqlplus dbdream/oracle@localhost/PROD |
03 | SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 16 11:48:17 2015 |
05 | Copyright (c) 1982, 2013, Oracle. All rights reserved. |
08 | ORA-01017: invalid username/password; logon denied |
12 | ORA-01017: invalid username/password; logon denied |
16 | ORA-01017: invalid username/password; logon denied |
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$; |
3 | USERID USERHOST TERMINAL RETURNCODE SPARE1 |
4 | ---------- ---------- ---------- ---------- ---------- |
5 | DBDREAM secdb1 pts/1 1017 oracle |
11g默认不止开启了错误密码登陆的审计，正常登录到数据库的连接也会被审计到，下面先通过正确的密码登陆数据库。
01 | [oracle@secdb1 ~]$ sqlplus dbdream/dbdream@localhost/PROD |
03 | SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 16 11:54:21 2015 |
05 | Copyright (c) 1982, 2013, Oracle. All rights reserved. |
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 |
查询AUD$表会发现，这个连接也被记录了。
1 | SQL> select userid,userhost,terminal,returncode,spare1 from aud$; |
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; |
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 |
11 | SYS@EMREP> show parameter audit |
14 | ------------------------------------ ----------- ------------------------------ |
15 | audit_file_dest string /u01/app/oracle/admin/EMREP/ad |
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; |
05 | SYS@EMREP> startup force |
06 | ORACLE instance started. |
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 |
15 | SYS@EMREP> show parameter audit |
18 | ------------------------------------ ----------- ------------------------------ |
19 | audit_file_dest string /u01/app/oracle/admin/EMREP/ad |
21 | audit_sys_operations boolean FALSE |
22 | audit_syslog_level string |
audit_trail是静态参数，修改后需要重启数据库才能生效。使用错误的密码登陆数据库，看看是否会被审计到。
01 | [oracle@dbdream admin]$ sqlplus dbdream/oracle@localhost/EMREP |
03 | SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 16 13:13:57 2015 |
05 | Copyright (c) 1982, 2005, Oracle. All rights reserved. |
08 | ORA-01017: invalid username/password; logon denied |
12 | ORA-01017: invalid username/password; logon denied |
16 | ORA-01017: invalid username/password; logon denied |
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$; |
10g版本的审计默认是不记录错误密码登陆的信息，需要手动设置。
1 | SYS@EMREP> audit session whenever not successful; |
再次使用错误密码登陆数据库，此时就会被记录下来。
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 |
05 | SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 16 13:15:04 2015 |
07 | Copyright (c) 1982, 2005, Oracle. All rights reserved. |
10 | ORA-01017: invalid username/password; logon denied |
14 | ORA-01017: invalid username/password; logon denied |
18 | ORA-01017: invalid username/password; logon denied |
20 | SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus |
22 | SYS@EMREP> select userid,userhost,terminal,returncode,spare1 from aud$; |
24 | USERID USERHOST TERMINAL RETURNCODE SPARE1 |
25 | ---------- ---------- ---------- ---------- ---------- |
26 | DBDREAM dbdream pts/2 1017 oracle |
那么正常登录到数据库是否会被审计记录下来呢？下面使用正确的密码登陆数据库。
01 | [oracle@dbdream admin]$ sqlplus dbdream/dbdream@localhost/EMREP |
03 | SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 16 13:16:07 2015 |
05 | Copyright (c) 1982, 2005, Oracle. All rights reserved. |
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 |
11 | DBDREAM@localhost/EMREP> |
13 | SYS@EMREP> select userid,userhost,terminal,returncode,spare1 from aud$; |
15 | USERID USERHOST TERMINAL RETURNCODE SPARE1 |
16 | ---------- ---------- ---------- ---------- ---------- |
17 | DBDREAM dbdream pts/2 1017 oracle |
查询发现正常登录数据库的操作并没有被记录下来，要想记录正常登录的信息，也需要手动配置。
01 | SYS@EMREP> audit session whenever successful; |
06 | [oracle@dbdream admin]$ sqlplus dbdream/dbdream@localhost/EMREP |
08 | SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 16 13:17:51 2015 |
10 | Copyright (c) 1982, 2005, Oracle. All rights reserved. |
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 |
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 |
22 | SYS@EMREP> select userid,userhost,terminal,returncode,spare1 from aud$; |
24 | USERID USERHOST TERMINAL RETURNCODE SPARE1 |
25 | ---------- ---------- ---------- ---------- ---------- |
26 | DBDREAM dbdream pts/2 1017 oracle |
27 | DBDREAM dbdream pts/2 0 oracle |
11g简化了审计的配置，但是AUD$表会越来越大，需要定期清理，而很多人是不会注意这些的，就会导致system表空间使用率很高。
建议继续学习：
- 一个登陆认证系统 (阅读：3450)
- Oracle中审计删除（DELETE）操作的触发器 (阅读：1788)
QQ技术交流群：445447336，欢迎加入！
扫一扫订阅我的微信号：IT技术博客大学习
[393] 招聘技巧一二