oracle审计：获取用户密码错误登陆失败的主机名和IP地址

最新推荐文章于 2022-11-14 13:31:33 发布

cuankun4601

最新推荐文章于 2022-11-14 13:31:33 发布

阅读量2.4k

点赞数

文章标签：数据库

背景：因业务要求，DBA将oracle数据库应用账户（SCOTT）修改为新的密码。然而，该SCOTT对应了多个应用主机上的多个不同应用，由于应用运维用户对应用程序不熟悉，导致无法及时修改全部应用程序账号密码，应用程序账号一直尝试使用原来的旧密码去登陆数据库，导致oracle数据库的应用账号一直被锁：LOCKED(TIMED)。经查询审计表aud$,可以查到对应的主机名，但应用运维人员反馈主机名还是无法获取信息，需要提供ip地址。
以下提供方法获取ip地址。

0.查看审计参数信息

SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /opt/app/oracle/admin/oratest/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB

1.建表LOGON_TABLE ：

SQL> create table LOGON_TABLE as
2 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') logon_time,
3 sys_context('USERENV','SESSION_USER') username,
4 sys_context('USERENV','HOST') host,
5 sys_context('USERENV','OS_USER') os_user,
6 sys_context('USERENV','IP_ADDRESS') ip_address
7 from dual
8 /

Table created.

2.建触发器，每次登陆成功之后就把信息写入 LOGON_TABLE 表

SQL> create or replace trigger tri_logon after logon on database
2 begin
3 insert into LOGON_TABLE
4 ( logon_time,
5 username,
6 host,
7 os_user,
8 ip_address
9 )
10 values
11 ( to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
12 sys_context('USERENV', 'SESSION_USER'),
13 sys_context('USERENV','HOST'),
14 sys_context('USERENV','OS_USER'),
15 sys_context('USERENV','IP_ADDRESS'));
16 end;
17 /

Trigger created.

3.此时可以查看到该表中信息。

SQL> col LOGON_TIME for a30
SQL> col USERNAME for a15
SQL> col HOST for a20
SQL> col OS_USER for a15
SQL> col IP_ADDRESS for a20
SQL> select * from LOGON_TABLE order by LOGON_TIME where rownum<20;
LOGON_TIME USERNAME HOST OS_USER IP_ADDRESS
------------------------------ --------------- -------------------- --------------- --------------------
2017-04-02 13:34:29 SYS test-for-lihb oracle
2017-04-02 13:35:29 SYS test-for-lihb oracle
2017-04-02 13:36:29 SYS test-for-lihb oracle
2017-04-02 13:37:29 SYS test-for-lihb oracle
2017-04-02 13:37:40 DBSNMP test-for-lihb oracle 127.0.0.1
2017-04-02 13:37:40 DBSNMP test-for-lihb oracle 127.0.0.1
2017-04-02 13:38:30 SYS test-for-lihb oracle
2017-04-02 13:38:51 DBSNMP test-for-lihb oracle 127.0.0.1
2017-04-02 13:39:27 DBSNMP test-for-lihb oracle 127.0.0.1
2017-04-02 13:39:30 SYS test-for-lihb oracle
2017-04-02 13:40:30 SYS test-for-lihb oracle
2017-04-02 13:40:30 SYS test-for-lihb oracle
2017-04-02 13:41:30 SYS test-for-lihb oracle
2017-04-02 13:42:03 SYS test-for-lihb oracle
2017-04-02 13:42:30 SYS test-for-lihb oracle
2017-04-02 13:42:40 DBSNMP test-for-lihb oracle 127.0.0.1
2017-04-02 13:42:40 DBSNMP test-for-lihb oracle 127.0.0.1
2017-04-02 13:43:30 SYS test-for-lihb oracle
2017-04-02 13:43:51 DBSNMP test-for-lihb oracle 127.0.0.1

4.查看状态码为1017（错误密码）的审计信息

SQL> col USERID for a20
SQL> col USERHOST for a30
SQL> col TERMINAL for a30
SQL> col CLIENTID for a30
SQL> set linesize 150 pagesize 100
SQL> select userid, userhost, terminal, clientid from aud$ where returncode=1017;
no rows selected

5.将LOGON_TABLE，aud$清空用以录入尝试使用错误密码登陆的审计信息

SQL> truncate table LOGON_TABLE;
Table truncated.

SQL> truncate table aud$;
Table truncated.

SQL> select * from LOGON_TABLE order by LOGON_TIME;
no rows selected

SQL> select userid, userhost, terminal, clientid from aud$ where returncode=1017;
no rows selected

6.重新查询aud$可以获取【错误密码】登陆的用户信息，可以知道主机名为【WORKGROUP\LIHB02】。将scott用户改为原密码之后，在LOGON_TABLE获取到主机名【WORKGROUP\LIHB02】对应的ip地址为【192.168.209.32】

SQL> select userid, userhost, terminal, clientid from aud$ where returncode=1017;
USERID USERHOST TERMINAL CLIENTID
-------------------- ---------------------------------------- ------------------------------ ------------------------------
SCOTT WORKGROUP\LIHB02 LIHB02

SQL> select * from LOGON_TABLE order by LOGON_TIME;
LOGON_TIME USERNAME HOST OS_USER IP_ADDRESS
------------------------------ --------------- -------------------- --------------- --------------------
2017-04-02 13:49:31 SYS test-for-lihb oracle
2017-04-02 13:50:05 SCOTT WORKGROUP\LIHB02 lenovo 192.168.209.32
2017-04-02 13:50:05 SCOTT WORKGROUP\LIHB02 lenovo 192.168.209.32

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31441616/viewspace-2140633/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31441616/viewspace-2140633/