[DataBase] 04. SQL(Structured Query Language) - Intermediate(3)

 

SQL(Structured Query Language) - Intermediate(3)

#Database/Concepts/Temp

View

첫 번째 챕터에서 다뤘던 “View of Data”에 대해 다시 떠올려봅시다.

복습 | View of Data —> “Data abstraction을 통해 실제 물리적으로 저장되어 있는 데이터를 그대로 가져와서 보는게 아니라, 사용자가 필요한 데이터만 보고 접근할 수 있게 되었다”

교수님 강의자료만 보면 View에 대해 정확히 이해하기 쉽지 않습니다. View가 무엇인지만 가지고 View를 어떤 상황에 어떻게 적용해야 하는지 감을 익히는게 매우 중요합니다. 따라서 View에 대한 이해를 돕기 위해 하나의 에피소드를 예로 들어보겠습니다.

에피소드 : IT기업의 직원 정보 데이터베이스

예를 들어 어떤 IT기업의 데이터베이스에 직원들의 이름, 부서명, 연봉 정보, 깃허브 계정 정보를 담고 있는 employee 테이블이 있다고 해보자.

총무과에서도 데이터베이스에 접근할 거고, 인사과, 개발팀 등에서도 직원 정보에 대한 데이터베이스에 접근하게 된다. 이 때 단순히 쿼리를 날려서 데이터를 가져오게 된다면 어떠한 문제점과 단점이 존재할까?

성능 문제 (+귀찮음)
총무과에서 데이터베이스에 접근할 때는 깃허브 계정 정보가 필요 없기 때문에 데이터베이스에 접근할 때 마다 매 번 select name, income from employee 라는 쿼리를 날려서 데이터를 가져와야 한다.

민감한 정보 노출
총무과 직원이 실수로 select * from employee를 날린다면 민감한 정보가 이 직원에게 노출될 수 있다.

유지보수의 어려움

• 변경의 어려움: 테이블 구조가 변경될 때, 각 부서의 쿼리를 일일이 수정해야 하는 번거로움이 있다.
• 데이터베이스 관리 복잡성 증가: 여러 부서가 다양한 쿼리를 작성하고 관리하게 되면, 데이터베이스 관리가 복잡해지고, 데이터베이스 관리자가 모든 쿼리를 이해하고 최적화하기 어려워진다.

위와 같은 문제점을 해결하기 위해서 View 라는 가상의 테이블을 사용한다.

SQL에서 View는 가상의 테이블이다. 이 가상 테이블은 실제 데이터를 물리적으로 저장하지 않고, 다른 테이블이나 뷰에 대한 SELECT 쿼리의 결과를 마치 테이블처럼 사용할 수 있도록 정의된 것이다. 뷰는 데이터베이스에서 읽기 전용으로 사용할 수도 있고, 특정 조건 하에 데이터를 업데이트할 수도 있다.

위 에피소드에 View를 적용하면 어떤 점들이 개선될까?

employee 테이블로부터 employee_manager라는 가상의 테이블(View)를 만든다. 이 테이블에는 직원 이름과 연봉에 대한 attribute만을 포함시킨다. (View 문법에 대해선 아래서 따로 다룰 겁니다)

CREATE VIEW employee_manager AS
SELECT employee_name, salary
FROM employee;

이제 총무과 직원은 employee_manager라는 가상의 테이블(View)를 처음에 만들어 둘 때만 복잡한 쿼리를 날리면 되고, 필요한 데이터를 얻어올 때 매 번 복잡한 쿼리를 날릴 필요가 없다
(단순히 select * from employee_manager 만 날려주면 됨)
이에 따라 필요한 데이터만 뽑아내는데 걸리는 시간 또한 줄어들게 되는건 자명하다

또한 View를 사용하기 이전에는 employee 테이블의 구조가 변경된다면 복잡한 쿼리를 처음부터 다시 작성해야 했지만, 이제는 가상의 테이블(View)를 만드는 쿼리만 수정해주면 된다.

(데이터 접근할 때 필요한 select * from employee_manager 요거는 수정할 필요가 없겠죠?)

또한 총무과 직원들은 employee_manager 테이블에만 접근할 수 있도록 제한하여 불필요한 정보의 노출을 방지할 수 있게 되었다. (접근 제한과 관련된 내용도 이번 챕터 마지막에서 다룰 거에요)

이렇게 에피소드 예시를 통해서 View를 사용하는 이유와 장점에 대해서 살펴보았습니다. 아래 강의자료 내용은 정리하는 느낌으로 살펴보면 될 거에요.

View

• 일부 경우에는 모든 사용자가 전체 논리 모델(즉, 데이터베이스에 저장된 모든 실제 relation)을 보는 것이 바람직하지 않습니다.
• 예를 들어, 강사의 이름과 부서를 알아야 하지만 급여는 알 필요가 없는 사람이 있습니다. 이 사람은 SQL로 다음과 같은 relation을 볼 수 있어야 합니다.

select ID, name, dept_name
from instructor

• 뷰(View)는 특정 사용자의 보기에서 특정 데이터를 숨기는 메커니즘을 제공합니다.
• 사용자가 "가상 관계(table)"로 볼 수 있도록 만드는 모든 관계는 뷰(View)라고 합니다.
• SQL에서 뷰(View)는 일종의 가상 테이블입니다.
  • 뷰(View)도 데이터베이스의 실제 테이블처럼 행과 열을 가지고 있습니다.
  • 데이터베이스에 존재하는 하나 이상의 테이블에서 필드를 선택하여 뷰(View)를 만들 수 있습니다.
  • 뷰(View)는 테이블의 모든 행을 가질 수도 있고 특정 조건에 따라 특정 행만 가질 수도 있습니다.

Syntax

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;

• view_name: 뷰의 이름
• table_name: 테이블의 이름
• condition: 행을 선택하기 위한 조건

예제: StudentDetails 단일 테이블에서 DetailsView라는 View 생성

CREATE VIEW DetailsView AS
SELECT NAME, ADDRESS
FROM StudentDetails
WHERE S_ID < 5;

• 뷰(View) 내의 데이터를 보려면 테이블을 조회하는 것과 같은 방식으로 뷰를 조회할 수 있습니다.

SELECT *
FROM DetailsView;

결과

예제: StudentDetails와 StudentMarks 두 테이블에서 MarksView라는 이름의 뷰(View)를 생성

CREATE VIEW MarksView AS
SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;

• 여러 테이블에서 View를 생성하려면 SELECT 문에 여러 테이블을 포함하면 됩니다.

마찬가지로 MarksView 정보를 조회하려면

SELECT *
FROM MarksView;

이렇게 간단한 쿼리만 날려주면 된다

결과

Index

SQL에서 인덱스(Index)는 데이터베이스의 테이블에 대해 검색 성능을 향상시키기 위해 사용되는 데이터 구조이다. 인덱스는 책의 색인처럼, 특정 열의 값을 빠르게 찾을 수 있도록 돕는 역할을 한다.

• 많은 쿼리는 테이블의 레코드 중 작은 부분만 참조합니다.
  • 특정 값을 가진 레코드를 찾기 위해 모든 레코드를 읽는 것은 비효율적입니다.
  • relation의 attribute에 대한 인덱스는 데이터베이스 시스템이 관계의 모든 튜플을 스캔하지 않고도 해당 attribute에 대해 지정된 값을 가진 튜플을 효율적으로 찾을 수 있게 하는 데이터 구조입니다.
    • 인덱스는 SELECT, DELETE, UPDATE 같은 작업을 빠르게 수행하여 대량의 데이터를 조작할 수 있습니다.
    • 하나의 테이블에는 하나 이상의 INDEX 테이블이 포함될 수 있습니다.
• 인덱스가 없을 경우
  • 예를 들어, MySQL은 첫 번째 행부터 시작하여 관련 행을 찾기 위해 전체 테이블을 읽어야 합니다.
    • 테이블이 클수록 비용이 더 많이 듭니다.
  • 테이블에 해당 열에 대한 인덱스가 있으면 MySQL은 모든 데이터를 보지 않고도 데이터 파일의 중간 위치를 빠르게 결정할 수 있습니다.
    • 이는 모든 행을 순차적으로 읽는 것보다 훨씬 빠릅니다.

Syntax

create index <name> on <relation-name>(attribute);

• 인덱스는 create index 를 사용하여 생성한다.

예시: student table에 index 생성

create table student
(ID varchar (5),
name varchar(20) not null,
dept_name varchar (20),
tot_cred numeric (3,0) default 0,
primary key(ID))

create index studentID_index on student(ID) 

쿼리

select * from student where ID = '12345';

• 이는 모든 학생 레코드를 보지 않고 인덱스를 사용하여 필요한 레코드를 찾을 수 있도록 실행할 수 있습니다.

참고 | 인덱스의 동작 원리

알고리즘 공부를 해본 사람이라면 대충 감이 올텐데,

데이터를 미리 정렬을 해두면 데이터를 탐색하는데 매우 효율적이고, 해시나 비트맵을 사용하는 것도 데이터 탐색에 효율적이다.

인덱스에서도 동일하게 적용된다. 인덱스를 위해 일반적으로 B-트리(B-tree) 구조를 사용하고, 해시 인덱스나 비트맵 인덱스도 사용한다.

이에 대해선 나중에 다룰 기회가 있을 것이다.

Authorization

• 우리는 사용자가 데이터베이스의 일부에 대해 여러 형태의 권한을 가질 수 있도록 할 수 있습니다.
  • Read - 데이터 읽기만 가능하며 수정은 불가능
  • Insert - 새 데이터 삽입은 가능하지만 기존 데이터 수정은 불가능
  • Update - 수정 가능하지만 데이터 삭제는 불가능
  • Delete - 데이터 삭제 가능
• 이러한 각 권한 유형을 privilege라 합니다.
  • 우리는 사용자가 데이터베이스의 특정 부분(관계 또는 뷰)에 대해 이러한 유형의 권한을 모두, 없거나, 조합하여 가질 수 있도록 할 수 있습니다.
• 데이터베이스 스키마를 수정할 수 있는 privilege의 형태
  • Index - 인덱스 생성 및 삭제 가능
  • Resources - 새로운 relation 생성 가능
  • Alteration - relation에서 attribute 추가 또는 삭제 가능
  • Drop - relation 삭제 가능

Syntax

grant <privielge list> on <relation or view> to <user list>

에는

• user-id
• public (모든 유효한 사용자에게 권한 부여)
• A role(역할) (role에 대한 자세한 내용은 나중에 설명)

를 지정할 수 있습니다.

예시:

grant select on department to Amit, Satoshi;

•  뷰에 대한 권한을 부여하는 것은 기본 relation에 대한 권한을 부여하는 것을 의미하지 않습니다.
  • View 에피소드를 생각해보자. employee_manager View에 접근할 수 있다고 employee 테이블에 접근할 수 있는건 아니다.
•  권한 부여자는 지정된 항목에 대해 이미 권한을 가지고 있어야 합니다(또는 데이터베이스 관리자여야 합니다).

에는

• select: relation에 대한 읽기 접근을 허용하거나 뷰를 사용하여 쿼리할 수 있는 권한
  • 예시: 사용자 U₁, U₂, U₃에게 instructor relation에 대한 select 권한 부여
  • grant select on instructor to U₁, U₂, U₃;
• insert: 튜플을 삽입할 수 있는 권한
• update: SQL update 문을 사용하여 업데이트할 수 있는 권한
• delete: 튜플을 삭제할 수 있는 권한
• all privileges: 허용 가능한 모든 권한을 위한 단축어로 사용됨

를 지정할 수 있습니다.

Revoking Authorization in SQL

revoke 문은 권한을 취소하는 데 사용됩니다.

Syntax

revoke <privilege list> on <relation or view> from <user list>;

예시:

revoke select on student from U₁, U₂, U₃;

•  는 권한을 취소할 모든 권한일 수 있습니다.
• 에 public이 포함된 경우, 명시적으로 부여된 권한을 제외한 모든 사용자가 권한을 잃게 됩니다.
• 권한 취소에 의존하는 모든 권한도 취소됩니다.

Roles

privilige(권한)의 묶음이라고 생각하면 된다.
IT기업에 Manager 직책을 가진 사람들 모두에게 일일히 CRUD 권한을 세부적으로 부여하는 대신, Manager라는 일종의 Roles(역할)을 부여한다. 이 역할에는 권한 정보를 담는다.

• role은 사용자가 데이터베이스에서 접근/업데이트할 수 있는 내용을 구별하는 방법입니다.
• role은 보안 모델의 설정 및 유지를 용이하게 하기 위해 생성됩니다.
• 이는 사용자에게 부여될 수 있는 관련 권한의 명명된 그룹입니다.
• 데이터베이스에 많은 사용자가 있을 때 사용자에게 권한을 부여하거나 취소하는 것이 어려워집니다.

예시

Role 생성

• 먼저, 데이터베이스 관리자(DBA)가 role을 생성해야 합니다. 그 후 DBA는 role과 사용자에게 권한을 부여할 수 있습니다. 

CREATE ROLE manager;

• 구문에서 'manager'는 생성될 role의 이름입니다.
• role이 생성되면, DBA는 GRANT 문을 사용하여 사용자에게 role을 할당하고 role에 권한을 부여할 수 있습니다.
• role을 통해 권한을 부여하거나 취소하는 것이 모든 사용자에게 직접 권한을 부여하는 것보다 쉽습니다.

Role에 권한 부여

GRANT create table, create view
TO manager;
Grant succeeded.

• 먼저, 매니저 역할(Role)을 생성한 후 매니저가 테이블과 뷰를 생성할 수 있도록 허용합니다.

사용자에게 Role 부여

GRANT manager TO SAM, STARK;
Grant succeeded.

• 그런 다음 Sam과 Stark에게 매니저 Role(역할)을 부여합니다.
• 이제 Sam과 Stark는 테이블과 뷰를 생성할 수 있습니다. 사용자에게 여러 Role이 부여된 경우, 모든 Role과 관련된 모든 권한을 받습니다.

Role에서 특정 권한 취소

REVOKE create table FROM manager;

• Revoke를 사용하여 '매니저' Role에서 테이블 생성 권한을 제거합니다.

Role 삭제

DROP ROLE manager;

• drop을 사용하여 데이터베이스에서 Role을 삭제합니다.

정리

Join
Join에는 크게 3가지 종류가 존재한다.

• Natural Join : 두 테이블 간에 동일한 이름의 컬럼들을 자동으로 사용하여 조인
• Inner Join : ON 조건에 따라 두 테이블에서 일치하는 행만 조인
• Outer Join : 일치하는 행이 없더라도, 한쪽 또는 양쪽 테이블의 모든 행을 포함하여 조인

Outer Join
Outer Join은 어느 테이블의 튜플을 모두 포함할 지에 따라 3가지로 분류할 수 있다.

• Left Outer Join
• Right Outer Join
• Full Outer Join

View
SQL에서 가상 테이블을 View라고 한다. View는 실제 데이터를 물리적으로 저장하지 않고, 다른 테이블이나 뷰에 대한 SELECT 쿼리의 결과를 마치 테이블처럼 사용할 수 있도록 정의되어있다.

View를 사용했을 때 다음과 같은 이점을 얻을 수 있다.

• 가상의 테이블(View)를 처음에 만들어 둘 때만 복잡한 쿼리를 날리면 되고, 필요한 데이터를 얻어올 때 매 번 복잡한 쿼리를 날릴 필요가 없다
• 필요한 데이터만 뽑아내는데 걸리는 시간 또한 줄어든다
• 데이터를 가져온 원본 테이블의 구조가 변경되어도 가상의 테이블(View)를 만드는 쿼리만 수정해주면 된다.
• 불필요한 정보의 노출을 방지할 수 잇다.

Index
SQL에서 Index는 책의 색인처럼, 특정 열의 값을 빠르게 찾을 수 있도록 돕는 역할을 한다.

Authorization
사용자가 데이터베이스 내의 특정 리소스(테이블, 뷰, 함수, 프로시저 등)에 접근하거나 조작할 수 있는 권한을 설정하는 과정을 의미하고, 이 권한을 privilege 라고 한다.
권한을 부여하기 위해 Grant 절을 사용하고, 권한을 뺏기 위해 Revoke 절을 사용한다.

Role
SQL에서 사용자들에게 일괄적으로 권한을 부여하고 관리하기 위해 사용하는 privilege의 묶음이다. 여러 사용자에게 공통적으로 적용할 권한을 하나의 단위로 관리할 수 있다는 장점이 존재한다.

끝!

Ref) Database System Concepts - 7th edition (Abraham Silberschatz)