OraFAQ Forum: Security » Lock user / ACCOUNT_STATUS in DBA

Home » RDBMS Server » Security » Lock user / ACCOUNT_STATUS in DBA_USERS (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Lock user / ACCOUNT_STATUS in DBA_USERS (merged) [message #233384]

Thu, 26 April 2007 00:02

ketu0001
Messages: 7
Registered: April 2007

Junior Member

How d 2 things are differentiated internally?
1.User get locked because of login attempts with wrong password.
2. User is revoked by administrator.

What all are the possible values of account_status in dba_users table?

Thanks

Report message to a moderator

Re: lock user in oracle?? [message #233390 is a reply to message #233384]

Thu, 26 April 2007 00:42

Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India

Senior Member

hi ketu,
U can practically see the effect by trying it yourself..

1)
In oracle 10g,
Suppose, we have FAILED_LOGIN_ATTEMPTS = 10
and PASSWORD_LOCK_TIME = 5.

Means, you can try 10 times to login.. If you tried 10 times and could not login because of incorrect password then, your account will be locked. In this case, the User tab of EM as well as the dba_users table will show the user as Locked(Timed).
Dba then can unlock such users or it will be autimatically unlocked after days specified in PASSWORD_LOCK_TIME..

2)
For your second question, I am not exactly get what you are asking..
If you are asking about 'revoking the privilage to connect to the oracle from the user' then, it's a differet case.
In that case, user account is unlocked but it's not having CREATE SESSION system privilage. So when such user tries to login, the error about that 'insufficient privilage, logon denied ' (ORA-01045) will be returned..

Hope this helps you clear your concept. Try it practically yourself by creating a dummy user..

Regards...

Report message to a moderator

Re: possible values of account_status in dba_users table? [message #233400 is a reply to message #233384]

Thu, 26 April 2007 01:09

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

HI,
What all are the possible values of account_status in dba_users table?


ACCOUNT_STATUS VARCHAR2(32) NOT NULL Account status: 

OPEN



EXPIRED

EXPIRED(GRACE)

LOCKED(TIMED)

LOCKED

EXPIRED & LOCKED(TIMED)

EXPIRED(GRACE) & LOCKED(TIMED)

EXPIRED & LOCKED

EXPIRED(GRACE) & LOCKED
 

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

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

2.

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

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

3.
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.
SQL> alter user taj password expire;

User altered.

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

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


5.
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.
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:10:48 SQL> alter profile default limit failed_login_attempt 1;
alter profile default limit failed_login_attempt 1
                            *
ERROR at line 1:
ORA-02376: invalid or redundant resource


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

[Updated on: Thu, 26 April 2007 01:19]

Report message to a moderator

Re: lock user in oracle?? [message #233403 is a reply to message #233390]

Thu, 26 April 2007 01:12

ketu0001
Messages: 7
Registered: April 2007

Junior Member

Hi Dipali,

Thanks a lot.
Can u please tell me the significance of following values:
ACCOUNT_STATUS VARCHAR2(32) NOT NULL Account status:
OPEN
EXPIRED
EXPIRED(GRACE)
LOCKED(TIMED)
LOCKED
EXPIRED & LOCKED(TIMED)
EXPIRED(GRACE) & LOCKED(TIMED)
EXPIRED & LOCKED
EXPIRED(GRACE) & LOCKED

These are the possible values for account_status column in dba_users table. I got these values from link http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4174.htm

U told about locked (timed) - it is when account get locked bcoz of login attempts with wrong password.

I think "LOCKED" is when account is revoked by administrator, which I had asked in 2nd point.

What about remaining?

Please reply.

Regards
Ketu

Report message to a moderator

Re: lock user in oracle?? [message #233430 is a reply to message #233390]

Thu, 26 April 2007 02:23

ketu0001
Messages: 7
Registered: April 2007

Junior Member

Hi Mohammad Taj,

Thanks a lot for such a detailed explanation.

Regards
Ketu.

[Updated on: Thu, 26 April 2007 02:25]

Report message to a moderator

Re: lock user in oracle?? [message #233451 is a reply to message #233403]

Thu, 26 April 2007 03:15

Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India

Senior Member

hi ketu,

The status of user is decided based on the value of the options defined when you create the profile. They are,

FAILED_LOGIN_ATTEMPTS : Means for how many times you can try to login.. After these many sequential unsuccessful tris to login, the user account will be locked for the days specified in PASSWORD_LOCK_TIME. (i.e. LOCKED (TIMED))

PASSWORD_LOCK_TIME : for this much amount of time, your password wil be locked, if your account is locked because you are failed to login successfully and

PASSWORD_GRACE_TIME : the number of days, after your password expires. During that time, user can use that password, but reminded to change password.

PASSWORD_LIFE_TIME : the number of days, the password can remain in force.

So all the possible values are decided by these parameters
OPEN : you can use this a/c to login
EXPIRED: a/c is expired as you have tried to unsuccessfully login for 3 times..
EXPIRED(GRACE): Means, a/c is expried but grace is given to change the password
LOCKED(TIMED): a/c is locked as you have tried to unsuccessfully login for FAILED_LOGIN_ATTEMPTS times..

EXPIRED(GRACE) & LOCKED(TIMED)
EXPIRED & LOCKED
EXPIRED(GRACE) & LOCKED
These are when above situations occurs simulteneously..

Regards..

Report message to a moderator

Re: lock user in oracle?? [message #233464 is a reply to message #233451]

Thu, 26 April 2007 04:07

ketu0001
Messages: 7
Registered: April 2007

Junior Member

THANKS A LOT DIPALI...!
You explained the concept very nicely.

Can u please tell me IN WHICH TABLE FOLLOWING PARAMETERS ARE LISTED:
PASSWORD_LOCK_TIME, PASSWORD_GRACE_TIME, PASSWORD_LIFE_TIME, FAILED_LOGIN_ATTEMPTS.

desc dba_users gives me following output:
SQL> desc dba_users
Name Null? Type
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)

[Updated on: Thu, 26 April 2007 04:11]

Report message to a moderator

Re: lock user in oracle?? [message #233465 is a reply to message #233464]

Thu, 26 April 2007 04:15

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Can u please tell me how to set the values for these parameters

Are you read my previous post ?

http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_6010.htm

and check how many profile is created in your database