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

krishnamrajukrishnamraju Registered 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

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited March 31

    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
    
    [[email protected] ~]$ 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:

    [[email protected] ~]$ 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)
    
  • krishnamrajukrishnamraju Registered User

    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_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    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)
    
    [[email protected] ~]$ 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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file