Why password_auditor table not showing the password expired even after password_life_time ends?
krishnamraju
Community Edition User ✭
Hi Community Team,
I have faced one issue regarding user access , please find the following sql's.
vadmin=> select version();
version
Vertica Analytic Database v7.2.3-15
(1 row)
---User password create time and profile name
vadmin=> select user_name,password_create_time,is_current_password,profile_name from passwords where user_name ilike 'arunj';
user_name | password_create_time | is_current_password | profile_name
-------------+----------------------------------+---------------------+--------------------------
arunj | 2018-01-20 12:24:13.635201+05:30 | t | DWH_ENDUSER_PROFILE
(1 row)
---Profile name and password life time
vadmin=> select profile_name,password_life_time from profiles where profile_name = 'DWH_ENDUSER_PROFILE';
profile_name | password_life_time
--------------------------+--------------------
DWH_ENDUSER_PROFILE | 60
(1 row)
vadmin=> select user_name,acctexpired from password_auditor where user_name ilike 'arunj';
user_name | acctexpired
-------------+-------------
arunj | f
(1 row)
vadmin=>select sysdate();
sysdate
2018-03-31 16:53:21.487544
(1 row)
My question is
Why password_auditor tables column acctexpired showing false even after password lifte is crossed. Due to this some users not getting the password expired hint while connecting.
Comments
Hi,
What client tool is being used? vsql, ODBC, JDBC, ADO.net?
Works okay in 9.0.1 (At least when the client is VSQL):
dbadmin=> select version(); version ------------------------------------ Vertica Analytic Database v9.0.1-3 (1 row) dbadmin=> select user_name,password_create_time,is_current_password,profile_name from passwords where user_name ilike 'jim'; user_name | password_create_time | is_current_password | profile_name -----------+-------------------------------+---------------------+-------------- jim | 2018-02-21 14:50:50.825673-05 | t | jim_profile (1 row) dbadmin=> select profile_name,password_life_time from profiles where profile_name = 'jim_profile'; profile_name | password_life_time --------------+-------------------- jim_profile | 1 (1 row) dbadmin=> select user_name,acctexpired from password_auditor where user_name ilike 'jim'; user_name | acctexpired -----------+------------- jim | f (1 row) dbadmin=> select sysdate; sysdate ---------------------------- 2018-03-31 10:52:21.949443 (1 row) dbadmin=> \q [dbadmin@s18384357 ~]$ vsql -U jim -w jim1 The password has expired. Changing password for jim New password:Note that the ACCTEXPIRED field in the PASSWORD_AUDITOR indicates if the current password has been forced to expire by superuser.
Example:
[dbadmin@s18384357 ~]$ vsql Welcome to vsql, the Vertica Analytic Database interactive terminal. Type: \h or \? for help with vsql commands \g or terminate with semicolon to execute query \q to quit dbadmin=> select user_name,acctexpired from password_auditor where user_name ilike 'jim'; user_name | acctexpired -----------+------------- jim | f (1 row) dbadmin=> alter user jim password expire; ALTER USER dbadmin=> select user_name,acctexpired from password_auditor where user_name ilike 'jim'; user_name | acctexpired -----------+------------- jim | t (1 row)Hi Jim,
Thanks for the revert.
Currently we are using vertica version 7.2.3-15.
Users in my organization uses dbvisulizer and SAS and etc for database access. Some users getting password expire details in dbvizulizer connection message panel but some users are not getting, may be due to password_auditor table not showing as expire.
As of now we are checking password create time manually.
Hope it won't repeat in future versions.
Hi,
Glad to hear that you have a update plan!!!
Fyi... I was able to test with 7.2.3-26.
dbadmin=> select version(); version ------------------------------------- Vertica Analytic Database v7.2.3-26 (1 row) dbadmin=> select user_name,password_create_time,is_current_password,profile_name from passwords where user_name ilike 'jim'; user_name | password_create_time | is_current_password | profile_name -----------+-------------------------------+---------------------+-------------- jim | 2018-03-31 11:15:46.447622-04 | t | jim_profile (1 row) dbadmin=> select profile_name,password_life_time from profiles where profile_name = 'jim_profile'; profile_name | password_life_time --------------+-------------------- jim_profile | 1 (1 row) dbadmin=> select sysdate; sysdate ---------------------------- 2018-04-01 11:25:37.518812 (1 row) dbadmin=> select sysdate; sysdate ---------------------------- 2018-04-01 11:25:37.518812 (1 row) [dbadmin@vertica7 ~]$ vsql -U jim The password has expired. Changing password for jim New password:Also,I get this message from DBVisualizer:
Seems okay.