[리눅스 기초] 데이터베이스 SQL 활용

1. 데이터베이스와 사용자 관리

1-1. 데이터베이스 사용자

데이터베이스는 사용자 계정을 통해 접근을 제어한다. 사용자 계정에는 사용자 이름, 접속을 허용할 호스트, 비밀번호 등이 설정된다.

• 사용자 이름(User): 데이터베이스에 접근하는 계정
• 호스트(Host): 해당 계정의 접속이 허용되는 위치
• 비밀번호(Password): 사용자 인증에 사용하는 정보

MySQL에서는 사용자명@호스트 형태로 계정을 구분할 수 있다. 예를 들어 localhost는 해당 서버 내부에서 접속하는 경우를 의미한다.

데이터베이스는 일반적으로 외부 공인망에 직접 노출하기보다 사설망에 배치하고, 필요한 서버에서만 접근하도록 구성하는 것이 안전하다.

또한 관리자 계정인 root를 일상적인 작업에 사용하는 대신, 필요한 권한만 부여한 일반 계정을 사용하는 것이 바람직하다.

 

2. SQL의 주요 명령어

SQL은 데이터베이스의 구조를 정의하고, 데이터를 조작하며, 접근 권한을 관리하기 위한 언어다.

2-1. DDL(Data Definition Language)

DDL은 데이터베이스와 테이블의 구조를 생성하거나 변경하고 삭제하는 언어다.

• CREATE: 데이터베이스, 테이블, 사용자 등의 생성
• ALTER: 기존 테이블 구조 변경
• DROP: 데이터베이스나 테이블 삭제

테이블에 열을 추가하거나 제거하고, 열의 이름과 데이터 타입을 변경하는 작업도 DDL에 해당한다.

2-2. DML(Data Manipulation Language)

DML은 테이블에 저장된 데이터를 조회하고 추가, 수정, 삭제하는 언어다. 실제 데이터 관리에서 가장 자주 사용하는 SQL 명령어다.

명령어기능

SELECT	데이터 조회
INSERT	데이터 삽입
UPDATE	데이터 수정
DELETE	데이터 삭제

데이터를 조회할 때는 필요한 열을 지정할 수 있으며, 모든 열을 조회하려면 *를 사용할 수 있다.

2-3. DCL(Data Control Language)

DCL은 데이터베이스의 접근 권한을 관리하는 언어다.

• GRANT: 사용자에게 권한 부여
• REVOKE: 사용자에게 부여한 권한 회수

권한은 특정 데이터베이스나 테이블을 대상으로 설정할 수 있다. 모든 사용자에게 모든 권한을 부여하기보다 업무에 필요한 권한만 부여하는 것이 중요하다.

데이터베이스 작업의 큰 흐름은 다음과 같이 이해할 수 있다.

1. DDL로 데이터 구조를 준비한다.
2. DML로 데이터를 조회하고 조작한다.
3. DCL로 사용자의 접근 권한을 관리한다.

단, 실제 작업에서는 사용자 권한을 먼저 설정하거나 중간에 변경하는 등 순서가 달라질 수 있다.

 

3. 데이터 조회와 조건식

3-1. SELECT와 WHERE

SELECT는 테이블의 데이터를 조회하는 명령어다. WHERE 절을 사용하면 특정 조건을 만족하는 데이터만 조회할 수 있다.

조건식은 데이터의 값이나 범위를 비교하는 데 사용한다.

• =: 값이 같은지 비교
• >: 초과 여부 비교
• >=: 이상 여부 비교
• <: 미만 여부 비교
• <=: 이하 여부 비교

조건을 결합하거나 반전할 때는 논리 연산자를 사용한다.

• AND: 모든 조건이 참일 때 참
• OR: 조건 중 하나 이상이 참일 때 참
• NOT: 조건의 참과 거짓을 반전
• LIKE: 문자열의 패턴을 기준으로 검색

LIKE는 특정 문자열을 포함하거나 특정 형태와 일치하는 데이터를 찾을 때 유용하다.

3-2. WHERE 절과 SQL Injection

SQL Injection은 사용자가 입력한 값이 안전하게 처리되지 않아 SQL 구문의 일부로 해석되면서 발생하는 보안 취약점이다.

웹 애플리케이션이 사용자 입력을 SQL 문장에 직접 결합하면 공격자가 조회 조건을 변조하거나 의도하지 않은 SQL 구문을 실행하게 만들 수 있다.

특히 WHERE 절의 조건을 조작하면 원래 의도한 조건과 다른 데이터가 조회될 수 있다. 하지만 SQL Injection은 조회 기능에만 국한되지 않으며, 애플리케이션의 권한과 취약한 쿼리에 따라 데이터 수정이나 삭제 등으로 이어질 수도 있다.

주요 예방 방법

• SQL 쿼리에 사용자 입력을 직접 문자열로 결합하지 않는다.
• 준비된 문장(Prepared Statement)과 매개변수 바인딩을 사용한다.
• 입력값의 형식과 범위를 검증한다.
• 데이터베이스 계정에는 필요한 최소 권한만 부여한다.
• 오류 메시지와 데이터베이스 정보를 외부에 불필요하게 노출하지 않는다.

입력값 검증만으로 SQL Injection을 완전히 방지할 수는 없다. 가장 중요한 기본 원칙은 사용자 입력과 SQL 명령어를 분리하여 처리하는 것이다.

 

4. 데이터베이스 권한 관리

4-1. 권한 부여와 회수

데이터베이스 사용자는 계정이 존재한다는 이유만으로 모든 작업을 수행할 수 있는 것은 아니다. 각 계정에는 허용된 범위의 권한이 부여된다.

예를 들어 특정 사용자가 특정 데이터베이스의 테이블을 조회하거나 수정할 수 있도록 권한을 설정할 수 있다.

• 권한 부여: GRANT
• 권한 회수: REVOKE
• 권한 범위: 데이터베이스 전체 또는 특정 테이블 등

서비스용 계정에는 실제 업무에 필요한 권한만 부여해야 한다. 일반적인 데이터 조회 계정에 불필요한 삭제나 관리자 권한까지 부여하면 계정 탈취 시 피해 범위가 커질 수 있다.

또한 데이터베이스에 직접 접속하여 작업하는 경우뿐 아니라, 웹 애플리케이션의 설정 파일에 저장된 데이터베이스 접속 정보도 안전하게 관리해야 한다. 해당 정보에는 데이터베이스 이름, 사용자 이름, 비밀번호, 포트 등이 포함될 수 있다.

 

5. 데이터베이스 백업과 복원

데이터베이스 백업은 장애, 데이터 손상, 잘못된 수정이나 삭제 등에 대비해 데이터를 별도로 보관하는 작업이다.

백업 방식은 크게 물리적 백업과 논리적 백업으로 나눌 수 있다.

5-1. 물리적 백업

물리적 백업은 데이터베이스의 실제 데이터 파일을 복사하거나 보관하는 방식이다.

• 데이터 파일을 직접 보관한다.
• 데이터 규모와 환경에 따라 빠르게 백업할 수 있다.
• 데이터베이스 버전, 운영체제, 파일 형식 및 설정 등 환경의 영향을 받을 수 있다.
• 복원할 환경과 파일의 호환성을 확인해야 한다.

MySQL의 데이터 디렉터리는 일반적으로 /var/lib/mysql이지만, 실제 위치는 설정에 따라 달라질 수 있다.

실행 중인 데이터베이스의 파일을 단순 복사하는 것만으로는 일관성이 보장되지 않을 수 있다. 따라서 데이터베이스를 정지하거나 데이터베이스가 제공하는 일관성 있는 백업 절차를 사용해야 한다.

5-2. 논리적 백업

논리적 백업은 데이터베이스의 구조와 데이터를 SQL 문장 등의 논리적 형태로 내보내는 방식이다.

MySQL에서는 mysqldump를 사용해 데이터베이스를 파일로 내보낼 수 있다.

• 텍스트 파일 형태로 저장할 수 있다.
• 데이터베이스 구조와 데이터를 다른 환경으로 옮기는 데 유용하다.
• 복원할 때 SQL 문장을 다시 실행하는 과정이 필요할 수 있다.
• 데이터 규모에 따라 백업과 복원에 시간이 오래 걸릴 수 있다.

논리적 백업은 일반적으로 데이터베이스의 실제 데이터와 테이블 구조를 복원하는 데 활용하지만, 서버의 모든 설정과 계정 환경까지 자동으로 완전히 복원해 주는 것은 아니다.

5-3. 백업 복원과 검증

백업 파일을 보관하는 것만으로는 충분하지 않다. 실제로 복원할 수 있는지 확인해야 한다.

일반적인 논리적 백업과 복원 과정은 다음과 같다.

1. 복원할 데이터베이스 환경을 준비한다.
2. 백업 파일을 이용해 데이터베이스와 테이블 구조 및 데이터를 복원한다.
3. 테이블과 데이터가 정상적으로 복구되었는지 확인한다.
4. 필요한 설정과 사용자 권한을 점검한다.

백업 파일은 원본 데이터베이스와 분리된 위치에도 보관하는 것이 좋다. 원본 서버에 장애가 발생하더라도 백업을 이용할 수 있도록 하기 위해서다.

 

6. SQL Injection 실습에서 알아야 할 보안 개념

SQL Injection을 이해하려면 웹 애플리케이션, 데이터베이스, 사용자 입력의 관계를 알아야 한다.

웹 애플리케이션은 사용자가 입력한 정보를 받아 데이터베이스에 전달한다. 이때 입력값이 SQL 구문의 일부로 해석되면 공격자가 의도한 방식으로 쿼리의 의미가 바뀔 수 있다.

 

SQL Injection은 입력값 검증을 하지 않아서 발생하는 보안 취약점으로, DB와 웹 응용애플리케이션에서 입력된 데이터에 대한 유효성 검증을 하지 않을 경우, 공격자가 입력 폼이나 URL 입력란에 SQL 구문을 삽입하여 DB로부터 정보를 열람하거나 조작할 수 있는 보안 약점을 의미한다.

 

7. CI/CD와 데이터베이스 운영

CI/CD는 애플리케이션 개발부터 배포까지의 과정을 자동화하는 데 활용되는 개념이다.

• CI(Continuous Integration): 코드 변경 사항을 지속적으로 통합하고 빌드 및 테스트를 수행한다.
• CD(Continuous Delivery/Deployment): 검증된 애플리케이션을 배포 가능한 상태로 준비하거나 실제 환경에 배포한다.

데이터베이스 구조 변경도 애플리케이션 배포 과정에서 함께 관리할 수 있다. 다만 데이터베이스 변경은 기존 데이터와 서비스에 영향을 줄 수 있으므로, 변경 사항 검토와 백업, 테스트 및 복구 계획을 고려해야 한다.

 

핵심 요약

• DDL은 데이터베이스 구조를 정의하고 변경하며 삭제하는 언어다.
• DML은 데이터를 조회·삽입·수정·삭제하는 언어로, SELECT, INSERT, UPDATE, DELETE가 핵심이다.
• DCL은 데이터베이스 접근 권한을 관리하며, GRANT와 REVOKE를 사용한다.
• WHERE 절은 조건에 맞는 데이터를 조회하는 데 사용하며, 비교 연산자와 논리 연산자를 활용한다.
• SQL Injection은 사용자 입력이 SQL 구문으로 해석되어 발생하는 취약점이다. 매개변수 바인딩과 최소 권한 적용이 중요하다.
• 데이터베이스 계정은 사용자와 접속 호스트에 따라 구분할 수 있으며, 관리자 계정의 사용과 외부 접근을 제한해야 한다.
• 물리적 백업은 실제 데이터 파일을 보관하고, 논리적 백업은 데이터 구조와 내용을 SQL 등의 형태로 내보낸다.
• 백업 복원 테스트를 수행해야 실제 장애 상황에서 데이터를 복구할 수 있다.
• CI/CD는 개발과 배포를 자동화하며, 데이터베이스 구조 변경도 안전한 검증 및 복구 절차와 함께 관리해야 한다.