Last year during all SAP InnoJam, many teams managed to get locked out of own SAP HANA systems hosted on the SAP Cloud Platform.

With the SAP Cloud Platform developer edition, you can create your SAP HANA MDC (Multi Database Container) tenant, and you even get access to the “holy” SYSTEM user.

But “with great power comes great responsibility” as Spider man once said!

Therefore, you can brilliantly run a demo of your last 36h of hard work, or simply have nothing to show 5 minutes before your presentation.

I’d rather have everyone on the bright side, as unfortunately there is nothing much you could do to get back your access.

I don’t mean there is no way to reset the SYSTEM password in HANA and here is the documentation for that: Reset the SYSTEM User’s Password

(Note: if the link is not valid anymore, search for “SAP HANA Administration Guide” > “Security Administration” > “Managing SAP HANA Users” > “Database Users”)

But this requires an “ssh” access to the physical machine, and unfortunately (or may be fortunately), only the SAP Cloud Platform Team operation team have that access.

So, let’s get started!

Adjust the password policy

We all faced that annoying but necessary pain named the “password policy” which forces you to update your password every now and then, or to include at least one special character.

The cool thing now that you own your HANA instance, is that you can do whatever you want (but at your own risk).

You will find more details here for the Password Policy Configuration Options.

(Note: if the link is not valid anymore, search for “SAP HANA Security Guide” > “SAP HANA Authentication and Single Sign-On” > “Password Policy”)

For example, I usually change the following parameters:

• Minimum Password Length (minimal_password_length)
• User must change password on first logon (force_first_password_change)
• Number of Last Used Passwords That Cannot Be Reused (last_used_passwords)
• Number of Allowed Failed Logon Attempts (maximum_invalid_connect_attempts)

Give your SYSTEM user twin brother!

This is from my point of view a “must do” thing whenever you create a new database, especially if you work within the boundaries of the SAP Cloud Platform.

If you want to know the reason, it simple: everyone knows that you have a “SYSTEM” user in your SAP HANA instance, and this one is visible in the cloud, so anyone who knows your account identifier and your instance id could potentially try to access your instance.

To give an idea of the URL they will access, here is the url pattern:

https://<instance id><account identifier>trial.hanatrial.ondemand.com/sap/hana/xs/ide

Also, and unfortunately, many people will use “trial”, “hana” or “mdc” as their instance id (and I know as a tutorial author I’m somehow guilty of that as I recommend to use the name I provide in my content to ease the progression).

With an on-premise version like with SAP HANA, express edition, your firewall will protect you from external access. But still, if you have clumsy fingers like me, you may hit that problem, and having a backup user with “privileges” is not a bad idea.

Let’s do it

Here I will make the assumption that you are using SAP HANA from the SAP Cloud Platform and have successfully completed the instance creation and accessed at least once the SAP HANA Cockpit as described in the following tutorial:

Setup your trial SAP HANA MDC instance

Now, you can now execute the first 2 steps of the following tutorial:

Setup a HANA user account

But for step 3 (“Create your user”), you will run the following SQL instead.

Adjust the password policy

ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') SET ('password policy', 'minimal_password_length') = '0' WITH RECONFIGURE;
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') SET ('password policy', 'force_first_password_change') = 'false' WITH RECONFIGURE;
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') SET ('password policy', 'last_used_passwords') = '0' WITH RECONFIGURE;
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') SET ('password policy', 'maximum_invalid_connect_attempts') = '10' WITH RECONFIGURE;

Now your system is configured to:

• password with minimum length of 0
• it won’t force users to change their password on first connect
• allow to reuse any password in case they want to change it
• allow up to 10 failed login attempt before lacking the user

When a user is locked, the “User Lock Time” (password_lock_time) is by default 1440 minutes (1 day). But now you know what to do if you want to change that.

There is also a specific parameter to exclude the SYSTEM user from being locked (password_lock_for_system_user) if you want, but this means that you leave the door open for brute-force connection attack on the user from my point of view.

Give your SYSTEM user a twin brother

DROP USER SYSTEM_BACKUP CASCADE;

CREATE USER SYSTEM_BACKUP PASSWORD Welcome17 NO FORCE_FIRST_PASSWORD_CHANGE;
ALTER USER  SYSTEM_BACKUP DISABLE PASSWORD LIFETIME;

call _SYS_REPO.GRANT_ACTIVATED_ROLE ('sap.hana.admin.roles::Administrator','SYSTEM_BACKUP');
call _SYS_REPO.GRANT_ACTIVATED_ROLE ('sap.hana.ide.roles::Developer','SYSTEM_BACKUP');

GRANT AFL__SYS_AFL_APL_AREA_EXECUTE TO HCPPSTRIAL;
GRANT AFLPM_CREATOR_ERASER_EXECUTE TO HCPPSTRIAL;

GRANT CREDENTIAL ADMIN TO SYSTEM_BACKUP;
GRANT ROLE ADMIN TO SYSTEM_BACKUP;
GRANT SESSION ADMIN TO SYSTEM_BACKUP;
GRANT TABLE ADMIN TO SYSTEM_BACKUP;
GRANT TENANT ADMIN TO SYSTEM_BACKUP;
GRANT USER ADMIN TO SYSTEM_BACKUP;

Off course, you should personalize the username (SYSTEM_BACKUP) and the password (Welcome17).

Unlock a user

And last but not least here is how to unlock a deactivated user:

ALTER USER MYUSER RESET CONNECT ATTEMPTS;
ALTER USER MYUSER DROP CONNECT ATTEMPTS;

Now, you should not get locked out of your own database anymore!

Thank you for reading

If you want to get hands on materials on SAP product, the SAP Developer Center provides a multitude of online tutorials made by the SAP developer community for the community. For the full tutorial library, see the Tutorial Navigator

You can also:

• Subscribe to the SAP Developer News monthly newsletter for updates
• Follow us on
  • Twitter @sapdev
  • YouTube: SAP Community
  • Google+: SAP Developers
• Watch out for our next CodeJam events

And of course, if you found this blog useful, remember that “sharing is caring”! 🙂