天天看點
傳回

oracle登入時報錯1017,Oracle 審計失敗的使用者登陸(Oracle audit)

對于線上交易系統，且Oracle使用者在使用預設的profile的情形下，多使用者共享相同的資料庫使用者及密碼，任意使用者輸入錯誤密碼累計達到10次以上，其帳戶會被自動鎖定使得交易被迫臨時終止将産生不小的損失。故有必要對那些失敗的帳戶登陸進行分析以預估是否存在惡意***等。Oracle提供了審計功能用于審計那些失敗的Oracle使用者登陸來進行風險評估。本文即是描述如何開啟審計失敗的使用者登陸。本文不涉及審計的具體的描述資訊，僅僅描述如何審計失敗的使用者登陸。詳細完整的審計大家可以參考Oracle Database Security Guide。

1、帳戶被鎖定的情形通常情況下，帳戶可以由DBA手動鎖定，也可能是由于錯誤的密碼輸入次數超出了profile中failed_login_attempts 次數的限制而被鎖定。

a、手動鎖定的情形

[email protected]> select username,account_status,lock_date from dba_users where username like'USR%';

USERNAME ACCOUNT_STATUS LOCK_DATE

------------------------------ -------------------------------- -----------------

USR2 OPEN

USR1 OPEN

[email protected]> alteruser usr1 account lock;

[email protected]> select username,account_status,lock_date from dba_users where username like'USR%';

USERNAME ACCOUNT_STATUS LOCK_DATE

------------------------------ -------------------------------- -----------------

USR2 OPEN

USR1 LOCKED 20131023 16:37:37

b、登陸失敗超出的情形

[email protected]> selectname,lcount fromuser$ wherename='USR2';

NAME LCOUNT

------------------------------ ----------

USR2 10

[email protected]> select username,account_status,lock_date from dba_users where username like'USR%';

USERNAME ACCOUNT_STATUS LOCK_DATE

-------------- ------------------ -----------------

USR2 LOCKED(TIMED) 20131023 16:41:48 -->使用者usr2登陸10次之後帳戶被鎖定，其狀态不同于手動鎖定的使用者，為LOCKED(TIMED)

USR1 LOCKED 20131023 16:37:37a、手動鎖定的情形

[email protected]> select username,account_status,lock_date from dba_users where username like 'USR%';

USERNAME ACCOUNT_STATUS LOCK_DATE

------------------------------ -------------------------------- -----------------

USR2 OPEN

USR1 OPEN

[email protected]> alter user usr1 account lock;

[email protected]> select username,account_status,lock_date from dba_users where username like 'USR%';

USERNAME ACCOUNT_STATUS LOCK_DATE

------------------------------ -------------------------------- -----------------

USR2 OPEN

USR1 LOCKED 20131023 16:37:37

b、登陸失敗超出的情形

[email protected]> select name,lcount from user$ where name='USR2';

NAME LCOUNT

------------------------------ ----------

USR2 10

[email protected]> select username,account_status,lock_date from dba_users where username like 'USR%';

USERNAME ACCOUNT_STATUS LOCK_DATE

-------------- ------------------ -----------------

USR2 LOCKED(TIMED) 20131023 16:41:48 -->使用者usr2登陸10次之後帳戶被鎖定，其狀态不同于手動鎖定的使用者，為LOCKED(TIMED)

USR1 LOCKED 20131023 16:37:37

2、如何開啟審計失敗的使用者登陸開啟審計需要做如下設定

a、設定參數 audit_trail = { none | os | db [, extended] | xml [, extended] }

b、設定參數 audit_file_dest = ''

c、開啟登陸失敗審計 audit session whenever not successful;

d、執行下面的SQL來檢視那些使用者經曆了登陸失敗的情形

select userid, userhost, terminal, clientid from aud$ where returncode=1017;

關于參數audit_trail，

當值為DB時，非sys帳戶的審計資訊都會被記錄到表SYS.AUD$，會占用system表空間，存在資源占用問題，當然也可将其部署到非系統表空間。sys帳戶登陸成功與失敗都會生成審計檔案。

當值為OS時，所有的審計記錄被寫入到作業系統檔案，對于高度安全的資料庫，Oracle建議采用該設定，理由很簡單，高度安全，寫入DB的話，整個系統忙得不亦樂乎。

如果資料庫處于隻讀模式且該參數值為DB時，Oracle 内部設定audit_trail為OS，細節可檢視alert log。其餘的幾個值可參考Oracle Database Reference。

3、示範配置審計登陸失敗(oracle 10g)

g[email protected]> select * from v$version where rownum<2;

BANNER

----------------------------------------------------------------

Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

--Oracle 10g下目前資料庫的配置，如下，也是預設配置

[email protected]> show parameter audit

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

audit_file_dest string /users/oracle/OraHome10g/rdbms

/audit

audit_sys_operations boolean FALSE

audit_syslog_level string

audit_trail string NONE

--下面修改存儲審計檔案位置

[email protected]> ho mkdir -p /u02/database/SYBO2SZ/audit

goex_a[email protected]> alter system set audit_trail='DB' scope=spfile;

[email protected]> alter system set audit_file_dest='/u02/database/SYBO2SZ/audit' scope=spfile;

[email protected]> audit session whenever not successful;

[email protected]> conn / as sysdba

[email protected]> shutdown immediate;

[email protected]> startup

[email protected]> ho ls /u02/database/SYBO2SZ/audit

C:\Users\robinson.cheng>sqlplus scott/[email protected] --嘗試使用錯誤的密碼從用戶端來登陸

[email protected]> select userid, userhost, terminal from aud$ where returncode=1017;

USERID USERHOST TERMINAL

------------------------------ ------------------------------ ------------------------------

SCOTT TRADESZ\DEVELOPERPC01 DEVELOPERPC01

SCOTT TRADESZ\DEVELOPERPC01 DEVELOPERPC01

SCOTT TRADESZ\DEVELOPERPC01 DEVELOPERPC01

USR2 SZDB pts/1

USR2 SZDB pts/1

--有關具體的審計生成的OS檔案參考接下來的示範g[email protected]> select * from v$version where rownum<2;

BANNER

----------------------------------------------------------------

Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

--Oracle 10g下目前資料庫的配置，如下，也是預設配置

[email protected]> show parameter audit

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

audit_file_dest string /users/oracle/OraHome10g/rdbms

/audit

audit_sys_operations boolean FALSE

audit_syslog_level string

audit_trail string NONE

--下面修改存儲審計檔案位置

[email protected]> ho mkdir -p /u02/database/SYBO2SZ/audit

goex_a[email protected]> alter system set audit_trail='DB' scope=spfile;

[email protected]> alter system set audit_file_dest='/u02/database/SYBO2SZ/audit' scope=spfile;

[email protected]> audit session whenever not successful;

[email protected]> conn / as sysdba

[email protected]> shutdown immediate;

[email protected]> startup

[email protected]> ho ls /u02/database/SYBO2SZ/audit

C:\Users\robinson.cheng>sqlplus scott/[email protected] --嘗試使用錯誤的密碼從用戶端來登陸

[email protected]> select userid, userhost, terminal from aud$ where returncode=1017;

USERID USERHOST TERMINAL

------------------------------ ------------------------------ ------------------------------

SCOTT TRADESZ\DEVELOPERPC01 DEVELOPERPC01

SCOTT TRADESZ\DEVELOPERPC01 DEVELOPERPC01

SCOTT TRADESZ\DEVELOPERPC01 DEVELOPERPC01

USR2 SZDB pts/1

USR2 SZDB pts/1

--有關具體的審計生成的OS檔案參考接下來的示範

4、示範配置審計登陸失敗(oracle 11g)

--Oracle 11g下，預設已經開啟了審計功能，也就是說如果審計失敗的登陸帳戶，無須單獨執行audit session whenever not successful;

[email protected]> select * from v$version where rownum<2;

BANNER

-------------------------------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

[email protected]> show parameter audit

NAME TYPE VALUE

------------------------------------ --------------------------------- ------------------------------

audit_file_dest string /u03/database/usbo/adump

audit_sys_operations boolean FALSE

audit_syslog_level string

audit_trail string DB

--下面是審計産生的檔案

[email protected]> ho ls -hltr /u03/database/usbo/adump |tail -2

-rw-r----- 1 oracle asmadmin 758 Oct 21 16:29 usbo_ora_4502_1.aud

-rw-r----- 1 oracle asmadmin 763 Oct 21 16:49 usbo_ora_5652_1.aud

--使用系統使用者登陸

C:\Users\robinson.cheng>sqlplus sys/[email protected] as sysdba

[email protected]> ho ls -hltr /u03/database/usbo/adump |tail -2

-rw-r----- 1 oracle asmadmin 763 Oct 21 16:49 usbo_ora_5652_1.aud

-rw-r----- 1 oracle asmadmin 773 Oct 22 15:41 usbo_ora_13497_1.aud

--系統使用者登陸被審計，審計檔案中給出了比較詳細的描述

sy[email protected]> ho more /u03/database/usbo/adump/usbo_ora_13497_1.aud

Audit file /u03/database/usbo/adump/usbo_ora_13497_1.aud

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

andReal Application Testing options

ORACLE_HOME = /u01/app/oracle/db_1

System name: Linux

Node name: linux1.orasrv.com

Release: 2.6.18-194.el5PAE

Version: #1 SMP Mon Mar 29 20:19:03 EDT 2010

Machine: i686

Instance name: usbo

Redo thread mounted by this instance: 1

Oracle process number: 31

Unix process pid: 13497, p_w_picpath: [email protected]

Tue Oct 22 15:41:45 2013 +08:00

LENGTH : '180'

ACTION :[7] 'CONNECT'

DATABASEUSER:[3] 'sys'

PRIVILEGE :[6] 'SYSDBA'

CLIENT USER:[14] 'Robinson.Cheng'

CLIENT TERMINAL:[13] 'DEVELOPERPC01'

STATUS:[1] '0'---->登陸成功的狀态碼

DBID:[10] '3456778221'

C:\Users\robinson.cheng>sqlplus sys/[email protected] as sysdba

[[email protected] adump]$ more usbo_ora_13677_1.aud

Audit file /u03/database/usbo/adump/usbo_ora_13677_1.aud

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

andReal Application Testing options

ORACLE_HOME = /u01/app/oracle/db_1

System name: Linux

Node name: linux1.orasrv.com

Release: 2.6.18-194.el5PAE

Version: #1 SMP Mon Mar 29 20:19:03 EDT 2010

Machine: i686

Instance name: usbo

Redo thread mounted by this instance: 1

Oracle process number: 31

Unix process pid: 13677, p_w_picpath: [email protected]

Tue Oct 22 15:44:59 2013 +08:00

LENGTH : '181'

ACTION :[7] 'CONNECT'

DATABASEUSER:[3] 'sys'

PRIVILEGE :[4] 'NONE'

CLIENT USER:[14] 'Robinson.Cheng'

CLIENT TERMINAL:[13] 'DEVELOPERPC01'

STATUS:[4] '1017'---->登陸失敗的狀态碼1017

DBID:[10] '3456778221'

--下面使用普通的帳戶登陸，沒有相應的os審計檔案，但是被添加到了表SYS.AUD$

C:\Users\robinson.cheng>sqlplus scott/[email protected]

--Author : Leshami

--Blog : http://blog.csdn.net/leshami

[email protected]> select sessionid,userid,userhost,comment$text,spare1,ntimestamp# from aud$ where returncode=1017;

SESSIONID USERID USERHOST COMMENT$TEXT SPARE1 NTIMESTAMP#

---------- ------ ------------------------- ---------------------------------------- ------------------ -------------------------------

1470011 SCOTT TRADESZ\DEVELOPERPC01 Authenticated by: DATABASE; Client addre Robinson.Cheng 21-OCT-13 08.51.15.528497 AM

ss: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168

.7.133)(PORT=53432))

1480153 SCOTT TRADESZ\DEVELOPERPC01 Authenticated by: DATABASE; Client addre Robinson.Cheng 22-OCT-13 08.06.49.012661 AM

ss: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168

.7.133)(PORT=60613))

1480154 SCOTT TRADESZ\DEVELOPERPC01 Authenticated by: DATABASE; Client addre Robinson.Cheng 22-OCT-13 08.09.41.927143 AM

ss: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168

.7.133)(PORT=60622))--Oracle 11g下，預設已經開啟了審計功能，也就是說如果審計失敗的登陸帳戶，無須單獨執行audit session whenever not successful;

[email protected]> select * from v$version where rownum<2;

BANNER

-------------------------------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

[email protected]> show parameter audit

NAME TYPE VALUE

------------------------------------ --------------------------------- ------------------------------

audit_file_dest string /u03/database/usbo/adump

audit_sys_operations boolean FALSE

audit_syslog_level string

audit_trail string DB

--下面是審計産生的檔案

[email protected]> ho ls -hltr /u03/database/usbo/adump |tail -2

-rw-r----- 1 oracle asmadmin 758 Oct 21 16:29 usbo_ora_4502_1.aud

-rw-r----- 1 oracle asmadmin 763 Oct 21 16:49 usbo_ora_5652_1.aud

--使用系統使用者登陸

C:\Users\robinson.cheng>sqlplus sys/[email protected] as sysdba

[email protected]> ho ls -hltr /u03/database/usbo/adump |tail -2

-rw-r----- 1 oracle asmadmin 763 Oct 21 16:49 usbo_ora_5652_1.aud

-rw-r----- 1 oracle asmadmin 773 Oct 22 15:41 usbo_ora_13497_1.aud

--系統使用者登陸被審計，審計檔案中給出了比較詳細的描述

sy[email protected]> ho more /u03/database/usbo/adump/usbo_ora_13497_1.aud

Audit file /u03/database/usbo/adump/usbo_ora_13497_1.aud

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/db_1

System name: Linux

Node name: linux1.orasrv.com

Release: 2.6.18-194.el5PAE

Version: #1 SMP Mon Mar 29 20:19:03 EDT 2010

Machine: i686

Instance name: usbo

Redo thread mounted by this instance: 1

Oracle process number: 31

Unix process pid: 13497, p_w_picpath: [email protected]

Tue Oct 22 15:41:45 2013 +08:00

LENGTH : '180'

ACTION :[7] 'CONNECT'

DATABASE USER:[3] 'sys'

PRIVILEGE :[6] 'SYSDBA'

CLIENT USER:[14] 'Robinson.Cheng'

CLIENT TERMINAL:[13] 'DEVELOPERPC01'

STATUS:[1] '0' ---->登陸成功的狀态碼

DBID:[10] '3456778221'

C:\Users\robinson.cheng>sqlplus sys/[email protected] as sysdba

[[email protected] adump]$ more usbo_ora_13677_1.aud

Audit file /u03/database/usbo/adump/usbo_ora_13677_1.aud

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/db_1

System name: Linux

Node name: linux1.orasrv.com

Release: 2.6.18-194.el5PAE

Version: #1 SMP Mon Mar 29 20:19:03 EDT 2010

Machine: i686

Instance name: usbo

Redo thread mounted by this instance: 1

Oracle process number: 31

Unix process pid: 13677, p_w_picpath: [email protected]

Tue Oct 22 15:44:59 2013 +08:00

LENGTH : '181'

ACTION :[7] 'CONNECT'

DATABASE USER:[3] 'sys'

PRIVILEGE :[4] 'NONE'

CLIENT USER:[14] 'Robinson.Cheng'

CLIENT TERMINAL:[13] 'DEVELOPERPC01'

STATUS:[4] '1017' ---->登陸失敗的狀态碼1017

DBID:[10] '3456778221'

--下面使用普通的帳戶登陸，沒有相應的os審計檔案，但是被添加到了表SYS.AUD$

C:\Users\robinson.cheng>sqlplus scott/[email protected]

--Author : Leshami

--Blog : http://blog.csdn.net/leshami

[email protected]> select sessionid,userid,userhost,comment$text,spare1,ntimestamp# from aud$ where returncode=1017;

SESSIONID USERID USERHOST COMMENT$TEXT SPARE1 NTIMESTAMP#

---------- ------ ------------------------- ---------------------------------------- ------------------ -------------------------------

1470011 SCOTT TRADESZ\DEVELOPERPC01 Authenticated by: DATABASE; Client addre Robinson.Cheng 21-OCT-13 08.51.15.528497 AM

ss: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168

.7.133)(PORT=53432))

1480153 SCOTT TRADESZ\DEVELOPERPC01 Authenticated by: DATABASE; Client addre Robinson.Cheng 22-OCT-13 08.06.49.012661 AM

ss: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168

.7.133)(PORT=60613))

1480154 SCOTT TRADESZ\DEVELOPERPC01 Authenticated by: DATABASE; Client addre Robinson.Cheng 22-OCT-13 08.09.41.927143 AM

ss: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168

.7.133)(PORT=60622))

5、使用過程分析失敗登陸的審計記錄

CREATEORREPLACEPROCEDURE auditlogin (since VARCHAR2, times PLS_INTEGER)

IS

user_id VARCHAR2 (20);

CURSOR c1

IS

SELECT userid, COUNT (*)

FROM sys.aud$

WHERE returncode = '1017'AND ntimestamp# >= TO_DATE (since, 'yyyy-mm-dd')

GROUPBY userid;

CURSOR c2

IS

SELECT userhost, terminal, TO_CHAR (ntimestamp#, 'YYYY-MM-DD:HH24:MI:SS')

FROM sys.aud$

WHERE returncode = '1017'AND ntimestamp# >= TO_DATE (since, 'yyyy-mm-dd') AND userid = user_id;

ct PLS_INTEGER;

v_userhost VARCHAR2 (40);

v_terminal VARCHAR (40);

v_date VARCHAR2 (40);

BEGIN

OPEN c1;

DBMS_OUTPUT.enable (1024000);

LOOP

FETCH c1

INTO user_id, ct;

EXIT WHEN c1%NOTFOUND;

IF (ct >= times)

THEN

DBMS_OUTPUT.put_line ('USER BROKEN ALARM:' || user_id);

OPEN c2;

LOOP

FETCH c2

INTO v_userhost, v_terminal, v_date;

DBMS_OUTPUT.put_line (CHR (9) || 'HOST:' || v_userhost || ',TERM:' || v_terminal || ',TIME:' || v_date);

EXIT WHEN c2%NOTFOUND;

END LOOP;

CLOSE c2;

END IF;

END LOOP;

CLOSE c1;

END;

/

[email protected]> exec auditlogin('2013-10-22',2);

USER BROKEN ALARM:SCOTT

HOST:TRADESZ\DEVELOPERPC01,TERM:DEVELOPERPC01,TIME:2013-10-22:08:06:49

HOST:TRADESZ\DEVELOPERPC01,TERM:DEVELOPERPC01,TIME:2013-10-22:08:09:41

HOST:linux1.orasrv.com,TERM:pts/1,TIME:2013-10-23:08:58:34

HOST:linux1.orasrv.com,TERM:pts/1,TIME:2013-10-23:08:58:34

USER BROKEN ALARM:USR1

HOST:linux1.orasrv.com,TERM:pts/1,TIME:2013-10-23:09:01:36

HOST:linux1.orasrv.com,TERM:pts/1,TIME:2013-10-23:09:11:13

HOST:linux1.orasrv.com,TERM:pts/1,TIME:2013-10-23:09:11:13

oracle登入時報錯1017
上一篇: oracle登入時報錯1017,ORACLE 登入失敗使用者的IP位址
下一篇: oracle登入時報錯1017,Oracle記錄登入失敗的觸發器

繼續閱讀