Why password_auditor table not showing the password expired even after password_life_time ends?

krishnamraju

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.

Jim_Knicely

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)

krishnamraju

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.

Jim_Knicely

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:

Long Message:
[Vertica]VJDBC Authentication failed because the password has expired.

Details:
   Type: java.sql.SQLException
   Error Code: 100067
   SQL State: 28000

Seems okay.