HANA - Procedure to activate/ deactivate a specific database user

Question

I'm trying to develop a procedure in HANA that activates and deactivates a special database user and logs everything in this regard.

I like that the user always has the same initial password after activation - but is forced to change it when he logs in.

Do you have an idea of how I can implement this?

Currently, I am failing due to the password policy. I don't want to touch this policy and it says that the last 5 passwords are not allowed to be used.

Is there a way to give the user any password five times with a random function and the sixth time the default password?

Any feedback regarding the implementation, improvements, and further ideas is welcome.

Thanks in advance.

Answer 1

You asked for feedback:

• working around security policies that presumably have been set on purpose is not a good idea

• implementing the logging yourself does not seem like a good idea either. SAP HANA provides auditing as a feature. Instead of writing logging code, consider setting up an appropriate audit policy

• the idea of having a user with the same post-activation password that then needs to be changed is rather insecure. It's literally like leaving the key to your house right outside the door together with the instruction to swap the locks after entering. This is not how security works.

• there is no need to manually COMMIT after ALTER USER

• seeing that your code expects a whole list of users it looks like one potential usage scenario for this could be test or training users. If that is the case, the recommendation is to not bother with user re-activation at all.
  Either create eternal single-use accounts (e.g. TRAIN01-2020-01 ... TRAIN01-2020-01) or drop and recreate the users whenever a new set of users should use the accounts. This will avoid the need to "dance around" a 5 last passwords policy altogether.

• PLEASE choose names for the procedures that make it clear, what the code is supposed to do. "START"/"STOP" has no relation to the code.

• What is the purpose of keeping the state of de-activated accounts in the table? HANA keeps track of the current state incl. the deactivation timestamp automatically.

• coming back to the idea of "simply using 5 random passwords" before setting the password to a well-known one: nothing in your code handles the case when the random password has been produced before. Also: usually the password policies also limit how often per day passwords can be changed - precisely to avoid strategies like this.

Hope this helps, nevertheless.

Answer 2

I think it's bad to give any advice when someone tries to reinvent the wheel and workaround security policies, because someone else will need to support this monstrous solution.

But anyway, why you require any random password if finally you'll set a desired one? Just loop over 6 predefined passwords and set them one after another. For auditing you can create an audit policy for that users and use standard supported tools to track changes.

EDIT: This is the code about "loop over 6 predefined passwords":

do( in iv_base_pwd nvarchar(20) => 'qwe')
begin
  declare lv_pwd_count int;
  declare lv_i int := 0;
  
  select value
    into lv_pwd_count
  from m_password_policy
  where property = 'last_used_passwords';
  
  while lv_i <= lv_pwd_count do
    execute immediate 'alter user myuser password ''' || iv_base_pwd || '_' || lv_i | '''';
    lv_i := lv_i + 1;
  end while;

  execute immediate 'alter user myuser password ''' || iv_base_pwd || '''';;
end;