Purpose:
A technical database user was locked many times. Therefore, an application, such as a third-party BI
reporting tool, cannot connect to the SAP Hana Database. Checked the traces and HANA studio to
investigate the root cause.
In the Hana studio as shown below
Investigation and Finding:
Executed SQL statement.
ALTER USER ZUSER ACTIVATE USER NOW;
ALTER USER ZUSER RESET CONNECT ATTEMPTS;
ALTER USER ZUSER DISABLE PASSWORD LIFETIME;
Unfortunately, Hana tenant database user
"ZUSER" gets deactivated again after some time.
It seems that the SQL statement "
ALTER USER ZUSER DISABLE PASSWORD LIFETIME;" was not
effective for this database user (Disable Password lifetime will work only for technical user such as
"SYSTEM", "SYS", "_SYS_REPO" etc) because of too many invalid connect attempts error.
We have tried to find the root cause of whether the password was changed for the affected specific
database user, who locked the database user, and find out the client host or IP address.
Executed SQL statement as shown below
As shown above, highlighted in yellow color, the password was not changed at the Database level
for almost 10 months.
Reason:
The reason that the database user was getting locked due to a maximum number of invalid connect
attempts exceeded the value defined in the password policy parameter
"max_invalid_connect_attempts”. The default value is 6.
This could be due to an incorrect password updated in the Third-party application that connects to
the Hana tenant Database in case of MDC.
However, to identify who locked the database user or find out the client host or IP address then
audit
policy can help
us to identify the origin of the invalid connection attempts.
The procedure to create and enable audit policy in HANA is described in the SAP OSS note1855211.
1855211 - Analyzing user locked situations - SAP for Me
Resolution:
To prevent a Hana DB user from being locked many times after reaching a number of allowed failed
logon attempts setting in the password policy, we have to set the User Lock Time setting in the
password policy to 0. But as this will be impact to all database users, therefore, create a
specific usergroup for that user, to restrict the change to just this user.
This will unlock the database user ZUSER immediately without Hana DBA intervention i.e. there is
no need for HANA DBA to unlock the database user either in the Hana Studio or SQL command.
Note that setting the
password_lock_time to 0
disables the functionality of the parameter
"max_invalid_connect_attempts" as mentioned in the link given below.
https://help.sap.com/docs/SAP_HANA_ONE/102d9916bf77407ea3942fef93a47da8/61662e3032ad4f8dbdb5063a21a7...
Reference:
1855211 - Analyzing user locked situations - SAP for Me
https://help.sap.com/docs/SAP_HANA_ONE/102d9916bf77407ea3942fef93a47da8/61662e3032ad4f8dbdb5063a21a7...
Thanks for reading!
Follow for more such posts by clicking on FOLLOW =>
aprao9089
Please share your thoughts and feedbacks on this blog in a comment.