유저의 상태를 확인하는 SQL입니다.

SELECT USERNAME,  ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE, PROFILE
FROM DBA_USERS
where ACCOUNT_STATUS != 'OPEN';

USERNAME  ACCOUNT_STATUS    LOCK_DATE          EXPIRY_DATE        PROFILE
---------- ----------------- ------------------- ------------------- --------
OUTLN      EXPIRED & LOCKED  2012/01/27 17:12:07 2012/01/27 17:12:07  DEFAULT
DIOFTP    EXPIRED                                              2012/02/08 12:03:54  DEFAULT
PUSER      LOCKED                    2012/02/08 12:03:54                  DEFAULT
WOONG      EXPIRED(GRACE)                                2012/05/18 12:49:01  DEFAULT

DEFAULT Profile을 쓰는 유저중에 몇몇 정상적이지 않은 상황으로 보이네요.
해당 프로파일을 먼저 확인해봅니다.

SELECT *
FROM DBA_PROFILES
WHERE PROFILE = 'DEFAULT'
AND RESOURCE_TYPE LIKE 'PASSWORD';

PROFILE  RESOURCE_NAME            RESOURCE LIMIT         
-------- ------------------------- -------- ----------------
DEFAULT  FAILED_LOGIN_ATTEMPTS    PASSWORD 5 
DEFAULT  PASSWORD_LIFE_TIME        PASSWORD 90             
DEFAULT  PASSWORD_REUSE_TIME      PASSWORD 1440           
DEFAULT  PASSWORD_REUSE_MAX        PASSWORD 10             
DEFAULT  PASSWORD_VERIFY_FUNCTION  PASSWORD VERIFY_FUNCTION
DEFAULT  PASSWORD_LOCK_TIME        PASSWORD 90             
DEFAULT  PASSWORD_GRACE_TIME      PASSWORD 7


패스워드와 관련된 리소스를 확인해보면 다음과 같습니다.
FAILED_LOGIN_ATTEMPTS
: 로그인을 실패했을 때 로그인이 제한되는 횟수. (위 설정에서는 다섯번이 되겠네요.)


PASSWORD_LIFE_TIME
: 암호를 변경해야만 하는 일수를 의미. (마찬가지로 90일동안 사용가능하겠네요.)


PASSWORD_LOCK_TIME
: 암호 오입력으로 로그인 실패시 일시동안 락을 걸 시간을 의미.(1분(1/(24*60))동안 잠깁니다.)


PASSWORD_GRACE_TIME
: 기존에 사용했던 암호를 다시 사용하기 위해 경과 되어야하는 시간을 의미.


PASSWORD_REUSE_TIME
: 기존에 사용했던 암호를 바로 다시 사용하지 못하도록 몇개의 암호를 재입력하지 못하게 한다.


PASSWORD_REUSE_MAX
: 암호 변경안내 메세지를 출력하기 시작할 -Day를 의미하며 이 설정으로 인해 ORA-28002 동작.


PASSWORD_VERIFY_FUNCTION
: 암호 복잡성 여부를 설정 할 수 있음.

최초 설치 시 각 회사에서 정한 보안규정에 맞게 아래처럼 PROFILE을 조정한 후 Profile에 대한 Limit을 해제하게 되면 Expired를 만나보지 못하겠지요. 간혹 이 부분을 간과하고 EXPIRED되는 경우가 발생합니다. 맨위의 예처럼요.

그런 경우 다음 절차로 처리하게 됩니다.

1. 프로파일을 먼저 변경합니다.
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED;

2. LOCK으로 상태가 변했다면
ALTER USER username ACCOUNT UNLOCK;

3. 이미 만기(EXPIRED)나 만기도래(EXPIRED(GRACE))가 된 경우 OPEN상태로 변경하기 위해서는 패스워드변경이 불가피합니다.

  암호를 모른다면..권한분리로 DBA가 모든 암호를 알지 못하는 시대죠? 또, DBA권한을 개발자에게 줄 수도 없습니다.

이런 상황에서..
3-1 sys.USER$테이블에서 암호화된 패스워드를 구합니다.

SELECT PASSWORD
FROM SYS.USER$
WHERE NAME = 'username';

3-2 그리고 identified by value구문을 이용하여 패스워드를 덧씌웁니다.
ALTER USER IDENTIFIED BY VALUE '위에서 구한 암호화된 패스워드'