계정 프로파일 ( PROFILE )

[ 1 ] 프로파일 목적

1. 자원 관리
   • CPU, 메모리, 세션 수, 기타 자원 사용량 제한
   • 데이터베이스 자원이 특정 사용자에게 독점되지 않도록 설정
2. 보안 정책 강화
   • 비밀번호 정책(복잡성, 길이, 만료 기간 등) 설정
   • 계정 잠금 및 비밀번호 실패 횟수 제한
3. 시스템 안정성 보장
   • 무한 세션 생성이나 자원 낭비 방지

 

 

[ 2 ] 프로파일 항목별 분류 

 

1. 세션 및 CPU 제한

RESOURCE_NAME	DESCRIPTION
SESSIONS_PER_USER	사용자당 생성할 수 있는 동시 세션 수의 최대값.
CPU_PER_SESSION	세션당 사용할 수 있는 CPU 시간(백만 분의 1초 단위).
CPU_PER_CALL	SQL 호출당 사용할 수 있는 CPU 시간(백만 분의 1초 단위).
CONNECT_TIME	세션 연결 시간(분 단위)에 대한 최대값.
IDLE_TIME	세션이 비활성 상태로 유지될 수 있는 최대 시간(분 단위).

 

 

2. 읽기 작업 제한

RESOURCE_NAME	DESCRIPTION
LOGICAL_READS_PER_SESSION	세션당 읽을 수 있는 논리 블록(데이터 블록)의 최대값.
LOGICAL_READS_PER_CALL	SQL 호출당 읽을 수 있는 논리 블록(데이터 블록)의 최대값.

 

 

3. 비밀번호 정책

RESOURCE_NAME	DESCRIPTION
PASSWORD_LIFE_TIME	비밀번호의 유효 기간(일 기준).
PASSWORD_GRACE_TIME	비밀번호 만료 전 경고 기간(일 기준).
PASSWORD_REUSE_TIME	이전 비밀번호를 다시 사용할 수 있기까지의 기간(일 기준).
PASSWORD_REUSE_MAX	같은 비밀번호를 재사용할 수 있는 최대 횟수.
PASSWORD_VERIFY_FUNCTION	비밀번호의 복잡성을 검증하는 함수. NULL로 설정하면 기본 검증 없음.
PASSWORD_LOCK_TIME	비밀번호 실패로 인해 계정이 잠겼을 때 자동 해제까지 걸리는 시간(일 기준).
FAILED_LOGIN_ATTEMPTS	비밀번호 입력 실패 허용 횟수.

 

 

4. 메모리 및 자원 제한

RESOURCE_NAME	DESCRIPTION
PRIVATE_SGA	특정 사용자 세션이 사용할 수 있는 전용 SGA 메모리 크기 제한(바이트 단위).
COMPOSITE_LIMIT	CPU, 메모리, I/O 등 종합 자원의 최대값(서비스 유닛 단위).

 

 

5. 계정 활동 제한 (Oracle 19c 이상)

RESOURCE_NAME	DESCRIPTION
INACTIVE_ACCOUNT_TIME	계정이 비활성 상태로 유지될 수 있는 최대 기간(일 기준).

 

 

[ 3 ] 프로파일 테스트

  3.1 테스트 시나리오 
      1.1. STUNDENT_PROFILE을 생성하고, 해당 프로파일에 읽기 작업 제한을 둔다. 
         - 읽기 작업 제한 : Logical_Reads_Per_session, Logical_Reads_Per_Call 
      1.2. Logical_Reads_Per_Call : SQL 호출 Cal 당 10,000 블록 읽지 않도록 하기 
      1.3. KTH 계정에 STUDENT_PROFILE 적용 
      1.4  첫번째 테스트  : KTH 계정 접속 후 DBA_TABLES 조회 
      1.5. Logical_Reads_Per_Call : Unlimited ( 무한대 ) 설정   
             Logical_Reads_Per_session : 1 Call에 10,000 Block 초과하지 않도록 하기
      1.6  두번째 테스트  : KTH 계정 신규 접속 후 DBA_TABLES 조회 
      1.7  Profile 삭제 
      1.8  Profile 강제 삭제
   

 

 

---

    3.2 테스트 시나리오 수행  
      3.2.1. STUNDENT_PROFILE을 생성하고, 해당 프로파일에 읽기 작업 제한을 둔다. ( SYS 계정으로 수행 )
         1) 읽기 작업 제한 항목 2가지 : Logical_Reads_Pers_session, Logical_Reads_Per_Call
         2) SYS 계정으로 프로파일을 생성하더라도, ora-65140 : invalid common profile name 에러가 발생한다.
             해결책 : alter session set "_oracle_script" = true; 를 수행후, 아래 PROFILE 생성하기.

CREATE PROFILE STUDENT_PROFILE LIMIT
COMPOSITE_LIMIT	UNLIMITED
PRIVATE_SGA	UNLIMITED
INACTIVE_ACCOUNT_TIME	UNLIMITED
PASSWORD_GRACE_TIME	7
PASSWORD_LOCK_TIME	1
PASSWORD_VERIFY_FUNCTION	NULL
PASSWORD_REUSE_MAX	UNLIMITED
PASSWORD_REUSE_TIME	UNLIMITED
PASSWORD_LIFE_TIME	180
FAILED_LOGIN_ATTEMPTS	10
CONNECT_TIME	UNLIMITED
IDLE_TIME	UNLIMITED
LOGICAL_READS_PER_CALL	UNLIMITED
LOGICAL_READS_PER_SESSION	UNLIMITED
CPU_PER_CALL	UNLIMITED
CPU_PER_SESSION	UNLIMITED
SESSIONS_PER_USER	UNLIMITED
;

 

---

      3.2.2. Logical_Reads_Per_session : SQL 호출 당 10,000 블록 읽지 않도록 하기 ( SYS 계정으로 수행 )

ALTER PROFILE STUDENT_PROFILE LIMIT 
LOGICAL_READS_PER_CALL 10000 ;

-- BUFFER 알아보기
SELECT SQL_TEXT, BUFFER_GETS FROM V$SQL WHERE SQL_TEXT LIKE '%1004%'

 

---

    3.2.3. KTH 계정에 STUDENT_PROFILE 적용  ( SYS 계정으로 수행 )

ALTER USER KTH PROFILE STUDENT_PROFILE;

 

---

    3.2.4  첫번째 테스트  : KTH 계정 접속 후 DBA_TABLES 조회  ( 테스트 계정 "KTH" 로 수행 )

SELECT /* 10041 */ * FROM DBA_TABLES WHERE ROWNUM <= 1600;
SELECT /* 10042 */ * FROM DBA_TABLES WHERE ROWNUM <= 1700;

ORA-02395가 발생한 10042 주석이 있는 SQL은 BUFFER가 10,000이 넘는다

ORA-02395 : 위 10042 SQL 호출시 발생하는 에러이다. Call 마다 최대 읽을 수 있는 허용치인 10,000을 초과하기 때문이다.

 

---

    3.2.5. Logical_Reads_Per_Call : Unlimited ( 무한대 ) 설정 ( SYS 계정으로 수행 )   
              Logical_Reads_Per_session : 세션 누적 Logical Block이 10,000 Block 초과하지 않도록 하기
                                                                     ( SYS 계정으로 수행 )

ALTER PROFILE STUDENT_PROFILE LIMIT 
LOGICAL_READS_PER_CALL UNLIMITED 
LOGICAL_READS_PER_SESSION 10000;

 

---

    3.2.6  두번째 테스트  : KTH 계정 "신규" 접속 후 DBA_TABLES 조회   ( 테스트 계정 "KTH" 로 수행 )

SELECT /* 10041 */ * FROM DBA_TABLES WHERE ROWNUM <= 1600; -- 2805 블록 소모

ORA-02394 : 위 10041 SQL을 4번째 반복 수행하게되면 발생된다. 왜냐하면 4번 수행시 세션당 읽을수 있는 허용치인 10,000을 초과하기 때문이다.

 

---

    3.2.7 Profile 삭제 
      : 불필요한 Profile을 삭제시도하였으나, 유저가 사용 중이면 삭제 못하여 아래 에러가 발생한다.

DROP PROFILE STUDENT_PROFILE;

KTH 계정에 "STUDENT_PROFILE"을 사용중이므로 삭제되지 않는다.

 

---

    3.2.8 Profile 강제 삭제 
       : 계정에 사용중인 Profile은 Cascade Option을 기입하여 강제 삭제 가능하다. 이후 게정의 Profile을 보면
         Default Profile로 변경되어 있다. 

DROP PROFILE STUDENT_PROFILE CASCADE;

STUNDENT_PROFILE -> DEFAULT PROFILE로 변경됨

 

효율적인 DB "자원관리",  "보안정책",  "시스템 안정성"을 위해서는, 계정별 프로파일 정책을 수립해야한다.
불필요한 작업 및 자원 낭비 제거 할 수 있는 좋은 기능이며, DB 안정성에도 많은 도움이 된다.