开源中文网

您的位置: 首页 > 数据库应用 > Oracle > 正文

Account LOCKED(TIMED)

来源:  作者:

Recently I have met several times on 10g Release 2 that some users consistently get locked with status LOCKED(TIMED). One good example is with the DBSNMP and SYSMAN users, but more critical situation is that product accounts are locked which will down your traffic. 


I have tried to unlock the account using "alter user oracle account unlock",although the SQL command run successfully, you will find the user oracle is still locked when query the status of it " select USERNAME, ACCOUNT_STATUS from dba_users where USERNAME = 'ORACLE'; 


It turned out that the FAILED_LOGIN_ATTEMPTS attribute for the DEFAULT profile has been changed in 10.2.0.2 (actually 10.2.0.1 and above) from UNLIMITED to the value of 10. Well, that’s good from security point of view. On the other hand, this is really dangerous, especially during or after migrations while chances are high that some process will try to connect with wrong credentials. This can easily end up with a service outage because an application can’t connect. 

One way to resolve it is to change the DEFAULT profile. However, I would recommend leaving it 10 by default and, instead, create a new profile and assign the critical production users to this profile: 

CREATE PROFILE DEFAULT_10GR1 LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;ALTER USER [USERNAME] PROFILE DEFAULT_10GR1; 
The next step should be to review this policy with your security officer. By the way, this must be a substantive discussion – a production DBA should be keen to avoid service outages by any means, while a security officer’s priority is preventing unauthorized access. 




Appenix A (Got from metalink) 

Applies to: 
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.1 
Information in this document applies to any platform. 

Goal 
This document has been authored by an Oracle customer, and has not been subject to an independent technical review. 
Authored by: Syed Sabdar Hussain 
Title: Consultant – Oracle DBA & Oracle Apps DBA 
Company: Saudi Hollandi Bank 

An application user account, in one of our Oracle 10g Databases, is being locked every time. Below are our findings and solution to resolve the issue. 

Solution 
Details: 
Oracle Database Version: 10g R2 (10.2.0.1) 
Application User: APPUSR 
Error: ORA-28000: the account is locked 

Login as SYSDBA 

SQL> conn /as sysdba 

Check the APPSUSR account status. 

SQL> SELECT username, account_status FROM dba_users WHERE username= ‘APPUSR’; 
USERNAME ACCOUNT_STATUS PROFILE 
-------------------- -------------------- --------------- 
APPUSR LOCKED(TIMED) DEFAULT 

Here we can see the account status is LOCKED (TIMED) and the default user’s profile is DEFAULT. 

Check the resource limits of DEFAULT profile. 
SQL> SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT'; 

RESOURCE_NAME RESOURCE LIMIT 
-------------------------------- -------- ---------- 
COMPOSITE_LIMIT KERNEL UNLIMITED 
SESSIONS_PER_USER KERNEL UNLIMITED 
CPU_PER_SESSION KERNEL UNLIMITED 
CPU_PER_CALL KERNEL UNLIMITED 
LOGICAL_READS_PER_SESSION KERNEL UNLIMITED 
LOGICAL_READS_PER_CALL KERNEL UNLIMITED 
IDLE_TIME KERNEL UNLIMITED 
CONNECT_TIME KERNEL UNLIMITED 
PRIVATE_SGA KERNEL UNLIMITED 
FAILED_LOGIN_ATTEMPTS PASSWORD 10 
PASSWORD_LIFE_TIME PASSWORD UNLIMITED 
PASSWORD_REUSE_TIME PASSWORD UNLIMITED 
PASSWORD_REUSE_MAX PASSWORD UNLIMITED 
PASSWORD_VERIFY_FUNCTION PASSWORD NULL 
PASSWORD_LOCK_TIME PASSWORD UNLIMITED 
PASSWORD_GRACE_TIME PASSWORD UNLIMITED 

All resource limits for DEFAULT profile is set to UNLIMITED, but only for FAILED_LOGIN_ATTEMPTS attribute, it’s set to some value (10). Due to this the user account keeps getting locked(timed).When we check in the Oracle Documentations, it’s stated that FAILED_LOGIN_ATTEMPTS attribute for DEFAULT profile has been changed from 10.2.0.1 from UNLIMITED to 10. 

What we can do is, either we may need to change the resource limit for FAILED_LOGIN_ATTEMPTS attribute in DEFAULT profile, or create a new profile for that user with FAILED_LOGIN_ATTEMPTS attribute value set to UNLIMITED. But for security reasons, we will not tamper the DEFAULT profile, which is not recommended too. Then let’s go for creating a new profile and assign that profile to the user. 

Create a profile. 

SQL> CREATE PROFILE APPUSR_DEFAULT LIMIT 
2 COMPOSITE_LIMIT UNLIMITED 
3 SESSIONS_PER_USER UNLIMITED 
4 CPU_PER_SESSION UNLIMITED 
5 CPU_PER_CALL UNLIMITED 
6 LOGICAL_READS_PER_SESSION UNLIMITED 
7 LOGICAL_READS_PER_CALL UNLIMITED 
8 IDLE_TIME UNLIMITED 
9 CONNECT_TIME UNLIMITED 
10 PRIVATE_SGA UNLIMITED 
11 FAILED_LOGIN_ATTEMPTS UNLIMITED 
12 PASSWORD_LIFE_TIME UNLIMITED 
13 PASSWORD_REUSE_TIME UNLIMITED 
14 PASSWORD_REUSE_MAX UNLIMITED 
15 PASSWORD_VERIFY_FUNCTION NULL 
16 PASSWORD_LOCK_TIME UNLIMITED 
17 PASSWORD_GRACE_TIME UNLIMITED; 

Profile created. 

Assign the newly created profile to the user as default profile. 

SQL> ALTER USER appusr PROFILE appusr_default; 

User altered. 

Unlock the user account: 

SQL> ALTER USER appusr ACCOUNT UNLOCK; 

User altered. 

Now check again the status of APPUSR user. 

SQL> SELECT username, account_status FROM dba_users WHERE username= ‘APPUSR’; 
USERNAME ACCOUNT_STATUS PROFILE 
-------------------- -------------------- --------------- 
APPUSR OPEN APPUSR_DEFAULT 

Tags:Account LOCKED
关于开源中文网 - 联系我们 - 广告服务 - 网站地图 - 版权声明