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
