oracle: unexpire and unlock accounts

Some of our customers’ applications are built around Oracle, so we have to fight the beast from time to time. Unfortunately, some of the surprizes the beast has to offer are quite random and rare, and due to this we tend to simply forget how we fixed and/or circumvented the issues previously.

As usual, google is your friend and one of the most valuable resources on the net we’ve found is www.orafaq.com and most notably its security FAQs [1]

So this is just an attempt of a small cheat sheat to help our overloaded brains 🙂

  1. expired and locked accounts – the basics
    Now, as of version 11g, Oracle has enabled account expiration per default for many vital accounts (such as SYSMAN, SYS, …). Quite a weird idea in my view, but who knows what hyper security things some wise engineer had in mind when doing so.

    Beware that we run Oracle under various Linux flavours, so things might be different for you.

    • become database admin
      First log into your host running oracle and become the oracle user.
      % sqlplus /nolog
      SQL> connect / as SYSDBA
      Connected
      SQL>
    • find out which accounts are expired
      select username, account_status from dba_users where ACCOUNT_STATUS LIKE '%EXPIRED%';
    • unexpire an account
      once an account has been expired, it can only be revived by assigning it a new password:
      ALTER USER scott IDENTIFIED BY password;
    • unlock an account
      ALTER USER scott ACCOUNT UNLOCK;
    • disable default password expiry [2]
      this all depends on the profile a user belongs to, to disable password expiry for all users assigned the default user profile do this:
      ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

  2. Enterprise Manager: unable to connect to instance
    One of the worst things that can happen in that course is if Enterprise Manager reports “Failed to connect to database instance: ORA-28001: the password has expired (DBD ERROR: OCISessionBegin)” and then simply rejects to work with the database.

    So far, we’ve found two accounts to be potential culprits for the problem: SYSMAN and DBSNMP

    • unexpire SYSMAN [3]
      Quite tricky, because you have to change things on two sides: First unexpire the account as explained above. And now tweak oracle configuration.
      As oracle user:
      1. unexpire and unlock the account as explained above
      2. % emctl stop dbconsole
      3. change into
        ORACLE_HOME/<HostName_SID>/sysman/config, for us this would be for example:
        % cd /opt/oracle/111/klotho_ABSDEV/sysman/config
      4. edit the emoms.properties file
        and change the oracle.sysman.eml.mntr.emdRepPwd property to the new password you gave the SYSMAN user.
        Then change the oracle.sysman.eml.mntr.emdRepPwdEncrypted property from TRUE to FALSE (sidenote: Oracle will revert to TRUE automagically once it is restarted).
      5. change into
        ORACLE_HOME/<HostName_SID>/sysman/emd, for us this would be for example:
        % cd /opt/oracle/111/klotho_ABSDEV/sysman/emd
      6. edit the target.xml file
        and edit those two properties:
        <Property NAME="UserName" VALUE="SYSMAN" ENCRYPTED="FALSE"/>
        <Property NAME="password" VALUE="TheNewPassword" ENCRYPTED="FALSE"/>
      7. % emctl start dbconsole
      8. under “normal” circumstances, everything should be fine now 🙂

So, as said above this is just a “small” cheat sheet for some annoyances we have met with the great oracle, of course there is much much more to know about the beast 😉

[1] http://www.orafaq.com/wiki/Oracle_database_Security_FAQ
[2] http://www.odi.ch/weblog/posting.php?posting=520
[3] http://www.articles.freemegazone.com/oracle-sysman-account-locked.php?ref=2

Spread the love

14
Leave a Reply

avatar
11 Comment threads
3 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
8 Comment authors
DanDanoracle: unexpire and unlock accounts – Roseltek IncExpired database password on SOA_INFRA - Fusion AppliedHow To Unlock An Expired User Account Oracle | files&click Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
Anonymous
Guest
Anonymous

You could do that if you need the new password to be different or the following seems to be a lot easier and I did it straight from toad…

Logon as valid sysdba user thats not locked

–verfiy account is expired
select username, account_status from dba_users order by username;

ALTER USER SYSMAN IDENTIFIED BY pickNewPassword;
ALTER USER SYSMAN ACCOUNT UNLOCK;
–make sure its now unlocked
select username, account_status from dba_users where username = ‘SYSMAN’;

ALTER USER SYSMAN IDENTIFIED BY PutYourOldPasswordBack;

This way to don’t have to fuss with all the other steps to establish new password.

LynC
Guest
LynC

That still leaves the account with an expiry date. i.e. it does not do the job.

Amby
Guest

To make the account not have an expiry date create a new profile that sets the PASSWORD_LIFE_TIME to UNLIMITED i.e.

CREATE PROFILE TEST_PRF LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED;

Modify the user account to use the new profile:

ALTER USER MYUSER PROFILE TEST_PRF;

This way the password will never expire for the users that uses this profile

homayun
Guest
homayun

Thanks very much, It worked well.

valiantvimal
Guest

Thank you bro..I learned about password expiry and its solution from here..thanks for sharing.

Regards,
Vimal.

Douglas Barry
Guest

I have been trying to open my account but is not fort opening.

leena
Guest
leena

my system account locked in oracle 12c
can plz tell me how to unlock it…

Kev
Guest
Kev

Thank you for posting this, saved my skin a few times!

imadjundi
Guest

this was really helpful
thanks for sharing

trackback

[…] oracle: unexpire and unlock accounts | riaschissl – Some of our customers’ applications are built around Oracle, so we have to fight the beast from time to time. Unfortunately, some of the surprizes the beast has to … […]

trackback

[…] had to figure out a quick way of “un-expiring” my passwords. I found a good blog posted here by Udo […]

trackback

[…] Source: oracle: unexpire and unlock accounts | riaschissl […]

Dan
Guest
Dan

Great layout and nice answer, a tweak to unexpire an account:

select ‘alter user “‘||d.username||'” identified by values ”’||u.password||”’;’ c
from dba_users d, sys.user$ u
where d.username = upper(‘&&username’)
and u.user# = d.user_id;

then

alter user “SCOTT” identified by values ‘F894844C34402B67’;

Dan
Guest
Dan

Nice layout and answer, another tweak to your unexpire answer:

select ‘alter user “‘||d.username||'” identified by values ”’||u.password||”’;’ c
from dba_users d, sys.user$ u
where d.username = upper(‘&&username’)
and u.user# = d.user_id;

then

alter user “SCOTT” identified by values ‘F894844C34402B67’;

Post Navigation