[Relational database] 07. 보안과 뷰 ***

Chapter 1: 데이터베이스 보안

• 데이터베이스 보안 개요
• User 개체
• 접근 제어
• 재량 접근 제어
• 특권(Privilege)
• 필수 접근 제어

실습 7-1: 사용자 생성 및 권한 제어

Chapter 2: View

• View 개요
• View 생성과 삭제
• View에 대한 갱신

실습 7-2: 뷰의 생성과 사용 및 보안 설정

---

Chapter 1: 데이터베이스 보안

데이터베이스 보안 개요

• 안전한 DB 응용 설계시 고려하는 목표 3가지.
  1. 보안(Securty): 정보가 권한 없는 사용자에게 누출되어서는 안된다.
  2. 무결성(Integrity): 권한이 있는 사용자에 대해서만 데이터 수정이 허용되어야 한다.
  3. 가용성(Availability): 권한 있는 사용자의 접근이 거부되어서는 안된다.

 

User 개체

• DB의 개체에 대한 접근/삭제/갱신에 대한 권한이 부여.
• DBMS는 DB에서 사용자(User)를 생성/관리 할 수 있는 기능들을 지원.
• OS와 통합되거나, 독립접으로 관리.

# USER 생성.
CREATE USER <User ID> IDENTIFIED BY <Password>

 

 

접근 제어

• DB의 사용자들은 자신의 업무를 수행하는데 필요한 데이터에만 접근.
• DBMS의 2가지 접근 제어 방식
  1. 재량 접근 제어(Discretionary Access Control)
     • 특권(Privilege)을 사용자에게 부여하는 방식.
     • 대부분의 DBMS가 사용하는 방식.
  2. 필수 접근 제어(Mandatory Access Control)
     • 모든 DB 개체에 등급을 매기고 사용자 마다 등급을 부여
     • 군대와 같이 철저한 보안이 필요한 곳에 사용.
     • SQL 1999 표준은 필수 접근 제어를 지정하지 않음.

 

재량 접근 제어 (Discretionary Access Control, DAC)

• GRANT: 사용자에게 기반 테이블이나 뷰에 대한 특권을 부여

GRANT <특권> ON <개체> TO <사용자> [WITH GRANT OPTIONS]

• REVOKE: 사용자에게 부여된 특권 허가를 취소

GRANT <특권> ON <개체> FROM <사용자> [CASCADE]

 

특권(Privilege)

• 특정 조치 또는 작업을 실행하는데 필요한 권한.
• 권한이 부여된 사용자는 개체를 작성/접근 가능하고, GRANT로 소유한 개체에 대한 권한을 다른 사용자에게 전달할 수 있다. 

필수 접근 제어 (Mandatory Access Control, MAC)

• 필수 DB 개체에 각각의 보안 등급을 지정
  (테이블, 뷰, 튜플, 필드 등 모든 개체는 개별 보안 등급을 가짐)
• 모든 주체는 보안 등급에 해당하는 허가 등급이 지정.
• SQL 1999 표준은 필수 접근 제어를 지정하지 않음.

 

실습: 사용자 생성 및 권한 제어 (MySQL)

Terminal 1: root User

Terminal 2: Test User

# Terminal 1에서 root로 로그인.
% mysql -u root -p

# 시스템의 사용자 확인.
mysql> SELECT host, user FROM mysql.User;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+

mysql> CREATE USER Test IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT host, user FROM mysql.User;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | Test             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+


# Terminal 2에서 새 사용자(Test)로 로그인.
% mysql -u Test -p

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.01 sec)


# Terminal 1에서 Test User에게 특권(Privilege) -> SELECT 부여.
mysql> GRANT SELECT ON Module06.Aircraft TO Test;
Query OK, 0 rows affected (0.00 sec)


# Terminal 2에서 Test User가 접근할 수 있는 DB 확인.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Module06           |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM Module06.Aircraft;
+------------+----------------+---------------------+
| AircraftNo | KindOfAircraft | Airline             |
+------------+----------------+---------------------+
|        101 | Boeing 747     | 대한항공            |
|        102 | Boeing 727     | 대한항공            |
|        103 | Airbus A380    | 아시아나 항공       |
|        104 | Airbus A300    | 대한항공            |
|        105 | Boeing 737-800 | 제주항공            |
|        106 | Airbus A220    | 대한항공            |
+------------+----------------+---------------------+
6 rows in set (0.01 sec)

# Error: Test User가 Flight 테이블에 대한 SELECT 특권이 없음.
mysql> SELECT AircraftNo, Airline
    -> FROM Module06.Aircraft AS a INNER JOIN Module06.Flight AS f
    -> ON a.AircraftNo = f.AircraftNo;
ERROR 1142 (42000): SELECT command denied to user 'Test'@'localhost' for table 'flight'


# Terminal 1에서 Test User에게 Flight 테이블의 FlightNo Column에 대한 SELECT 특권 부여.
mysql> GRANT SELECT(FlightNo) ON Module06.Flight TO Test;
Query OK, 0 rows affected (0.00 sec)

# Terminal 2에서 특권 확인.
mysql> SELECT FlightNo FROM Module06.Flight;
+----------+
| FlightNo |
+----------+
|        1 |
|        2 |
|        8 |
|        5 |
|        6 |
|        7 |
|        3 |
|        4 |
+----------+
8 rows in set (0.01 sec)

# Error: Flight 테이블의 모든 데이터를 조회. -> Test User는 FlightNo Column에만 특권 있음.
mysql> SELECT * FROM Module06.Flight;
ERROR 1142 (42000): SELECT command denied to user 'Test'@'localhost' for table 'flight'


# Test User에게서 특권(Privilege) 제거

# Terminal 1에서 Test User에게 부여된 특권(Privilege) 확인
mysql> show grants for Test;
+----------------------------------------------------------------+
| Grants for Test@%                                              |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `Test`@`%`                               |
| GRANT SELECT ON `module06`.`aircraft` TO `Test`@`%`            |
| GRANT SELECT (`FlightNo`) ON `module06`.`flight` TO `Test`@`%` |
+----------------------------------------------------------------+
3 rows in set (0.00 sec)

# Module06.Aircraft의 SELECT 특권 회수.
mysql> REVOKE SELECT ON Module06.Aircraft FROM Test;
Query OK, 0 rows affected (0.00 sec)

# Terminal 2에서 확인 -> Error
mysql> SELECT * FROM Module06.Aircraft;
ERROR 1142 (42000): SELECT command denied to user 'Test'@'localhost' for table 'aircraft'

# Terminal 1에서 Test User에게 Module06 DB의 모든 개체에 대한 특권 부여
mysql> GRANT ALL Privileges ON Module06.* TO Test;
Query OK, 0 rows affected (0.00 sec)

# Terminal 2에서 확인
mysql> SELECT * FROM Module06.Flight;
+----------+------------+--------------------+--------------------+---------+---------------------+
| FlightNo | AircraftNo | Depareture         | Arrival            | Price   | FlightDate          |
+----------+------------+--------------------+--------------------+---------+---------------------+
|        1 |        101 | 인천               | 샌프란시스코       | 1230000 | 2022-10-23 10:20:00 |
|        2 |        101 | 샌프란시스코       | 인천               | 1320000 | 2022-10-26 13:00:00 |
|        3 |        105 | 김포               | 제주               |   72000 | 2022-11-23 09:00:00 |
|        4 |        105 | 김포               | 김해               |   68000 | 2022-11-12 17:30:00 |
|        5 |        103 | 인천               | 프랑크푸르트       | 1480000 | 2022-12-01 18:00:00 |
|        6 |        103 | 프랑크푸르트       | 인천               | 1560000 | 2022-12-10 10:00:00 |
|        7 |        104 | 김해               | 김포               |   70000 | 2022-11-13 11:00:00 |
|        8 |        101 | 인천               | 샌프란시스코       | 1230000 | 2022-11-15 10:00:00 |
+----------+------------+--------------------+--------------------+---------+---------------------+
8 rows in set (0.01 sec)


# Terminal 1에서 Test User의 모든 특권 회수.
mysql> REVOKE ALL Privileges ON Module06.* FROM Test;
Query OK, 0 rows affected (0.00 sec)

# Terminal 2에서 확인.
mysql> SELECT * FROM Module06.Flight;
ERROR 1142 (42000): SELECT command denied to user 'Test'@'localhost' for table 'flight'

 

---

Chapter 2: View

View 개요

• 저장장치에 물리적으로 존재하지 않지만, 사용자에게 있는 것처럼 간주되는 하나 이상의 테이블로부터 유도된 가상의 테이블로 데이터의 논리적 독립성을 제공하는 DB 개체.
• DBMS의 강력한 기능 중 하나로, 접근 제어를 향상시키며 논리적 데이터 독립성을 제공.
• 뷰(View)는 외부 스키마를 구현하는 방법 중 하나로, 실제 DB에 저장된게 아니라 필요할 때 뷰 정의에 따라 계산됨.
  
  항공 시스템(Airline Control System)에서 일반인에게 보여주는 항공편과 특수 항공편은 의도적으로 구분된다.
  특수 항공편(전세기, 정부 및 군용 항공편, 화물 전용 항공편 ... 등)은 일반 사용자에게 보여줄 필요가 없는 항공편이다. 
  따라서 일반인에 대한 외부 스키마와 ACS에 대한 외부 스키마를 View를 이용해 따로 만들어서 보여준다.

스키마 구조

 

View 생성과 삭제

• DDL 문을 사용해 뷰 생성/삭제.

# 뷰 생성.
CREATE VIEW <뷰 이름>
AS
<쿼리 식>

# 뷰 삭제.
DROP VIEW <뷰 이름>

# 모든 뷰 확인
SHOW FULL TABLES IN Module06 WHERE TABLE_TYPE = 'VIEW';

# 항공권 예약 정보를 표시하는 뷰 생성.
CREATE VIEW ReservationInfo
AS
SELECT p.PassengerNo, PassengerName, ReservedDate, Deparetures, Arrival, FlightDate
FROM
	Passenger AS p INNER JOIN Reservation AS R ON p.PassengerNo = r.PassengerNo
    INNER JOIN Flight AS f ON f.FlightNo = r.FlightNo;
      
# Table에 대한 직접적인 접근 없이, View에서 필요한 결과 산출.
# 이순신 고객이 예약한 항공권의 출발지와 도착지, 출발 시간.
SELECT * FROM ReservationInfo
WHERE PassengerName = '이순신';

# 뷰 생성시, Table의 Column 이름 정의.
CREATE VIEW FlightInfo (No, Aircraft, Airline, FromPort, InPort, Price)
AS
SELECT a.AircraftNo, KindOfAircraft, Airline, Deparetures, Arrival, Price
FROM Flight AS f INNER JOIN Aircraft AS a ON f.AircraftNo = a.AircraftNo;

 

View에 대한 갱신

• 사용자는 뷰와 기반 테이블을 갱신할 필요가 없어야 한다.
• 하나의 기반 테이블에 대해서 셀렉션, 프로젝션 만으로 진행되고 집단 연산을 사용하지 않는 뷰만 갱신 허용.
• View는 외부 스키마의 구성을 위해 사용자별로 데이터를 보는 관점을 맞추어 주는 것. 
• 갱신 가능 뷰 (Updateable View): 데이터를 갱신하면 기본 테이블의 값이 변경됨.

 

실습 7-2: 뷰의 생성과 사용 및 보안 설정

# Module06 DB에 생성된 뷰 확인.
mysql> show full tables in module06 where TABLE_TYPE LIKE 'VIEW';
+--------------------+------------+
| Tables_in_module06 | Table_type |
+--------------------+------------+
| flightinfo         | VIEW       |
| reservationinfo    | VIEW       |
+--------------------+------------+

# 뷰 삭제.
mysql> DROP VIEW flightInfo, reservationInfo;

mysql> show full tables in module06 where TABLE_TYPE LIKE 'VIEW';
Empty set (0.00 sec)


# 갱신 가능 뷰 생성.
mysql> SELECT * FROM Aircraft;
+------------+----------------+---------------+
| AircraftNo | KindOfAircraft | Airline       |
+------------+----------------+---------------+
|        101 | Boeing 747     | 대한항공      |
|        102 | Boeing 727     | 대한항공      |
|        103 | Airbus A380    | 아시아나 항공 |
|        104 | Airbus A300    | 대한항공      |
|        105 | Boeing 737-800 | 제주항공      |
+------------+----------------+---------------+
6 rows in set (0.00 sec)


# 대한항공이 소유한 비행기에 대한 뷰 생성.
mysql> CREATE VIEW KoreanAir (No, Type)
    -> AS
    -> SELECT AircraftNo, KindOfAircraft FROM Aircraft
    -> WHERE Airline = '대한항공';
Query OK, 0 rows affected (0.00 sec)

# 생성한 뷰 데이터 확인.
mysql> SELECT * FROM KoreanAir;
+-----+-------------+
| No  | Type        |
+-----+-------------+
| 101 | Boeing 747  |
| 102 | Boeing 727  |
| 104 | Airbus A300 |
+-----+-------------+
4 rows in set (0.00 sec)


# KoreanAir 뷰에 데이터 삽입.
mysql> INSERT INTO KoreanAir VALUES(106,'Airbus A220');

mysql> SELECT * FROM Aircraft;
+------------+----------------+---------------+
| AircraftNo | KindOfAircraft | Airline       |
+------------+----------------+---------------+
|        101 | Boeing 747     | 대한항공      |
|        102 | Boeing 727     | 대한항공      |
|        103 | Airbus A380    | 아시아나 항공 |
|        104 | Airbus A300    | 대한항공      |
|        105 | Boeing 737-800 | 제주항공      |
|        106 | Airbus A220    | NULL          |
+------------+----------------+---------------+


# KoreanAir 뷰에서 106번 비행기의 운항사를 대한항공으로 업데이트.
# Error: KoreanAir 뷰는 Airline Column을 포함하지 않음.
mysql> UPDATE KoreanAir SET
    -> Airline = '대한항공'
    -> WHERE No = 106;
ERROR 1054 (42S22): Unknown column 'Airline' in 'field list'

# Aircraft Table에서 업데이트.
mysql> UPDATE Aircraft SET
    -> Airline = '대한항공'
    -> WHERE AircraftNo = 106;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

# KoreanAir 뷰에서 데이터 갱신 확인.
mysql> SELECT * FROM KoreanAir;
+-----+-------------+
| No  | Type        |
+-----+-------------+
| 101 | Boeing 747  |
| 102 | Boeing 727  |
| 104 | Airbus A300 |
| 106 | Airbus A220 |
+-----+-------------+
4 rows in set (0.00 sec)

# 아시아나 항공 항공기에 대한 뷰 생성.
mysql> CREATE VIEW AsianaAir (No, Type)
    -> AS
    -> SELECT AircraftNo, KindOfAircraft FROM Aircraft
    -> WHERE Airline = '아시아나 항공';
Query OK, 0 rows affected (0.00 sec)

# 제주 항공 항공기에 대한 뷰 생성.
mysql> CREATE VIEW JejuAir (No, Type)
    -> AS
    -> SELECT AircraftNo, KindOfAircraft FROM Aircraft
    -> WHERE Airline = '제주항공';
Query OK, 0 rows affected (0.00 sec)

# 전체 데이터 확인.
mysql> SELECT * FROM KoreanAir
    -> UNION
    -> SELECT * FROM AsianaAir
    -> UNION
    -> SELECT * FROM JejuAir;
+-----+----------------+
| No  | Type           |
+-----+----------------+
| 101 | Boeing 747     |
| 102 | Boeing 727     |
| 104 | Airbus A300    |
| 106 | Airbus A220    |
| 103 | Airbus A380    |
| 105 | Boeing 737-800 |
+-----+----------------+
6 rows in set (0.00 sec)


# 항공편과 예약 정보 뷰 생성. (오타: 테이블에 Depareture로 되어있음. departure 아님 조심.)
mysql> CREATE VIEW FlightInfo (No, Depareture, Arrival, Price, Date, AircraftNo, AircraftType, Airline) 
AS 
SELECT f.FlightNo, Depareture, Arrival, Price, FlightDate, a.AircraftNo, KindOfAircraft, Airline 
FROM Flight AS f INNER JOIN Aircraft AS a ON f.AircraftNo = a.AircraftNo;
Query OK, 0 rows affected (0.01 sec)

# 예약에 대한 뷰 생성.
mysql> CREATE VIEW ReservationInfo (No, Name, Grade, ReservedDate, FlightNo)
    -> AS
    -> SELECT p.PassengerNo, PassengerName, Grade, ReservedDate, f.FlightNo
    -> FROM
    -> Passenger AS p INNER JOIN Reservation AS R ON p.PassengerNo = r.PassengerNo
    ->     INNER JOIN Flight AS f ON f.FlightNo = r.FlightNo;
Query OK, 0 rows affected (0.00 sec)

# 각 뷰에 대한 정보 확인.
mysql> desc FlightInfo;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| No           | int         | NO   |     | NULL    |       |
| Depareture   | varchar(20) | YES  |     | NULL    |       |
| Arrival      | varchar(20) | YES  |     | NULL    |       |
| Price        | int         | YES  |     | NULL    |       |
| Date         | datetime    | YES  |     | NULL    |       |
| AircraftNo   | int         | NO   |     | NULL    |       |
| AircraftType | varchar(20) | NO   |     | NULL    |       |
| Airline      | varchar(10) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

mysql> desc ReservationInfo;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| No           | int         | NO   |     | NULL    |       |
| Name         | varchar(10) | NO   |     | NULL    |       |
| Grade        | int         | YES  |     | 1       |       |
| ReservedDate | date        | NO   |     | NULL    |       |
| FlightNo     | int         | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

# 이순신 승객의 예약 정보 확인.
mysql> SELECT name, ReservedDate, Depareture, Arrival, Date
    -> FROM ReservationInfo AS r INNER JOIN FlightInfo As f
    -> ON r.FlightNo = f.No
    -> WHERE r.name = '이순신';
+-----------+--------------+--------------------+--------------------+---------------------+
| Name      | ReservedDate | Depareture         | Arrival            | Date                |
+-----------+--------------+--------------------+--------------------+---------------------+
| 이순신    | 2022-10-11   | 인천               | 샌프란시스코       | 2022-10-23 10:20:00 |
| 이순신    | 2022-10-11   | 샌프란시스코       | 인천               | 2022-10-26 13:00:00 |
+-----------+--------------+--------------------+--------------------+---------------------+
2 rows in set (0.00 sec)

# 대한항공에서 운항하는 항공편 정보 확인.
mysql> SELECT Depareture, Arrival, Price, Date, Type
    -> FROM FlightInfo AS f INNER JOIN KoreanAir AS k
    -> ON f.AircraftNo = k.no;
+--------------------+--------------------+---------+---------------------+-------------+
| Depareture         | Arrival            | Price   | Date                | Type        |
+--------------------+--------------------+---------+---------------------+-------------+
| 인천               | 샌프란시스코       | 1230000 | 2022-10-23 10:20:00 | Boeing 747  |
| 샌프란시스코       | 인천               | 1320000 | 2022-10-26 13:00:00 | Boeing 747  |
| 인천               | 샌프란시스코       | 1230000 | 2022-11-15 10:00:00 | Boeing 747  |
| 김해               | 김포               |   70000 | 2022-11-13 11:00:00 | Airbus A300 |
+--------------------+--------------------+---------+---------------------+-------------+
4 rows in set (0.00 sec)



# 보안 설정

# Terminal 1: root, Terminal 2: Test

# Terminal 2 접속
% mysql -u Test -p

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)


# Terminal 1에서 5개의 View에 대한 SELECT 특권 부여.
mysql> GRANT SELECT ON Module06.Asianaair TO Test;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON Module06.FlightInfo TO Test;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON Module06.JejuAir TO Test;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON Module06.KoreanAir TO Test;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON Module06.ReservationInfo TO Test;
Query OK, 0 rows affected (0.00 sec)

# Terminal 2에서 접속 가능한 DB 확인.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Module06           |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

# Terminal 2에서 Module06 DB 사용.
mysql> USE Module06;

mysql> show tables;
+--------------------+
| Tables_in_module06 |
+--------------------+
| asianaair          |
| flightinfo         |
| jejuair            |
| koreanair          |
| reservationinfo    |
+--------------------+
5 rows in set (0.00 sec)

# '홍길동' 승객의 예약 정보 확인.
mysql> SELECT Name, ReservedDate, Depareture, Arrival, Date
    -> FROM ReservationInfo AS r INNER JOIN FlightInfo AS f
    -> ON r.FlightNo = f.No
    -> WHERE r.name = '홍길동';
+-----------+--------------+------------+---------+---------------------+
| Name      | ReservedDate | Depareture | Arrival | Date                |
+-----------+--------------+------------+---------+---------------------+
| 홍길동    | 2022-11-09   | 김포       | 제주    | 2022-11-23 09:00:00 |
| 홍길동    | 2022-10-22   | 김포       | 김해    | 2022-11-12 17:30:00 |
+-----------+--------------+------------+---------+---------------------+
2 rows in set (0.00 sec)

# 권한이 부여되지 않은 릴레이션에 접근 불가.
mysql> SELECT * FROM Passenger;
ERROR 1142 (42000): SELECT command denied to user 'Test'@'localhost' for table 'passenger'

 

---

 

 

출처: https://github.com/gikpreet/class-relational_database

GitHub - gikpreet/class-relational_database