How USER_DEACTIVED field works in USERS View

[vivekbhoj]

Hi Everyone,

As we know that USERS Monitoring View shows information about all the users.

USER_DEACTIVATED filed specifies "If a User is Deactivated or not"

USERS - SAP HANA SQL and System Views Reference - SAP Library

When I see data from USERS view, I have observed that the value of this field is TRUE if I am manually deactivating a User but if a User is deactivated automatically(for reason of password expiry) then this View field USER_DEACTIVATED shows value as "FALSE" while it should be "TRUE" as the user is deactivated.

I even checked P_USERS_ table and there also I found that if a User is deactivated automatically(for reason of password expiry) then this table shows USER_DEACTIVATED field value as '0'.

As shown in the below screenshot the User is Deactivated because the password is expired.

But when I check the same User in USERS table, it shows USER_DEACTIVATED field value as 'FALSE'

So, I want to know how this USER_DEACTIVATED field works? Does it show value TRUE only on manually deactivating User?

Because if this is the case then I will have to manually check each user to find out which Users are active and which are not?

We are using HANA Rev 82.

Regards,

Vivek

[vivekbhoj · Accepted solution]

Lars Breddemanncan you please shed some light on this?

Regards,

Vivek

[lbreddemann · Accepted solution]

I see that the documentation should be clearer here.

From what I can see, the deactivation through password-expiry is actually not tracked at all.

Current versions of SAP HANA studio simply seem to check for whether the password lifetime had been exceeded or not.

The user technically is not locked as such, but could log on after a password change.

The LOCKED information in the USERS table shows - as you correctly mentioned - that an account had been actively locked and needs to be specifically unlocked again to be used. The password lifetime doesn't change that.

PASSWORD_CHANGE_NEEDED just means: the current password had been set by the admin and the password rules say that it needs to be changed by the user before anything else.

Now for the monitoring purpose to find out which users can currently effectively access the database, you may use a statement similar to those used by SAP HANA studio:

SELECT user_name,

     MAX(CASE WHEN U.ADMIN_GIVEN_PASSWORD = 'TRUE'

               AND PP.PROPERTY = 'maximum_unused_inital_password_lifetime'

               AND ADD_DAYS(GREATEST(IFNULL (U.LAST_SUCCESSFUL_CONNECT, U.CREATE_TIME), U.PASSWORD_CHANGE_TIME), PP.VALUE) < CURRENT_TIMESTAMP

              THEN 1

              WHEN U.ADMIN_GIVEN_PASSWORD = 'FALSE'

                    AND PP.PROPERTY = 'maximum_unused_productive_password_lifetime'

                    AND ADD_DAYS(IFNULL (U.LAST_SUCCESSFUL_CONNECT, U.CREATE_TIME), PP.VALUE) < CURRENT_TIMESTAMP

              THEN 2

              ELSE 0

        END) LOCK_REASON

FROM SYS.USERS U,

     SYS.M_PASSWORD_POLICY PP

WHERE

pp.property in ('maximum_unused_inital_password_lifetime', 'maximum_unused_productive_password_lifetime')

group by user_name

This would return 1 when the user account still has the initial password but the max. lifetime for initial passwords has passed.

It returns 2 if the password wasn't the initial anymore but the account hasn't been used for longer than <maximum_unused_productive_password_lifetime> days.

If none of the conditions apply, then a zero is returned.

As you can tell this query doesn't cover INVALID_CONNECT_ATTEMPTS - so you may work this in, too.

My view on this is that the USERS table contains rather static information on activation/deactivation while the more dynamic aspects (password lifetime, invalid connection attempts, etc. ) need to be evaluated dynamically.

And that makes sense as you could change the parameters and then the formerly "locked" users would be able to be used again.

- Lars

- Lars

[vivekbhoj · Accepted solution]

Thanks a lot Lars

Regards,

Vivek

[Former Member]

Hello,

That's what I observed too. It seems USER_DEACTIVATED field will show value TRUE only when a user is manually deactivated.

For some time I thought that a combination of fields USER_DEACTIVATED and PASSWORD_CHANGE_NEEDED would provide the desired result.

But with a few more checks I found out that PASSWORD_CHANGE_NEEDED does not specify whether the user has to change the password next time because it has expired.

(Not really sure what the manual is trying to tell me with this: Specifies if user is forced to change his password himself: 'TRUE', 'FALSE' )

But to check for expired passwords, I found a little query by Lars Breddemann in this thread:

https://scn.sap.com/thread/3149275

regards

[vivekbhoj]

Hi Josef,

Thanks for your reply.

I know that we can check for expired passwords but then a User can be deactivated based on many other reasons - so that way I would need to check each user.

Regards,

Vivek