[DATABASE] 뷰 (VIEW), 인라인 뷰

■ 뷰(VIEW)의 정의

- 뷰는 가상 객체로, 하나 이상의 테이블 또는 뷰의 데이터를 볼 수 있게 한다. 

- 실제로 테이블은 아니지만 테이블처럼 사용 가능

- 다른 뷰를 참조해 새로운 뷰를 생성할 수 있다.

 

■ 뷰(VIEW)의 사용 목적

- [1] 편리성 : 복잡한 명령/수식 등을 간결하게 사용 가능

- [2] 보안성 : 중요 데이터를 외부로 제한적으로 제공

  * DBO의 원천데이터를 선별적으로 가공하여 사용자에게 (조회)권한 부여

 

■ 뷰(VIEW)의 실제 형태

- .bat 파일과 같이 실제로는 Select ~ from SQL문이 들어가있는 형태

 

■ 뷰(VIEW)를 생성할 수 있는 권한 부여 *최고 권한자만 권한을 부여할 수 있다.

grant create view to 계정

 

■ 뷰(VIEW)의 생성 및 수정

>> 아래에서 예제를 사용하기 위한 테이블 생성 코드

CREATE TABLE MEMBER
(
   EIN     NUMBER(5)      NOT NULL, --EMPLOYMENT ID NUMBER
   NAME    CHAR(5 CHAR)   NOT NULL,
   ADDR    VARCHAR2(150)  NOT NULL, 
   SSN     CHAR(15 CHAR) NOT NULL, -- SOCIAL SECURITY NUMBER
   HIREDAY DATE,
   
   CONSTRAINT UNIQUE_MEMBER_EIN UNIQUE(EIN)
);

ALTER TABLE MEMBER
  ADD CONSTRAINT PK_MEMBER_EIN PRIMARY KEY(EIN);
  
INSERT INTO MEMBER VALUES(1, '김길동', '경기도 김포시 장기동', '951221-1354888','1998-07-22');
INSERT INTO MEMBER VALUES(2, '나길동', '서울시 관악구 테헤란로', '931221-2354888','1998-07-22');
INSERT INTO MEMBER VALUES(3, '다길동', '서울시 관악구 문성로', '950821-1354888','1998-07-22');
INSERT INTO MEMBER VALUES(4, '라길동', '천안시 동남구 대흥동', '921221-1354888','1998-07-22');
INSERT INTO MEMBER VALUES(5, '마길동', '천안시 동남구 대흥동', '911221-2354888','1998-07-22');
INSERT INTO MEMBER VALUES(6, '바길동', '삼척시 원덕읍 호산리', '950321-1354888','1998-07-22');
INSERT INTO MEMBER VALUES(7, '사길동', '삼척시 원덕읍 호산리', '650721-2354888','1998-07-22');
INSERT INTO MEMBER VALUES(8, '아길동', '삼척시 원덕읍 호산리', '630121-1354888','1998-07-22');
INSERT INTO MEMBER VALUES(9, '자길동', '서울시 관악구 문성로', '571221-2354888','1998-07-22');
INSERT INTO MEMBER VALUES(10, '차길동', '경기도 김포시 장기동', '881221-1354888','1998-07-22');

SELECT * FROM MEMBER

CREATE OR REPLACE 뷰이름
AS
  /* SQL 문장 */

[예제1 _ 뷰 만들기]

-- 맴버 테이블에서 입사연도/입사월/입사일을 구하는 뷰

CREATE OR REPLACE VIEW MEMBER_HIREDATES
AS
	SELECT
	  MB.*,
	  TO_NUMBER(TO_CHAR(HIREDAY, 'YYYY')) AS HIRE_YEAR,
	  TO_NUMBER(TO_CHAR(HIREDAY, 'MM')) AS HIRE_MONTH,
	  TO_NUMBER(TO_CHAR(HIREDAY, 'DD')) AS HIRE_DAY
	FROM MEMBER MB
	WHERE SAHIRE IS NOT NULL
    
-- 생성한 뷰를 통해 입사연도별 인원수 구하기
SELECT
  HIRE_YEAR,
  COUNT(*) 인원수
FROM MEMBER_HIREDATES
GROUP BY HIRE_YEAR

[예제2_ 뷰의 보안성을 이해하는 예제]

-- 주민 등록번호 마스킹 처리한 MEMBER테이블을 뷰로 저장
CREATE OR REPLACE VIEW SSN_MASKED_VIEW
AS
	SELECT
	  EIN, NAME, ADDR, 
	  (
	    SUBSTR(SSN, 1, 7)
	    ||
	    '*******'
	  )AS SSN,
	  HIREDAY
	FROM MEMBER

SELECT * FROM SSN_MASKED_VIEW;

>> 결과

 

■ 인라인 뷰 사용하기

- 테이블/뷰에서 모든 데이터 + 별도의 함수식에 의한 데이터를 불러올 때나,

- 코드를 간소화하고 싶을 때에 인라인 뷰를 사용한다.

SELECT
  임시별칭.*,
  CASE문 또는 수식
FROM (SELECT * FROM 테이블/뷰) 임시별칭

[예제]

-- 맴버 테이블에서 계절만 따로 뽑아오기

SELECT
  MB.*,
  CASE FLOOR(TO_NUMBER(TO_CHAR(HIREDAY,'DD')) / 3) 
     WHEN 1 THEN '봄'
     WHEN 2 THEN '여름'
     WHEN 3 THEN '가을'
     ELSE '겨울'
  END AS 생일
FROM (SELECT * FROM MEMBER) MB   -- MB라는 뷰가 임시로 하나 생성된 것과 같은 원리(실제로 생성x)

 

 

■ 뷰(VIEW)의 사용 권한 부여

- DBO(실제 테이블 소유 계정)는 뷰를 통해 DBA(테이블을 사용하는 계정)에게 일부의 데이터만 선택하거나 보안화하여 제공한다.

- 사용 권한을 부여하는 코드는 아래와 같다.

GRANT SELECT ON 뷰이름 TO 계정명

 

[예제]

-- [새로운 계정에 조회 권한만 부여할 때]
-- SQLPLUS에서 아래와 같이 작성

-- 1. 최고 권한자로 로그인
SQLPLUS SYSTEM/ORACLE;

-- 2. 새로운 계정 생성
CREATE USER MYTEST1 IDENTIFIED BY MYTEST1;

-- 3. 새로운 계정에 연결 권한 부여
GRANT CONNECT TO MYTEST1;

-- 4. 사용할 뷰를 가진 DBO계정으로 연결
CONN TEST1/TEST1;

-- 5. 새로운 계정에 뷰 권한 부여
GRANT SELECT ON SSN_MASKED_VIEW TO MEMBER_TEST;

-- 6. 새로운 계정으로 연결
CONN MYTEST1/MYTEST1;

-- 7. 뷰 조회
SELECT * FROM SSN_MASKED_VIEW;

 

 

[출처]

- https://coding-factory.tistory.com/417

- 국비지원 수업 내용 참조