共计 3664 个字符,预计需要花费 10 分钟才能阅读完成。
查系统默认的策略,连续验证 10 次错误帐户即会被锁
SQL> select resource_name, limit from dba_profiles where profile=’DEFAULT’;
RESOURCE_NAME LIMIT
——————————– —————————————-
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
RESOURCE_NAME LIMIT
——————————– —————————————-
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
16 rows selected.
查看用户被锁状态
SQL> select username,account_status from dba_users where username=’USER1′;
USERNAME ACCOUNT_STATUS
—————————— ——————————–
USER1 LOCKED(TIMED)
SQL> select name,lcount from user$ where name=’USER1′;
NAME LCOUNT
—————————— ———-
USER1 10
先处理问题,将验证错误次数改为不受限制,解锁用户
SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
Profile altered.
SQL> alter user user1 account unlock;
User altered.
再查看用户验证的错误次数,如果此帐户一直在验证,可以看到次数一直在增加
SQL> select name,lcount from user$ where name=’USER1′;
通过日志文件 /u01/app/Oracle/diag/tnslsnr/localhost/listener/alert/log.xml 追查请求来源 ip,但是效果不理想
1. 看不到请求的用户名,看不到请求结果,对请求来源 ip 判断可能有误
2. 日志过多,暂时想不到关键字过滤
[oracle@localhost adump]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 12-MAY-2016 11:46:39
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=iZ11y546tzlZ)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 – Production
Start Date 10-MAY-2016 09:44:40
Uptime 2 days 2 hr. 1 min. 59 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary…
[oracle@localhost ~]$ tail -f /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
<msg time=’2016-05-12T11:52:33.423+08:00′ org_id=’oracle’ comp_id=’tnslsnr’
type=’UNKNOWN’ level=’16’ host_id=’localhost’
host_addr=’10.174.70.172′>
<txt>12-MAY-2016 11:52:33 * (CONNECT_DATA=(SERVICE_NAME=orcl)(CID=(PROGRAM=C:\Program?Files??x86?\PremiumSoft\Navicat?Premium\navicat.exe)(HOST=HUJF-PC)(USER=hujf))) * (ADDRESS=(PROTOCOL=tcp)(HOST=110.82.160.106)(PORT=59584)) * establish * orcl * 0
</txt>
</msg>
设置格式,查 returncode 为 1017 的,可以很清楚看到验证的用户 (userid) 计算机名 (userhost,局域网有用) 请求来源 ip(comment$text)
SQL> set pagesize 100;
SQL> set linesize 150;
SQL> select sessionid,userid,userhost,comment$text,spare1,ntimestamp# from aud$ where returncode=1017;
53080 USER1
WORKGROUP\HUJF-PC
Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=110.82.160.106)(PORT=59584))
hujf
12-MAY-16 03.52.34.569085 AM
53085 SYSTEM
WORKGROUP\HUJF-PC
Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=110.82.160.106)(PORT=6720))
hujf
12-MAY-16 03.55.39.857892 AM
[oracle@localhost ~]$ oerr ora 28000
28000, 00000, “the account is locked”
// *Cause: The user has entered wrong password consequently for maximum
// number of times specified by the user’s profile parameter
// FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account
// *Action: Wait for PASSWORD_LOCK_TIME or contact DBA
[oracle@localhost ~]$ oerr ora 1017
01017, 00000, “invalid username/password; logon denied”
// *Cause:
// *Action:
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址:http://www.linuxidc.com/Linux/2016-05/131328.htm