데이터베이스 보안 - 4주차. 구조화 질의어 (SQL)

(세종대학교) 데이터베이스 보안 - 수업 정리 내용

4.1. 구조화 질의어 (SQL) 개념

4.1.1. SQL

• Structured Query Language
  • 관계형 데이터베이스 관리 시스템의 데이터를 관리하기 위해 개발된 표준 질의어
  • 데이터 제어 뿐만 아니라 다양한 기능 제공
  • DBMS에게 원하는 내용을 알려주고, 결과를 얻는데 사용하는 데이터베이스 전용 언어
• 일반 프로그래밍 언어와의 차이
  • 자바나 C와 같은 완전한 프로그래밍 언어는 아님
  • 데이터 부속어 : 데이터 생성 및 처리하는 문법만 가짐
  • 모두 테이블에 의존적으로 이뤄지는 입출력
• 분류
  • 3주차 참고
  • DDL, DML, DCL

4.1.2. SQL 표준화

• 역사

  • 1970년대 후반
    • 관계형 데이터베이스 개발 → IBM의 SEQUEL이 시스템 R에 질의하기 위해 개발
  • 1983년
    • ANSI에서 관계형 데이터베이스를 위한 질의어에 대한 표준화 작업 시작
  • 1986년
    • ANSI에 의해 SQL-86(SQL1)라는 표준명으로, 관계형 데이터베이스 표준 언어가 채택
  • 1987년
    • ISO에 의해 표준 언어로 채택
  • 1992년
    • SQL2 표준화 작업 완료
  • 1999년
    • SQL3 표준화 작업 완료 → 객체 데이터베이스 기능 포함
    • 2003년 발표 → XML 기능 포함
  • 2019년
    • SQL 발표

• 표준의 중요성

  • 표준 규격화해서 생성하면 DBMS 종류 상관 없이 공유 가능

• 표준 미준수 이유

  • 방대하고 복잡한 SQL 표준 내용 → 완벽한 구현 어려움
  • 이미 확보된 고정 고객
    • 표준 제정 전후 호환성 문제

4.2. 데이터 정의어 (DDL)

• Data Definition Language
  • 스키마, 테이블, 뷰, 인덱스 등을 정의(생성)하거나 변경, 또는 삭제할 때 사용하는 언어
  • CREATE, ALTER, DROP

4.2.1. CREATE문

CREATE TABLE 테이블_이름(
 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값]
 [PRIMARY KEY (속성_리스트)]
 [UNIQUE (속성_리스트)]
 [FOREIGN KEY (속성_리스트) REFERENCES 테이블_이름(속성_리스트)][ON DELETE 옵션][ON UPDATE 옵션]
 [CONSTRAINT 이름] [CHECK(조건)]

• 속성 정의

  • 테이블 구성 속성 타입 지정 및 NULL 값 허용 여부 또는 기본 값 지정 등 정의

  • 데이터 타입 : 정수, 문자, 시간 등

    

  • NOT NULL

    • 속성에 NULL 값 허용하지 않음
    • 기본적으로 NULL 값 허용 (옵션 값 붙이지 않으면)
    • ex) USER_ID VARCHAR(10) NOT NULL;

  • DEFAULT

    • 기본값 명확히 지정 가능
    • 숫자면 숫자 그대로, 문자나 날짜 데이터는 작은 따옴표, 대소문자 구별함
    • ex
      • USER_AGE INT DEFAULT 0;
      • STUDENT_GARDE CHAR(2) DEFAULT 'A0';

  • PRIMARY KEY

    • 각 튜플을 식별할 수 있는 값
    • 가능한 기본키 지정 → NULL 값 허용 안 됨
    • ex
      • RIMARY KEY(USER_ID);
      • PRIMARY KEY(CUSTOMER_ID, PRODUCT_ID);

  • UNIQUE KEY 정의

    • 대체키 지정 키워드
    • 기본키와 달리 NULL 값 가능

  • FOREIGN KEY 정의

    • 외래키 지정 키워드
      • 다른 테이블의 기본키와 동일
      • 출처를 밝히기 위해 참조되는 테이블과 속성 제시
    • ex
      • FOREIGN KEY(USER_NUM) REFERENCES USER(NUM);

  • 제약조건 정의

    • CONSTRAINT 키워드로 정의
      • 도메인 무결성 제약조건
        • CHECK 키워드 또는 CHECK, CONSTRAINT 키워드와 함께 지정
      • 개체 무결성 제약조건
        • PRIMARY KEY, NOT NULL 키워드를 통해 제공 가능
      • 참조 무결성 제약조건
        • FOREIGN KEY 지정 시 REFERENCE 키워드 사용하여 제공

• 예제

  주문 테이블은 주문번호, 주문고객, 주문제품, 수량, 배송지, 주문일자 속성으로 구성되고, 주문번호 속성이 기본키다. 주문고객 속성이 고객 테이블의 고객아이디 속성을 참조하는 외래키이고, 주문제품 속성이 제품 테이블의 제품번호 속성을 참조하는 외래키가 되도록 주문 테이블 생성해보라.

    CREATE TABLE 주문(
    주문번호 CHAR(3) NOT NULL,
    주문고객 CHAR(20),
    주문제품 CHAR(3),
    수량 INT,
    배송지 VARCHAR(30),
    주문일자 DATE,
    PRIMARY KEY(주문번호),
    FOREIGN KEY(주문고객) REFERENCES 고객(고객아이디),
    FOREIGN KEY(주문제품) REFERENCES 제품(제품번호)
    );

4.2.2. ALETER문

• 기본 구조

  • 테이블을 ALTER TABLE 문으로 변경
    • 속성 추가
      • ALTER TABLE 테이블 이름 ADD 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값];
    • 속성 삭제
      • ALTER TABLE 테이블_이름 DROP COLUMN 속성_이름;
    • 제약조건 추가
      • ALTER TABLE 테이블_이름 ADD CONSTRAINT 제약조건_이름 제약조건_내용;
    • 제약조건 삭제
      • ALTER TABLE 테이블_이름 DROP CONSTRAINT 제약조건_이름;

• 예제

  고객 테이블에 20세 이상의 고객만 가입할 수 있다는 데이터 무결성 제약조건을 추가해보자.

    ALTER TABLE 고객 ADD CONSTRAINT CHk_AGE CHECK(나이>=20);

4.2.3. DROP문

• 기본 구조

  • 테이블을 삭제하는 문
  • 삭제하는 테이블을 다른 테이블이 참조하고 있다면 삭제 수행 불가 → 먼저 참조 없애야함
  • DROP TABLE 테이블_이름;

• 예제

  배송업체 테이블을 삭제해보자.

    DROP TABLE 배송업체;

4.3. 데이터 조작어 (DML) (1)

• Data Manipulation Language
  • 테이블 내 데이터를 검색, 삽입, 수정, 삭제하는데 사용되는 언어

4.3.1. SELECT/FROM 문

• 기본 구조

  • SELECT [ALL|DISTINCT] 속성_리스트 FROM 테이블_리스트;
  • 테이블 내 데이터를 검색하는 문
  • 속성 및 테이블이 다수일 경우 콤마(,)로 구분 (속성 다 포함하고 싶으면 * 쓰기)
  • ALL - 중복 허용, DISTINCT - 중복 허용 하지 않음

• 예제

  제품 테이블에서 제조업체 속성을 중복 없이 검색해보자

    SELECT DISTINCT 제조업체
    FROM 제품;

4.3.2. WHERE 조건

• 기본 구조

  • SELECT/FROM 문 뒤에 쓰여져 조건을 만족하는 데이터 검색할 때 사용
  • 비교 연산자와 논리 연산자 이용
    • 비교 연산자
      • =, <>, >, <, ≤, ≥
    • 논리 연산자
      • and, or, not
      • 조건 여러 개 결합 시

• 예제

  주문 테이블에서 apple 고객이 주문했거나 15개이상주문된 제품의 주문제품, 수량, 주문일자, 주문고객을 검색해보자.

    SELECT 주문제품, 수량, 주문일자, 주문고객
    FROM 주문
    WHERE 주문고객='apple' OR 수량 >=15;

• LIKE 키워드

  • 검색 조건을 부분적으로만 알아도 사용 가능
  • 문자열 이용하는 조건에 사용 가능
  • 기호
    • % : 0개 이상의 문자 대체
    • _ : 1개 문자 대체

• 예제

  고객 테이블에서 고객아이디가 5자인 고객의 고객아이디, 고객이름, 등급을 검색해보자.

    SELECT 고객아이디, 고객이름, 등급
    FROM 고객
    WHERE 고객아이디 LIKE '_____';

4.3.3. 집계 함수와 GROUP BY

• 집계 함수

  • SQL에서 특정 속성 값을 통계적으로 계산한 결과를 검색하기 위해 이용
  • 열 함수 (Column)
  • SELECT문과 함께 자주 사용하는 다섯 가지 집계 함수
    • 모든 데이터
      • count
      • max
      • min
    • 숫자 데이터
      • sum
      • avg
  • 주의사항
    • NULL인 값은 계산에서 제외
    • WHERE 조건에서 사용불가
    • SELECT 문이나 HAVING 조건에서만 사용 가능

• 예제

  제품 테이블에서 모든 제품의 단가 평균을 검색해보자.

    SELECT AVG(단가) AS '단가 평균'
    FROM 제품;

• GROUP BY

  • 특정 속성 값이 동일한 튜플을 모아 그룹을 만들고 그룹별로 검색
  • HAVING 키워드 추가하여 그룹에 대한 조건 추가 가능
  • 그룹별 검색하는 SELECT 문 기본 형식

• 예제

  제품 테이블에서 제품을 3개 이상 제조한 제조업체별로 제품의 개수와, 제품 중 가장 비싼 단가를 검색해보자.

    SELECT 제조업체, COUNT(*) AS 제품수, MAX(단가) AS 최고가
    FROM 제품
    GROUP BY 제조업체 HAVING COUNT(*)>=3;

4.4. 데이터 조작어 (DML) (2)

4.4.1. INSERT 문

• 기본 구조
  • 테이블에 새로운 튜플 삽입하는 문
    • 테이블에 튜플 직접 삽입
      • INSERT INTO 테이블_이름[(속성리스트)] VALUES (속성리스트);
    • 부속 질의문 이용해 삽입
      • INSERT INTO 테이블_이름[(속성_리스트)] SELECT문;
      • 대신 컬럼수 동일해야함

4.4.2. UPDATE 문

• 기본 구조

  • 테이블에 저장된 데이터 수정
    • 특정 속성 값 수정
    • UPDATE 테이블_이름 SET 속성_이름 = 값1, 속성_이름 = 값2, ... [WHERE조건];

• 예제

  판매 데이터베이스에서 정소화 고객이 주문한 제품의 주문수량을 5개로 수정해보자

    UPDATE 주문
    SET 수량 = 5
    WHERE 주문고객 IN (SELECT 고객아이디 FROM 고객 WHERE 고객이름='정소화');

4.4.3. DELETE 문

• 기본 구조

  • 테이블에 저장된 데이터를 삭제하기 위해 사용하는 명령어
  • DELETE FROM 테이블_이름 [WHERE 조건];

• 예제

  판매 데이터베이스에서 정소화 고객이 주문한 내역을 주문 테이블에서 삭제해보자.

    DELETE FROM 주문 WHERE 주문고객 IN (SELECT 고객아이디 FROM 고객 WHERE 고객이름='정소화');

4.5. 데이터 제어어 (DCL)

4.5.1. GRANT 문

• 객체 권한 부여

  • 객체 소유자가 다른 사용자에게 객체에 대한 사용 권한 부여
  • GRANT 권한 ON 객체 TO 사용자 [WITH GRANT OPTION];
  • 부여 가능한 5가지 주요 권한
    • INSERT, UPDATE, DELETE, SELECT, REFERENCES
  • 옵션
    • PUBLIC
      • 모든 사용자에게 똑같은 권한을 부여하는 키워드
    • WITH GRANT OPTION
      • 사용자가 자신이 부여받은 권한을 다른 사용자에게도 부여할 수 있도록 허락하는 옵션

• 예제

  고객 테이블에 대한 검색 권한을 WITH GRANT OPTION을 포함하여 사용자 Lee에게 부여해보자.

    GRANT SELECT ON 고객 TO Lee WITH GRANT OPTION;

• 시스템 권한 부여

  • 데이터베이스 관리자가 부여
  • 데이터베이스 관리와 관련된 작업에 대한 권한
    • DDL 같은거
  • DBA가 시스템 권한을 부여할 때도 GRANT 명령어 사용
  • 역할기반 접근제어
    • 역할을 생성하고, 특정 개체에 ROLE을 부여하고, 사용자에게 다수의 ROLE 부여 가능

• 예제

  테이블을 생성할 수 있는 시스템 권한을 사용자 Song에게 부여해보자.

    GRANT CREATE TABLE TO Song;

4.5.2. REVOKE 문

• 기본 구조

  • GRANT 명령을 통해 다른 사용자에게 권한을 부여한 사용자가 자신이 부여한 권한을 취소하는 것
  • 객체 권한 취소와 시스템 권한 취소로 구별
  • REVOKE 권한 ON 객체 FROM 사용자 CASCADE | RESTRICT;
    • CASCADE : 트리 구조의 사용자 모두 권한 취소
    • RESTRICT : 특정 사용자만 권한 취소

• 예제

  Kim이 Hong에게 부여한 고객 테이블에 대한 검색 권한을 취소하면서 Hong이 다른 사용자에게 부여한
  고객 테이블에 대한 검색 권한도 함께 취소하도록 해보자.

    REVOKE SELECT ON 고객 FROM Hong CASCADE;

• 예제

  Hong이 다른 사용자에게 권한을 부여한 적이 없는 경우에만 Kim이 Hong에게 부여한 고객 테이블에 대한 검색 권한을 취소하는 명령문을 작성해보자.

    REVOKE SELECT ON 고객 FROM Hong RESTRICT;