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 story

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:

1. Start the database in console mode as described in the database administration guide
2. 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.
3. Now start the instance again in the regular way: “/usr/sap/<SID>/HDB<instance>/exe/sapcontrol -nr <instance> -function StartSystem HDB“
4. 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.
5. Activate the SYSTEM user: “ALTER USER SYSTEM ACTIVATE USER NOW“
6. Verify that the SYSTEM user is in fact working again.
7. If you created an emergency user admin: delete that emergency user, get a regular user administrator working again.
8. 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:

1. 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:
   /********* Begin
   Procedure Script ************/
     v_sql nvarchar(128) := ‘alter user SYSTEM activate user now’;
   BEGIN
     EXEC v_sql;
   END;
   /********* 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.
2. 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.
3. 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” ();
4. 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 🙂 .

Have fun,

Richard