SQLD: 오답노트(3)

1. ALL과 ANY

정의

• ALL: 서브쿼리의 모든 결과값과 비교하여 조건이 모두 만족할 때만 TRUE 반환
• ANY: 서브쿼리의 결과값 중 하나라도 조건을 만족하면 TRUE 반환

사용법

-- ALL 구문
SELECT column_name
FROM table_name
WHERE column_name operator ALL (subquery);

-- ANY 구문
SELECT column_name
FROM table_name
WHERE column_name operator ANY (subquery);

비교 연산자(operator) 종류:

• = (같음)

• (큼)

• < (작음)

• = (크거나 같음)

• <= (작거나 같음)
• <> (같지 않음)

구체적 예시

ALL 예시

SELECT ProductName 
FROM Products 
WHERE Price > ALL (
    SELECT Price 
    FROM Products 
    WHERE CategoryID = 2
);

이 쿼리는 카테고리 2의 모든 제품보다 가격이 비싼 제품명을 조회함

ANY 예시

SELECT ProductName 
FROM Products 
WHERE ProductID = ANY (
    SELECT ProductID 
    FROM OrderDetails 
    WHERE Quantity = 10
);

이 쿼리는 주문 수량이 10인 제품 중 하나라도 해당하는 제품명을 조회함

특징	ALL	ANY
조건 충족	모든 값 만족	하나라도 만족
주요 용도	최대값 비교	최소값 비교
결과 범위	더 제한적	더 포괄적

2. CUBE

정의

CUBE는 SQL에서 다차원적인 소계를 계산할 수 있는 그룹 함수로, GROUP BY 절에서 사용됨. 결합 가능한 모든 값에 대하여 다차원 집계를 생성하며, 표시된 인수들에 대한 계층별 집계를 구할 수 있음.

사용법

SELECT column1, column2, aggregate_function
FROM table_name
GROUP BY CUBE(column1, column2);

주요 특징

데이터 출력 형태

• 표준 집계(기본 그룹화)
• 각 컬럼별 SUBTOTAL
• 전체 TOTAL

ROLLUP과의 차이점

• 인수의 순서가 달라도 결과가 동일함
• 모든 조합에 대한 소계를 계산
• 시스템 부하가 ROLLUP보다 큼

구체적 예시

SELECT department, position, SUM(salary)
FROM employees
GROUP BY CUBE(department, position);

결과 예시
| department | position | sum(salary) |
|------------|----------|-------------|
| IT | Manager | 50000 |
| IT | NULL | 80000 |
| NULL | Manager | 120000 |
| NULL | NULL | 200000 |

3. 분석 함수와 WINDOW 절 해석

주요 구성요소

SELECT 문의 구조

• EMPNO, ENAME, JOB, SAL: 기본 컬럼 조회
• LAST_VALUE: 윈도우 함수 사용
• PARTITION BY: 데이터 분할
• ORDER BY: 정렬 기준
• RANGE BETWEEN: 윈도우 범위 지정

사용된 문법 설명

LAST_VALUE 함수

LAST_VALUE(컬럼명) OVER (
    PARTITION BY 그룹화할_컬럼
    ORDER BY 정렬할_컬럼
    RANGE BETWEEN start_point AND end_point
)

• 윈도우 내의 마지막 값을 반환하는 함수
• 파티션 내에서 지정된 범위의 마지막 값을 가져옴

PARTITION BY

• JOB 기준으로 데이터를 그룹화
• 같은 직무(JOB)끼리 분리해서 계산

RANGE BETWEEN 구문

• CURRENT ROW: 현재 행
• UNBOUNDED FOLLOWING: 파티션의 마지막 행까지
• 현재 행부터 마지막 행까지의 범위를 지정

실행 과정

1. DEPTNO가 20인 직원들만 필터링
2. JOB별로 그룹 생성
3. SAL 기준으로 정렬
4. 각 행에서 현재 행부터 마지막 행까지 중 최종 급여값 계산
5. 그 결과를 C1 컬럼에 표시

예시 결과

EMPNO	ENAME	JOB	SAL	C1
7369	SMITH	CLERK	800	1100
7876	ADAMS	CLERK	1100	1100
7566	JONES	MANAGER	2975	2975
7788	SCOTT	ANALYST	3000	3000

4. UNBOUNDED FOLLOWING 설명

정의

UNBOUNDED FOLLOWING은 현재 행부터 파티션의 마지막 행까지의 모든 행을 포함하는 범위를 지정하는 윈도우 프레임 옵션

문법 구조

ROWS BETWEEN start_point AND UNBOUNDED FOLLOWING
-- 또는
RANGE BETWEEN start_point AND UNBOUNDED FOLLOWING

주요 특징

• 파티션 내에서 현재 행부터 마지막 행까지의 모든 데이터를 포함
• 반드시 윈도우 프레임의 끝점(endpoint)으로만 사용 가능
• ORDER BY 절과 함께 사용하여 정렬된 결과 내에서 범위 지정

사용 예시

-- 현재 행부터 마지막까지의 최소값 찾기
SELECT Year, DepartmentID, Revenue,
MIN(Revenue) OVER (
    PARTITION BY DepartmentID 
    ORDER BY [YEAR] 
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) as MinRevenueBeyond
FROM REVENUE;

5. UNBOUNDED 윈도우 프레임 옵션

UNBOUNDED의 의미

• UNBOUNDED: "경계가 없는", "무제한의"라는 의미
• 파티션 내에서 처음부터 끝까지의 모든 행을 포함할 때 사용

종류와 특징

UNBOUNDED PRECEDING

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

• 파티션의 첫 번째 행부터 현재 행까지
• 누적 집계 시 자주 사용
• 예: 누적 합계, 누적 평균 계산

UNBOUNDED FOLLOWING

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

• 현재 행부터 파티션의 마지막 행까지
• 역방향 집계 시 사용
• 예: 현재부터 마지막까지의 최대값

구체적 예시

-- UNBOUNDED PRECEDING 예시
SELECT empno, ename, sal,
SUM(sal) OVER (
    ORDER BY sal
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as running_total
FROM emp;

-- UNBOUNDED FOLLOWING 예시
SELECT empno, ename, sal,
MAX(sal) OVER (
    ORDER BY sal
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) as max_remaining
FROM emp;

윈도우 프레임 옵션 비교

옵션	범위	주요 용도
UNBOUNDED PRECEDING	처음 ~ 현재	누적 계산
UNBOUNDED FOLLOWING	현재 ~ 끝	역방향 계산
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	처음 ~ 끝	전체 범위 계산

활용 팁

• 누적 합계: UNBOUNDED PRECEDING
• 이동 평균: ROWS BETWEEN PRECEDING AND CURRENT ROW
• 전체 집계: BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
• 남은 데이터 분석: CURRENT ROW AND UNBOUNDED FOLLOWING

6. RANGE와 BETWEEN

사용법

BETWEEN 포함 구문

OVER (
    ORDER BY column_name
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

BETWEEN 없는 구문

OVER (
    ORDER BY column_name
    RANGE UNBOUNDED PRECEDING
)

주요 특징

• RANGE는 BETWEEN 없이도 사용 가능
• BETWEEN을 생략할 경우 기본값은 'RANGE UNBOUNDED PRECEDING'
• 단, 이 경우 끝점은 자동으로 CURRENT ROW가 됨

비교

구문	의미	사용 시점
RANGE BETWEEN	명시적 범위 지정	상세한 범위 필요시
RANGE만 사용	기본값 사용	간단한 누적 계산시

예시

-- BETWEEN 사용
SELECT sal,
SUM(sal) OVER (
    ORDER BY sal 
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as sum1

-- BETWEEN 생략
SELECT sal,
SUM(sal) OVER (
    ORDER BY sal 
    RANGE UNBOUNDED PRECEDING
) as sum2

두 쿼리는 동일한 결과를 반환함

7. EXISTS와 ROWNUM 조합 분석

구문 해석

WHERE EXISTS (
    SELECT 1 
    FROM EMP X 
    WHERE X.DEPTNO 
    AND ROWNUM = 1
);

각 요소 설명

EXISTS 연산자

• 서브쿼리가 하나 이상의 행을 반환하면 TRUE 반환
• 조건을 만족하는 데이터의 존재 여부만 확인
• SELECT 1은 실제 값이 중요하지 않고 행의 존재 여부만 확인하는 용도

ROWNUM = 1

• Oracle의 의사 열(Pseudo Column)로 각 행에 순차적인 번호 부여
• ROWNUM = 1 조건은 첫 번째 행만 검색
• 성능 최적화를 위해 사용 (전체 결과를 확인할 필요 없이 첫 행만 확인)

실행 과정

1. EMP 테이블에서 X.DEPTNO 조건을 만족하는 행 검색
2. ROWNUM = 1 조건으로 첫 번째 행만 필터링
3. 해당 행이 존재하면 EXISTS는 TRUE 반환
4. 메인 쿼리에서 이 조건을 만족하는 행들만 선택

최적화 관점

• ROWNUM = 1 사용으로 전체 테이블 스캔 방지
• EXISTS는 조건을 만족하는 첫 행을 찾는 즉시 검색 중단
• 대용량 데이터 처리 시 성능상 이점

사용 예시

-- 부서가 있는 직원만 조회
SELECT * FROM EMPLOYEE e
WHERE EXISTS (
    SELECT 1 
    FROM DEPARTMENT d 
    WHERE d.DEPT_ID = e.DEPT_ID 
    AND ROWNUM = 1
);

8. 계층형 쿼리와 경로 추출

주요 함수 설명

SYS_CONNECT_BY_PATH

• 계층 구조의 경로를 문자열로 생성
• 형식: SYS_CONNECT_BY_PATH(컬럼, '구분자')
• 루트에서 현재 노드까지의 경로를 표시

SUBSTR

• 문자열 추출 함수
• SUBSTR(문자열, 시작위치)
• 여기서는 2번째 문자부터 끝까지 추출 (첫 '>' 제거)

계층형 쿼리 구조

SELECT ... FROM table_name
START WITH 시작조건
CONNECT BY [NOCYCLE] 연결조건

START WITH

• 계층 구조의 시작점 지정
• 여기서는 ENAME이 'SCOTT'인 행부터 시작

CONNECT BY

• 계층 구조의 연결 조건 지정
• PRIOR: 이전 레코드를 참조
• EMPNO = PRIOR MGR: 현재 직원번호가 이전 관리자 번호와 연결

실행 과정

1. SCOTT부터 시작
2. SCOTT의 관리자를 찾아 상위로 이동
3. 경로를 >로 구분하여 연결
4. 최종 경로에서 첫 > 제거

예시 결과

-- 입력 데이터 예시
ENAME   MGR
KING    null
JONES   7839
SCOTT   7566

-- 결과 예시
PATH
SCOTT>JONES>KING

활용

• 조직도 표현
• 게시판의 답변 구조
• 카테고리 계층구조
• 관리자-직원 관계도 표현

9. REGEXP_SUBSTR 정규표현식 분석

구문 해석

SELECT REGEXP_SUBSTR('ABBBC', 'AB{1,3}') AS C1 FROM DUAL;

각 요소 설명

정규표현식 패턴 'AB{1,3}'

• A: 문자 'A' 하나를 의미
• B{1,3}: 문자 'B'가 1회 이상 3회 이하 반복
• 전체 패턴: 'A' 다음에 'B'가 1~3번 반복되는 패턴 찾기

패턴 매칭 방식

1. 'A'를 찾음
2. 그 뒤에 오는 'B'의 개수를 카운트 (1~3개까지만)
3. 조건에 맞는 첫 번째 패턴을 반환

실행 결과 해석

• 입력 문자열: 'ABBBC'
• 매칭되는 패턴: 'ABB'
  • A 다음에 B가 2번 나오는 패턴이 매칭됨
  • 세 번째 B는 최대 반복 횟수(3)를 초과하지 않음

유사한 패턴 예시

-- B가 정확히 2번 반복
'AB{2}'    -- 'ABB' 매칭

-- B가 1번 이상 반복
'AB+'      -- 'ABBB' 매칭

-- B가 0번 이상 반복
'AB*'      -- 'A', 'AB', 'ABB' 등 매칭

10. REGEXP_LIKE와 역참조 패턴

정규표현식 패턴 '(.)\1' 해석

구성요소

• (.) : 임의의 한 문자를 캡처하는 그룹
  • . : 모든 단일 문자와 매칭
  • () : 캡처 그룹을 만듦
• \1 : 첫 번째 캡처 그룹의 역참조
  • 앞에서 캡처한 문자와 동일한 문자를 찾음

동작 방식

1. (.)로 한 문자를 캡처
2. \1로 동일한 문자가 바로 뒤에 나오는지 확인
3. 연속으로 같은 문자가 나오는 패턴 매칭

구체적 예시

-- 매칭되는 이름 예시
'ALLEN'  -- 'LL' 때문에 매칭됨
'SCOTT'  -- 'TT' 때문에 매칭됨
'JAMES'  -- 매칭 안됨 (중복 문자 없음)

다양한 패턴 예시

-- 다른 유형의 패턴들
'(.)(.)\\2\\1'   -- ABBA 형태 찾기
'(.).*\\1'       -- 같은 문자가 어디든 있는 경우
'([A-Z])\\1'     -- 대문자가 연속으로 반복

실제 활용 예

-- 연속된 같은 문자가 있는 모든 이름 찾기
SELECT ename 
FROM emp
WHERE REGEXP_LIKE(ename, '(.)\1');

-- 3번 연속된 같은 문자 찾기
SELECT ename 
FROM emp
WHERE REGEXP_LIKE(ename, '(.)\1\1');

매칭 결과 예시

ENAME	매칭되는 부분	설명
ALLEN	LL	L이 연속 2번
SCOTT	TT	T가 연속 2번
MILLER	LL	L이 연속 2번

주의사항

• 대소문자 구분함
• Oracle에서는 역슬래시를 두 번 써야할 수도 있음 (\1)
• 정규표현식은 DB 버전에 따라 지원 범위가 다를 수 있음

11. INSERT FIRST와 조건부 삽입

INSERT FIRST 특징

• 여러 조건 중 처음으로 만족하는 조건만 실행
• 나머지 조건은 만족하더라도 무시됨
• ELSE 절은 모든 조건이 거짓일 때 실행

실행 순서

1. T4 테이블에서 C1 값을 조회
2. 각 행에 대해 조건을 위에서부터 순차적으로 검사
3. 첫 번째로 만족하는 조건의 INSERT문만 실행

예시 설명

INSERT FIRST
WHEN C1 >= 2 THEN 
    INTO T1
WHEN C1 >= 3 THEN 
    INTO T2
ELSE 
    INTO T3
SELECT C1 FROM T4;

동작 방식
| C1 값 | 실행되는 INSERT | 설명 |
|-------|----------------|------|
| 1 | T3 | 모든 조건 불만족, ELSE 실행 |
| 2 | T1 | 첫 번째 조건 만족 |
| 3 | T1 | C1>=3이지만 첫 조건에서 이미 처리 |
| 4 | T1 | 마찬가지로 첫 조건에서 처리 |

INSERT FIRST vs INSERT ALL

-- INSERT FIRST: 첫 조건만 실행
INSERT FIRST
WHEN sal >= 5000 THEN INTO high_salary
WHEN sal >= 3000 THEN INTO mid_salary
SELECT * FROM employees;

-- INSERT ALL: 모든 조건 실행
INSERT ALL
WHEN sal >= 5000 THEN INTO high_salary
WHEN sal >= 3000 THEN INTO mid_salary
SELECT * FROM employees;

급여가 6000인 경우:

• INSERT FIRST: high_salary에만 삽입
• INSERT ALL: high_salary와 mid_salary 모두에 삽입

12. 중복 데이터 삭제 쿼리 분석

쿼리 구조 분석

DELETE FROM T
WHERE ROWID IN (
    SELECT ROWID 
    FROM (
        SELECT ROWID,
               ROW_NUMBER() OVER (
                   PARTITION BY C1, C2, C3 
                   ORDER BY ROWID
               ) AS RN
        FROM T1
    )
    WHERE RN > 1
);

실행 과정

1. 내부 서브쿼리에서 ROW_NUMBER() 할당
2. RN > 1인 ROWID 선택
3. 해당 ROWID를 가진 행들 삭제

주요 요소 설명

ROW_NUMBER()

• 파티션 내에서 순차적 번호 부여
• PARTITION BY로 그룹화된 데이터 내에서 번호 매김
• ORDER BY ROWID로 동일 그룹 내 순서 결정

실행 결과 예시

ROWID	RN	결과
1	1	유지
2	2	삭제
3	1	유지
4	2	삭제
5	1	유지
6	2	삭제
7	3	삭제

중복 제거 방식

• 같은 값(C1, C2, C3)을 가진 행들 중
• 첫 번째 행(RN=1)은 보존
• 나머지 행(RN>1)은 모두 삭제

활용

• 테이블의 중복 데이터 제거
• 데이터 정제 작업
• 유일성 보장이 필요한 경우

13. ROWID 분석

ROWID 정의

• 데이터베이스의 각 행(row)에 대한 물리적 주소값
• Oracle에서 자동 생성되는 고유한 행 식별자
• 행의 물리적 위치를 나타내는 base-64 형식의 값

ROWID 특징

• 테이블의 각 행마다 유일한 값 보유
• 데이터베이스에 영구적으로 저장
• 행에 대한 가장 빠른 접근 방법 제공

ROWID 구성요소

• 데이터 객체 번호
• 데이터 파일 내의 데이터 블록 위치
• 데이터 블록 내의 행 위치 (첫 행은 0)
• 데이터 파일 번호 (첫 파일은 1)

주의사항

• 기본키로 사용하면 안 됨
• 데이터 삭제/재삽입 시 값이 변경될 수 있음
• INSERT, UPDATE, DELETE로 값 변경 불가

사용 예시

-- ROWID 조회
SELECT ROWID, last_name 
FROM employees 
WHERE department_id = 20;

-- ROWID로 특정 행 접근
SELECT * FROM employees 
WHERE ROWID = 'AAASd4AABAAAAX3AAA';

14. ROW_NUMBER() 상세 설명

기본 개념

ROW_NUMBER()는 결과 집합의 각 행에 순차적인 고유 번호를 부여하는 윈도우 함수입니다. 파티션별로 정렬된 순서에 따라 각 행에 고유한 순번을 매깁니다.

동작 방식

파티션이 없는 경우

SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) as rank,
       employee_name, 
       salary
FROM employees;

• 전체 결과셋에서 순차적으로 1,2,3... 번호 부여
• salary 기준 내림차순으로 정렬 후 번호 부여

파티션이 있는 경우

SELECT ROW_NUMBER() OVER (
    PARTITION BY department_id 
    ORDER BY salary DESC
) as dept_rank,
    department_id,
    employee_name,
    salary
FROM employees;

• 각 부서(department_id)별로 독립적으로 1부터 시작하는 번호 부여
• 부서 내에서 salary 순으로 정렬 후 번호 부여

실제 예시

department_id	employee_name	salary	dept_rank
10	Smith	5000	1
10	Jones	4000	2
20	Brown	6000	1
20	Davis	5500	2

주요 특징

• 동일한 값이라도 항상 고유한 번호 부여
• PARTITION BY는 선택사항이지만 ORDER BY는 필수
• NULL 값도 순서에 포함하여 번호 부여
• 파티션 내에서 순서는 ORDER BY 절에 따라 결정

15. 다중 컬럼 PARTITION BY 분석

기본 문법

ROW_NUMBER() OVER (
    PARTITION BY C1, C2, C3 
    ORDER BY ROWID
)

예시 데이터 분석

C1, C2, C3
1, 1, 1  -> RN=1
1, 1, 1  -> RN=2  (중복 데이터)
1, 2, 1  -> RN=1  (새로운 파티션)
1, 2, 2  -> RN=1  (새로운 파티션)
1, 1, 3  -> RN=1  (새로운 파티션)
1, 1, 3  -> RN=2  (중복 데이터)

파티션 구분 방식

• (C1=1, C2=1, C3=1) 그룹
• (C1=1, C2=2, C3=1) 그룹
• (C1=1, C2=2, C3=2) 그룹
• (C1=1, C2=1, C3=3) 그룹

실행 결과

C1	C2	C3	ROW_NUMBER
1	1	1	1
1	1	1	2
1	2	1	1
1	2	2	1
1	1	3	1
1	1	3	2

특징

• 세 컬럼 값이 모두 동일한 행들끼리 그룹화
• 각 그룹 내에서 독립적으로 번호 부여
• 하나의 값이라도 다르면 새로운 파티션으로 처리

16. 날짜 형식 CHECK 제약조건 분석

제약조건 해석

-- 테이블 생성
CREATE TABLE T1 (
    C1 VARCHAR2(8)
);

-- CHECK 제약조건 추가
ALTER TABLE T1 ADD CONSTRAINT T1_C1 CHECK (
    C1 = TO_CHAR(
        TO_DATE(C1, 'YYYYMMDD'), 
        'YYYYMMDD'
    )
);

동작 방식

1. C1에 입력된 문자열을 TO_DATE로 날짜로 변환 시도
2. 변환된 날짜를 다시 TO_CHAR로 문자열로 변환
3. 원본 값(C1)과 변환 결과를 비교

제약조건의 목적

• 유효한 날짜 형식(YYYYMMDD)만 입력 가능하도록 제한
• 존재하지 않는 날짜는 입력 불가

입력 예시

-- 성공 케이스
INSERT INTO T1 VALUES ('20240101');  -- 유효한 날짜
INSERT INTO T1 VALUES ('20231225');  -- 유효한 날짜

-- 실패 케이스
INSERT INTO T1 VALUES ('20241301');  -- 13월은 없음
INSERT INTO T1 VALUES ('20240231');  -- 2월 31일은 없음
INSERT INTO T1 VALUES ('ABCD1234');  -- 날짜 형식 아님

제약조건 검증 결과

입력값	결과	이유
20240101	성공	유효한 날짜
20241301	실패	유효하지 않은 월
ABCD1234	실패	날짜로 변환 불가
20240229	성공	윤년의 유효한 날짜
20230229	실패	평년의 존재하지 않는 날짜

활용

• 날짜 데이터 정합성 검증
• 잘못된 날짜 입력 방지
• 날짜 형식 표준화

17. TO_CHAR와 TO_DATE 변환 과정 상세 분석

함수 분석

TO_CHAR(
    TO_DATE(C1, 'YYYYMMDD'),  -- 1단계
    'YYYYMMDD'                -- 2단계
)

변환 과정 예시

입력값 'C1' = '20240101' 경우:

1. TO_DATE(C1, 'YYYYMMDD')

   • 입력: '20240101' (문자열)
   • 형식: 'YYYYMMDD'로 해석
   • 결과: 2024년 1월 1일 (날짜형)

2. TO_CHAR(날짜, 'YYYYMMDD')

   • 입력: 2024년 1월 1일 (날짜형)
   • 형식: 'YYYYMMDD'로 변환
   • 결과: '20240101' (문자열)

왜 필요한가?

• 유효성 검증: 날짜로 변환 가능한지 확인
• 형식 표준화: 동일한 형식으로 저장
• 자동 필터링: 잘못된 날짜는 변환 과정에서 에러

예시로 보는 동작

-- 케이스 1: 유효한 날짜
'20240101' -> 2024/01/01 -> '20240101' (성공)

-- 케이스 2: 잘못된 날짜
'20241301' -> 변환 실패 (13월은 없음)
'ABCD1234' -> 변환 실패 (날짜 형식 아님)

형식 지정자(YYYYMMDD)의 역할

단계	형식 지정자 역할
TO_DATE	입력 문자열을 어떻게 해석할지
TO_CHAR	날짜를 어떤 형식으로 출력할지

다른 형식으로 지정하면?

-- 다른 형식 사용 시
TO_CHAR(
    TO_DATE('20240101', 'YYYYMMDD'),
    'DD-MON-YYYY'
) 
-- 결과: '01-JAN-2024'

이렇게 되면 원본 값과 달라져서 CHECK 제약조건 실패

18. Oracle 권한 유형별 분석

RESOURCE 권한

• 개발자를 위한 기본 권한 묶음(Role)
• 포함된 주요 권한:
  • CREATE TABLE
  • CREATE SEQUENCE
  • CREATE TRIGGER
  • CREATE PROCEDURE
  • CREATE CLUSTER
  • CREATE TYPE
  • CREATE OPERATOR
  • CREATE INDEXTYPE

CONNECT 권한

• 데이터베이스 접속을 위한 기본적인 권한 묶음
• Oracle 12c 이후 버전에서는 CREATE SESSION만 포함
• 이전 버전에서는 더 많은 권한이 포함되었었음

CREATE SESSION 권한

• 데이터베이스 로그인 권한
• 가장 기본적인 시스템 권한
• 이 권한 없이는 데이터베이스 접속 불가
• CONNECT 롤에 포함되어 있음

CREATE USER 권한

• 새로운 사용자 계정 생성 권한
• 데이터베이스 관리자(DBA) 레벨의 권한
• 사용자 생성, 수정, 삭제 가능
• 보안상 중요한 권한으로 신중하게 부여해야 함

권한 비교표

권한	레벨	주요 용도	포함된 권한
RESOURCE	개발자용 롤	객체 생성/관리	CREATE TABLE 등 다수
CONNECT	기본 접속용 롤	DB 접속	CREATE SESSION
CREATE SESSION	시스템 권한	로그인	단일 권한
CREATE USER	관리자 권한	계정 관리	사용자 생성/관리

일반적인 권한 부여 패턴

-- 일반 사용자용
GRANT CONNECT, RESOURCE TO username;

-- 관리자용
GRANT CONNECT, RESOURCE, CREATE USER TO username;

-- 최소 권한
GRANT CREATE SESSION TO username;

주의사항

• 필요한 최소한의 권한만 부여
• CREATE USER는 신중하게 부여
• RESOURCE는 개발 환경에서만 부여
• 권한 관리는 보안 정책에 따라 수행

19. WITH ADMIN OPTION과 WITH GRANT OPTION 비교

기본 정의

WITH ADMIN OPTION

• 시스템 권한이나 롤(Role) 부여 시 사용
• 권한을 받은 사용자가 다른 사용자에게 동일 권한을 부여/철회 가능

WITH GRANT OPTION

• 객체 권한 부여 시 사용
• 특정 객체에 대한 권한을 다른 사용자에게 부여/철회 가능

주요 차이점

구분	WITH ADMIN OPTION	WITH GRANT OPTION
적용 대상	시스템 권한, 롤	객체 권한
권한 범위	전체 시스템 레벨	특정 객체 레벨
권한 회수	상위 사용자 권한 회수 시 하위 사용자 권한 유지	상위 사용자 권한 회수 시 하위 사용자 권한도 함께 회수

권한 회수(REVOKE) 특징

ADMIN OPTION

• 권한 회수 시 연쇄적으로 발생하지 않음
• 각 사용자의 권한을 개별적으로 회수해야 함

GRANT OPTION

• 권한 회수 시 CASCADE로 연쇄적 회수 발생
• 하위 사용자들의 권한이 자동으로 회수됨

20. 권한 관리의 실제 적용

시스템 권한 예시

-- ADMIN OPTION 사용
GRANT CREATE SESSION TO user1 WITH ADMIN OPTION;
GRANT CREATE USER TO scott WITH ADMIN OPTION;

객체 권한 예시

-- GRANT OPTION 사용
GRANT SELECT ON employees TO user1 WITH GRANT OPTION;
GRANT SELECT ON dept TO oracle WITH GRANT OPTION;

주의사항

• 권한 부여 시 최소 권한 원칙 준수
• ADMIN OPTION 사용 시 권한 추적 필요
• GRANT OPTION 사용 시 CASCADE 효과 고려
• 보안과 성능을 고려한 권한 관리 필요