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/61662e3032ad4f8dbdb5063a21a7d706.html

Reference:

1855211 – Analyzing user locked situations – SAP for Me

https://help.sap.com/docs/SAP_HANA_ONE/102d9916bf77407ea3942fef93a47da8/61662e3032ad4f8dbdb5063a21a7d706.html

Thanks for reading!

Follow for more such posts by clicking on FOLLOW => Prasad Rao

Please share your thoughts and feedbacks on this blog in a comment.