이번 포스팅에서는 SQL Server에 Linux를 설치하고 Window에 SQL Server를 설치하는 것에 비해 Linux가 가진 장점에 대해 심도 깊게 알아보는 과정입니다.
SQL Server 설치 및 Management Stduio로 기본 작업 수행
싱글 SQL Server 만들기
새로운 리소스 그룹을 만들고 가상 네트워크도 만듭니다.
서브넷도 추가해줍니다.
SQL Server on Linux용 가상 머신을 생성합니다.
이미지는 Ubuntu Server 22.04 LTS, 크기는 D2s_v3를 선택해 줍니다.
생성이 완료되면 네트워크 설정 메뉴에서 네트워크 보안 그룹으로 이동 후 포트 규칙 만들기를 클릭합니다. 이후 MSSQL 기본 포트인 1433을 추가합니다.
생성한 VM을 Putty 프로그램과 연결해 줍니다.
이제 생성한 VM에 SQL Server를 설치합니다.
이 내용은 앞서 포스팅한 내용을 참고해서 그대로 따라하면 됩니다.
SQL Server가 설치되었다면 추가적으로 관련 툴을 더 설치해주겠습니다. 리눅스 서버 안에서 직접 쿼리를 날리거나 데이터를 관리하려면 이 도구들이 반드시 필요합니다.
아래 명령어로 리포지토리를 추가로 등록해 줍니다.
curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc
curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
이후 sudo apt-get update 을 수행해 업데이트 후 sudo apt-get install mssql-tools18 unixodbc-dev 명령어로 unixODBC 개발자 패키지를 설치합니다.
mssql-tools18 패키지에는 가장 중요한 두 가지 프로그램이 들어 있습니다. sqlcmd (SQL 명령줄 유틸리티)는 윈도우의 SSMS(SQL Server Management Studio)를 아주 가볍게 줄여서 터미널(검은 화면)로 옮겨놓은 것이라고 보시면 됩니다. bcp (대량 복사 유틸리티)은 수만, 수백만 건의 대용량 데이터를 파일에서 DB로 넣거나, 반대로 뽑아낼 때 사용하는 '데이터 고속도로'입니다.
unixodbc-dev는 리눅스 운영체제와 SQL Server가 서로 대화할 수 있게 해주는 표준 연결 다리(ODBC 드라이버)의 개발용 패키지입니다.
설치 과정에서 아래와 같이 라이선스 조항에 동의하냐는 질문이 올라오면 Yes 버튼을 선택하여 Enter를 입력해 줍니다.
Bash 셸에서 /opt/mssql-tools18/bin/ 환경 변수에 PATH를 추가합니다.
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bash_profile
source ~/.bash_profile
대화형/비로그인 세션을 위해 bash 셸에서 sqlcmd 및 bcp에 액세스할 수 있도록 설정하려면 다음 명령을 사용하여 PATH 파일에서 ~/.bashrc를 수정합니다.
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc
SQL Server가 Linux 상에서 정상적으로 수행되고 있는지 확인하기 위해 몇 가지 기본적인 작업들을 Command 상에서 수행해 봅니다.
아래 화면과 같이 sqlcmd -S localhost -U SA -P ‘비밀번호’ -C 명령어로 SQL Server on Linux에 접속할 수 있습니다.
CREATE Database 구문을 통해 testdb를 한 번 만들어 봅시다.
GO 쿼리로 testdb를 사용해보도록 Context를 변경해 줍니다.
또한 해당 Databaes에 테이블을 생성한 뒤 만들어진 Table에 샘플로 데이터를 몇 개 INSERT 수행해 봅니다.
MDF, LDF 파일로 데이터베이스 Attach 하기
다음으로 MDF, LDF 파일로 데이터베이스 Attach를 해보는 과정입니다.
우리가 SQL Server에서 테이블을 만들고 데이터를 넣으면, 그 모든 내용은 MDF, LDF 파일에 나누어 저장됩니다.
MDF (Master Data File / Primary Data File)는 우리가 만든 테이블의 구조(Schema), 실제 데이터 값, 인덱스, 뷰, 저장 프로시저 등이 들어있는 본체입니다.
LDF (Log Data File / Transaction Log File)는 데이터베이스에서 일어나는 모든 변경 사항(추가, 수정, 삭제 등)을 시간 순서대로 기록한 파일입니다.
MDF, LDF 파일을 Attach 하기 위해 미리 준비된 mdf, ldf 파일을 특정 위치에 저장시켜 봅니다. SQL Server on Linux는 보통 /var/opt/mssql/data 경로의 하위에 mdf 파일 및 ldf 파일을 보관해 놓고 있습니다. 다른 별도의 경로에 데이터 파일을 저장해도 정상적으로 Attach가 되는 것은 맞으나 이번에는 /var/opt/mssql/data 경로에다가 파일을 저장해 놓고 Attach 하는 작업을 수행해 봅니다.
우선 사용할 MDF, LDF 파일을 파일질라와 같은 FTP 프로그램을 이용해 업로드 합니다.
이후 /var/opt/mssql/data 경로에 복사합니다.
복사는 정상적으로 수행 되었으나 다른 mdf, ldf 파일과 다운로드 받은 파일이 약간 다른 것을 확인할 수 있습니다. 이는 방금 전 Root 계정으로 복사했기 때문에 소유자 및 소유 그룹이 모두 root로 되어있는 것을 확인할 수 있습니다. 여기서 mssql에게 해당 파일들의 소유권을 넘겨주고 mssql에서 지정한 방식으로 사용하기 위해서는 해당 파일들의 소유권을 mssql로 넘겨 주어야 합니다.
이를 위해서는 아래와 같은 명령어로 chown 명령어를 수행하여 소유 그룹과 소유자를 모두 mssql로 바꿔줍니다.
이제 Attach 할 파일을 특정 경로에 준비해 두었으므로, SSMS 상에서 attach 작업을 수행해 줍니다. Attach는 T-SQL 쿼리로 직접 Attach 하거나 UI 상에서 마법사(Wizard)를 통해 Attach 해줄 수 있습니다. 먼저 UI 상에서 작업하는 것을 수행해 봅니다.
Object Explorer(객체 탐색기)에서 Database를 선택하고 오른쪽 마우스 클릭하여 Attach를 클릭합니다.
그러면 아래와 같이 데이터베이스 연결 창이 나타나게 되고, 여기서 추가 버튼을 클릭해 줍니다.
Add 버튼을 클릭하면 아래와 같이 /var/opt/mssql 경로를 기본적으로 찾게 됩니다.여기서 전 단계에서 다운로드 받아 두었던 mdf, ldf 파일이 저장되어 있는 위치 data 디렉토리를 더블 클릭하여 /var/opt/mssql/data 경로로 이동해 줍니다. 저장해 두었던 FabrikamFiber.mdf 파일을 선택하고 확인 버튼을 클릭해 줍니다.
그러면 아래와 같이 두 개의 파일(로그 파일과 데이터 파일)이 나타나는 것을 확인할 수 있으며, 이를 확인 버튼을 클릭하여 Attach 시켜 줍니다.
이제 Object Explorer에는 FABRIKAM으로 시작하는 Database 파일이 하나 추가 된 것을 확인할 수 있습니다.
DB 백업 파일로 복원하기
이번에는 대표적인 샘플 데이터베이스 중 하나인 AdventureWorks2014를 다운로드 받아서 복원해 보는 작업을 진행해보겠습니다.
백업파일 저장 경로를 생성 후 AdventureWorks2014 파일을 wget 명령어로 다운로드 받습니다.
cd /var/opt/mssql
mkdir backup
cd backup
wget https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2014.bak
마찬가지로 chown mssql:mssql AdventureWorks2014.bak 파일의 소유권을 mssql에 넘겨 줍니다.
이제 앞서 생성한 디렉토리의 권한 또한 mssql로 변경해 주기 위한 작업을 진행합니다. cd .. 우선 명령어로 윗 단계 디렉토리로 돌아가 줍니다.
chown mssql:mssql backup 명령어를 통해 backup 디렉토리의 소유권 또한 mssql로 변경하여 줍니다.
이번에는 SSMS 상에서 T-SQL 구문으로 복원 작업을 수행해 봅니다. 먼저 Windows Server가 설치된 머신에 접속하여 SSMS를 엽니다. 기존에 열려있었다면 그대로 수행하고, 혹시 다시 열어야 한다면 앞 단계의 설명을 확인할 수 있습니다. SSMS가 열렸으면 아래 화면과 같이 New Query 버튼을 클릭합니다.
쿼리 창에 Restore Database 스크립트를 수행해 줍니다.
USE master
GO
RESTORE DATABASE AdventureWorks
FROM DISK = '/var/opt/mssql/backup/AdventureWorks2014.bak'
WITH MOVE 'AdventureWorks2014_Data' TO '/var/opt/mssql/data/AdventureWorks2014_Data.mdf',
MOVE 'AdventureWorks2014_Log' TO '/var/opt/mssql/data/AdventureWorks2014_Log.ldf'
GO
아래 화면과 같이 방금 복원한 데이터베이스가 AdventureWorks라는 이름으로 정상 복원되었음을 확인할 수 있습니다.
보안 기능
로그인과 DB 사용자 생성
SQL Server에서의 로그인은 SQL Server에 연결할 수 있으며, master database에 제한적인 권한을 가지면서 접근할 수 있는 객체입니다. 사용자 DB에 접근하기 위해서는 로그인에 추가로 데이터베이스 유저라고 하는 데이터베이스 수준의 식별 객체가 필요하게 됩니다. 이는 로그인과 나란히 아이덴티티를 맞춰줄 수 있습니다. 로그인과 유저는 각각 다른 객체라고 볼 수 있습니다. 로그인이 서버 수준의 객체라면, 유저는 DB 수준의 객체입니다.
사용자들은 각각의 데이터베이스에 특화되어 각각의 데이터베이스 자체에서 생성되어야 하고, 그들에게 권한을 줘야 합니다. 앞서 만든 AdventureWork2014 데이터베이스를 활용하여 Create Login과 Create User 구문으로 로그인과 유저를 생성해봅시다.
User 이름이 Larry이면서 동시에 로그인 이름도 Larry인 것과 매핑합니다.
SSMS에 접속하여 쿼리에서 선택된 DB를 master DB로 설정하고 아래와 같은 쿼리를 실행해 로그인 객체를 만들어줍니다.
CREATE LOGIN Larry WITH PASSWORD = 'Pass@Word123';
다음으로 사용자 DB인 AdventureWorks에서 Larry라는 유저 객체를 추가로 만들어줍니다.
USE AdventureWorks;
GO
CREATE USER Larry;
GO
객체 탐색기의 AdventureWorks 데이터베이스를 선택해서 보안 > 사용자를 차례로 들어가게 되면 방금 생성한 유저인 Larry가 들어있는 것을 확인할 수 있고 master 테이블의 보안 > 로그인을 통해 들어가면 Larry를 동일하게 확인할 수 있습니다.
master 데이터베이스는 어떤 데이터베이스가 있는지, 서버 설정은 무엇인지, 그리고 어떤 로그인들이 있는지에 대한 정보를 모두 이곳에 저장합니다.
SQL Server 관리자 계정은 어떤 DB라도 접근 가능하며, 더 많은 로그인 및 각각의 DB에 맞는 사용자들을 만들어 줄 수 있습니다. 누군가 DB를 만든 사람은 DB 소유자(Owner)가 되며, 해당 DB에 연결할 수 있으며, DB 소유자(Owner)는 더 많은 유저들을 추가할 수 있습니다.
권한 부여
나중에 다른 로그인들에게 ALTER ANY LOGIN 권한을 그들에게 줌으로써 더 많은 로그인을 생성할 수 있도록 권한을 줄 수 있습니다.
아래의 SQL 문을 실행하면 Larry는 이제 SQL Server 인스턴스 전체에서 새로운 로그인을 생성, 수정, 삭제할 수 있는 강력한 권한을 갖게 됩니다. Larry는 본인의 계정으로 로그인한 뒤 CREATE LOGIN, ALTER LOGIN, DROP LOGIN 등의 명령어를 실행할 수 있습니다.
USE master
GRANT ALTER ANY LOGIN TO Larry;
GO
DB 내에서는 ALTER ANY USER 권한을 줌으로써 다른 유저들에게 더 많은 유저들을 생성할 수 있는 권한을 줄 수 있습니다.
USE AdventureWorks
GO
GRANT ALTER ANY USER TO Larry;
GO
방금 생성한 첫 번째 사람은 DB 소유자 계정이며 관리자 역할을 수행하게 될 예정입니다. 그러나 이러한 유저는 데이터베이스의 모든 권한을 가지고 있습니다. 따라서 해당 사용자가 가져야 하는 권한 보다 더 많은 권한을 불필요하게 가질 수 있습니다. 이런 경우 내장된 고정 DB 역할을 사용해서 몇몇 권한을 일반적인 카테고리에 따라 할당할 수 있습니다. 예를 들어 db_datareader는 데이터베이스의 모든 테이블들을 읽을 수 있으나 변경은 할 수 없는 역할이 있습니다.
이제 ALTER ROLE 구문을 활용해서 멤버쉽을 부여해 볼 예정입니다. 아래 구문을 통해 Jerry라는 로그인과 유저를 만듭니다.
CREATE LOGIN Jerry WITH PASSWORD = 'Pass@word123';
USE AdventureWorks
GO
CREATE USER Jerry
GO
SQL Server Audit
SQL Server Audit(감사) 기능은 DB 엔진에 발생한 이벤트에 대한 추적과 기록을 남길 수 있게 해주는 기능입니다. 감사는데이터베이스에서 무슨 일이 일어나는지, 누가, 언제, 무엇을 했는지 기록하는 '블랙박스'와 같습니다. 데이터베이스는 기업의 핵심 정보를 담고 있기 때문에, 누가 어떤 데이터를 조회하거나 변경했는지 추적하는 것은 보안과 규정 준수를 위해 매우 중요합니다. SQL Server 감사가 바로 이 역할을 수행합니다.
예를 들어, '민감한 고객 정보를 담고 있는 테이블에 누군가 접근했는지', ' 권한이 없는 사용자가 로그인하려고 시도했는지'와 같은 이벤트를 기록할 수 있습니다. 이번 실습에서는 감사를 정하여 어떻게 감사가 수행되는지 확인할 수 있습니다. 한 번 감사 기능이 만들어지고 활성화 되면 대상은 기입됩니다.
우선 SSH로 SQL Server on Linux 머신에 접속하여 sudo su 명령어로 root 계정으로 전환해 줍니다. 그리고 mkdir Samples 명령어로 디렉토리를 하나 만들고 이것의 소유권 또한 chown -R mssql:mssql Samples/ 명령어로 변경해 줍니다.
그리고 다시 SSMS 상에서 해당 서버에 연결하여 master DB를 사용해 놓도록 한 상태에서 아래와 같이 서버 감사(Server Audit) 객체를 생성하는 쿼리를 수행합니다.
FILEPATH = '\var\opt\mssql\Samples'에 감사 결과 로그파일을 저장합니다.
WITH 절 안에 있는 구문은 감사가 실행될 때의 세부적인 메커니즘을 설정합니다.
USE [master]
CREATE SERVER AUDIT [TestAudit]
TO FILE (
FILEPATH = '\var\opt\mssql\Samples'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = 'e53eb508-875d-495b-a3cb-15af580e7e3b'
)
그리고 아래 쿼리를 연속해서 수행하여 감사를 활성화 시켜줍니다.
ALTER SERVER AUDIT [TestAudit] WITH (STATE = ON)
그리고 연속해서 아래 구문을 수행해 줍니다.
TestServerAuditSpecification이라는 이름의 서버 감사 사양을 생성합니다. 이것은 서버 수준에서 발생하는 행동들을 정의하는 규칙 세트입니다. 이 규칙을 통해 수집된 데이터를 이전에 만든 TestAudit이라는 저장소에 보내겠다는 연결 고리입니다.
SCHEMA_OBJECT_ACCESS_GROUP을 추가함으로써 누군가 데이터베이스 객체(테이블, 뷰, 함수 등)에 접근하여 SELECT, INSERT, UPDATE, DELETE 등의 작업을 수행할 때 그 내역을 기록하라는 명령입니다.
CREATE SERVER AUDIT SPECIFICATION [TestServerAuditSpecification]
FOR SERVER AUDIT [TestAudit]
ADD (SCHEMA_OBJECT_ACCESS_GROUP)
WITH (STATE = ON)
활성화 상태를 한 번 조회합니다.
SELECT name, is_state_enabled FROM sys.server_audits WHERE name = 'TestAudit';
켜져있다면 아래의 명령어를 실행합니다.
-- 1. master DB로 이동
USE [master];
GO
-- 2. 시스템 테이블 조회 (조회 이벤트 발생)
SELECT TOP 10 * FROM sys.objects;
GO
-- 3. 테스트용 임시 테이블 생성 및 조회 (스키마 객체 접근 이벤트 발생)
CREATE TABLE AuditTestTable (ID INT, Name NVARCHAR(50));
INSERT INTO AuditTestTable VALUES (1, 'Test');
SELECT * FROM AuditTestTable;
DROP TABLE AuditTestTable;
GO
이제 만들어진 감사 동작을 확인해 봅니다. 아래 쿼리문을 수행하여 감사 이벤트를 읽어봅니다.
--감사 로그 확인
SELECT * FROM fn_get_audit_file('/var/opt/mssql/Samples/TestAudit*.sqlaudit', null, null);
자주 사용하는 주요 컬럼은 다음과 같습니다.
| 컬럼명 | 설명 |
| event_time | 감사 이벤트가 발생한 시간 |
| action_id | 수행된 작업의 ID (예: SL = 로그인 성공, AL = 로그인 실패) |
| succeeded | 작업 성공 여부 (1 = 성공, 0 = 실패) |
| session_server_principal_name | 세션의 사용자 이름 (sa, dbo 등) |
| server_principal_name | 서버 수준 사용자 이름 |
| database_name | 작업이 수행된 데이터베이스 이름 |
| statement | 수행된 SQL 문장 (가능한 경우) |
| client_ip | 접속한 클라이언트 IP 주소 |
| duration_milliseconds | 작업에 걸린 시간 (있다면) |
Row-Level Security
RLS는 쿼리를 실행하는 사용자가 누구냐에 따라 행 단위 접근을 제어하는 기술입니다. 이 기능은 사용자가 자신의 데이터만 접근하게 할 때 유용한 방법입니다. LS는 말 그대로 쿼리를 실행하는 사용자가 누구냐에 따라 데이터의 특정 ' 행(Row)'에 대한 접근을 제어하는 기술입니다.
이 기능은 여러 사용자가 같은 테이블을 공유하지만, 각 사용자는 자신의 데이터만 보거나 수정할 수 있어야 할 때 매우 유용합니다. 마치 회사의 인사 관리 시스템에서 직원 A는 자신의 급여 정보만 볼 수 있고, 직원 B는 자신의 정보만 볼 수 있게 만드는 것과 같습니다.
RLS는 다음과 같은 원리로 작동합니다.
- 보안 정책 생성: 먼저, 어떤 조건에 따라 데이터 접근을 제어할지 정의하는 보안 정책(Security Policy)을 만듭니다.
- 함수 기반 필터링: 이 정책에는 사용자의 로그인 이름이나 역할 등을 확인하는 함수(Function)가 포함됩니다. 이 함수가 참(True)을 반환하는 행만 사용자에게 보여주거나, 수정할 수 있도록 허용합니다.
- 투명한 제어: 가장 큰 장점은 이 모든 과정이 사용자에게 투명하게(Transparently) 적용된다는 점입니다. 사용자는 별도의 조건을 쿼리에 추가할 필요 없이, 평소와 같이 쿼리를 실행해도 RLS가 자동으로 불필요한 데이터를 필터링해 줍니다.
두 명의 유저를 만들어서 다른 행 수준 접근을 Sales.SalesOrderHeader 테이블에서 보여줄 예정입니다.
우선 두 명의 User를 생성합니다.
USE AdventureWorks
GO
CREATE USER Manager WITHOUT LOGIN;
CREATE USER SalesPerson280 WITHOUT LOGIN;
각각에게 Sales.SalesOrderHeader를 읽을 수 있도록 합니다.
GRANT SELECT ON Sales.SalesOrderHeader TO Manager;
GRANT SELECT ON Sales.SalesOrderHeader TO SalesPerson280;
새로운 Security 스키마를 하나 만들고, 인라인 table-value 함수를 생성합니다.
이 함수는 SalesPersonID 열이 SalesPerson 로그인한 ID와 매칭되거나 - 'SalesPerson' + CAST(@SalesPersonID as VARCHAR(16)) = USER_NAME()
또는 쿼리를 수행하는 유저가 매니저 유저이면 1을 반환해주는 함수입니다. - OR (USER_NAME() = 'Manager')
--Security 라는 스키마 생성
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_securitypredicate(@SalesPersonID AS int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE ('SalesPerson' + CAST(@SalesPersonID as VARCHAR(16)) = USER_NAME())
OR (USER_NAME() = 'Manager');
만든 함수를 적용하는 Policy를 생성합니다.
먼저 SalesFilter라는 이름의 보안 정책 객체를 생성합니다.
: 사용자가 Sales.SalesOrderHeader 테이블을 조회할 때, SQL Server가 자동으로 fn_securitypredicate 함수를 호출합니다. 함수 결과가 1인 행만 사용자에게 보여주고, 그렇지 않은 행은 존재하지 않는 것처럼 숨깁니다.
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesPersonID)
ON Sales.SalesOrderHeader,
ADD BLOCK PREDICATE Security.fn_securitypredicate(SalesPersonID)
ON Sales.SalesOrderHeader
WITH (STATE = ON);
아래 쿼리를 통해 SalesPerson280으로 수행해 봅니다.
결과에 SalesPersonID가 280인 부분들만 불러와진 것을 볼 수 있습니다.
EXECUTE AS USER 명령어를 사용하면 현재 세션의 보안 문맥(Security Context)이 지정된 사용자로 변경됩니다. 이때 REVERT는 이 변경된 문맥을 직전의 상태로 되돌리는 기능을 수행합니다. SalesPerson280의 권한으로 수행하던 작업을 마치고, 다시 본래의 관리자(sa 또는 dbo) 권한으로 복귀합니다.
아래 쿼리를 통해 Manager로 수행해 봅니다.
결과에 SalesPersonID가 다양하게 불러와진 것을 볼 수 있습니다.
이제 만들어진 규칙이 만약 필요없게 되면 Disable 시켜놓을 수 있습니다. 아래 구문의 쿼리를 수행하게 되면 규칙이 비활성화 됩니다.
ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);
Dynamic Data Masking
Dynamic Data Masking (동적 데이터 마스킹) 기능은 사용자의 민감한 데이터에 대해서 특정 열에 대해서 부분 마스킹 또는 전체 마스킹을 걸어 제한적으로 노출 시킬 수 있는 기능입니다. 데이터베이스에 저장된 민감한 정보(예: 주민등록번호, 이메일 주소, 신용카드 번호)를 권한이 없는 사용자에게는 부분적으로 또는 완전히 가릴 수 있습니다.
이 기능의 가장 큰 장점은 실제 데이터는 그대로 유지한 채, 쿼리를 실행하는 시점에 실시간으로 데이터를 마스킹하여 보여준다는 것입니다. 따라서 개발자나 일반 사용자는 데이터에 접근하더라도 민감한 정보는 볼 수 없게 되어 정보 유출의 위험을 크게 줄일 수 있습니다.
DDM은 ALTER TABLE 구문을 사용하여 특정 열(Column)에 마스킹 규칙을 적용하는 방식으로 작동합니다. SQL Server는 다양한 내장 마스킹 함수를 제공합니다.
Default: 전체 값을 마스킹합니다. (문자열은 XXXX, 숫자는 0)
Email: 이메일 주소의 첫 글자와 도메인 부분을 제외하고 마스킹합니다. (예: aXXX@XXXX.com)
Partial: 지정한 만큼의 글자만 남기고 나머지를 마스킹합니다. (예: 123-XXXX-XXXX)
Random: 지정한 범위 내의 임의의 숫자로 마스킹합니다.
이 기능을 사용하기 위해서는 ALTER TABLE 구문을 활용하여 Person.EmailAddress 테이블에 EmailAddress 열에 Masking 함수를 생성해 볼 예정입니다. 아래 Dynamic Data Masking 쪽 쿼리를 수행시킵니다.
USE AdventureWorks
GO
ALTER TABLE Person.EmailAddress
ALTER COLUMN EmailAddress
ADD MASKED WITH (FUNCTION = 'email()');
이제는 아래와 같이 유저를 하나 추가하여 그 유저로 조회를 수행합니다.
-- 로그인 없는 TestUser 계정을 하나 만들어서 해당 유저에게 SELECT 권한 부여
CREATE USER TestUser WITHOUT LOGIN;
GRANT SELECT ON Person.EmailAddress TO TestUser;
-- 조회 수행
EXECUTE AS USER = 'TestUser';
SELECT EmailAddressID, EmailAddress FROM Person.EmailAddress;
REVERT;
아래와 같이 마스킹 된 이메일 데이터가 보여지는 것을 확인할 수 있습니다.
Transparent Data Encryption
데이터베이스 관점에서 위험할 수 있는 것은 누군가 정보를 훔쳐가려는 사람이 하드 드라이브에서 직접 DB 파일을 훔쳐갈 수 있다는 점입니다. 내부 직원이나 외부 침입자나 데이터베이스 파일(.mdf 혹은 .ldf)을 물리적으로 가져갈 수 있는 상황이 발생할 수 있습니다.
TDE는 이러한 상황에서 데이터가 해독되지 않도록 막아주는 최후의 보루 역할을 합니다.
TDE의 작동 원리 TDE는 다음과 같은 원리로 작동합니다.
- 파일 암호화: TDE를 활성화하면, SQL Server는 데이터베이스 파일(.mdf), 로그 파일(.ldf)을 암호화하여 하드 드라이브에 저장합니다.
- 마스터 키 관리: 암호화에 사용되는 대칭 키(Symmetric Key)는 마스터 데이터베이스(master DB)에 저장됩니다. SQL Server 엔진은 이 키를 사용하여 데이터를 암호화하고 복호화할 수 있습니다.
마스터 키 없이는 데이터베이스 파일이 읽히거나 복구될 수 없습니다. 즉, 누군가 파일만 훔쳐가더라도 암호화 키가 없으면 그 파일은 의미 없는 데이터 덩어리에 불과합니다.
TDE 구성은 아래 순서와 같습니다.
A. 마스터 키 생성
B. 마스터 키에 의해 보호되는 인증서 생성 또는 인증서 지정
C. 인증서에 의해 보호되는 DB 암호화 키 생성
D. DB를 암호화를 사용하도록 지정
TDE 쿼리를 찾아, 앞서 만들었던 testdb에 이 작업을 진행해 볼 예정입니다. 우선 마스터 DB를 활용하여 마스터 키와 인증서를 각각 만들어 줍니다.
USE master;
GO
-- 마스터 키 생성
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@word123';
GO
-- 인증서 생성
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My Database Encryption Key Certificate';
GO
이제 DB를 지정하여 DB 암호화키를 만들고 이 DB에 TDE를 적용시켜 봅니다.
-- DB 지정
USE testdb;
GO
-- DB 암호화 키 생성
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
-- DB에 TDE 적용
ALTER DATABASE testdb
SET ENCRYPTION ON;
인증서와 암호화키를 다른 곳에 백업해 두라는 경고 메시지가 나타납니다.
TDE를 비활성화 시켜 놓기 위해서는 아래 구문을 활용합니다.
ALTER DATABASE testdb SET ENCRYPTION OFF;
암호화 복호화 동작은 SQL Server에 의해 백그라운드 스레드로 스케쥴링 됩니다. 카탈로그 뷰를 활용하거나 DMV를 통해 이 동작을 확인할 수 있습니다.
TDE가 활성화된 데이터베이스의 백업 파일 또한 DB 암호화 키를 사용해서 암호화 됩니다. 결과적으로 백업파일을 복원할 때에 DB 암호화 키를 보호하고 있는 인증서가 반드시 필요합니다. 이 의미는 DB를 백업하기 위해서는 데이터 손실을 막기 위해 서버의 인증서 역시 백업해 둬야합니다. 인증서가 더 이상 사용할 수 없을 때 데이터 손실이 발생합니다.
'Microsoft Azure' 카테고리의 다른 글
| SQL Server in Docker & Kubernetes (0) | 2026.01.22 |
|---|---|
| SQL Server on Linux 성능 향상 (0) | 2026.01.21 |
| SQL Server on Linux 고가용성 그룹(AG) 구성 (0) | 2026.01.17 |
| SQL Server on Windows 고가용성 그룹(AG) 구성 (3) (0) | 2026.01.14 |
| SQL Server on Windows 고가용성 그룹(AG) 구성 (2) (0) | 2026.01.14 |