Post

Table의 데이터 보안

Posted Updated
By Blake Woo 8 min read

데이터 보안관련 SQL

한 개의 데이터 베이스를 여러개의 서비스가 사용할 수 있다.
그런데 이런 경우 각각의 계정마다 특정 테이블만 엑세스할 수 있도록 제한을 걸지 않는다면
다른 서비스가 사용하고 있는 테이블을 침범해 데이터 무결성을 깨뜨릴 수도 있다. 그리고 특히 유지보수나 여타 다른 목적으로 DB의 열람 기능만으로도 업무를 처리할 수 있는데 열람이 아닌 수정이나 삭제 권한까지 준다면 이는 치명적인 취약점이 될 수있다. 따라서 각 계정마다 테이블에 대한 권한을 지정해서 관리해야한다.

권한 부여

테이블 자체에 대한 권한

테이블 생성, 스키마 변경, 테이블 삭제에 대한 권한을 지정할 수 있다.

다음과 같은 명령어로 다른 스키마에 대해 테이블 삭제, 테이블 변경, 테이블 삭제에 대한 권한을 부여할 수 있다.

1
2
3

GRANT CREATE ANY TABLE TO user;
GRANT ALTER ANY TABLE TO user;
GRANT DROP ANY TABLE TO user;

다른 스키마 인덱스 생성, 수정 제거 권한은 다음과 같은 명령어를 사용한다.

1
2
3

GRANT CREATE ANY INDEX TO user;
GRANT ALTER ANY INDEX TO user;
GRANT DROP ANY INDEX TO user;

테이블과 인덱스와는 별개로 테이블에 엑세스할 수 있는 권한은 따로 있다.

1
2

GRANT CREATE SESSION TO user;
-- user에 엑세스 권한 부여

이러한 엑세스 권한이 없다면 엑세스 자체가 안된다.

위의 권한들은 아래와 같은 명령어로 권한을 회수 할 수 있다.

1
2
3
4
5
6
7

REVOKE CREATE SESSION FROM user;
REVOKE CREATE TABLE FROM user;
REVOKE DROP ANY TABLE FROM user;
REVOKE CREATE ANY TABLE FROM user;
REVOKE CREATE ANY INDEX FROM user;
REVOKE ALTER ANY INDEX TO user;
REVOKE DROP ANY INDEX FROM user;

테이블 레코드에 대한 권한

테이블 레코드에 대한 CRUD 권한을 부여한다. GRANT 명령어를 사용하며 아래와 같은 형태로 사용한다.

1
2

GRANT priviliges ON table TO user;
-- priviliges, table, user는 각각 설정값

table은 대상 테이블, user는 대상 계정이름이며 다수에 대해서 지정하고 싶을 경우 콤마로 구분하여 입력하면 된다. priviliges에 들어갈 권한은 아래와 같다.

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • ALL : CRUD 전체에 대한 권한

다음의 권한을 회수하려면 아래의 명령어를 입력한다.

1
2

REVOKE priviliges ON table TO user;
-- priviliges, table, user는 각각 설정값

VIEW

한마디로 Virtual Table이다. 별도로 Table이 생성되는 것은 아니고 VIEW는 지정된 대로 SELECT 구문을 실행시켜서 가상의 TABLE을 만들어주는 것이다.

아래의 형태로 생성할 수 있다.

1
2
3
4
5

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 지정하고자하는_뷰이름
  [(column_aliases)]
AS
  SELECT 문
  [WITH READ ONLY]
  • OR REPLACE : 뷰가 없으면 생성, 있으면 수정 된다.
  • FORCE : 쿼리의 테이블, 칼럼, 함수등이 존재하지 않아도 생성이 가능하다.
  • NORORCE : 뷰를 생성할 때 칼럼이나 함수 같은 구성요소가 존재하지 않으면 생성되지 않는다.
  • column_aliases : SELECT 칼럼의 별칭을 미리 정의할 수 있다.
  • WITH READ ONLY : SELECT만 가능하게 제한을 걸 수 있다.

VIEW TABLE은 단순 뷰와 복합 뷰로 나뉜다.

종류

단순 뷰

단일 테이블에 대해 필요한 컬럼으로 생성한 view를 말한다.
다시 말해서 JOIN이나 FUNCTION, GROUP BY, UNION 등을 사용하지 않는다. 단순 뷰는 일반적인 TABLE과 같이 CRUD가 모두 가능하다.

생성 예시는 아래와 같다.

1
2
3
4
5
6

CREATE VIEW cid30Student(ID, Name, Grade)
AS
SELECT sid, sname, grade 
FROM student 
WHERE cid = 30;
-- cid가 30인 student의 sid, sname, grade로 view를 생성

만약에 해당 뷰를 수정하고 싶다면 중간에 OR REPLACE 구문을 추가하면 VIEW TABLE의 DROP 없이 수정이 가능하다.

1
2
3
4
5
6

CREATE OR REPLACE VIEW cid30Student(ID, Name, Grade)
AS
SELECT sid, sname, grade 
FROM student 
WHERE cid = 30;
-- cid가 30인 student의 sid, sname, grade로 view를 생성하되 있으면 해당 쿼리대로 수정

만든 뷰를 삭제하고 싶다면 아래의 명령어로 삭제한다.

1

DROP VIEW cid30Student;

복합 뷰

복합 뷰는 단순 뷰와 다르게 JOIN이나 FUNCTION, GROUP BY, UNION 등을 사용해서 만든 VIEW로 INSERT, UPDATE, DELETE가 불가능하다. 단, 조인만 복합 뷰인 경우에는 제한적으로 사용이 가능할 수 있다.

아래는 복합 뷰의 생성 예시이다.

1
2
3
4
5

CREATE OR REPLACE VIEW view_student_circle
AS 
    SELECT s.sid, s.sname, s.grade, c.name
    FROM student s, circle c 
    where s.cid = c.circle_id;

장단점

장점

  • 데이터베이스 엑세스 제한하는데 용이하다.
  • 쿼리를 단순하게 해준다.
  • 어플리케이션을 위한 데이터 독립성을 지원한다. 즉 동일한 데이터를 쓰는 다른 어플리케이션을 위해서 별도의 테이블을 구성하지 않아도 된다.

단점

  • INDEX를 이용할 수 없다.

참고자료

Database
Computer science Database
This post is licensed under CC BY 4.0 by the author.