Reactivating the SYSTEM user when no User with USER ADMIN is Available
Recently an interesting question reached us from a customer – it was one of those questions that you might not have an immediate answer for and which thus triggers a tiny bit of R&D.
The question was this: “SAP recommends that the SYSTEM user be deactivated [personal note: yes, absolutely and beyond any doubt – SYSTEM should not be used in regular database operations]. What options do you have if you need to reactivate the SYSTEM user, but for whatever reason all other database users with privilege USER ADMIN are unavailable?“
Now the question first sounds pretty hypothetical – there is no good reason why all your regular administrative users which have the USER ADMIN privilege should be unavailable. Still, you can construct such situations. I have recently managed to get all regular users deactivated by simply deactivating the “force_first_password_change” setting, but leaving the initial password lifetime at a finite value, and also not disabling the password life time of users. All users that I had created in this situation were deactivated after the initial password life time had passed.
So let us assume you find yourself in this situation:
- SYSTEM user is locked
- You have no other database users available with the USER ADMIN privilege
There are at least two ways to resolve this situation. The first one requires a database restart but does not have any further prerequisites. The second one does not need the restart, but requires that you still have some database users available that have development capabilities.
Procedure One – Requiring Database Restart
I’m sure you are aware of the emergency procedure for resetting the SYSTEM user’s password which is described in section 3.1.4 of the SAP HANA administration guide. This procedure allows you to start the database in so-called “console mode”, which gives you a SQL console for the SYSTEM user, without requiring the SYSTEM user’s password. The million-dollar-question is, of course, whether you can use this procedure also when user SYSTEM is deactivated. The good news is: yes you can!
So here’s our procedure:
- Start the database in console mode as described in the database administration guide
- User SYSTEM is not allowed to unlock itself. You therefore have the following options:
- If there are other database users with privilege USER ADMIN, but these users are locked/deactivated, you can activate/unlock one of them. The procedure to unlock/activate depends on the reason for the user being deactivated. It’s either a password reset, or an “alter user <name> activate user now“
- Alternatively, you may create a dummy user and grant USER ADMIN to this user:
CREATE USER emergency_user_admin PASSWORD WhySoComplicated?;
GRANT USER ADMIN TO emergency_user_admin;
Shut down the system that you have started in emergency mode:
- Stop the indexserver process by entering “quit” in the SQL console
- Stop the nameserver and compileserver that you have started by hitting Ctrl+C in their respective linux terminals.
- Now start the instance again in the regular way: “/usr/sap/<SID>/HDB<instance>/exe/sapcontrol -nr <instance> -function StartSystem HDB“
- Once the system is started, log on with either the unlocked regular user administrator, or with the emergency_user_admin, depending on what you did in step 2.
- Activate the SYSTEM user: “ALTER USER SYSTEM ACTIVATE USER NOW“
- Verify that the SYSTEM user is in fact working again.
- If you created an emergency user admin: delete that emergency user, get a regular user administrator working again.
- With that regular user admin, deactivate the SYSTEM user, and activate any other locked/deactivated user admin.
Procedure Two – Requiring Development Capabilities
The second procedure does not require a system restart. I’m sure that in production systems you will appreciate this.
It does, however, require that you can create and activate a SQLscript procedure in the SAP HANA repository – either in the modeler or using the developer workbench, whatever you prefer. And you must be able to execute that procedure.
If you are having this trouble in your production system, and if you have deactivated SYSTEM, I’m pretty certain your HANA is als “read only” – i.e. you do not have development accounts in this database. So the requirements are that you need to be able to import (including activation) a stored procedure into your system, and that at least one active user has the execute privilege on the database schema in which the runtime object of the procedure will reside (e.g. execute on schema _SYS_BIC).
If you do have development users in the troubled system, then you are probably good to go, as any database user with minimal development privileges can create and activate stored procedures, and in most cases they will also have permission to test, i.e. execute, these activated procedures.
One more prerequisite is that the system parameter sqlscript_mode must be set to UNSECURE – the reason is that we need to use dynamic SQL in the procedure, which needs a read/write procedure. Read/write procedures are only possible if SQLSCRIPT_MODE = UNSECURE.
Here’s what you have to do:
- Create a stored procedure in the repository as read/write procedure, in definer mode. In the body of the procedure, we will activate the SYSTEM user. If you create the procedure using the SAP HANA modeler, the following body is sufficient:
Procedure Script ************/
v_sql nvarchar(128) := ‘alter user SYSTEM activate user now’;
/********* End Procedure Script ************/
You can of course activate any other locked database user which might have the USER ADMIN privilege – just change the content of v_sql accordingly.
- If the procedure has not been created in the troubled system, transport it. If you transport using the HALM application or CTS+, activation is performed implicitly. If the procedure has been created locally in the right system, you need to activate it.
- Call the procedure. For an activated procedure which has been created with the modeler in package system-local.public.developer and is named “ACTIVATE_SYSTEM_USER”, the call is:
call _SYS_BIC.“system-local.public.developer/ACTIVATE_SYSTEM_USER” ();
- Finally you want to clean up. This means: perform steps 7/8/9 from the first procedure. And you also want to remove the sqlscript procedure from the repository, making sure that also the runtime object gets deleted.
A lot of text for a teeny tiny problem. If you ever find yourself in a situation requiring one of the above emergency procedures, please leave a comment here 🙂 .
Nice trick Richard. Thanks for sharing
One question, well in fact two:
1.- Is really needed to restart HANA db for SYSTEM user exhcnage password?
2.- Is there any way to see/know what is locking again and again a user login? Is there any way to know when was the last time a password change was done?
Thanks for your post!
I've a question to the procedure used in part 2:
Which permissions are needed to execute the stored procedure?
Or the other way around: Does the user which will execute the script need the USER ADMIN privilege?
Regarding your question: This is a REAL situation we hit sometimes! Imagine an environment with different administrators. Furthermore you have a security concept which restricts the USER ADMIN privilege to only one technical database user. And additionally you have some rules to change passwords on regular base. Furthermore according to parameter maximum_invalid_connect_attempts after 6 invalid logins a database user is locked.
First pitfall: Admins have the passwords stored in the HANA studio. If it is stored within the configuration the user became immediately locked after a password change if different admins startup their studio afterwards.
Second pitfall: If the password is changed but not clearly communicated (assume complex passwords!) the invalid login attempts counter is increasing fastly and leads also to a locked user.
thanks for your response here - and confirming that the problem is not as theoretical as one might intuitively assume.
The procedure needs to be created in "definer" mode - which means that regardless of who executes the procedure (the invoking user), the statements in the procedure body will be executed by the user who created the procedure. Now in activated procedures (created using SAP HANA modeler, or developer perspective in Studio or the browser-based development workbench) - in such activated procedures, the technical activation and thus creation of the procedure is performed by the _SYS_REPO user.
And this _SYS_REPO user has all the required privileges, i.e. you do not need to give the privileges to the named user who executes the procedure.
So what you really have to do is to find a way to get the procedure into the system; and have at least one user with execute privilege on the procedure (or on the schema that contains the procedure). This more or less means that you need some kind of development (and possibly transport) capability in place.
When you build the stored procedure using the modeler, it will per default end up in the _SYS_BIC schema - so here you'd need someone with execute on that schema - it's a rather typical privilege for at least some users to have in systems where data modeling is used.
Generally speaking, you can choose the target schema for the stored procedure (in particular, if you use development workbench or perspective, you must actively choose the target schema). Any schema works, as long as the _SYS_REPO user has privileges to create objects in that schema. A trivial choise would be the user schema of the admin user that you want to work with in order to execute this trick. The admin user owns their own schema - they can grant the necessary CREATE ANY privilege to _SYS_REPO. And because they own the schema, they automatically have execute premissions on the created procedure.
Short answer: no, for the procedure-trick to work, you do not need a user that has the USER ADMIN system privilege.
I tried the second option and it didn't work. I created a read/write procedure in definer mode in the default schema for my database user.
Any add-ons to this?
I believe this can also be done using the 'Reset SYSTEM users password' option as described in the SAP HANA Administration Guide. Similar to the procedure #1 described above, this option also requires database restart.
The documentation says 'If you previously deactivated the SYSTEM user, it is now also reactivated. This means you will need to deactivate it again.'
Procedure One – Requiring Database Restart
does no longer work for HANA 1 SPS09 and higher and HANA 2
To resolve the issue follow SAP Note
3001091 – Your SYSTEM User is Deactivated and Disabled to Connect by Client to the HANA Database