デュアルパスワード

 

• MySQL

(8.4.0)

https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0090

8.0.14以降で使用可能

 

select user, authentication_string, user_attributes
from mysql.user
where user = 'user01'
\G

create user user01@'%' identified with mysql_native_password by 'user01';

select user, authentication_string, user_attributes
from mysql.user
where user = 'user01'
\G

mysql -u user01 -puser01 -e "status;"

 

プライマリーパスワードを登録する

alter user user01@'%' identified with mysql_native_password by 'user11' retain current password;

select user, authentication_string, user_attributes
from mysql.user
where user = 'user01'
\G

mysql -u user01 -puser01 -e "status;"
mysql -u user01 -puser11 -e "status;"

セカンダリーパスワードを破棄する

alter user user01@'%' discard old password;

select user, authentication_string, user_attributes
from mysql.user
where user = 'user01'
\G

mysql -u user01 -puser01 -e "status;"
mysql -u user01 -puser11 -e "status;"

 

• Oracle

(23c)

https://docs.oracle.com/en/database/oracle/oracle-database/21/dbseg/configuring-authentication.html#GUID-D3D32319-5641-4C20-9371-CE08137D0F1D

https://qiita.com/plusultra/items/36eb2e1651da617904db

PASSWORD_ROLLOVER_TIME
パスワードを変更した際に古いパスワードも一定時間（日）有効にする機能

CREATE PROFILE prof1
LIMIT
PASSWORD_ROLLOVER_TIME 1;

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

CREATE USER user01 IDENTIFIED BY user01 PROFILE prof1;
grant dba to user01;

echo "select sysdate from dual;" | sqlplus user01/user01@freepdb1 

SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'USER01';
ALTER USER user01 IDENTIFIED BY user11;
SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'USER01';

echo "select sysdate from dual;" | sqlplus user01/user01@freepdb1 
echo "select sysdate from dual;" | sqlplus user01/user11@freepdb1 

 

 

• PostgreSQL

(16)

調べた限りなし

• SQL Server

(2022)

調べた限りなし