This content has been marked as final. Show 6 replies
Sid wrote:Check this
Just came across weird thing in 18.104.22.168 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 22.214.171.124 user account status is maintained somewhere else as well. Do anyone knows where?
Tried same in 126.96.36.199, 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 .
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.
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.
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.