[Oracle - Password 에러] ORA-20001 , ORA-65096

안녕하세요 이번글에선 오라클에서 유저 생성시 흔히 볼 수 있는 에러와 해결 방법을 포스팅하겠습니다.

 

 

ORA-20001

 

■ user 생성 시 패스워드가 8자리 미만이여서 에러 발생

SQL> create user kim identified by kim;
create user kim identified by kim
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 8        

 

■ 해결 방법

#vi /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlpwdmg.sql       => 패스워드 limit 확인
Rem *************************************************************************
Rem BEGIN Password Management Parameters
Rem *************************************************************************

-- This script alters the default parameters for Password Management
-- This means that all the users on the system have Password Management
-- enabled and set to the following values unless another profile is
-- created with parameter values set to different value or UNLIMITED
-- is created and assigned to the user.

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX  UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
INACTIVE_ACCOUNT_TIME UNLIMITED
PASSWORD_ROLLOVER_TIME 0
PASSWORD_VERIFY_FUNCTION ora12c_verify_function;

/**
The below set of password profile parameters would take into consideration
recommendations from Center for Internet Security[CIS Oracle 11g].

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 3
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX  20
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION ora12c_verify_function;
*/




SQL> select * from user_password_limits;

RESOURCE_NAME             LIMIT
-------------------------------- ----------------------------------------
FAILED_LOGIN_ATTEMPTS         3
PASSWORD_LIFE_TIME         60
PASSWORD_REUSE_TIME         365
PASSWORD_REUSE_MAX         5
PASSWORD_VERIFY_FUNCTION     VERIFY_FUNCTION_11G         => 해당 파라미터 null로 수정
PASSWORD_LOCK_TIME         1
PASSWORD_GRACE_TIME         7

7 rows selected.



SQL> alter profile DEFAULT limit PASSWORD_VERIFY_FUNCTION null;

Profile altered.




SQL> select * from user_password_limits;

RESOURCE_NAME             LIMIT
-------------------------------- --------------------------------------------------------------------------------------------------------------------------------
FAILED_LOGIN_ATTEMPTS         3
PASSWORD_LIFE_TIME         60
PASSWORD_REUSE_TIME         365
PASSWORD_REUSE_MAX         5
PASSWORD_VERIFY_FUNCTION     NULL
PASSWORD_LOCK_TIME         1
PASSWORD_GRACE_TIME         7
INACTIVE_ACCOUNT_TIME         UNLIMITED
PASSWORD_ROLLOVER_TIME         0

9 rows selected.

 

 

 

 

ORA-65096

SQL> create user kim identified by kim;
create user kim identified by kim
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

 

 

 

오라클 12c부터는 공통계정앞에 c##을 붙이도록 네이밍 규칙이 바뀌었습니다. C## 키워드가 붙는 이유는 12c 버전부터 등장하는 CDB, PDB 개념 때문이라고 합니다.

 

 

■ c##을 붙여주니 유저가 정상적으로 만들어집니다.

SQL> create user c##kim identified by kim;

User created.

 

 

 

■ 권한 줄때도 필요합니다..

SQL> grant dba to c##kim;

Grant succeeded.

 

 

 

■ 앞에 c##을 안붙이고 11g처럼 편하게 하기 위해서는 아래와 같이 파라미터 수정하면 됩니다.

SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.