뷰(View)와 인라인뷰, 그 외 객체와 DCL

보안을 위한 뷰부터 SELECT의 끝판왕인 인라인뷰와 TOP-N 분석, 시퀀스·인덱스 같은 기타 객체, 그리고 권한을 관리하는 DCL까지 — Oracle SQL 기초 시리즈의 마지막 글을 정리했다.

게시 2025/02/17

By 이소연

40 분읽는 시간

Oracle SQL/DB 학습 노트 시리즈의 마지막 글이다. 데이터를 제한해서 보여주는 뷰, SELECT 문의 끝판왕인 인라인뷰, 시퀀스·인덱스 같은 기타 객체, 그리고 권한을 관리하는 DCL까지 한 번에 정리한다.

[ 목차 ]

뷰(View) — 목적과 단순·복합 뷰
뷰 생성·수정·제거
복합 뷰와 뷰를 통한 DML
인라인뷰 — SELECT 문의 끝판왕
ROWNUM과 TOP-N 분석
그 외 데이터베이스 객체 — 시퀀스·인덱스·동의어
DCL — 사용자·권한·롤
핵심 용어 정리

ㅇ 뷰(View) — 목적과 단순·복합 뷰

뷰(view)는 테이블의 데이터를 제한해서 보여주는 객체다. 가장 큰 목적은 보안이다.

데이터 액세스를 제한 : 테이블은 부서 전체를 다 볼 수 있다. 하지만 10번 부서 사람이 굳이 20번 부서 사람의 정보까지 볼 필요는 없다. 뷰를 이용하면 10번 부서 사원의 정보만 제한해서 보여줄 수 있다
복잡한 질의를 쉽게 작성 : TOP-N 분석처럼 복잡한 쿼리를 뷰로 감싸 두면 매번 다시 짜지 않아도 된다
데이터 독립성을 제공
동일한 데이터로부터 다양한 결과를 도출

뷰는 테이블의 데이터를 제한해서 보여주는 것이므로, 바탕이 되는 테이블이 먼저 존재해야 한다. 보여주는 방식에 따라 두 종류로 나뉜다.

단순 뷰 : 테이블 데이터를 원본 그대로 보여준다
복합 뷰 : 변형된 데이터를 보여준다. 예를 들어 AVG(salary)처럼 가공한 값

ㅇ 뷰 생성·수정·제거

뷰를 만드는 문법은 CREATE TABLE ... AS 서브쿼리와 거의 같다. 테이블 대신 뷰로 바꿔 주면 된다.

  
-- 80번 부서의 정보만 가져오는 뷰
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
   FROM employees
   WHERE department_id = 80;

DESC empvu80이나 SELECT * FROM empvu80으로 잘 만들어졌는지 확인하면 된다. 사용자 입장에서는 이게 뷰인지 테이블인지 알 방법이 없다. SELECT 문장을 똑같이 사용하면 그만이다.

테이블을 만들 때처럼 열 별칭을 줄 수도 있다.

  
CREATE VIEW salvu50
AS SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY
   FROM employees
   WHERE department_id = 50;

여기서는 테이블의 열을 그대로 쓰지 않고 salary*12로 새로운 열(연봉)을 만들어 냈다. 결국 뷰를 잘 쓰려면 SELECT를 얼마나 잘 다루느냐가 핵심이 된다. 컬럼 헤딩을 지정하는 방법은 테이블을 만들 때와 동일하게 두 가지다.

CREATE 절에 컬럼 이름을 명시하는 방법
AS 서브쿼리의 SELECT 리스트절에서 별칭(alias)을 주는 방법

뷰와 테이블의 차이

뷰의 핵심은 물리적인 데이터를 갖지 않는다는 점이다. 뷰에 질의를 수행하면, 저장해 둔 서브쿼리를 재실행해서 마치 데이터를 가지고 있는 것처럼 보여 줄 뿐이다.

테이블	뷰
물리적 구조가 생성되어 저장 공간을 할당받는다	가짜 테이블(논리 테이블). 물리적 구조가 없어 저장 공간을 따로 할당하지 않는다
데이터 딕셔너리에 테이블 구조·정보를, 파일에 실제 데이터 값을 저장한다	데이터 딕셔너리에 뷰를 만들 때 사용한 서브쿼리 문장을 텍스트로 저장한다
수납 공간이 있어야만 저장할 수 있다	실제 데이터가 아니라 서브쿼리 텍스트만 가지고 있다

이 차이가 잘 드러나는 예제가 있다. 기본 테이블에서 100번 사원의 급여가 100일 때,

A 테이블 : CREATE TABLE AS 서브쿼리로 12*salary를 저장
B 뷰 : CREATE VIEW AS 서브쿼리로 12*salary를 저장

둘 다 처음엔 100번 사원의 연봉으로 1200을 보여준다. 그런데 원본 테이블의 급여가 100에서 200으로 바뀌면?

A 테이블은 독립된 테이블로 복사된 것이라 1200 그대로다
B 뷰는 질의할 때마다 서브쿼리가 재실행되므로 2400으로 바뀐다

뷰는 테이블과 조인할 수도, 뷰끼리 조인할 수도 있다. SELECT 문장만 잘 쓰면 웬만한 건 뷰로 다 구할 수 있다.

뷰 수정과 제거

테이블은 구조를 가지고 있어서 ALTER로 변경했지만, 뷰는 구조가 없으므로 CREATE OR REPLACE로 수정한다. 기존 서브쿼리 위에 새로운 서브쿼리를 덮어쓰는 것이다. 현업에서는 대부분 뷰를 만들 때부터 아예 CREATE OR REPLACE를 쓴다.

뷰 제거는 DROP VIEW다. 뷰를 지워도 바탕이 된 테이블은 그대로 남는다. 뷰는 데이터를 가진 게 아니라 서브쿼리 텍스트만 가지고 있었기 때문이다.

ㅇ 복합 뷰와 뷰를 통한 DML

복합 뷰

복합 뷰는 복잡한 질의를 간단하게 볼 수 있도록 감싸 준다. 부서별 최소·최대·평균 급여가 궁금하면, 매번 그룹 함수 쿼리를 짜는 대신 이 뷰만 SELECT * 하면 된다.

  
CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary), MAX(e.salary), AVG(e.salary)
   FROM employees e, departments d
   WHERE e.department_id = d.department_id
   GROUP BY d.department_name;

뷰를 통한 DML

뷰 자체에 INSERT·UPDATE·DELETE를 수행하면 바탕 테이블에도 반영된다. 단, 단순 뷰, 즉 테이블 값을 그대로 보여주는 경우에만 가능하다.

다만 이 기능은 거의 쓰지 않는다. 뷰는 애초에 보안을 위해 만든 객체인데, 뷰를 통해 DML을 하면 성능도 떨어지고 보안에도 취약해진다. 가능은 하지만 사용하지 않는다고 보면 된다.

오히려 보안을 강조하기 위해 서브쿼리의 마지막에 옵션을 붙인다.

WITH CHECK OPTION : 뷰를 통한 INSERT·UPDATE가 일어날 때, 그 뷰가 조회할 수 없는 행은 생성되지 않도록 막아 무결성을 검사한다
WITH READ ONLY : 뷰를 통한 DML 작업 자체를 거부한다

ㅇ 인라인뷰 — SELECT 문의 끝판왕

서브쿼리는 어디에 쓰이느냐에 따라 역할이 달라진다.

WHERE·HAVING 절에서 쓰이면 → 값의 역할
FROM 절에서 쓰이면 → 인라인뷰(가상 테이블)의 역할

인라인뷰(inline view)는 SQL 문에서 별칭을 사용하는 서브쿼리다. 대표적으로 FROM 절에 들어가는 서브쿼리가 인라인뷰의 한 종류다. 원래 FROM 절에는 테이블을 쓰지만, 서브쿼리로 가상의 테이블을 만들어 별칭을 붙이면 그게 인라인뷰가 된다.

인라인뷰는 스키마 객체가 아니다. 이 문장이 실행되는 동안에만 서브쿼리의 결과를 테이블처럼 사용하는 가상 테이블이다.

  
SELECT a.last_name, a.salary, a.department_id, b.maxsal
FROM employees a, (SELECT department_id, MAX(salary) maxsal
                   FROM employees
                   GROUP BY department_id) b
WHERE a.department_id = b.department_id
  AND a.salary < b.maxsal;

서브쿼리 b는 부서번호별 최대 급여를 구하는 가상 테이블이다
본쿼리 SELECT 절의 앞 세 컬럼은 a 테이블, maxsal은 가상 테이블 b에서 왔으므로 → 조인 조건이 필요하다
WHERE 절에 조인 조건을 적고 각 컬럼에 접두어를 붙인다
AND 조건은 “b의 maxsal보다 적은 급여를 받는 사원”을 찾는다 (결국 각 부서에서 최대 급여를 받는 사원만 제외하고 모두 찾는 셈이다)

인라인뷰를 쓰면 원하는 조건을 자유롭게 꾸려 훨씬 다양한 결과를 만들어 낼 수 있다. FROM 절에는 테이블-테이블, 테이블-뷰, 뷰-뷰, 인라인뷰-뷰, 인라인뷰-인라인뷰 등 어떤 조합이든 올 수 있다.

인라인뷰는 SELECT 문에 충분히 익숙해진 뒤라야 제대로 쓸 수 있는, 말하자면 SELECT 문의 끝판왕이다.

ㅇ ROWNUM과 TOP-N 분석

ROWNUM

ROWNUM은 출력된 행의 번호를 돌려주는 컬럼이다. 우리가 만든 테이블에 저장된 데이터가 아니라, SYSDATE처럼 시스템이 제공하는 가상 컬럼이다. 행의 순서를 제어하거나 WHERE 절에서 “몇 번까지만 출력” 하는 식으로 쓴다.

  
SELECT ROWNUM, last_name, salary
FROM employees
WHERE ROWNUM > 4;   -- no rows selected

WHERE ROWNUM > 4처럼 큰 값을 조건으로 주면 결과가 나오지 않는다. ROWNUM은 행이 출력되어야만 번호를 리턴하는데, 첫 행부터 “4보다 크다”를 만족하지 못해 아무것도 출력되지 않기 때문이다. WHERE ROWNUM <(=) 숫자 꼴로만 의미가 있다.

TOP-N 분석이 어려운 이유

급여를 많이 받는 상위 3명, 적게 받는 하위 3명을 뽑는 것이 TOP-N 분석이다. 순진하게 짜 보면 이렇게 된다.

  
SELECT ROWNUM, last_name, salary
FROM employees
WHERE ROWNUM < 4
ORDER BY salary;

ROWNUM	LAST_NAME	SALARY
2	Kochhar	17000
3	De Haan	17000
1	King	24000

작은 값 3명이 아니라, 맨 위 3명을 뽑은 뒤 그 안에서만 정렬됐다. ORDER BY 절이 맨 마지막에 실행되기 때문이다. 먼저 ROWNUM이 1, 2, 3… 순으로 매겨지고 → WHERE로 그중 3개만 출력된 뒤 → 마지막에 그 3개 안에서 정렬이 일어난 것이다.

TOP-N 분석을 제대로 하려면

WHERE 절이 ORDER BY보다 먼저 실행되므로, 인라인뷰로 먼저 정렬해 두고 거기에 ROWNUM을 붙여 잘라내야 한다.

  
SELECT ROWNUM, last_name, salary
FROM (SELECT last_name, salary
      FROM employees
      ORDER BY salary)
WHERE ROWNUM < 4;

FROM 절의 인라인뷰에서 먼저 정렬을 끝내고, 본쿼리의 WHERE ROWNUM < 4로 상위 3개만 출력한다. 여기서 본쿼리 SELECT 절의 last_name, salary는 employees 테이블이 아니라 인라인뷰에서 가져온 컬럼이다. 본쿼리의 FROM 절에 employees 테이블은 없다.

WHERE·HAVING의 서브쿼리에서는 정렬이 의미 없어 ORDER BY를 쓰지 않았지만(성능만 떨어진다), FROM 절의 서브쿼리에서는 ORDER BY가 핵심 역할을 한다.

그렇다면 차라리 테이블을 처음부터 정렬해 두면 편하지 않을까? 그렇지 않다. 데이터 값이 바뀔 때마다 정렬이 깨져 테이블을 재구축해야 하고(물건을 사 올 때마다 수납 구조를 통째로 바꾸는 격), I/O가 엄청나게 발생한다. 그래서 정렬은 인라인뷰로 가상 테이블을 만들어 처리한다.

실습 1 — 50번 부서 상위 3명의 연봉

50번 부서에 근무하는 사원 중 연봉을 많이 받는 상위 3명의 이름과 연봉을 구하시오.

연봉으로 정렬하려고 salary*12를 그대로 쓰면 오류가 난다. FROM 절의 테이블은 employees가 아니라 인라인뷰인데, 그 인라인뷰에는 salary 컬럼이 없기 때문이다. 산술식에는 컬럼 이름을 쓸 수 없으니 서브쿼리에서 별칭을 붙여 본쿼리에서 사용 가능한 상태로 만들어야 한다.

  
SELECT ROWNUM, last_name, annsal
FROM (SELECT last_name, salary*12 annsal
      FROM employees
      ORDER BY annsal DESC)
WHERE ROWNUM < 4;

ROWNUM	LAST_NAME	ANNSAL
1	Davies	480000
2	Rajs	360000
3	King	288000

ORDER BY 절은 맨 마지막에 실행되므로 별칭 annsal을 쓸 수 있다. 결론은 서브쿼리에 별칭을 붙이자는 것이다.

실습 2 — 평균 급여 하위 3개 부서

평균 급여가 적은 하위 3개 부서의 이름과 평균 급여를 출력하시오.

조인을 본쿼리에 두면 한 번 더 수행해야 하는 쿼리가 되어 성능이 떨어진다. 조인까지 인라인뷰 안에서 끝내 하나의 데이터 셋으로 만드는 것이 좋다.

  
SELECT department_name, avg_sal
FROM (SELECT d.department_name, AVG(e.salary) avg_sal
      FROM departments d, employees e
      WHERE d.department_id = e.department_id
      GROUP BY d.department_id
      ORDER BY AVG(e.salary) ASC)
WHERE ROWNUM < 4;

만드는 순서로 정리하면 이렇다.

인라인뷰(서브쿼리) 먼저 : 부서 이름별 평균 급여를 구한다
- SELECT 절 : department_name, AVG(salary)
- FROM 절 : departments d, employees e
- 조인이 일어났으니 조인 조건 : d.department_id = e.department_id
- 그룹 함수를 썼으니 GROUP BY
- 마지막으로 ORDER BY로 평균 급여를 오름차순 정렬
본쿼리 : 부서 이름과 평균 급여를 SELECT, 마지막에 ROWNUM으로 조건

자주 하는 실수 — 그룹 함수 AVG(salary)는 SELECT 절에서는 그냥 쓸 수 있지만, 테이블/인라인뷰를 만드는 서브쿼리 안에서는 컬럼 이름 규칙에 위배된다. 이때는 반드시 별칭을 붙여 줘야 한다. 또 정렬 기준은 salary가 아니라 우리가 원하는 평균값 AVG(salary)여야 한다.

ㅇ 그 외 데이터베이스 객체 — 시퀀스·인덱스·동의어

시퀀스(SEQUENCE)

엑셀에서 숫자를 하나 넣고 드래그하면 자동 번호가 생성되듯, 시퀀스는 중복되지 않는 고유 번호를 자동으로 생성하는 객체다. 주로 기본 키(PK) 값을 만드는 데 쓴다. 현업에서 몇십, 몇백만 건의 행을 일일이 중복 검사할 수는 없으니 시퀀스를 쓰면 PK를 편하게 채울 수 있다.

단, 고유 ‘번호’이므로 숫자에만 쓸 수 있다. PK가 문자나 날짜라면 시퀀스를 쓸 수 없다.

  
-- 기존 부서 번호와 겹치지 않도록 300번부터 시작
CREATE SEQUENCE dept_deptid_seq
    INCREMENT BY 10
    START WITH 300
    MAXVALUE 9999;

INCREMENT BY : 시퀀스 증가값
START WITH : 처음 생성되는 시퀀스 번호
MAXVALUE : 최대로 생성될 번호 (도달하면 더 이상 생성하지 않는다)
기본값은 NOCACHE, NOCYCLE다. CACHE를 주면 번호를 20개씩 미리 만들어 메모리에 확보해 두어 추출 성능이 좋아지는 대신 공간을 조금 차지한다. CYCLE은 MAXVALUE에 도달하면 다시 START 값부터 시작하므로, PK에 쓰는 시퀀스라면 절대 주면 안 된다

시퀀스는 값을 저장하고 있는 객체가 아니라 값을 생성해 주는 생성기다. 데이터 딕셔너리에 저장되어 있다. 음료수 자판기에 비유할 수 있는데, 자판기는 그대로 있고 동전을 넣고 버튼을 눌러야 음료수를 뱉어낸다.

NEXTVAL : 동전을 넣는 역할. 시퀀스에서 다음 값을 추출한다
CURRVAL : 추출된 현재 값이 저장되는 곳. NEXTVAL이 한 번 실행된 뒤에만 사용할 수 있다

  
INSERT INTO departments
VALUES (dept_deptid_seq.NEXTVAL, 'IT', 100, 1700);

부서 번호 자리에 300, 310을 직접 넣지 않고 dept_deptid_seq.NEXTVAL을 넣는다. 세 번 실행하면 300, 310, 320번 행이 생성된다.

시퀀스를 쓰다 보면 번호에 공백(gap)이 생긴다. 예를 들어 300, 310, 320을 추출해 INSERT한 뒤 트랜잭션을 롤백하면, 테이블의 행은 취소되어 270번까지로 돌아가지만, 시퀀스가 다음에 주는 값은 270이 아니라 330이다. 시퀀스는 한 번 추출한 값을 다시 추출하지 않기 때문이다. 롤백은 테이블에 저장된 데이터를 되돌리는 것이고, 시퀀스는 데이터를 저장하지 않는 생성기라 롤백과 무관하다. 이것이 시퀀스의 독립성이다.

또한 시퀀스는 공유 가능한 객체다. 자판기를 모두가 같이 쓰듯 A와 B가 동시에 사용할 수 있다(반면 테이블은 데이터가 복사될 수 있어 보안상 공유하지 않는다). 공유로 인해서도 번호 공백이 생길 수 있으니 당황하지 않으면 된다.

수정은 객체이므로 ALTER SEQUENCE로 한다.

INCREMENT BY : 다음에 생성될 번호부터 영향을 준다. 이미 추출된 값에는 영향이 없다
MAXVALUE : 키우는 건 언제든 가능하지만, 줄일 때는 현재까지 추출된 번호보다 큰 값으로만 가능하다
START WITH : 변경 불가다. 이미 300번 이상이 생성됐는데 시작값을 200으로 줄이면 중간에 300이 끼어 있는 모순이 생기기 때문이다. 바꾸려면 시퀀스를 삭제하고 같은 이름으로 재생성하는 수밖에 없다

제거는 DROP SEQUENCE다. 생성기를 없애는 것이므로 이미 추출해 테이블에 들어간 번호들은 사라지지 않는다.

인덱스(INDEX)

SELECT 문이 실행되려면 파싱 → 검증 → 실행 계획 수립 → 실행을 거친다. 여기서 실행 계획이란 테이블에 어떻게 접근해 데이터를 가져올지를 정하는 것이다. 가장 기본은 테이블 풀 스캔으로, 테이블에 저장된 모든 데이터를 처음부터 뒤져 원하는 값을 찾는다. 1900년대부터 쌓인 데이터에서 2000년대 데이터 만 건만 보고 싶어도 풀 스캔은 전부 뒤지므로, 테이블이 커질수록 성능이 크게 떨어진다.

인덱스는 검색 속도를 높이기 위해 컬럼에 설치하는 객체다. 책의 목차와 같다.

인덱스는 테이블이 아니라 컬럼에 설치한다
기본적으로 오름차순으로 정렬해 배열 구조로 저장하고, 오른쪽에는 포인터(행의 주소인 ROWID)를 저장해 실제 행과 연결한다
검색 시 인덱스에서 값을 찾고 포인터를 따라 해당 행으로 바로 이동한다(인덱스 스캔). 특히 범위 검색에서 탁월하다

그렇다고 모든 컬럼에 인덱스를 거는 건 위험하다.

인덱스는 검색(SELECT) 전용이다. 인덱스 창고를 들렀다가 테이블 창고로 가는, 물리적으로 두 번 방문하는 구조라(고객센터에 들러 위치를 물어보고 찾아가는 격) 무분별하게 쓰면 오히려 풀 스캔보다 느리다
보통 전체의 10~15% 정도를 검색할 때 효과적이고, 그 이상이면 풀 스캔이 낫다
DML을 할 때는 최악이다. 정렬을 유지해야 하므로 테이블이 바뀔 때마다 인덱스를 재구축한다
중복이 없는 컬럼에 쓰면 좋다(PK·UNIQUE 컬럼에는 인덱스가 자동으로 설치된다). 반대로 성별처럼 값이 두세 개뿐인 컬럼은 절반을 뒤져야 하므로 최악이다

인덱스는 개발자보다는 DBA나 튜너의 영역이다. 설계 단계에서 “여기에 인덱스를 설치하라”고 명시돼 있으면 만들면 된다.

동의어(SYNONYM)

객체에 다른 이름을 붙여 주는 객체다. 자주 사용하지는 않으니 개념만 알아 두면 된다.

ㅇ DCL — 사용자·권한·롤

마지막은 DCL(Data Control Language), 곧 데이터 제어 언어다. 수많은 사용자가 동시에 접근하는 환경에서 데이터 보안을 담당한다. 보안에는 두 측면이 있다.

권한 없는 사용자가 접근하지 못하도록 막는 것 — DB 내부보다 인증서 같은 운영체제 차원의 외부 요소가 강하다
권한 있는 사용자가 불법적인 작업을 하지 못하도록 막는 것 — 이것이 DCL이다

이 관리는 일반 사용자(hr 계정 등)가 할 수 없고 DBA(데이터베이스 관리자)가 한다. DBA는 DB에 접근하는 사용자를 두 영역으로 나눠 관리한다. 하나는 conn hr/hr 같은 아이디·패스워드이고, 다른 하나는 그렇게 생성된 사용자를 권한으로 관리하는 것이다(DCL).

시스템 권한과 객체 권한

시스템 권한 : DB에 접근하고 작업할 수 있는 권한으로 100가지가 넘는다(유저 생성·제거, 테이블 제거·백업 등). 오로지 DBA만 부여할 수 있다
객체 권한 : 테이블·뷰·시퀀스 같은 객체에 무언가를 할 수 있는 권한이다. DBA와 그 객체의 소유주가 관리한다

system 계정이 곧 DBA 계정이며, 그보다 상위에는 자동으로 만들어지는 SYSDBA(데이터 백업 등 DB 구조 전반), SYSOPER(백업 권한은 없음) 같은 강력한 권한이 있다. 비밀번호를 잊으면 conn /as sysdba로 접근해 바꿀 수 있다.

사용자 생성과 권한 부여·회수

쇼핑몰에 가면 가장 먼저 계정을 만들고 등급을 올리듯, DB도 ① 유저 생성 → ② 권한 부여 → ③ 권한 회수의 흐름으로 사용자를 관리한다. 모두 DBA만 할 수 있다.

  
CREATE USER test IDENTIFIED BY test;

IDENTIFIED BY로 패스워드를 지정한다. 다만 현업에서는 이렇게만 만들지 않는다. 이대로 두면 test 계정이 DB의 모든 자원을 쓸 수 있게 되므로, 메모리·블록 등 사용할 자원을 제한하는 명령문을 덧붙인다.

이 상태로는 test 계정이 접속조차 못 한다. 권한을 부여해야 한다.

  
GRANT create session TO test;          -- DB에 접속할 수 있는 권한

이제 접속은 되지만 테이블을 만들려고 하면 ORA-01031: insufficient privileges 오류가 난다. 테이블 생성 권한을 줘 보자.

  
GRANT create table TO test;

그래도 ORA-01950: no privileges on tablespace 'SYSTEM' 오류가 난다. 테이블을 만들 권한은 생겼지만 저장 공간(테이블스페이스)을 쓸 권한이 없다는 뜻이다. 결국 저장 공간까지 줘야 비로소 테이블이 만들어진다.

  
GRANT connect, resource TO test;       -- hr 계정이 가진 권한이 바로 이것

회수는 REVOKE다.

  
REVOKE connect, resource FROM test;    -- 회수하면 다시 테이블을 못 만든다

GRANT와 REVOKE로 권한을 부여하고 회수한다. 명령 자체는 간단하지만, 현업에서는 이 권한을 줄 수 있는 강력한 계정(DBA)으로 안전하게 접속하고 관리하는 일이 까다롭다. 그 계정이 뚫리는 순간 더 이상 내 DB가 아니게 되기 때문이다.

롤(ROLE) — 권한의 집합

계정 수가 적으면 누구에게 어떤 권한이 있는지 엑셀로 정리할 수 있다. 하지만 수천만 명을 일일이 관리할 수는 없다. 롤(role)은 여러 권한을 모아 놓은 집합이다. 대표적인 롤이 앞에서 본 CONNECT와 RESOURCE다.

CONNECT : 접속 관련 시스템 권한 약 40가지
RESOURCE : 락·메모리·파일 등 자원 관련 권한 약 20가지

롤의 장점은 이렇다. 매니저라는 롤을 만들어 대한·코차 두 사용자에게 부여해 두면, 나중에 GRANT c, d TO 매니저로 롤에 권한을 추가하는 순간 두 사람 모두에게 자동으로 c, d 권한이 생긴다. 롤에 딸린 사람이 1명이든 100명이든 상관없어진다. 그래서 현업에서는 대리롤·과장롤·인사과롤처럼 직급별·업무별 롤을 만들어 관리하고, 등급이 바뀌면 기존 권한을 회수하고 새 롤을 부여한다.

  
CREATE ROLE manager;                   -- 아직 빈 깡통
GRANT create table, create view TO manager;   -- 롤에 권한 부여
GRANT manager TO daehan, kochhar;      -- 사용자에게 롤 부여

암호 변경과 계정 잠금

자기 계정의 패스워드는 스스로 바꿀 수 있다(타 계정은 못 바꾼다). 비밀번호를 여러 번 틀리면 계정이 잠기기도 하는데, 잠금과 해제는 ALTER USER로 한다.

  
ALTER USER hr ACCOUNT LOCK;            -- 계정 잠금
ALTER USER hr ACCOUNT UNLOCK;          -- 잠금 해제

객체 권한과 권한 이양

객체의 소유주는 객체를 만든 사람이며, CREATE TABLE을 하면 그 객체에 대한 모든 권한이 자동으로 생긴다. 객체 권한은 A 계정이 만든 emp 테이블을 B 계정이 사용할 수 있게 해 주는 권한이다. GRANT ... ON 객체 TO ...처럼 대상 객체를 ON 절로 지정한다.

DBA가 A 유저에게 객체 권한을 줬을 때, A가 그 권한을 다시 B에게 넘길 수 있을까? 기본적으로는 불가능하다. 단, 권한을 줄 때 옵션을 붙이면 이양할 수 있다.

객체 권한 : WITH GRANT OPTION으로 주면 받은 사람이 다시 다른 사람에게 이양할 수 있다
시스템 권한 : WITH ADMIN OPTION으로 이양 가능하다

회수할 때 차이도 있다. 객체 권한은 회수하면 그로부터 이양된 종속 권한까지 함께 사라지지만, 시스템 권한은 회수해도 이양받은 쪽에 남아 있다. 또 시스템 권한은 그 권한을 부여한 사람만 회수할 수 있다.

ㅇ 핵심 용어 정리

용어	한 줄 정의
뷰(VIEW)	테이블 데이터를 제한해 보여주는 객체. 물리적 데이터 없이 서브쿼리를 재실행
단순/복합 뷰	원본 그대로 / 가공한 데이터를 보여주는 뷰
인라인뷰	`FROM` 절에 별칭과 함께 쓰는 서브쿼리(가상 테이블)
ROWNUM	출력된 행 번호를 돌려주는 가상 컬럼
TOP-N 분석	인라인뷰로 먼저 정렬 + ROWNUM으로 상위 N개 추출
시퀀스	중복 없는 고유 번호를 만드는 생성기. PK 값 생성에 사용
인덱스	검색 속도를 높이는 객체. 컬럼에 설치, SELECT 전용
DCL	권한으로 사용자를 제어하는 언어(GRANT/REVOKE)
롤(ROLE)	여러 권한을 모아 놓은 집합

데이터베이스는 개발자와 떼려야 뗄 수 없는 관계다. 서비스를 만들려면 데이터가 필요하고, 그 데이터를 저장·관리하려면 DB가 구축돼야 하며, DB를 활용하려면 SQL을 쓸 수밖에 없다. 프로젝트를 진행할 때마다 DB는 늘 따라다니므로 수시로 관심을 두고 들여다보는 것이 좋다.

이로써 DB 기초 → SELECT → 조인 → 집계 → 서브쿼리 → DML → DDL → 뷰/DCL로 이어진 Oracle SQL 기초 시리즈를 마무리한다. 여기까지가 SQL의 기본기이고, 더 다양한 스킬이 궁금하다면 같은 흐름 위에서 한 걸음씩 더 파 보면 된다.

CS, Database