Search This Blog

Thursday, April 26, 2007

ACCOUNT_STATUS of ORACLE USER

My Signature Article
Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips



Normal STATUS for database user


1.
SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
OPEN

2.


When DBA want to lock any user to access for database.
"alter user [username] ACCOUNT LOCK;



SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
LOCKED

3.

When FAILED_LOGIN_ATTEMPTS parameter configure according to this whenever user give wrong PASSWORD more than FAILED_LOGIN_ATTEMPTS parameter that time user account is locked for TIMED.


SQL> alter profile default LIMIT failed_login_attempts 1;

Profile altered.

SQL> conn taj/wrongpassword
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn taj/taj
ERROR:
ORA-28000: the account is locked

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
LOCKED(TIMED)

4.


when PASSWORD_LIFE_TIME parameter value cross by database user and PASSWORD_GRACE_TIME is not specified then user account is EXPIRED.



SQL> alter user taj password expire;

User altered.

SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
EXPIRED


5.



when PASSWORD_LIFE_TIME or PASSWORD_LOCK_TIME value cross by database user then user account EXPIRE with LOCK.



SQL> alter user taj password expire account lock;

User altered.

SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
EXPIRED & LOCKED


6.


when PASSWORD_LIFE_TIME parameter is reached by user and PASSWORD_GRACE_TIME parameter configure then some password will be use according PASSWORD_GRACE_TIME parameter but account status show EXPIRE(GRACE)



09:53:38 SQL> alter profile default limit password_lock_time 1/1440;

Profile altered.

09:53:47 SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
OPEN

09:53:59 SQL> alter profile default limit password_grace_time 2/1440;

Profile altered.

09:54:14 SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
OPEN


09:55:03 SQL> conn taj/taj
ERROR:
ORA-28002: the password will expire within 0 days


Connected.

09:55:08 SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
EXPIRED(GRACE)

7.
10:11:10 SQL> alter profile default limit failed_login_attempts 1;

Profile altered.

10:11:28 SQL> alter user TAJ password expire;

User altered.

10:12:25 SQL> conn taj/wrongpassword
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
10:12:31 SQL> conn sys as sysdba
Enter password:
Connected.
10:12:37 SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
EXPIRED & LOCKED(TIMED)

10:12:40 SQL>

8.

10:14:04 SQL> alter profile default limit password_lock_time 30/86400;

Profile altered.

10:14:20 SQL> alter profile default limit password_grace_time 1/1440;

Profile altered.

10:14:38 SQL> alter profile default limit failed_login_attempts 1;

Profile altered.

10:14:45 SQL> conn taj/taj
ERROR:
ORA-28002: the password will expire within 0 days


Connected.
10:15:16 SQL> conn sys as sysdba
Enter password:
Connected.
10:15:28 SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
EXPIRED(GRACE)

10:15:32 SQL> conn taj/wrongpassword
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
10:15:41 SQL> conn sys as sysdba
Enter password:
Connected.
10:15:45 SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
EXPIRED(GRACE) & LOCKED(TIMED)

9.
10:16:37 SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
EXPIRED(GRACE)

10:16:41 SQL> alter user taj account lock;

User altered.

10:16:48 SQL> select ACCOUNT_STATUS from dba_users where username = 'TAJ';

ACCOUNT_STATUS
--------------------------------
EXPIRED(GRACE) & LOCKED


NOte: All Configuration depand on below parameters

FAILED_LOGIN_ATTEMPTS
PASSWORD_LOCK_TIME
PASSWORD_GRACE_TIME
PASSWORD_LIFE_TIME


6 comments:

Salih KM said...

Very informative article. Wish you all the best for you and your sites.

M.S.Taj said...

Thank You...


Regards
Taj

Anonymous said...

Hi Taj, Its very infrormative, I have small requirement here... One of my oracle service users got expired in 2 months because of the perameter setting for DEFAULT profile, now i want to bring them up and open. I can change the profile and then alter the users manually but not sure which user is atual and which user is dummy there are 100s of users..... is there any other way aound to get this done at one go.

Anonymous said...

and in addition, in terms of ground beef, 95/5, 85/15 and 80/20 are all just that, no matter
source (in terms of macro-nutrient comp).

Review my blog :: precision nutrition lean eating program review

Anonymous said...

We need to have to surport chinnese people in thier fight to get a fair country
so chia will likely be a democratic
nation

Here is my web site 2 Girls Teach Sex Tori Black

Anonymous said...

Explanation with example is very very good