How to Unlock the User in HANA

This Topic will explain about how to unlock the schema in SAP HANA and below sql's provide information about how to unlock the user in SAP HANA .

Live Example : Our ERP Test schema was getting locked in SAP HANA and it was causing a downtime for the Customer application , we had to enable an audit policy on HANA to find out who’s locking the user .A database user may be locked because of the (default) password policy defined:

indexserver.ini > password policy > maximum_invalid_connect_attempts = 6

How to list all SAP HANA users with their lock status?

We want to list down all users created in SAP HANA system with locked/Unlocked status.

Requirement: We want to list SAP HANA users with their lock status.
Prerequisite:  We need a database user with DATA ADMIN or CATALOG READ assigned.

Steps:

   1> Connect to the required system via HANA Studio
   2> Open Administration perspective,  You can open it from Window -> Open -> Perspective -> Administration Console
   3> Execute following SQL in SQL console:

---this will list all users in HANA systems.
Select * from "SYS"."USERS";

Select * from "PUBLIC"."USERS";

---This will list down the deactivated/locked users.

Select * from "SYS"."USERS" WHERE USER_DEACTIVATED='TRUE';
Select * from "PUBLIC"."USERS" WHERE USER_DEACTIVATED='TRUE';

---This will list down the unlocked/active users.
Select * from "SYS"."USERS" WHERE USER_DEACTIVATED='FALSE';
Select * from "PUBLIC"."USERS" WHERE USER_DEACTIVATED='FALSE';



How to activate user which is locked and following message comes when try to connect;

vsa100 ABC/home> hdbsql -jU DEFAULT
Welcome to the SAP HANA Database interactive terminal.
Type:  \h for help with commands
       \q to quit
hdbsql=>

* 416: user is locked; try again later: lock time is 1440 minutes; user is locked until 2015-03-09 12:11:41.1530000 (given in UTC) [1440,2015-03-09 12:11:41.1530000] SQLSTATE: HY000

hdbsql=> exit
This is because user tried with too many invalid attempts.

If too many invalid attempts >> Issue the command - ALTER USER RESET CONNECT ATTEMPTS to activate it again.

Open HANA Studio, connect to the system >> expand security >> expand users >> identify the user and

double click to open the details of respective user

Click “Activate User” at the top right of the user window wizard, you will prompt to confirm.
To control password policy HANA has a monitoring view as below:-

hdbsql QNI=> select * from M_PASSWORD_POLICY
PROPERTY,VALUE
"last_used_passwords","5"
"maximum_invalid_connect_attempts","5"
"maximum_password_lifetime","182"
"maximum_unused_inital_password_lifetime","28"
"maximum_unused_productive_password_lifetime","365"
"minimal_password_length","8"
"minimum_password_lifetime","1"
"password_expire_warning_time","14"
"password_layout","A1a"
"password_lock_time","1440"
"force_first_password_change","true"
11 rows selected (overall time 12.331 msec; server time 1234 usec)

In case you would need user never face password expiry and locking issues due to failed login attempts, you can alter the configuration parameters as below:- so that the password policy on database gets change.

Second Method for Unlock the Schema by Using SQL :

Disable pwd expire for technical users :
ALTER USER DISABLE PASSWORD LIFETIME

UNLOCK USER :
alter user password ;

CHECK IF USER IS DEACTIVATED:
SELECT NAME, USER_DEACTIVATED, INVALID_CONNECT_ATTEMPTS FROM "SYS"."P_PRINCIPALS_" WHERE NAME='username'

CHECK INVALID PWD ATTEMPTS:
select * from sys.INVALID_CONNECT_ATTEMPTS where user_name='username'

FOR Reseeting INVALID PWD ATTEMPTS if any:
alter user username DROP CONNECT ATTEMPTS;

SQL Command  for Monitoring & Alerting the User:

Following commands help us to identify the failed attempts information or Invalid Password Attempts:

hdbsql ABC=select * from sys.INVALID_CONNECT_ATTEMPTS where user_name='username'

USER_NAME,SUCCESSFUL_CONNECT_TIME,INVALID_CONNECT_ATTEMPTS
"SAP","2014-06-10 10:32:21.105000000",5

Check for User Status (Deactivated/Activated) :

SELECT NAME, USER_DEACTIVATED, INVALID_CONNECT_ATTEMPTS FROM “SYS”.”P_PRINCIPALS_” WHERE NAME='username'

Below command will reset the failed connection attempts of the particular user: 

alter user reset connect attempts
1 row selected (2989 usec)

Disable Password Expire for technical users:

ALTER USER DISABLE PASSWORD LIFETIME

User Password Reset :

alter user username  password ;

Another workaround is to change the password of the respective user.

alter user ABC DROP CONNECT ATTEMPTS;
alter user ABC password ;
ALTER USER ABC FORCE PASSWORD CHANGE
ALTER USER ABC DISABLE PASSWORD LIFETIME