This discussion is archived
6 Replies Latest reply: Dec 21, 2012 8:58 AM by Sean Warden RSS

User account management on 11.2.0.3 standby

889328 Newbie
Currently Being Moderated
Hi,

Just came across weird thing in 11.2.0.3 release. If standby database is open in read only with redo apply and you try to connect to standby database with wrong password , your account gets locked.

When I checked dba_user view on standby database it showed user is open (obvious as on primary it is open). On primary I was able to connect with correct password but when tried on standby it gave error account locked.

When fired alter user <username> account unlock ; on standby it said ORA-28015 account unlocked.

This implies in 11.2.0.3 user account status is maintained somewhere else as well. Do anyone knows where?

Tried same in 11.2.0.2, it does not locks the account on standby at all even after repeated wrong password, also does not allow to run alter user <username> account unlock ; command .
  • 1. Re: User account management on 11.2.0.3 standby
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    Hi;

    I suggest close your issue here as answered than move your issue Forum Home » High Availability » DataGuard which is DG dedicated forum site.

    Regard
    Helios
  • 2. Re: User account management on 11.2.0.3 standby
    889328 Newbie
    Currently Being Moderated
    Don't think this is dataguard issue, so moving to DG forum wont be right. This is general database admin query hence raised here.
  • 3. Re: User account management on 11.2.0.3 standby
    mseberg Guru
    Currently Being Moderated
    Sid;

    I would perform the account unlock action on the primary database. And maybe a log switch before testing again.

    Please consider posting your results as your issue is new.

    Best Regards

    mseberg
  • 4. Re: User account management on 11.2.0.3 standby
    CKPT Guru
    Currently Being Moderated
    Sid wrote:
    Hi,

    Just came across weird thing in 11.2.0.3 release. If standby database is open in read only with redo apply and you try to connect to standby database with wrong password , your account gets locked.

    When I checked dba_user view on standby database it showed user is open (obvious as on primary it is open). On primary I was able to connect with correct password but when tried on standby it gave error account locked.

    When fired alter user <username> account unlock ; on standby it said ORA-28015 account unlocked.

    This implies in 11.2.0.3 user account status is maintained somewhere else as well. Do anyone knows where?

    Tried same in 11.2.0.2, it does not locks the account on standby at all even after repeated wrong password, also does not allow to run alter user <username> account unlock ; command .
    Check this
    ORA-28015     Account unlocked, but the database is open for read-only access
    Cause:     If this database is part of a Data Guard configuration, perform the account unlock action on the primary database.
    Action:     The database or pluggable database was open for read-only access. Although the account was successfully unlocked, this account status change only affected the current database instance.
  • 5. Re: User account management on 11.2.0.3 standby
    jbargallo Newbie
    Currently Being Moderated
    Hi, had the same issue here: Primary DB shows account is open and DG/Standby also shows account_status open on dba_users view, but when trying to connect with that user on standby instance says account is locked.

    If you do 'alter user xxx account lock' on primary db you see the change is transmited ok to standby (querying the dba_user there), then unlock again on primary and see it open again but still standby says that the account is locked when trying to log on there.

    This occurs because the account was locked on the standby (limit of wrong passwords attemps was reached on standby.. or something similar). Since the active dataguard cannot alter any tables (is read-only open), then it locks the account IN MEMORY of the standby instance. Thus, you have to unlock directly ON STANDBY, and that's when it says ORA-28015, Account unlocked, but the database is open for read-only access (which means, unlocked ok in memory, problem solved... but didn't change on dba_users table, which is read-only but anyway it's open there already since the problem was not there.) and in fact then you can logon ok on standby.

    I think this behaviour is by design for security reasons, because a standby can be attacked as well as a primary db, and obiously cannot depend on a lock provided by the primary which does not know anything of the attack... and standby cannot alter tables... so it's limited to lock changes on memory.

    I hope this answers the question.

    Best regards.
  • 6. Re: User account management on 11.2.0.3 standby
    Sean Warden Newbie
    Currently Being Moderated
    We faced almost the exact situation posted by jbargallo but the suggested fix did not work.

    Our DBA realized that there were still several inactive connections to both the primary and standby for the locked user in question. The DBA killed all of those connections, forced a log switch, and we were able to successfully log in on the standby.