View User Specific Settings

[Former Member]

I have a user that will be a service account for a another system. I don't want to have to worry about password expiration or first time password changes. I found here HANA Tutorials: How to disable password reset for technical users ? that I can disable that for the user.

ALTER USER <technical-user-name> DISABLE PASSWORD LIFETIME

My question is how do I view this change for this user after running this in the SQL console? Is there an easy way to pull up which users have this applied? I didn't see it reflected in the GUI when looking at the user parameters or anything.

[former_member182967]

Hi Rob,

Using this SQL to list users that are excluded from the password check as achieved with this DISABLE PASSWORD LIFETIME.thread/3540022

select SYS.P_USERS_.NAME, SYS.P_USERS_.OID,SYS.P_USER_PASSWORD_.PASSWORD_CHANGE_TIME from  
SYS.P_USER_PASSWORD_ right join SYS.P_USERS_ on   
SYS.P_USER_PASSWORD_.OID = SYS.P_USERS_.OID  
where SYS.P_USER_PASSWORD_.PASSWORD_CHANGE_TIME is null 

The above SQL is mentioned https://scn.sap.com/thread/3540022.

Regards,

Ning

[Former Member]

[12085]{302389}[71/-1] 2016-08-30 09:02:41.516552 i TraceContext     TraceContext.cpp(00942) : UserName=ROCHAPMAN, ApplicationUserName=rochapman, ApplicationName=HDBStudio, ApplicationSource=csns.sql.editor.SQLExecuteFormEditor$2$1.run(SQLExecuteFormEditor.java:911);, StatementHash=0fbb672e312c893b6510f69d32b0f316
[12085]{302389}[71/-1] 2016-08-30 09:02:41.516439 i Authorization    SQLFacade.cpp(01447) : UserId(5235701) is not authorized to do SELECT on ObjectId(2,0,oid=131398)
[12085]{302389}[71/-1] 2016-08-30 09:02:41.516591 i Authorization    SQLFacade.cpp(01873) : 
    schemas and objects in schemas : 
    SCHEMA-131073-SYS : {} , {SELECT}
        TABLE-131398-P_USERS_ : {} , {SELECT}
        TABLE-131424-P_USER_PASSWORD_ : {} , {SELECT}
[12085]{302389}[71/-1] 2016-08-30 09:02:41.516804 i Authorization    query_check.cc(03387) : User ROCHAPMAN tried to execute 'select SYS.P_USERS_.NAME, SYS.P_USERS_.OID,SYS.P_USER_PASSWORD_.PASSWORD_CHANGE_TIME from    
SYS.P_USER_PASSWORD_ right join SYS.P_USERS_ on     
SYS.P_USER_PASSWORD_.OID = SYS.P_USERS_.OID    
where SYS.P_USER_PASSWORD_.PASSWORD_CHANGE_TIME is null'

Which auth do I need to add to my user?

[former_member182967]

Hi Rob,

You have no select privilege on sys.p_users_ and p_user_password_.

I use DB user SYSTEM without authorization error.

Regards,

Ning

[Former Member]

I don't have access to the system user since this is a HEC owned system.

[former_member182967]

Hi Rob,

Sorry, I'm not familar with HEC.

Can you request a DB administrator user?

If you can not, then have to wait for user password expiration notification in HANA alerts(default days for password expiration is 60) to know which user is not excluded.

Regards,

Ning