How to change account_status in dba_users
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: How to change account_status in dba_users

  1. #1
    Join Date
    Jan 2001
    Posts
    138

    How to change account_status in dba_users

    I have a problem. I need to change account_status to open for one user whose account_status was set to EXPIRED by mistake (by assigning a profile with verify_function). If I knew the password I could chnage the password to the same password and be done with. But I don't know the password. Is there a way to manually update account_status column in dba_users to show it OPEN and not EXPIRED?

    Another user has account_status showing EXPIRED(GRACE). I need that user to show OPEN. Fortunaltely I know the password. I am guessing I can reset the password to the same password like alter user test identified by summer;

    Thanks for the help.

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Code:
    SQL> select account_status from dba_users where username='THOMAS';
    
    ACCOUNT_STATUS
    --------------------------------
    OPEN
    SQL> alter user thomas account lock;
    
    User altered.
    
    SQL> select account_status from dba_users where username='THOMAS';
    
    ACCOUNT_STATUS
    --------------------------------
    LOCKED
    
    SQL> alter user thomas account unlock;
    
    User altered.
    
    SQL> select account_status from dba_users where username='THOMAS';
    
    ACCOUNT_STATUS
    --------------------------------
    OPEN
    SQL> alter user thomas password expire;
    
    User altered.
    
    SQL> select account_status from dba_users where username='THOMAS';
    
    ACCOUNT_STATUS
    --------------------------------
    EXPIRED
    SQL> alter user thomas identified by thomas;
    
    User altered.
    
    SQL> select account_status from dba_users where username='THOMAS';
    
    ACCOUNT_STATUS
    --------------------------------
    OPEN
    Otherwise. no way...
    Last edited by Thomasps; 09-01-2006 at 06:46 AM.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Hay... This worked

    Code:
    SQL> select account_status from dba_users where username='THOMAS';
    
    ACCOUNT_STATUS
    --------------------------------
    EXPIRED
    
    SQL> select password from dba_users where username='THOMAS';
    
    PASSWORD
    ------------------------------
    063D8DE086C2860D
    
    SQL> alter user thomas identified by values '063D8DE086C2860D';
    
    User altered.
    
    SQL> select account_status from dba_users where username='THOMAS';
    
    ACCOUNT_STATUS
    --------------------------------
    OPEN
    SQL> conn thomas/thomas
    Connected.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  4. #4
    Join Date
    Jan 2001
    Posts
    138
    That is a problem. I can't reset password. This is a generic account that is used by cognos and if I reset password, it will need to be changed somewhere and we don't know where exactly it is harcoded. I have to be able to change the account status back to OPEN from EXPIRED.

  5. #5
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    The second one should work.. ???
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  6. #6
    Join Date
    Jan 2001
    Posts
    138
    I created a new account and expired password and tried your solution. it worked. Should work on any account right? The password values for my account in question that needs to be "un-expired" is 43BA7BBC412AE7CB. If I change the password like you had shown, it should show account_status as open, right?

    alter user test identified by values '43BA7BBC412AE7CB';

  7. #7
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Yes. but, you need not create a new account for this. Just change the password of the exisitng 'expired' account with 'values' option.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  8. #8
    Join Date
    Jan 2001
    Posts
    138
    Created the separate, new account to test it out.

    It worked. The account_status is now back to OPEN.

    Thanks for your help.

  9. #9
    It would still be wise to find out what the real password is for that account, and possilbly change it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width