KangKoon's Made ʕ•͡.̮•ʔ: 오라클의 개념

ㅇ ㅂㅇ

프로파일
프로파일은 다음 암호 및 자원 제한을 명명한 집합입니다.
• 암호 만기일 기능 및 암호 만기
• 암호 기록
• 암호 복잡성 확인
• 계정 잠금
• CPU 시간
• I/O(입출력) 작업
• 휴지 시간
• 연결 시간
• 메모리 공간(공유 서버만을 위한 전용 SQL 영역)
• 동시 세션

프로파일을 생성하면 데이터베이스 관리자가 이를 각 사용자에게 할당할 수 있으며 자원
제한을 활성화하면 Oracle 서버는 해당 사용자에게 정의한 프로파일에 따라 데이터베이스
사용 및 자원을 제한합니다.
Oracle 서버는 데이터베이스 생성 시 DEFAULT 프로파일을 자동으로 생성합니다.
특정 프로파일에 명시적으로 할당되지 않은 사용자는 DEFAULT 프로파일의 모든 제한을
따르는데 처음에는 모든 DEFAULT 프로파일에 제한이 없지만 데이터베이스 관리자는 기
본적으로 모든 사용자에게 제한을 적용하도록 값을 변경할 수 있습니다.

프로파일용도
• 자원을 많이 사용해야 하는 일부 작업을 사용자가 수행할 수 없도록 제한합니다.
• 사용자의 세션이 일정 시간 동안 휴지 상태면 데이터베이스를 로그오프하도록 합니다.
• 유사한 사용자에 대한 그룹 자원 제한을 활성화합니다.
• 사용자에게 자원 제한을 쉽게 할당합니다.
• 대용량의 복잡한 다중 사용자 데이터베이스 시스템에서 자원 사용을 관리합니다.
• 암호 사용을 제어합니다.

프로파일특성 • 프로파일 할당은 현재 세션에 영향을 주지 않습니다.
• 프로파일은 사용자에게만 할당할 수 있고 롤 또는 다른 프로파일에는 할당할 수 없습니다.
• 사용자 생성 시 프로파일을 할당하지 않으면 DEFAULT 프로파일을 자동으로 할당합니다.

암호 관리
데이터베이스 보안을 철저히 관리하기 위해 데이터베이스 관리자는 프로파일을 사용하여
Oracle 암호 관리를 제어합니다.
이 단원에서는 사용 가능한 암호 관리 기능에 대해 설명합니다.
• 계정 잠금: 사용자가 지정한 시도 횟수 내에 시스템에 로그인하지 못하면 계정을 자동으로 잠글 수 있습니다.
• 암호 만기일 기능 및 암호 만기: 암호에 실행 주기가 있어 암호가 만료되면 바꾸어야 합니다.
• 암호 기록: 지정한 기간 또는 암호 변경 횟수 동안 암호를 재사용하지 않았는지 확인하기 위해 새 암호를 검사합니다.
• 암호 복잡성 확인: 암호를 추측하여 시스템에 침입하려고 하는 침입자를 방지할 수 있을 만큼 암호가 복잡한지 확인하기 위해 암호의 복잡성을 검사합니다.

암호 관리 활성화

CREATE USER 또는 ALTER USER 명령을 사용하여 암호 설정을 제한할 프로파일을 생성
한 다음 해당 프로파일을 사용자에게 할당합니다.
프로파일 내의 암호 제한 설정은 항상 시행합니다.
암호 관리를 활성화한 상태에서 CREATE USER 또는 ALTER USER 명령을 사용하여 사용자계정을 잠그거나 잠금을 해제할 수 있습니다.

암호 계정 잠금
FAILED_LOGIN_ATTEMPTS 값에 도달하면 Oracle 서버가 자동으로 계정을 잠그며 이 계
정은 PASSWORD_LOCK_TIME이 정의한 시간이 지나면 자동으로 잠금을 해제하거나 또는 데이터베이스 관리자가 ALTER USER 명령을 사용하여 잠금을 해제해야 합니다.
데이터베이스 계정은 ALTER USER 명령을 사용하여 명시적으로 잠글 수 있으며 이 경우에는 계정 잠금을 자동으로 해제하지 않습니다.

암호 만기일 기능 및 암호 만기
PASSWORD_LIFE_TIME 매개변수는 암호를 변경해야 하는 최대 실행 주기를 설정합니다.
데이터베이스 관리자는 암호 만기 후 처음으로 해당 데이터베이스에 로그인할 때부터 시
작되는 유예 기간(PASSWORD_GRACE_TIME)을 지정할 수 있는데 이 유예 기간이 지날 때
까지는 사용자가 로그인하려고 할 때마다 경고 메시지를 생성하므로 유예 기간 안에 암호
를 변경합니다.
암호를 바꾸지 않으면 계정을 사용할 수 없습니다.
명시적으로 암호를 만료한 것으로 설정하여 사용자 계정 상태를 EXPIRED로 변경합니다.

> PASSWORD_LIFE_TIME은 경고만 이며 계정을 제한 하지 않음.
> PASSWORD_GRACE_TIME은 해당 기간 동안 경고 후 제한

암호 기록
암호 기록 검사는 사용자가 지정한 기간 동안 암호를 재사용할 수 없음을 확인하며 이러한 검사는 다음 중 하나를 사용하여 수행합니다.
• PASSWORD_REUSE_TIME: 주어진 일 수 동안 암호를 재사용할 수 없도록 지정하려면 PASSWORD_REUSE_TIME을 사용합니다.
• PASSWORD_REUSE_MAX: 사용자에게 이전 암호와 다른 암호를 정의하도록 하려면 PASSWORD_REUSE_MAX를 사용합니다. > 암호 변경 가능 횟수
매개변수를 DEFAULT 또는 UNLIMITED 외의 값으로 설정한 경우에는 다른 매개변수를
UNLIMITED로 설정해야 합니다.

암호 확인
사용자에게 새 암호를 할당하기 전에 PL/SQL 함수를 호출하여 해당 암호의 유효성을 확인 할 수 있습니다.
Oracle 서버가 기본 확인 루틴을 제공하거나 데이터베이스 관리자가 PL/SQL 함수를 작성 할 수 있습니다.

사용자 제공 암호 함수
새 암호 확인 함수를 추가할 경우 데이터베이스 관리자는 다음 제한 사항을 고려해야 합니다.
• 프로시저는 슬라이드에 표시한 사양을 사용해야 합니다.
• 프로시저는 성공인 경우 TRUE 값을, 실패인 경우 FALSE 값을 반환합니다.
• 암호 함수에서 예외 사항이 발생하면 오류를 반환하면서 ALTER USER 또는 CREATE USER 명령을 종료합니다.
• 암호 함수는 SYS 소유입니다.
• 암호 함수가 사용할 수 없게 되면 오류 메시지가 반환되며 ALTER USER 또는 CREATE USER 명령이 종료됩니다.

> BOOLEAN Type 이므로 1일 때는 사용 가능, 0일 때는 사용 불가

암호 확인 함수
Oracle 서버는 utlpwdmg.sql 스크립트에 의해 VERIFY_FUNCTION이라고 하는 기본
PL/SQL 함수 형태로 복잡성 확인 함수를 제공하며 이 함수는 SYS 스키마에서 실행합니다.
utlpwdmg.sql 스크립트를 실행하는 동안 Oracle 서버는 VERIFY_FUNCTION을 생성한
후 다음 ALTER PROFILE 명령을 사용하여 DEFAULT 프로파일을 변경합니다.
SQL> ALTER PROFILE DEFAULT LIMIT
2 PASSWORD_LIFE_TIME 60
3 PASSWORD_GRACE_TIME 10
4 PASSWORD_REUSE_TIME 1800
5 PASSWORD_REUSE_MAX UNLIMITED
6 FAILED_LOGIN_ATTEMPTS 3
7 PASSWORD_LOCK_TIME 1/1440
8 PASSWORD_VERIFY_FUNCTION verify_function;

프로파일 생성
다음 CREATE PROFILE 명령을 사용하여 암호를 관리합니다.
CREATE PROFILE profile LIMIT
[FAILED_LOGIN_ATTEMPTS max_value]
[PASSWORD_LIFE_TIME max_value]
[ {PASSWORD_REUSE_TIME
|PASSWORD_REUSE_MAX} max_value]
[PASSWORD_LOCK_TIME max_value]
[PASSWORD_GRACE_TIME max_value]
[PASSWORD_VERIFY_FUNCTION
{function|NULL|DEFAULT} ]

설명
PROFILE: 생성할 프로파일 이름입니다.
FAILED_LOGIN_ATTEMPTS: 사용자 계정을 잠그기 전에 사용자 계정으로 로그인 시도를 실패할 수 있는 횟수를 지정합니다.
PASSWORD_LIFE_TIME: 인증을 위해 동일한 암호를 사용할 수 있는 일 수를 제한하는데 이 기간 내에 암호를 바꾸지 않으면 암호가 만료되어 이후 연결을 거부합니다.
PASSWORD_REUSE_TIME: 암호를 재사용할 수 있게 될 때까지의 일 수를 지정하는데 PASSWORD_REUSE_TIME을 정수 값으로 설정한 경우에는 PASSWORD_REUSE_MAX를UNLIMITED로 설정해야 합니다.
PASSWORD_REUSE_MAX: 현재 암호를 재사용할 수 있기 전에 필요한 암호 변경 횟수를 지정하는데 PASSWORD_REUSE_MAX를 정수 값으로 설정한 경우에는 PASSWORD_REUSE_TIME을 UNLIMITED로 설정해야 합니다.
PASSWORD_LOCK_TIME: 지정된 로그인 연속 실패 횟수 이후 계정을 잠그는 일 수를 지정합니다.
PASSWORD_GRACE_TIME: 경고는 표시하지만 로그인을 허용하는 유예 기간 일 수를 지정하는데 유예 기간 동안 암호를 바꾸지 않으면 암호가 만료됩니다.
PASSWORD_VERIFY_FUNCTION: PL/SQL 암호 복잡성 확인 함수를 CREATE PROFILE 문에 인수로 전달하도록 합니다.

프로파일 변경
ALTER PROFILE 명령을 사용하여 프로파일에 지정한 암호 제한을 변경할 수 있습니다.
ALTER PROFILE profile LIMIT
[FAILED_LOGIN_ATTEMPTS max_value]
[PASSWORD_LIFE_TIME max_value]
[ {PASSWORD_REUSE_TIME
|PASSWORD_REUSE_MAX} max_value]
[PASSWORD_LOCK_TIME max_value]
[PASSWORD_GRACE_TIME max_value]
[PASSWORD_VERIFY_FUNCTION
{function|NULL|DEFAULT} ]
암호 매개변수를 하루 미만으로 설정하려는 경우:
1시간: PASSWORD_LOCK_TIME = 1/24
10분: PASSWORD_LOCK_TIME = 10/1400
5분: PASSWORD_LOCK_TIME = 5/1440

프로파일 삭제 : 암호 설정
DROP PROFILE 명령을 사용하여 프로파일을 삭제합니다.
DROP PROFILE profile [CASCADE]
설명:
profile: 삭제할 프로파일 이름입니다.
CASCADE: 프로파일을 할당한 사용자로부터 프로파일을 취소합니다. (Oracle 서버는 이러
한 사용자에게 DEFAULT 프로파일을 자동으로 할당하며 이 옵션을 지정하여 사용자에게
현재 할당되어 있는 프로파일을 삭제합니다.)
지침
• DEFAULT 프로파일은 삭제할 수 없습니다.
• 프로파일 삭제 시 변경 사항은 나중에 생성하는 세션에만 적용되고 현재 세션에는 적용되지 않습니다.

자원관리
다음 단계를 사용하여 프로파일로 자원의 사용을 제어합니다.
1. CREATE PROFILE 명령으로 프로파일을 생성하여 자원 및 암호 제한을 결정합니다.
2. CREATE USER 또는 ALTER USER 명령을 사용하여 프로파일을 할당합니다.
3. ALTER SYSTEM 명령을 사용하거나 초기화 매개변수 파일을 편집한 다음 인스턴스를 정지하고 재시작하여 자원 제한을 시행합니다.

이러한 단계는 다음 부분에서 상세하게 설명합니다.

자원 제한 활성화
RESOURCE_LIMIT 초기화 매개변수를 변경하거나 ALTER SYSTEM 명령을 사용하여 자원 제한 시행을 활성화 또는 비활성화합니다.
RESOURCE_LIMIT초기화매개변수
• 자원 제한 시행을 활성화 또는 비활성화하려면 초기화 파일에서 이 매개변수를 변경한 다음 인스턴스를 재시작합니다.
• TRUE 값은 시행을 활성화합니다.
• FALSE 값은 시행을 비활성화합니다. (기본값)
• 이 매개변수를 사용하여 강제 시행 구조를 활성화합니다.
• Use this parameter to enable enforcement architecture.
ALTER SYSTEM명령
• 인스턴스에 대한 자원 제한 시행을 활성화 또는 비활성화하려면 ALTER SYSTEM 명령을사용합니다.
• ALTER SYSTEM 명령을 사용하여 지정한 설정은 다시 변경하거나 해당 데이터 베이스를 종료할 때까지 유효합니다.
• 데이터베이스를 종료할 수 없을 경우에는 이 명령을 사용하여 시행을 활성화 또는 비활성화합니다.

세션 레벨에서 자원 제한 설정
지침
프로파일 제한은 세션 레벨, 호출 레벨 또는 두 레벨 모두에서 시행할 수 있는데 세션 레벨제한은 연결할 때마다 시행합니다.
세션 레벨 제한 초과
• 다음과 같은 오류 메시지를 반환합니다.
ORA-02391: exceeded simultaneous SESSION_PER_USER limit
• Oracle 서버는 사용자 연결을 해제합니다.
지침
• IDLE_TIME은 서버 프로세스만 계산하고 응용 프로그램 작업은 고려하지 않으며 장시간 실행하는 질의 및 다른 작업은 IDLE_TIME에 영향을 주지 않습니다.
• LOGICAL_READS_PER_SESSION은 메모리 및 디스크에서의 전체 읽기 횟수를 제한하며 I/O 집중 명령문이 메모리를 과도하게 사용하거나 디스크를 독점하여 사용할 수 없도록 합니다.
• PRIVATE_SGA는 공유 서버 구조를 실행할 때만 적용하며 MB 또는 KB로 지정할 수 있습니다.

호출 레벨에서 자원 제한 설정

호출 레벨 제한은 SQL 문 실행 중에 호출할 때마다 시행합니다.
호출 레벨 제한 초과
• 명령문 처리를 중지합니다.
• 명령문을 롤백합니다.
• 이전 명령문은 모두 그대로 남아 있습니다.
• 사용자의 세션은 연결한 상태로 남아 있습니다.

프로파일 생성 : 자원 제한
다음 CREATE PROFILE 명령을 사용하여 프로파일을 생성합니다.
CREATE PROFILE profile LIMIT
[SESSIONS_PER_USER max_value]
[CPU_PER_SESSION max_value]
[CPU_PER_CALL max_value]
[CONNECT_TIME max_value]
[IDLE_TIME max_value]
[LOGICAL_READS_PER_SESSION max_value]
[LOGICAL_READS_PER_CALL max_value]
[COMPOSITE_LIMIT max_value]
[PRIVATE_SGA max_bytes]

설명:
profile: 프로파일 이름입니다.
max_value: 정수로 UNLIMITED 또는 DEFAULT입니다.
max_bytes: 선택적으로 뒤에 KB 또는 MB가 붙는 정수로 UNLIMITED 또는 DEFAULT입
니다.
UNLIMITED: 이 프로파일을 할당받은 사용자는 이 자원을 무제한 사용할 수 있음을
나타냅니다.
DEFAULT: DEFAULT 프로파일에서 지정한 대로 이 프로파일은 이 자원에 대해 제한
함을 나타냅니다.
COMPOSITE_LIMIT: 서비스 단위로 표현한 세션에 대해 전체 자원 비용을 제한하며
Oracle은 다음 가중 합계로서 자원 비용을 계산합니다.
– CPU_PER_SESSION
– CONNECT_TIME
– LOGICAL_READS_PER_SESSION
– PRIVATE_SGA
RESOURCE_COST 데이터 딕셔너리 뷰는 다른 자원에 할당한 자원 제한을 제공합니다.

암호 및 자원 제한 정보 얻기
DBA_USERS를 사용하여 계정 상태에 대한 정보를 얻을 수 있습니다.
SQL> SELECT username, password, account_status,
2 FROM dba_users;

USERNAME PASSWORD ACCOUNT_STATUS
------------- ----------------------- ----------------------
SYS 8A8F025737A9097A OPEN
SYSTEM D4DF7931AB130E37 OPEN
OUTLN 4A3BA55E08595C81 OPEN
DBSNMP E066D214D5421CCC OPEN
HR BB69FBB77CFA6B9A OPEN
OE 957C7EF29CC223FC LOCKED

---------------------------------------------------------------------------------------------------

실습

1. Profile 조회

* 명령어

SELECT DISTINCT PROFILE FROM DBA_PROFILES;
- Profile의 목록을 확인하는 명령어

SELECT * FROM DBA_PROFILES
ORDER BY RESOURCE_TYPE;
- 각 Profile에 정의된 설정 값을 확인 하는 명령어

SELECT USERNAME, PROFILE FROM DBA_USERS;
- 각 User에게 할당된 Profile을 조회하는 명령어

모든 사용자가 DEFAULT Profile에 따라 제한됨

(Default 는 파일명으로 Profile이 지정 되지 않았을 때 자동으로 지정)

2. Profile 생성과 제한 설정

Profile 설정은 password와 리소스 두부분으로 나누어지는데 이중 리소스 관련 설정은 resource_limit가 반드시 true로 정의 되었을 때 유효함.

CREATE PROFILE LIMITED - Profile을 생성함

- UNLIMITED : 제한을 두지 않음.
- DEFAULT : DEFAULT profile과 동일한 값을 가짐
COMPOSITE_LIMT [<설정값> | UNLIMITED | DEFAULT]
- COMPOSITE_LIMIT : CONNECT_TIME, PRIVATE_SGA, CPU_PER_SESSION, READ_PER_SESSION 등의 값을 통해서 제한 함.
SESSION_PER_USER [<설정값> | UNLIMITED | DEFAULT]
- SESSION_PER_USER : 계정 당 접속 가능한 세션 숫자.
PRIVATE_SGA [<설정값> | UNLIMITED | DEFAULT]
- PRIVATE_SGA : Shared server 환경에서 SGA에 사용가능한 SP 전용 메모리 크기 (MB)
CONNECT_TIME [<설정값> | UNLIMITED | DEFAULT]
- CONNECT_TIME : 접속 유효 시간 (분 단위)
IDLE_TIME [<설정값> | UNLIMITED | DEFAULT]
- IDLE_TIME : 비활성(아무런 행동 없이) 접속 한계 (분 단위)
LOGICAL_READS_PER_CALL [<설정값> | UNLIMITED | DEFAULT]
- LOGICAL_READS_PER_CALL : 한 문장에서 읽기 가능한 block 개수
LOGICAL_READS_PER_SESSION [<설정값> | UNLIMITED | DEFAULT]
- LOGICAL_READS_PER_SESSION : 한 session에서 읽기 가능한 block 개수
CPU_PER_CALL [<설정값> | UNLIMITED | DEFAULT]
- CPU_PER_CALL : 한 문장에서 사용 가능한 CPU 시간 (1/100초)
CPU_PER_SESSION [<설정값> | UNLIMITED | DEFAULT]
- CPU_PER_SESSION : 한 session에서 사용 가능한 CPU 시간 (1/100초)
PASSWORD_VERIFY_FUNCTION [<설정값> | UNLIMITED | DEFAULT]
- PASSWORD_VERIFY_FUNCTION : Password 복잡성을 확인 하는 함수
PASSWORD_REUSE_MAX [<설정값> | UNLIMITED | DEFAULT]
- PASSWORD_REUSE_MAX : Password 재사용 가능까지 Password를 변경해야 하는 횟수
PASSWORD_REUSE_TIME [<설정값> | UNLIMITED | DEFAULT]
- PASSWORD_REUSE_TIME : Password 재사용 가능까지 제한 하는 시간
PASSWORD_LIFE_TIME [<설정값> | UNLIMITED | DEFAULT]
- PASSWORD_LIFE_TIME : Password의 유효 기간
FAILED_LOGIN_ATTEMPTS [<설정값> | UNLIMITED | DEFAULT]
- FAILED_LOGIN_ATTEMPTS : Password 오류(잘못입력) 연속 허용 횟수
PASSWORD_LOCK_TIME [<설정값> | UNLIMITED | DEFAULT]
- PASSWORD_LOCK_TIME : Password 오류에 의해 Lock이 유지 되는 시간 (일 단위)
PASSWORD_GRACE_TIME [<설정값> | UNLIMITED | DEFAULT]
- PASSWORD_GRACE_TIME : Password 만료 이후 암호 변경 까지 유예 기간 (일 단위)

-Profile에 대한 ALTER 문장은 create 문장과 동일함.

SHOW PARAMETER resource_limit;

ALTER SYSTEM SET resource_limit=true;

SHOW PARAMETER resource_limit;

create profile insa limit
sessions_per_user 1
idle_time 5
CONNECT_time 10;

SELECT * FROM dba_profiles
WHERE profile = 'INSA'
ORDER BY 3;

ALTER profile insa limit
failed_login_attempts 3
password_lock_time 1;

SELECT * FROM dba_profiles
WHERE profile = 'INSA'
ORDER BY 3;

3. Profile 할당과 적용

ALTER USER
PROFILE ;
- 사용자에게 profile을 할당함
- CREATE USER 명령을 통해 할당하는 것
- User에 Profile을 지정하지 않으면 default profile에 적용을 받음

create tablespace insa
datafile '/app/ora11g/oradata//insa01.dbf' SIZE 10M;
명령어를 통해 테이블 스페이스 생성

create user insa
IDENTIFIED BY insa
default tablespace insa
temporary tablespace temp
명령어를 통해 유저를 생성