Oracle 계정 암호 갱신 스크립트 (변경 주기에 따른)

계정 잠김 방지 등 개인적인 필요에 의해 만든 것이며, 개발DB 등에서 충분한 테스트 후 적용해 볼 수 있습니다.

 

1. shell script (상단 export는 필요에 따라 변경)

#!/bin/bash
export ORACLE_BASE=/oracle
export ORACLE_HOME=/oracle/product/19C
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=DB1

sqlplus -S "/as sysdba" @chg_pwd.sql

 

2. chg_pwd.sql (PROFILE 정책은 필요에 따라 변경)

SET ECHO OFF
SET TERMOUT OFF
SET PAGESIZE 0
SET HEADING OFF
SET FEEDBACK OFF
SET TIMING OFF
SET TRIMSPOOL ON
SET SERVEROUTPUT ON
SET LINESIZE 32767

SPOOL _alter_pwd.sql

SELECT 'alter user ' || username || ' identified by values ''' || u.password || ''';' sql_text
FROM dba_users d, sys.user$ u
WHERE d.username = u.name
AND TRIM(u.password) IS NOT NULL
AND ACCOUNT_STATUS IN ('EXPIRED(GRACE)', 'OPEN')
ORDER BY username;

SPOOL OFF

SPOOL chg_pwd.log APPEND

WHENEVER SQLERROR EXIT SQL.SQLCODE

PROMPT ===============================================================

SELECT '1. START : ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;

PROMPT 2. UNLIMIT PROFILE

ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_MAX UNLIMITED;
ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME UNLIMITED;

PROMPT 3. ALTER PASSWORD

SELECT username
FROM dba_users d, sys.user$ u
WHERE d.username = u.name
AND TRIM(u.password) IS NOT NULL
AND ACCOUNT_STATUS IN ('EXPIRED(GRACE)', 'OPEN')
ORDER BY username;

SET FEEDBACK ON
@@_alter_pwd.sql
SET FEEDBACK OFF

PROMPT 4. LIMIT PROFILE

ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_MAX 10;
ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME 365;

SELECT '5. END : ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;

PROMPT ===============================================================
PROMPT

SPOOL OFF

EXIT

 

3. 변경 일시 확인 (생성된 chg_pwd.log 확인하거나 아래 SQL문으로 변경일시-ptime- 확인)

SELECT u.name, u.ptime, u.password
FROM dba_users d, sys.user$ u
WHERE d.username = u.name
AND TRIM(u.password) IS NOT NULL
AND ACCOUNT_STATUS IN ('EXPIRED(GRACE)', 'OPEN')
ORDER BY username;