Home » RDBMS Server » Security » Lock user / ACCOUNT_STATUS in DBA_USERS (merged)
icon5.gif  Lock user / ACCOUNT_STATUS in DBA_USERS (merged) [message #233384] Thu, 26 April 2007 00:02 Go to next message
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
Re: lock user in oracle?? [message #233390 is a reply to message #233384] Thu, 26 April 2007 00:42 Go to previous messageGo to next message
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...
Re: possible values of account_status in dba_users table? [message #233400 is a reply to message #233384] Thu, 26 April 2007 01:09 Go to previous messageGo to next message
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

icon7.gif  Re: lock user in oracle?? [message #233403 is a reply to message #233390] Thu, 26 April 2007 01:12 Go to previous messageGo to next message
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


icon7.gif  Re: lock user in oracle?? [message #233430 is a reply to message #233390] Thu, 26 April 2007 02:23 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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..
icon7.gif  Re: lock user in oracle?? [message #233464 is a reply to message #233451] Thu, 26 April 2007 04:07 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
dba_profiles




Note: @Dipali >>> Very nice explanation.

Regards
Taj
Re: lock user in oracle?? [message #233475 is a reply to message #233465] Thu, 26 April 2007 04:46 Go to previous messageGo to next message
ketu0001
Messages: 7
Registered: April 2007
Junior Member
THANKS TAJ ...!
Re: lock user in oracle?? [message #233708 is a reply to message #233465] Fri, 27 April 2007 03:50 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Smile
Re: Lock user / ACCOUNT_STATUS in DBA_USERS (merged) [message #414829 is a reply to message #233384] Thu, 23 July 2009 13:17 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
how can I open an expired user?

Thanks
Ridhi
Re: Lock user / ACCOUNT_STATUS in DBA_USERS (merged) [message #414830 is a reply to message #233384] Thu, 23 July 2009 13:21 Go to previous message
BlackSwan
Messages: 22781
Registered: January 2009
Senior Member
http://www.lmgtfy.com/?q=oracle+how+can+I+open+an+expired+user
Previous Topic: How i can prevent login as os authintication
Next Topic: FGA - AUDIT FILES
Goto Forum:
  


Current Time: Tue Sep 16 08:52:44 CDT 2014

Total time taken to generate the page: 0.14786 seconds