Disable or Enable Password Expiration in Teradata

Here I have shown:

        1.     Disable or Enable Password Expiration for all user including DBC

        2.     Disable or Enable user password Expiration based on Profile

 

1.     Disable or Enable password Expiration for all user including DBC

The default setting for the ExpirePassword parameter is 0 in DBC.SysSecDefaults , that is, password do not expire. The range of allowable values (in days) for ExpirePassword is 0 through 32767.  Teradata recommends that you set the password expiration interval to a value between 90 and 270 days.  If you enter a negative value, the system accepts the UPDATE, but the value reverts to the default zero value at the next restart and writes an error message to the event log : 3698: SysSecDefaults table has a negative value in an integer column (Teradata, 2016).

 

1.1.  Disable all user password Expiration including DBC (Teradata, 2016):

               I.         In DBC.SysSecDefaults table set ExpirePassword to 0 to disable all user password expiration including dbc.

·       Login as dbc

·       sel PrimeIndex,ExpirePassword from dbc.syssecdefaults;

PrimeIndex

ExpirePassword

1

30

·       UPDATE DBC.SysSecDefaults SET Expirepassword = 0 WHERE PrimeIndex=1;

 

             II.         Restart the database for this change to take effect

 

1.2.  Enable all user password Expiration including DBC

               I.         In DBC.SysSecDefaults table set ExpirePassword to the number of days to enable  all user password expiration including DBC

·       Login as dbc

·       sel PrimeIndex,ExpirePassword from dbc.syssecdefaults;

PrimeIndex

ExpirePassword

1

0

·       Update Expirepassword to the number of days e.g 30 days

UPDATE DBC.SysSecDefaults SET Expirepassword = 30 WHERE PrimeIndex=1;

             II.         Restart the database for this change to take effect

 

2.     Disable or Enable user password Expiration based on Profile

You can choose password expiration based on profile. All users on that particular profile will be affected.

2.1.  Disable password expiration for a profile (Teradata)

I.         Login as DBC and Check the ExpirePassword value for the profile

SELECT profilename , ExpirePassword FROM DBC.ProfileInfoV where profilename='DeveloperP';

ProfileName

ExpirePassword

DeveloperP

30

 

 II.         MODIFY PROFILE  profile_name  AS PASSWORD = (EXPIRE = 0);

Example:

MODIFY PROFILE  DeveloperP AS PASSWORD = (EXPIRE = 0);

 

2.2.  Enable password expiration for a profile

 I.         Login as DBC and Check the ExpirePassword value for the profile

SELECT profilename , ExpirePassword FROM DBC.ProfileInfoV where profilename='DeveloperP';

ProfileName

ExpirePassword

DeveloperP

0

 

II.         Change the value to number of days that you want for expire. Example 30 days

MODIFY PROFILE  profile_name  AS PASSWORD = (EXPIRE = 30);

Example:

MODIFY PROFILE  DeveloperP AS PASSWORD = (EXPIRE = 30); 

 

References

Teradata. (2016, 04 18). Retrieved from http://knowledge.teradata.com/KCS/id/KCS001512

Teradata. (n.d.). Teradata Database, Tools and Utilities Release 16.00. Retrieved from https://info.teradata.com/htmlpubs/DB_TTU_16_00/index.html#page/Database_Management/B035-1100-160K/wau1472240749045.html

Comments