パスワード有効期限

• MySQL

(8.0.33)
https://dev.mysql.com/doc/refman/8.0/ja/alter-user.html

drop user if exists user2;

create user user2@'%' identified by 'user2';
grant process on *.* to 'user2'@'%';

select Host, User, password_expired, password_last_changed, password_lifetime
from mysql.user;

alter user user2@'%' PASSWORD EXPIRE INTERVAL 1 DAY;

alter user user2@'%' PASSWORD EXPIRE NEVER;

• Oracle

(23c)

select * from dba_profiles where PROFILE = 'DEFAULT' and RESOURCE_TYPE = 'PASSWORD';

alter profile default limit PASSWORD_LIFE_TIME 1;
alter profile default limit PASSWORD_GRACE_TIME 0;

alter profile default limit PASSWORD_LIFE_TIME unlimited;
alter profile default limit PASSWORD_GRACE_TIME unlimited;

drop user if exists USER2;

create user USER2 identified by USER2;
grant connect to USER2;

alter session set NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

select USERNAME, ACCOUNT_STATUS, EXPIRY_DATE
from dba_users
where username='USER2';

• PostgreSQL

(15)

drop user if exists user2;

create user user2 with login encrypted password 'user2';

\du

ALTER USER user2 VALID UNTIL '2023-06-24 11:00:00';
ALTER USER user2 VALID UNTIL 'infinity';

• SQL Server

(2022)
https://blog.sqlauthority.com/2016/09/27/sql-server-find-sql-login-will-expire/

use test
go

drop user user2;
go

use master
go

drop login user2;
go

create login user2 with password='user2', default_database=test, check_policy=off
go

use test
go

create user user2 for login user2;

SELECT
  name
, is_policy_checked
, is_expiration_checked
, LOGINPROPERTY(name, 'BadPasswordCount') AS 'BadPasswordCount'
, LOGINPROPERTY(name, 'BadPasswordTime') AS 'BadPasswordTime'
, LOGINPROPERTY(name, 'DaysUntilExpiration') AS 'DaysUntilExpiration'
, LOGINPROPERTY(name, 'DefaultDatabase') AS 'DefaultDatabase'
, LOGINPROPERTY(name, 'DefaultLanguage') AS 'DefaultLanguage'
, LOGINPROPERTY(name, 'HistoryLength') AS 'HistoryLength'
, LOGINPROPERTY(name, 'IsExpired') AS 'IsExpired'
, LOGINPROPERTY(name, 'IsLocked') AS 'IsLocked'
, LOGINPROPERTY(name, 'IsMustChange') AS 'IsMustChange'
, LOGINPROPERTY(name, 'LockoutTime') AS 'LockoutTime'
, LOGINPROPERTY(name, 'PasswordHash') AS 'PasswordHash'
, LOGINPROPERTY(name, 'PasswordLastSetTime') AS 'PasswordLastSetTime'
, LOGINPROPERTY(name, 'PasswordHashAlgorithm') AS 'PasswordHashAlgorithm'
FROM    sys.sql_logins
;

alter login user2 with CHECK_POLICY = ON, CHECK_EXPIRATION = ON;

※パスワードの有効期限はローカルセキュリティポリシーのパスワードの有効期間(デフォルト42日)が設定される