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:
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.
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:
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: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:
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 :smile: .
Have fun,
Richard
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
12 | |
10 | |
9 | |
7 | |
7 | |
7 | |
6 | |
6 | |
5 | |
4 |