SQL view 기본 정리

#sqlview #sql뷰 #개발자의도구들

​

컴퓨터공학과 학사과정 중 공부한 내용을 정리하였습니다.

\* 본글은 PC버전에 최적화 되어있습니다.

​

SQL VIEW

SQL에서의 VIEW에 대해 공부하면서 새롭게 알게된 내용들을 정리하였습니다.

​

INDEX

• WHY
• DEF
• simple view
• complex view
• Function Cols
• Read-only
• Characters
• Structure
• view가 저장되는 곳
• view 실행 순서
• Authorization
• view 생성 최소 권한
• view 단위의 권한 제한

​

WHY

그냥 데이터 베이스를 보여주면 되지 왜 궂이 VIEW를 쓰는지에 대한 근본적인 의문을 해결하는 것이 이번글의 첫번째 목적입니다.

​

1. 보안

VIEW를 흔히 보안상의 이유로 사용하는 경우가 많습니다. VIEW를 정의할 때 실제 테이블이 아닌 테이블의 일부 데이터만 포함하여 정의 할 수 있습니다.

​

1. 편의성

1번과 같은 매락인데, 데이터 베이스에 접근하여 데이터가 필요한 사용자가 본인이 필요한 데이터 col만을 커스텀하여 사용할 수 있습니다.

​

그 외에도 다양한 사용이유가 있겠지만, 이 두가지 측면에서 크게 벗어나지 않기 때문에 이 부분만 기억하셔도 됩니다.

​

DEF

simple view and complex view

VIEW를 정의하는 방법은 아래와 같습니다.

                                    CREATE VIEW stud_view -- 학생 테이블에 대한 view 생성
AS SELECT studno, name, univercity, age
   FROM student
   WHERE deptno = 101

VIEW를 생성할 때 핵심 키워드는 VIEW, AS로 반드시 명시를 해줘야 합니다. 위의 VIEW는 현재 student라는 단일 테이블에 의해 정의된 VIEW입니다. 이를 Simple View라고 합니다.

​

Simple View가 있으면 Complex View도 있겠죠? 아래는 두개 이상의 테이블을 참조하여 만든 VIEW입니다.

                                    CREATE VIEW stud_view -- 학생 테이블에 대한 view 생성
AS SELECT s.studno, s.name, s.univercity, s.age, d.dname, d.location
   FROM student s, department d
   WHERE s.deptno = d.deptno

이제 위의 VIEW에서 모든 학과 학생의 학과 이름, 위치엥 대한 정보를 추가하여 선언하였습니다.

VIEW에 대해 알고가면 좋은건, SELECET 문에서 사용가능한 어떤 COL도 VIEW로 정의가 가능하다는 것입니다. 즉 집계함수나, 여러 표현식으로 COL을 정의할 수 도 있습니다.

                                    CREATE VIEW emp_view -- 학생 테이블에 대한 view 생성
AS SELECT name, hiredate, max(sal), avg(sal)
   FROM employee
   WHERE deptno = 1101

단, 이때는 DML의 update, insert 명명문 실행이 불가능합니다.

                                    CREATE VIEW emp_view
AS SELECT deptno, max(sal), min(sal), avg(sal)
   FROM employee
   GROUP BY deptno

부서별 최고 급여, 최소 급여, 평균 급여에 대한 view 생성을 할때는 group by와 같은 절이 사용되는데 이 때는 DML조작이 불가능합니다. 즉 view가 read-only로 생성된 것이죠. 아래 좀 더 다양한 케이스를 정리해 두었습니다.

​

                                    DROP VOEW emp_view

\*drop은 DDL로 정의되는 테이블, 뷰, 인덱스 들을 삭제하는 공용 키워드임

Oracle에서 view를 직접적으로 수정하는 것은 지원하지 않기 때문에, 삭제후 새로 생성해야합니다.

​

​

Characters

다음은 view에 대한 특징들을 정리해 보았습니다.

                                    1. view는 물리적인 hard disk에 저장되지 않는다.

2. view에 DML 쿼리를 날릴 수 있다.
    2.1. DML쿼리는 실제 DB에 반영된다. (단, 원래 테이블의 제약조건을 따랴야한다)

3. view를 정의할 때, 표현식을 사용할 경우
    3.1. update, insert명령문 실행이 불가능하다.
    3.2. 표현식을 사용하면 반드시 별명을 지어줘야 한다.

4. view 정의시 Group by, Group methods, Distinct 절을 포함한 경우
   모든 종류의 DML 사용이 불가하다.(Read-Only)
    4.1. DML 사용 불가한 경우 추가: window, join, subquery, distinct, 집합 연산 등

Structure

자, 일단 기본적인 명령문과 특징들에 대해서 알아보았으니, 좀 더 본격적으로 깊게 VIEW를 공부해 봅시다.

앞서서 view는 물리적으로(DISK 내부)는 저장되지않는다고 하였었는데요. 그럼 view는 어디에서 참조되는 걸까요?

​

DB 시스템에는 데이터 딕셔너리라는 것이 존재하는데, 여기에는 데이터에 대한 정보(메타 데이터)가 담겨있습니다. 데이터 딕셔너리는 view에 대한 정보도 포함하고 있습니다.

​

데이터 딕셔너리는 view에 대한 다음 정보를 포함합니다.

                                    뷰의 이름
뷰를 정의하는 SELECT 문 -- view를 정의한 select문이 저장됨
뷰와 관련된 권한 정보
기타 뷰의 속성 정보

DB에 선언된 view의 모든 정보는 USER\_VIEWS라는 곳에서 볼 수 있습니다.

                                    SELECT * FROM USER_VIEWS

view를 참조하여 데이터를 가져오거나, DML을 사용할 때 다음과 같은 순서를 따릅니다.

                                    1. USER_VIEWS 데이터 딕셔너리에서 뷰에 대한 정의를 조회한다.

2. 기본 테이블에 대한 뷰의 접근 권한을 확인한다.

3. 뷰에 대한 질의를 기본 테이블에 대한 질의로 변경한다.

4. 기본 테이블에 대한 질의를 통해 데이터 검색

5. 검색된 결과 출력

이런 처리 과정을 통해 view에대한 질의 연산이 실제로는 실제 TABLE에 대한 질의인 것을 볼 수 있습니다.

​

                                    CREATE VIEW stud_view -- 학생 테이블에 대한 view 생성
AS SELECT studno, name, univercity, age
   FROM student
   WHERE deptno = 101

위에 선언된 simp view에 아래 질의를 적용해 봅시다.

                                    SELECT *
FROM stud_view
where name = '홍길동'

현재 view에 질의를 하고 있지만, 실제는 아래와 같은 질의가 DB에 적용됩니다.

                                    SELECT studno, name, univercity, age
FROM student
WHERE name = '홍길동' AND deptno = 101

이런 원리 때문에 view에 대한 질의는 실제 table에 반영이 되는 것입니다!

Authorization

기본적으로 view를 생성하려면 해당 테이블에 대한 최소 두가지 권한이 필요합니다.

                                    1. CREATE VIEW

2. SELECT

또한 simple view의 dml 권한은 해당 사용자가 해당 테이블에 대한 권한만큼만 가능합니다.

                                    user1 : create view, select, update

-> view 생성시
view에 대한 select, update만 가능, delete 불가

user가 해당 테이블에 가지는 권한 자체가, view의 DML 권한과 일치합니다.

user1이 해당 테이블에 대한 모든 권한을 가지고 있다고 해도, 생성된 view를 오직 read-only로만 처리하고 싶을 경우가 있습니다. 이럴때는 여러가지 방법을 사용할 수 있습니다.

​

권한철회(view 단위)

                                    CREATE VIEW stud_view
AS SELECT studno, name, univercity, age
   FROM student
   WHERE deptno = 101

user1은 현재 student에 대한 모든 권한이 있다고 가정하면, 해당 유저는 view를 통해 모든 DML조작이 가능합니다. 하지만, stud\_view는 오직 read-only 상태로만 만들고 싶습니다.

​

이때 아래와 같이 view에 대한 권한을 철회하면 됩니다.

                                    REVOKE UPDATE ON stud_view FROM user1;
REVOKE DELETE ON stud_view FROM user1;

-- 다시 부여할 때
GRANT UPDATE ON stud_view TO user1;

매번 사용자마다 쿼리를 날리는게 번거롭기 때문에, 보통은 role 기반으로 권한 부여 및 철회를 수행합니다.

​

두 번째는 view의 특성을 이용하는 건데요. 앞서서 view를 생성하고 나서 DML조작이 불가능한 경우가 여럿 있었습니다. 이점을 활용하면 쉽게 read-only로 view를 설계할 수 있습니다.

​

                                    CREATE VIEW lock_view AS
SELECT student_id, name, CONCAT(name, ' - locked') AS locked_name
FROM student;

CONCAT으로 인해 해당 view는 read-only로 생성되었습니다.