SQL Server_PDF

Transcription

1 개인정보보호를위한 SQL Server 보안가이드 SQL Server 의보안기본 SQL Server 의접근통제 SQL Server 의개인정보암호화 SQL Server 의접속기록의위 변조방지 시뮬레이션 부록 : 보안점검체크리스트, 개인정보보호법및관련법률

2

3 개인정보보호를위한 SQL Server 보안가이드 SQL Server의보안기본 SQL Server의접근통제 SQL Server의개인정보암호화 SQL Server의접속기록의위 변조방지 시뮬레이션 부록 : 보안점검체크리스트, 개인정보보호법및관련법률 SQL Server 보안가이드

4 Contents 목차 서문 포함된내용 연습용데이터베이스만들기...7 1장. SQL Server의보안기본 노출영역 불필요한기능비활성화 불필요한서비스중지 서비스계정 서비스계정선택 서비스계정권한설정 인증모드 인증모드이해 인증모드변경방법 암호정책 Windows 암호정책이해 Windows 암호정책활용 백업보안 백업수행권한제어 백업파일관리 장. SQL Server의접근통제 단계별접근통제 서버수준의접근통제 데이터베이스수준의접근통제 테이블수준의접근통제 열수준의접근통제 행및셀수준의접근통제

5 2. 서버역할과데이터베이스역할 역할이란 고정서버역할과데이터베이스역할 역할에멤버추가하기 사용자정의서버역할및데이터베이스역할 중요권한통제 sa 계정권한통제 sysadmin 역할권한관리 db_owner 역할권한통제 guest 계정권한통제 장. SQL Server의개인정보암호화 데이터암호화 데이터암호화이해및고려사항 대칭키를사용한암호화 비대칭키를사용한암호화 인증서를사용한암호화 HASHBYTES 함수를사용한암호화내용작성 데이터베이스암호화 데이터베이스암호화이해및고려사항 데이터베이스암호화하기 데이터베이스백업암호화 암호화된데이터베이스복원 데이터베이스암호화해제 장. SQL Server 의접속기록의위 변조방지 SQL Server 감사 SQL Server 감사에대한이해...91 SQL Server 보안가이드 3

6 1.2 감사와감사사양 서버감사만들기 서버감사만들기 서버감사활성화 데이터베이스감사사양 데이터베이스감사사양만들기 데이터액세스테스트 감사정보확인 감사정보테이블에기록하기 서버감사사양 서버감사사양만들기 감사로그확인 감사에서필터링사용하기 감사및감사사양제거 감사사양비활성화및제거 서버감사비활성화및제거 장. 시뮬레이션 예제데이터베이스만들기 접근통제 요구사항 해결책 개인정보암호화 요구사항 해결책 접속기록의위 변조방지 요구사항 해결책

7 부록 1. 보안점검체크리스트 부록 2. 개인정보보호법및관련법률 개인정보보호법요약 ) 목적 ) 대상 ) 다른법률과의관계 ) 개인정보보호관련조항 ) 벌칙 정보통신망이용촉진및정보보호등에관련법률및시행령 ) 목적 ) 대상 ) 방통위고시근거 ) 개인정보보호관련조항 ) 벌칙 개인정보의기술적ㆍ관리적보호조치기준 ) 목적 ) 관련근거 ) 주요내용 ) 대상사업자 ) 성격 ) 강제여부 개인정보의기술적ㆍ관리적보호조치기준주요내용 제 4조 : 접근통제 제 5조 : 접속기록의위 변조방지 ( 감사 ) 제 6조 : 개인정보의암호화 SQL Server 보안가이드 5

8 서문 본문서는개인정보보호법및관련법률에부합하기위해 Microsoft SQL Server( 이후 SQL Server로칭함 ) 에서수행해야할내용에대한가이드라인을제시할목적으로작성되었다. SQL Server가운영되는환경의개별특성에따라수행과정에다소차이가있을수있으므로, 본문서는가장일반적인상황에서의수행방법을 SQL Server 2012 Enterprise Edition을기준으로설명한다. 1. 포함된내용 본문서의각장과부록에포함된내용은다음과같다. 1 장 제목 SQL Server 보안기본 내용 SQL Server를운영하는데기본적으로필요한보안관련개념및구성방법에대한설명 2 장 SQL Server의접근통제 개인정보에대한접근을통제하기위해수행되어야하는단계별권한제어방법에대한설명 3 장 SQL Server의개인정보암호화 개인정보를암호화하기위해사용할수있는다양한데이터암호화및데이터베이스암호화사용방법에대한설명 4 장 SQL Server의접속기록의위ㆍ변조방지 개인정보에대한접속기록을남기고정기적으로모니터링하기위한 SQL Server 감사기능에대한설명 5 장 시뮬레이션 앞의과정에서설명한내용을전반적으로정리하기위해가상의요구사항과함께각요구사항에대한해결책설명 부록 1 보안점검체크리스트 업무상에서활용할수있는보안점검체크리스트 부록 2 개인정보보호법및관련법률 개인정보보호법및관련법률내용중에서본서에서다룬내용과밀접한관계가있는중요부분 6

9 2. 연습용데이터베이스만들기 본서에서는다음스크립트를사용해만든연습용데이터베이스를대상으로 SQL Server의보안과관련된기능을설명할예정이다. 다음스크립트를사용해연습용데이터베이스인 TestDB를만든다. -- 1) 데이터베이스만들기 USE master -- 이미존재하면제거 IF DB_ID( TestDB ) IS NOT NULL BEGIN ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE TestDB END -- 데이터베이스만들기 CREATE DATABASE TestDB USE TestDB -- 2) 테이블만들기 -- 기존테이블있으면제거 IF OBJECT_ID( dbo.customer ) IS NOT NULL DROP TABLE dbo.customer -- 테이블만들기 CREATE TABLE dbo.customer ( CustomerID varchar(10) PRIMARY KEY, Name nvarchar(10), AccountNo varchar(20), varchar(60), Point int SQL Server 보안가이드 7

10 ) -- 데이터추가 INSERT INTO dbo.customer VALUES( hong, N 홍길동, , hong@dbnuri.com, 5000) INSERT INTO dbo.customer VALUES( jiemae, N 일지매, , jiemae@sqlworld.pe.kr, 4000) INSERT INTO dbo.customer VALUES( han, N 한국인, , han@sqlroad.com, 6000) INSERT INTO dbo.customer VALUES( love, N 한사랑, , love@dbnuri.com, 4500) -- 3) 저장프로시저, 뷰만들기 -- 고객아이디를매개변수로받아해당고객정보를보여주는저장프로시저 CREATE PROC varchar(10) AS SELECT Name, AccountNo, , Point FROM dbo.customer WHERE CustomerID -- 포인트를기준으로상위 2 명을보여주는뷰 CREATE VIEW dbo.vw_customer_top_2 AS SELECT TOP (2) WITH TIES CustomerID, Name, AccountNo, , Point FROM dbo.customer ORDER BY Point DESC 8

11 1 SQL Server 의보안기본

12 10

13 1 장. SQL Server 의보안기본 개인정보보호와관련된실질적인내용을살펴보기전에 SQL Server의보안에대한기본개념을이해하도록한다. 만일이러한내용을이미숙지하고있는경우는바로다음장으로넘어가도록한다. 1. 노출영역 1.1 불필요한기능비활성화 SQL Server가제공하는다양한기능중에서필요하지않은기능은해당기능을비활성화해야한다. 불필요한기능이활성화되어있으면자원낭비의원인이될수있으며보안의허점이될수있기때문이다. 1) SQL Server Management Studio( 이후 SSMS로칭함 ) 를사용한노출영역구성다음과같이개체탐색기에서패싯 (Facets) 기능을사용해중요기능에대한활성화여부를선택한다. SQL Server 보안가이드 11

14 SQL Server 의보안기본 개체탐색기 대상서버마우스우측클릭 패싯 (A) 2) sp_configure를사용한기능제어 sp_configure 저장프로시저를사용해중요기능에대한활성화여부를선택한다. 기본옵션 옵션이름과값을지정해기능을활성화또는비활성화한다. 예 ) CLR 기능비활성화하기 -- CLR 비활성화 EXEC sp_configure clr enabled, 0 RECONFIGURE 12

15 고급옵션 show advanced options 기능을활성화한후대상옵션의기능을활성화또는비활성화한다. 예 ) xp_cmdshell 기능비활성화 -- 고급옵션활성화 EXEC sp_configure show advanced options, 1 RECONFIGURE -- xp_cmdshell 비활성화 EXEC sp_configure xp_cmdshell, 0 RECONFIGURE -- 고급옵션비활성화 EXEC sp_configure show advanced options, 0 RECONFIGURE 1.2 불필요한서비스중지 SQL Server는데이터베이스엔진이외에도다양한서비스를제공한다. 이러한서비스중에서사용하지않는서비스는설치과정에서제외시키거나, 설치후에도시작유형을제어 ( 시작유형을수동또는사용안함으로변경 ) 해서불필요한자원낭비및보안의허점이될수있는소지를제거해야한다. 설치되지않은서비스가향후필요해지는경우에설치과정을통해해당기능을추가할수있다. 이미설치된서비스에대한제어는 SQL Server 구성관리자를사용해서손쉽게수행할수있다. SQL Server 보안가이드 13

16 SQL Server 의보안기본 1) SQL Server 구성관리자실행하기다음과같이 SQL Server 프로그램그룹의구성도구에포함되어있는 SQL Server 구성관리자를실행한다. 시작 모든프로그램 Microsoft SQL Server 2012 구성도구 SQL Server 구성관리자 2) 서비스시작모드변경다음과같이 SQL Server 구성관리자의 SQL Server 서비스항목에서대상서비스의시작모드를변경한다. 14

17 SQL Server 구성관리자 SQL Server 서비스 대상서비스마우스우측클릭 속성 (R) 서비스탭 시작모드 2. 서비스계정 2.1 서비스계정선택 SQL Server 서비스계정은 SQL Server가운영되는동안 Windows 운영체제의자원또는다른서버의자원을접근해야하는경우에필요한권한을제공하는계정이다. 기존의계정을사용하기보다는 SQL Server 서비스를위한별도의도메인계정또는로컬계정을만들어사용하도록한다. 서비스계정은 SQL Server 설치과정에서설정하거나이후에 SQL Server 구성관리자를사용해서새로운서비스계정을설정하거나기존서비스계정의암호를설정해줄수있다. SQL Server 서비스계정변경다음과같이 SQL Server 구성관리자의 SQL Server 서비스항목에서대상서비스의서비스계정을변경한다. SQL Server 보안가이드 15

18 SQL Server 의보안기본 SQL Server 구성관리자 SQL Server 서비스 대상서비스마우스우측클릭 속성 (R) 로그온탭 계정지정 (H) 2.2 서비스계정권한설정 SQL Server 서비스계정은 SQL Server 서비스를수행하기위한최소의권한만을가져야한다. 필요이상의권한이설정되면 SQL Server를통한 Windows 자원에대한접근권한이강력한만큼보안의허점이커지게된다. SQL Server 설치과정에서또는 SQL Server 구성관리자에서서비스계정을설정하면해당계정에게 SQL Server 서비스를수행하는데필요한최소의권한이자동으로부여된다. 3. 인증모드 3.1 인증모드이해 인증이란 SQL Server에로그인하는계정이로그인할수있는계정인지아닌지를판단하는과정을이야기하며, 로그인할수있는계정의범위를선택하는것이인증모드이다. 인증모드는다음과같이두가지가있다. 16

19 인증모드 Windows 인증모드 설명 도메인또는로컬의 Windows 계정및그룹계정이 SQL Server에로그인할수있게허용하는모드로서 SQL Server 자체계정은로그인할수없게된다. SQL Server 및 Windows 인증모드 혼합모드라고도부르며 Windows 계정은물론 SQL Server 자체계정도 SQL Server에로그인할수있게허용하는모드이다. 이중혼합모드의경우는 SQL Server 자체계정도로그인할수있는가능성을제공해서업무의편의성을제공할수있으나, 그만큼관리지점이늘어나게되어보안사고의가능성도커지게된다. 그러므로가능한한 Windows 인증모드를사용하도록한다. 3.2 인증모드변경방법 다음과같이개체탐색기의서버속성화면에서인증모드를변경한다. 단, 인증모드변경은 SQL Server 서비스를재시작해야만영향을발휘하게된다. 개체탐색기 대상서버마우스우측클릭 속성 (R) 보안탭 서버인증변경 SQL Server 보안가이드 17

20 SQL Server 의보안기본 4. 암호정책 4.1 Windows 암호정책이해 Windows 운영체제의계정은 Active Directory의보안정책또는로컬보안정책에서암호정책과계정잠금정책을사용해보안에허점이없도록강력하게관리될수있다. 강력한암호정책은계정정보의유출로인해발생할수있는문제를해결할수있는가장기본적이고보다나은방법이다. 1) 암호정책다음과같이암호정책을사용해암호의사용기간및복잡성을설정한다. 시작 관리도구 로컬보안정책 보안설정 계정정책 암호정책 암호는복잡성을만족해야함 다음의조건을만족해야암호로설정되어사용될수있다. 계정이름또는연속된문자 2개를초과하는사용자전체이름의일부를포함하지않아야한다. 길이가최소한 6자이상이어야한다. 다음네가지범주중세가지의문자를포함해야한다. 영문대문자 (A - Z) 영문소문자 (a - z) 기본 10개숫자 (0-9) 알파벳이외의문자 ( 예 :!, $, #, %) 18

21 최근암호기억최근에사용된암호를다시설정할수없도록몇개의암호를기억하고있을지설정한다. 최대암호사용기간암호의최대사용기간을지정한다. 이기간이지나면암호를변경해야한다. 최소암호길이암호의최소길이를설정해서너무짧은암호를사용하지못하도록한다. 최소암호사용기간한번설정된암호를최소한사용해야하는기간을설정해빈번하게암호를바꿔최근의암호를다시사용하려는시도를막는다. 2) 계정잠금정책다음과같이계정잠금정책을사용해잘못된암호를사용해로그인을계속해서시도하지못하도록제어한다. 시작 관리도구 로컬보안정책 보안설정 계정정책 계정잠금정책 SQL Server 보안가이드 19

22 SQL Server 의보안기본 계정잠금기간잠긴계정이얼마동안잠겨있을지기간을설정한다. 계정잠금임계값로그인이몇번실패하면잠글것인지임계값을설정한다. 다음시간후계정잠금수를원래대로설정임계값에도달하기전로그인실패카운트를초기화할시간을설정한다. 4.2 Windows 암호정책활용 SQL Server에로그인하는 Windows 계정은자동적으로 Windows 운영체제의암호정책을준수하게되지만 sa와같은 SQL Server 자체계정은설정여부에따라암호정책준수여부가달라지므로이에대한적절한설정이필요하다. 다음과같이 SQL Server 계정을생성하거나변경하는과정에서관련항목에대한설정을수행한다. 20

23 암호정책강제적용 Windows 운영체제의암호정책을따를지여부를설정한다. 암호만료강제적용 Windows 운영체제의최대암호사용기간이지나면암호를변경하게할지설정한다. 다음로그인할때반드시암호변경다음로그인할때암호를변경해야로그인이가능하게할지설정한다. 5. 백업보안 5.1 백업수행권한제어 데이터베이스백업은데이터베이스가손상되거나데이터가유실될수있는만일의경우에대비하기위한것으로, 데이터베이스못지않은중요한의미를가진다. 데이터베이스백업파일이외부로유출되면기본적으로다른서버에서도손쉽게복원이되므로심각한정보유출의결과를가져온다. 데이터베이스백업파일이외부로유출되지않게하는것도중요하지만, 우선은아무나백업을받지못하도록권한설정을해야한다. 1) 데이터베이스를백업받을수있는권한특정데이터베이스를백업받기위해서는다음역할중의하나에멤버로서포함되어야한다. sysadmin 서버역할의멤버 db_owner 데이터베이스역할의멤버 db_backupoperator 데이터베이스역할의멤버 그러므로아무나위역할의멤버가되지않도록로그인계정및데이터베이스사용자계정에대한권한관리를해야한다. 현재위역할들에속해있는멤버는다음스크립트를사용해확인할수있다. sysadmin 서버역할의멤버확인 SELECT name, type_desc, is_disabled, create_date FROM sys.server_principals WHERE type IN ( S, U, R ) -- SQL_LOGIN, WINDOWS_LOGIN, SERVER_ROLE SQL Server 보안가이드 21

24 SQL Server 의보안기본 AND IS_SRVROLEMEMBER( sysadmin, name) = 1 ORDER BY name db_owner 데이터베이스역할의멤버확인 USE TestDB -- 대상데이터베이스이름지정 SELECT name, type_desc, create_date FROM sys.database_principals WHERE type IN ( S, U, R ) -- SQL_USER, WINDOWS_USER, DATABASE_ROLE AND IS_ROLEMEMBER( db_owner, name) = 1 ORDER BY name db_backupoperator 데이터베이스역할의멤버확인 USE TestDB -- 대상데이터베이스이름지정 SELECT name, type_desc, create_date FROM sys.database_principals WHERE type IN ( S, U, R ) -- SQL_USER, WINDOWS_USER, DATABASE_ROLE AND IS_ROLEMEMBER( db_backupoperator, name) = 1 ORDER BY name 2) 데이터베이스를복원할수있는권한참고로데이터베이스복원에대한권한이나역할은별도로존재하지는않는다. 데이터베이스복원은데이터베이스생성과정으로진행된다. 그러므로 CREATE DATABASE 문을수행할수있는권한이있으면데이터베이스백업파일로부터데이터베이스를복원할수있다. 결국데이터베이스백업파일이유출되지않도록원천차단하는것이최선의방법이다. 22

25 5.2 백업파일관리 데이터베이스를백업받지못하도록권한관리를하는것도중요하지만, 데이터베이스백업파일이외부로유출되지않게관리를철저히해야한다. 바로앞에서언급을했듯이데이터베이스파일로부터복원이쉽게수행되기때문이다. 물론데이터베이스암호화를통해이러한문제를어느정도해결할수있다고하지만, 그래도우선은백업파일이유출되지않게하는것이먼저이다. 1) NTFS 파티션권한제어백업파일이저장된폴더에아무나접근하지못하도록 Windows 운영체제에서해당폴더에대한권한제어를해야한다. 올바르지못한예다음화면의경우는 Backup 폴더에대해일반사용자가접근권한을가지고있음을보여준다. 이렇게되면일반계정에대한백업파일유출을막을수없게된다. SQL Server 보안가이드 23

26 SQL Server 의보안기본 올바른예다음화면의경우는 Backup 폴더에대해 Windows 관리자그룹과 SQL Server 서비스계정에게만접근권한이허용된상태를보여준다. 이렇게하면일반계정이백업폴더에접근할수없게통제된다. 2) 백업테이프에대한보안데이터베이스백업이하드디스크의파일로존재하지않고분리및이동이가능한테이프에존재하는경우해당테이프에대한접근통제가이루어져야한다. 물리적보안장치관리자만접근이허용된물리적인보관장치에백업테이프가보관되어관리되어야한다. 이동상황감시백업테이프가안전또는업무상의필요로인해다른곳으로이동되는경우이에대한기록및통제가필요하다. 24

27 2 SQL Server 의접근통제

28 26

29 2 장. SQL Server 의접근통제 개인정보의기술적ㆍ관리적보호조치기준 제 4조를보면접근통제에대하여다음과같이언급하고있다. 제 4조 : 접근통제 5 정보통신서비스제공자등은정보통신망을통한불법적인접근및침해사고방지를위해다음각호의기능을포함한시스템을설치 운영하여야한다. 1. 개인정보처리시스템에대한접속권한을 IP주소등으로제한하여인가받지않은접근을제한 2. 개인정보처리시스템에접속한 IP주소등을재분석하여불법적인개인정보유출시도를탐지 9 정보통신서비스제공자등은취급중인개인정보가인터넷홈페이지, P2P, 공유설정등을통하여열람권한이없는자에게공개되거나외부에유출되지않도록개인정보처리시스템및개인정보취급자의컴퓨터에조치를취하여야한다. 출처 : 방송통신위원회고시제 호 ( ) 개인정보의기술적ㆍ관리적보호조치기준 즉아무나개인정보처리시스템에접근하지못하도록접근에대한통제를수행하여야한다. SQL Server 는로그인계정과데이터베이스사용자계정에대한인증과권한제어를통해불법적인접근및침해를방지할수있는데이터베이스관리시스템이다. 내부사용자에의한개인정보유출이빈번하게발생하는이유는내부사용자가중요데이터에대한접근권한을정식으로가지고있기때문이다. 그러므로내부사용자에의한정보유출이언제든발생할수있음을인식하고명확한권한부여를통한접근통제를수행해야한다. 또한권한이필요없어지는경우 ( 퇴사및직원의업무변경등 ) 기존에부여된권한은즉시제거되어야한다. SQL Server 보안가이드 27

30 SQL Server 의접근통제 1. 단계별접근통제 SQL Server는다양한수준의접근통제가가능하다. 서버수준에서개체수준까지각단계별로접근을통제하는방법을살펴보자. 1.1 서버수준의접근통제 누군가가 SQL Server에접근하기위해서는사용할로그인계정이있어야한다. 꼭필요한사용자에게만로그인계정을만들어사용하도록해야한다. 로그인계정은 Windows 계정과 SQL Server 계정으로구분해서등록하게된다. 1) Windows 계정등록 Windows 도메인또는 Windows 로컬서버의개인계정및그룹계정이 SQL Server의로그인계정으로등록될수있으며, SQL Server 로그인계정으로등록된계정만 SQL Server에로그인할수있다. 다음의예는 KOREA라는이름의 Windows 서버에 Gildong 이라는개인계정과 HR_Employee 하는그룹계정이있다는가정에서진행된다. 다음과같이 Gildong 계정과 HR_Employee 그룹계정을 SQL Server 로그인계정으로등록한다. [T-SQL 문사용 ] USE Master -- Windows 개인계정등록하기 CREATE LOGIN [KOREA\Gildong] FROM WINDOWS -- Windows 그룹계정등록하기 CREATE LOGIN [KOREA\HR_Employee] FROM WINDOWS 28

31 [ 개체탐색기사용 ] 개체탐색기 보안 로그인마우스우측클릭 새로그인 (N) Windows 인증 (W) 2) SQL Server 계정만들기 Windows 도메인또는 Windows 로컬서버에로그인할수없는경우SQL Server 자체계정을만들어이계정으로 SQL Server에로그인할수있다. 다음과같이 Tarzan이라는이름의 SQL Server 자체로그인계정을만든다. 이때 Windows 계정등록과는달리암호를지정해주어야하며, Windows 보안정책준수여부를설정해야한다. SQL Server 보안가이드 29

32 SQL Server 의접근통제 [T-SQL 문사용 ] USE Master -- SQL Server 계정만들기 CREATE LOGIN Tarzan WITH PASSWORD = Pa$$w0rd, CHECK_EXPIRATION=ON, CHECK_POLICY=ON [ 개체탐색기사용 ] 개체탐색기 보안 로그인마우스우측클릭 새로그인 (N) SQL Server 인증 (S) 30

33 1.2 데이터베이스수준의접근통제 SQL Server에로그인한계정이특정데이터베이스에액세스하기위해서는우선해당데이터베이스의사용자계정과매핑이되어야하며, 매핑이되는데이터베이스사용자계정에게주어진권한하에서해당데이터베이스에포함된자원을액세스할수있다. 매핑이되는데이터베이스사용자계정이없다면원칙적으로해당데이터베이스에접근을할수없다. 1) 데이터베이스사용자계정만들기다음과같이앞의과정에서만든로그인계정을 TestDB 데이터베이스의사용자로매핑시킨다. [T-SQL 문사용 ] USE TestDB CREATE USER [KOREA\Gildong] FOR LOGIN [KOREA\Gildong] CREATE USER [KOREA\HR_Employee] FOR LOGIN [KOREA\HR_Employee] CREATE USER Tarzan FOR LOGIN Tarzan SQL Server 보안가이드 31

34 SQL Server 의접근통제 [ 개체탐색기사용 ] < 방법 1> 개체탐색기 보안 로그인 대상로그인계정마우스우측클릭 속성 (R) 사용자매핑페이지 이로그인으로매핑된사용자 : 로그인계정이액세스하려는데이터베이스선택 데이터베이스역할멤버자격 : 포함될데이터베이스역할선택 ( 우선그대로둠 ) 32

35 < 방법 2> 개체탐색기 대상데이터베이스 보안 사용자마우스우측클릭 새사용자 (N) 사용자유형 : 로그인을사용하는 SQL 사용자 사용자이름 : 로그인계정에매핑될데이터베이스사용자이름 ( 일반적으로로그인이름과동일하게 ) 로그인이름 : 데이터베이스사용자로매핑이될로그인계정선택 기본스키마 : 데이터베이스사용자의기본스키마 ( 선택하지않으면 dbo 스키마가기본스키마가됨 ) 2) 데이터베이스수준의권한부여개별개체에대한접근권한을부여하는것이바람직하나필요한경우데이터베이스수준의권한을할당할수있다. 예를들어데이터베이스에포함된모든테이블을 SELECT 할수있는권한을부여하거나데이터베이스에테이블을만들수있는권한을부여할수있다. SQL Server 보안가이드 33

36 SQL Server 의접근통제 다음과같이 Tarzan 사용자에게 TestDB 데이터베이스의모든테이블에대한 SELECT 권한을부여한다. [T-SQL 문사용 ] USE TestDB GRANT SELECT TO Tarzan [ 개체탐색기사용 ] 개체탐색기 데이터베이스 대상데이터베이스마우스우측클릭 속성 (R) 사용권한페이지 34

37 3) 불필요한권한제거더이상데이터베이스수준의권한이필요없다면필히부여된권한을제거하여불필요한액세스및데이터유출의가능성을최소화해야한다. 다음과같이 Tarzan 사용자에게주어진데이터베이스의모든테이블에대한 SELECT 권한을제거한다. USE TestDB REVOKE SELECT TO Tarzan 1.3 테이블수준의접근통제 데이터베이스사용자가테이블과같은특정개체에대한접근권한이필요한경우명시적으로권한을부여해주어야한다. SQL Server에서는사용자가개체에대해서권한을갖기위해서는다음두가지조건이동시에만족되어야한다. 명시적으로권한을부여 (GRANT) 받아야함 그어떤경로를통해서든권한이거부 (DENY) 되어서는안됨 1) 테이블수준의권한부여가장기본적인권한부여방법은사용자가접근하고자하는개체에대해개별적으로권한을부여하는것이다. 물론이렇게하면대상개체가많은경우관리자의작업량이증가되지만가장기본적이고보안의허점을피할수있는방법이다. 다음과같이 Tarzan 사용자에게 dbo.customer 테이블에대한 SELECT, INSERT, UPDATE, DELETE 권한을부여한다. [T-SQL 문사용 ] USE TestDB GRANT SELECT ON dbo.customer TO Tarzan SQL Server 보안가이드 35

38 SQL Server 의접근통제 GRANT INSERT ON dbo.customer TO Tarzan GRANT UPDATE ON dbo.customer TO Tarzan GRANT DELETE ON dbo.customer TO Tarzan [ 개체탐색기사용 ] 개체탐색기 데이터베이스 대상데이터베이스 테이블 대상테이블마우스우측클릭 속성 (R) 사용권한페이지 스키마 : 선택된테이블이포함된스키마 테이블이름 : 선택된테이블의이름 사용자또는역할 : 권한을받고자하는사용자선택 ( 다중선택가능 ) [ 선택한사용자또는역할 ] 에대한사용권한 : 선택된사용자또는역할에게부여할권한선택 36

39 2) 불필요한권한제거더이상테이블수준의권한이필요없다면필히부여된권한을제거하여불필요한액세스및데이터유출의가능성을최소화해야한다. 다음과같이 Tarzan 사용자에게주어진테이블에대한모든권한을제거한다. USE TestDB REVOKE DELETE ON dbo.customer TO Tarzan REVOKE INSERT ON dbo.customer TO Tarzan REVOKE SELECT ON dbo.customer TO Tarzan REVOKE UPDATE ON dbo.customer TO Tarzan 1.4 열수준의접근통제 SQL Server는열수준의권한을제어할수있는기능을제공한다. 열수준의권한을사용하면사용자가필요로하는열의데이터만접근가능하게하고, 나머지열에대해서는접근을차단할수있다. 1) 열수준의권한부여다음과같이 Tarzan 사용자에게는 dbo.customer 테이블에대한 SELECT 권한을부여하되, CustomerID, Name, 열에대해서만접근이가능하도록권한을부여한다. [T-SQL 문사용 ] USE TestDB GRANT SELECT ON dbo.customer(customerid) TO Tarzan GRANT SELECT ON dbo.customer(name) TO Tarzan GRANT SELECT ON dbo.customer( ) TO Tarzan SQL Server 보안가이드 37

40 SQL Server 의접근통제 [ 개체탐색기사용 ] 개체탐색기 데이터베이스 대상데이터베이스 테이블 대상테이블마우스우측클릭 속성 (R) 사용권한페이지 SELECT 권한부여 열사용권한버튼선택 2) 권한테스트만일위과정을통해 Tarzan 사용자에게주어진열에대한권한을테스트하고자한다면다음과같이 Tarzan 사용자로가장해서테스트할수있다. -- 권한확인 EXECUTE AS USER = Tarzan -- 사용자가장 SELECT CustomerID, Name, AccountNo, , Point FROM dbo.customer -- 권한이없는열을포함한 SELECT 문 ( 오류발생 ) /* 메시지 230, 수준 14, 상태 1, 줄 1 개체 Customer, 데이터베이스 TestDB, 스키마 dbo 의열 AccountNo 에대한 SELECT 권한이거부되었습니다. 메시지 230, 수준 14, 상태 1, 줄 1 개체 Customer, 데이터베이스 TestDB, 스키마 dbo 의열 Point 에대한 SELECT 권한이거부되 38

41 었습니다. */ SELECT CustomerID, Name, FROM dbo.customer -- 권한이부여된열만포함한 SELECT 문 ( 성공 ) REVERT -- 사용자가장해제 우선 SELECT 절에 * 를사용해서권한을부여받지못한열 (AccountNo, Point) 을포함해서표시하고자하는첫번째의 SELECT문은다음과같은오류메시지가표시되면서실패한다. 메시지 230, 수준 14, 상태 1, 줄 1 개체 Customer, 데이터베이스 TestDB, 스키마 dbo 의열 AccountNo 에대한 SELECT 권한이거부되었습니다. 메시지 230, 수준 14, 상태 1, 줄 1 개체 Customer, 데이터베이스 TestDB, 스키마 dbo 의열 Point 에대한 SELECT 권한이거부되었습니다. 반면에두번째 SELECT문은권한을부여받은열 (CustomerID, Name, ) 만을포함하므로정상적으로수행되어다음과같은결과를표시한다. 3) 불필요한권한제거더이상열수준의권한이필요없다면필히부여된권한을제거하여불필요한액세스및데이터유출의가능성을최소화해야한다. SQL Server 보안가이드 39

42 SQL Server 의접근통제 다음과같이 Tarzan 사용자에게주어진권한을제거한다. USE TestDB REVOKE SELECT ON dbo.customer(customerid) TO Tarzan REVOKE SELECT ON dbo.customer(name) TO Tarzan REVOKE SELECT ON dbo.customer( ) TO Tarzan 1.5 행및셀수준의접근통제 SQL Server에서는테이블의행수준또는셀수준의권한을제어할수있는기본적인기능은제공되지않는다. 하지만뷰또는저장프로시저를사용하면행수준및셀수준의접근을통제할수있다. 1) 뷰를사용한행및셀수준의접근통제만일 Tarzan 사용자계정에게 Customer 테이블에포함된행중에서홍길동, 일지매고객의행을제외한나머지행만접근할수있게하고자한다면, 그리고이행중에서 CustomerID, Name, 열의정보만접근할수있게하고자한다면다음과같이보이고자하는데이터만을갖는뷰를만들고이뷰에대한접근권한만을 Tarzan 사용자계정에게부여하면된다. -- 뷰만들기 USE TestDB CREATE VIEW dbo.vcustomer AS SELECT CustomerID, Name, FROM dbo.customer WHERE CustomerID NOT IN ( hong, jiemae ) -- 권한주기 DENY SELECT ON dbo.customer TO Tarzan GRANT SELECT ON dbo.vcustomer TO Tarzan 40

43 -- 권한확인 EXECUTE AS USER = Tarzan -- 사용자가장 SELECT * FROM dbo.vcustomer REVERT -- 사용자가장해제 이때 Tarzan 사용자계정이다른경로를통해서라도 dbo.customer 테이블의정보에접근하지못하도록 SELECT 권한을명시적으로거부 (DENY) 시키고 vcustomer 뷰에대해서만권한을부여한다. 기본테이블에대한권한이없어도뷰에대한권한만있으면뷰가보여주는데이터를볼수있기때문이다. 위스크립트의마지막부분의 SELECT문의결과는다음과같다. 2) 저장프로시저를사용한행및셀수준의접근통제저장프로시저를사용해도뷰와같이테이블의일부행및셀에대한접근만가능하도록통제할수있다. 다음과같이 CustomerID를매개변수로받아해당고객의 Name, 열의정보를보여주지만홍길동, 일지매고객의정보는볼수없도록하는저장프로시저를만들고 Tarzan 사용자계정에게 EXECUTE 권한을부여한다. -- 저장프로시저만들기 CREATE PROC varchar(10) AS SELECT Name, FROM dbo.customer WHERE (CustomerID NOT IN ( hong, jiemae )) AND (CustomerID -- 권한주기 GRANT EXECUTE ON dbo.uspcustomer TO Tarzan SQL Server 보안가이드 41

44 SQL Server 의접근통제 -- 권한확인 EXECUTE AS USER = Tarzan -- 사용자가장 EXEC dbo.uspcustomer hong REVERT -- 사용자가장해제 위스크립트의마지막권한확인부분의저장프로시저호출은홍길동고객의정보를보려고하므로저장프로시저가의도한대로아무정보도표시하지않는다. 2. 서버역할과데이터베이스역할 2.1 역할이란 역할 (Roles) 은사용자계정을그룹핑할수있는논리적인그룹이다. 역할을만들고이역할에권한을부여한후, 해당권한을필요로하는사용자계정을멤버로포함시키면그역할에게주어진권한이멤버들에도상속된다. 역할은권한을관리해야하는사용자계정이많은경우에이를쉽게관리할수있게해주어관리자의업무부담을줄여준다. 하지만주의할사항은로그인계정또는데이터베이스사용자계정이요구되는권한과동일한역할이존재하지않은경우좀더많은권한을가지고있는역할에멤버로추가시키는경우가많으며, 이로인해필요이상의권한이사용자에게주어지게된다는점이다. 2.2 고정서버역할과데이터베이스역할 SQL Server가설치되면서버수준에기본적으로존재하는역할을고정서버역할이라고하며, 데이터베이스가만들어지면데이터베이스내에기본적으로존재하는역할을고정데이터베이스역할이라고한다. 이들데이터베이스역할은기본적으로상당이많은권한을가지고있으므로멤버로포함시키고자하는경우각별한주의를기울여야한다. 42

45 1) 고정서버역할 서버역할설명 sysadmin 모든서버작업을수행할수있다. serveradmin 서버차원의구성옵션을변경하고서버를종료할수있다. securityadmin 로그인및해당속성을관리한다. 서버수준의사용권한을부여 (GRANT), 거부 (DENY) 및취소 (REVOKE) 할수있다. 또한데이터베이스에대한액세스권한이있는경우데이터베이스수준의사용권한을부여 (GRANT), 거부 (DENY) 및취소 (REVOKE) 할수도있으며, SQL Server의로그인암호를다시설정할수있다. processadmin SQL Server 인스턴스에서실행중인프로세스를종료할수있다. setupadmin 연결된서버를추가하거나제거할수있다. bulkadmin BULK INSERT 문을실행할수있다. diskadmin 디스크파일을관리하는데사용된다. dbcreator 데이터베이스를생성, 변경, 삭제및복원할수있다. public 모든 SQL Server 로그인은 public 서버역할에속한다. 다른역할과는달리 public 에서사용권한을부여, 거부또는취소할수있다. SQL Server 보안가이드 43

46 SQL Server 의접근통제 2) 고정데이터베이스역할 데이터베이스역할설명 db_owner 데이터베이스에서모든구성및유지관리작업을수행할수있고데이터베이스를삭제할수도있다. db_securityadmin 역할멤버자격을수정하고사용권한을관리할수있다. db_accessadmin Windows 로그인, Windows 그룹및 SQL Server 로그인의데이터베이스에대한액세스를추가하거나제거할수있다. db_backupoperator 데이터베이스를백업할수있다. db_ddladmin 데이터베이스에서모든 DDL( 데이터정의언어 ) 명령을실행할수있다. db_datawriter 모든사용자테이블에서데이터를추가, 삭제또는변경할수있다. db_datareader 모든사용자테이블의모든데이터를읽을수있다. db_denydatawriter 데이터베이스내의사용자테이블에있는데이터를추가, 수정또는삭제할수없다. db_denydatareader 데이터베이스내에있는사용자테이블의데이터를읽을수없다. public 모든데이터베이스사용자는 public 데이터베이스역할에속한다. 사용자에게보안개체에대한특정사용권한이부여되지않았거나거부된경우사용자는해당보안개체에대해 public으로부여된사용권한을상속받는다. 2.3 역할에멤버추가하기 KOREA\Gildong 로그인계정을 diskadmin 서버역할에추가하고, Tarzan 사용자계정을 TestDB 데이터베이스의 db_backupoperator 데이터베이스역할에포함시킨다. 44

47 [T-SQL 문사용 ] -- 서버역할에추가하기 ALTER SERVER ROLE diskadmin ADD MEMBER [KOREA\Gildong] -- 데이터베이스역할에추가하기 USE TestDB ALTER ROLE db_backupoperator ADD MEMBER Tarzan [ 개체탐색기사용 ] - 서버역할에추가 개체탐색기 보안 로그인 대상로그인마우스우측클릭 속성 (R) 서버역할페이지 서버역할선택 SQL Server 보안가이드 45

48 SQL Server 의접근통제 [ 개체탐색기사용 ] - 데이터베이스역할에추가 개체탐색기 데이터베이스 대상데이터베이스 보안 사용자 대상사용자마우스우측클릭 속성 (R) 멤버자격페이지 역할멤버선택 2.4 사용자정의서버역할및데이터베이스역할 앞에서언급한고정서버역할및데이터베이스역할외에도사용자가서버역할및데이터베이스역할을만들수있다. 이러한사용자정의서버역할및데이터베이스역할을사용하면같은권한을요구하는계정을좀더효율적으로관리할수있다. 업무의필요성에따라서역할을만들면되고, 역할에멤버를지정하는방법은고정서버및데이터베이스역할과동일하다. 참고로사용자정의서버역할을 SQL Server 2012부터제공되는기능이다. 46

49 3. 중요권한통제 3.1 sa 계정권한통제 sa 계정은 SQL Server 내에서가장강력한권한을갖는계정중의하나이다. 그러므로 sa 계정은최고관리자만사용해야하며, 가능하면사용하지않도록하거나혹은계정의이름을바꾸어사용하는것이바람직하다. 이러한 sa 계정에대한제어는다른시스템관리자계정, 즉 sysadmin의멤버인다른계정에의해수행될수있다. 1) sa 계정비활성화다음과같이 sa 계정을사용하지못하도록비활성화한다. [T-SQL 문사용 ] ALTER LOGIN sa DISABLE SQL Server 보안가이드 47

50 SQL Server 의접근통제 [ 개체탐색기사용 ] 개체탐색기 보안 sa 계정마우스우측클릭 속성 (R) 상태페이지 로그인사용안함선택 2) sa 계정이름변경다음과같이 sa 계정의이름을 sqladmin으로변경한다. [T-SQL 문사용 ] ALTER LOGIN sa WITH NAME = sqladmin 48

51 [ 개체탐색기사용 ] 개체탐색기 보안 로그인 sa 계정마우스우측클릭 이름바꾸기 (M) 새로운이름지정 3.2 sysadmin 역할권한관리 sysadmin 서버역할은 sa 계정과동일한권한을갖게되어이역할의멤버로포함된로그인계정은 sa 계정과동일한권한을부여받게된다. 이렇게되면최고권한을갖는계정이너무많아서버관리에혼란이가중될수있으며, 보안의심각한허점이될수있다. 그러므로 sysadmin 역할에계정을포함시킬때는신중한판단이필요하다. sysadmin 역할의권한을관리자가임으로제어할수없다. 단지역할에포함시킬지말지를결정할수있다. SQL Server 보안가이드 49

52 SQL Server 의접근통제 sysadmin 서버역할의멤버확인 다음과같이현재 sysadmin 서버역할에포함되어있는로그인계정을확인한다. [T-SQL 문사용 ] SELECT name, type_desc, is_disabled, create_date FROM sys.server_principals WHERE type IN ( S, U, R ) -- SQL_LOGIN, WINDOWS_LOGIN, SERVER_ROLE AND IS_SRVROLEMEMBER( sysadmin, name) = 1 ORDER BY name [ 개체탐색기사용 ] 개체탐색기 보안 서버역할 sysadmin 마우스우측클릭 속성 (R) 50

53 3.3 db_owner 역할권한통제 db_owner 데이터베이스역할은해당데이터베이스에대한소유자권한을갖는역할로서 dbo 사용자계정과동일한권한을갖게된다. 서버수준에서는 sysadmin이최고의권한을갖는역할이라면데이터베이스수준에서는 db_owner가최고의권한을갖는역할이다. 그러므로 db_owner 역할에너무많은데이터베이스사용자계정을포함시키지않아야한다. db_owner 데이터베이스역할의멤버확인 다음과같이현재 db_owner 데이터베이스역할에포함되어있는사용자계정을확인한다. [T-SQL 문사용 ] USE TestDB -- 대상데이터베이스이름지정 SELECT name, type_desc, create_date FROM sys.database_principals WHERE type IN ( S, U, R ) -- SQL_USER, WINDOWS_USER, DATABASE_ROLE AND IS_ROLEMEMBER( db_owner, name) = 1 ORDER BY name SQL Server 보안가이드 51

54 SQL Server 의접근통제 [ 개체탐색기사용 ] 개체탐색기 데이터베이스 대상데이터베이스 보안 역할 db_owner 마우스우측클릭 속성 (R) 3.4 guest 계정권한통제 guest 계정은해당데이터베이스에접근이허용되지않은, 즉데이터베이스사용자계정과매핑이되지않은로그인계정이해당데이터베이스를접근하려고할때사용되는데이터베이스사용자계정이다. 보안의허점을최소화하기위해 guest 계정은명확한목적이없는한사용되지못하게비활성화해야하며, 기본적으로사용자데이터베이스의 guest 계정은비활성화된상태로존재하게된다. 52

55 guest 계정비활성화하기 다음과같이 guest 계정의 TestDB 데이터베이스에대한 CONNECT 권한을제거해서 guest 계정을비활성화한다. [T-SQL 문사용 ] USE TestDB -- 대상데이터베이스이름지정 REVOKE CONNECT FROM guest SQL Server 보안가이드 53

56 54

57 3 SQL Server 의개인정보암호화 SQL Server 보안가이드 55

58 56

59 3 장. SQL Server 의개인정보암호화 개인정보의기술적ㆍ관리적보호조치기준 제 6조를보면개인정보암호화에대하여다음과같이언급하고있다. 제 6조 : 개인정보의암호화 1 정보통신서비스제공자등은비밀번호및바이오정보는복호화되지아니하도록일방향암호화하여저장한다. 2 정보통신서비스제공자등은주민등록번호, 신용카드번호및계좌번호에대해서는안전한암호알고리즘으로암호화하여저장한다. 출처 : 방송통신위원회고시제 호 ( ) 개인정보의기술적ㆍ관리적보호조치기준 즉고객의중요한정보는안전한암호알고리즘을사용해암호화해서불법적인데이터유출로부터고객의정보를보호해야한다. SQL Server에는열수준의다양한데이터암호화방법과데이터베이스수준의암호화방법을제공한다. 1. 데이터암호화 1.1 데이터암호화이해및고려사항 데이터암호화는특정열의데이터를암호화함수를사용해암호화하여복호화하지않은이상이데이터를보지못하도록하여, 유출된데이터에대해중요데이터를확인하지못하도록하는심층방어방법을이야기한다. 심층방어란한수준의보안이침해당했을경우이에대한피해를최소화하기위한또한수준의보안이구성되는상황을이야기한다. SQL Server 보안가이드 57

60 SQL Server 의개인정보암호화 1) 데이터암호화사용시고려사항 SQL Server는특정열의데이터를암호화하기위한다양한방법을제공한다. 하지만데이터암호화를사용하기위해서는다음과같은몇가지고려사항이필요하다. 암호화된열의사이즈증가 암호화에사용된암호또는키관리중요 암호화된데이터에대한인덱스생성및정렬불가능 암호화및복호화를위한 CPU 사용량증가 암호화및복호화함수에대한정확한이해 그러나중요한데이터를보호하기위해서는어느정도의성능문제나불편함을감수할수있어야한다. 2) 암호화대상열데이터를암호화하기위해서는어떤열의데이터를암호화할지그대상을먼저선정해야한다. 그렇다면테이블에포함된어떤열을암호화해야할까? 앞페이지에표시한 개인정보의기술적 관리적보호조치기준 제 6조에의하면고객의비밀번호, 바이오정보 ( 지문, 홍채등 ), 주민등록번호, 신용카드번호, 계좌번호와같은중요정보에대해서암호화하면된다. 고객의데이터를면밀히분석해서암호화대상열을선정하도록한다. 참고로주민등록번호를시스템운영을위한검색키로사용하는경우, 속도등성능을고려하여일부정보만암호화조치를취할수있다. 단주민등록번호뒷자리 6개번호이상암호화조치가필요하다. 3) 암호화알고리즘선택데이터를암호화할때사용하는알고리즘에는여러가지가있다. 이중에서신뢰할수있으며법에서요구하는수준을만족하는알고리즘을선택해야한다. 2012년 10월에행정안전부가발행한 개인정보암호화조치안내서 에서는안전한암호알고리즘을다음과같이예시하고있다. 구분알고리즘명칭 대칭키암호알고리즘 SEED, ARIA-128/192/256, AES-128/192/256, Blowfish, Camelia- 128/192/256, MISTY1, KASUMI 등 공개키암호알고리즘 RSA, KCDSA( 전자서명용 ), RSAES-OAEP, RSAES-PKCS1 등 일방향암호알고리즘 SHA-224/256/384/512, Whirlpool 등 참고 : 2012 년 10 월행정안전부발행 개인정보암호화조치안내서 58

61 SQL Server에서도위표에서언급한암호화알고리즘의일부를사용할수있으며, 앞으로살펴볼예제에서도그러한알고리즘을사용한다. 참고로 SQL Server의확장가능키관리 (EKM: Extensible Key Management) 를사용하면데이터베이스파일을보호하는암호화키를스마트카드, USB 장치또는 EKM/HSM(Hardware Security Module) 모듈과같은외부장치에저장할수있을뿐아니라데이터베이스관리자 (sysadmin 그룹의멤버제외 ) 로부터데이터를보호할수도있다. 즉, 외부 EKM/HSM 모듈에서데이터베이스사용자만액세스할수있는암호화키를사용하여데이터를암호화할수있다. 4) 데이터암호화방법특정열의데이터를암호화할수있는방법은다음과같다. 대칭키를사용한암호화및복호화 비대칭키를사용한암호화및복호화 인증서를사용한암호화및복호화 HASHBYTES 함수를사용한암호화 이중에서 HASHBYTES 함수를사용한암호화는복호화방법이없어단방향 ( 또는일방향 ) 암호화라고부르며, 나머지암호화방법은양방향암호화라고부른다. 1.2 대칭키를사용한암호화 1) 암호화및복호화방법대칭키를사용해특정열의데이터를암호화하기위해서는우선대칭키를만들어야한다. 그리고대칭키를열어사용가능하게한후, 관련함수를사용해암호화및복호화를해야한다. 암호화함수 : EncryptByKey() 복호화함수 : DecryptByKey() SQL Server 보안가이드 59

62 SQL Server 의개인정보암호화 2) 연습용테이블만들기데이터암호화기능을확인하기위해우선다음스크립트를사용해연습용테이블을만든다. USE TestDB -- 이미테이블이있으면제거 IF OBJECT_ID( dbo.testcustomer, U ) IS NOT NULL DROP TABLE dbo.testcustomer -- 테이블만들기 SELECT * INTO dbo.testcustomer FROM dbo.customer -- 확인 SELECT * FROM dbo.testcustomer 추가된데이터의내용은다음과같다. 이제다음과정에따라 TestCustomer 테이블의열중에서 AccountNo 열의데이터를대칭키를사용해암호화한다. 60

63 3) 관련구문대칭키를만드는 CREATE SYMMETRIC KEY의전체구문은다음과같다. CREATE SYMMETRIC KEY key_name [ AUTHORIZATION owner_name ] [ FROM PROVIDER Provider_Name ] WITH <key_options> [,... n ] ENCRYPTION BY <encrypting_mechanism> [,... n ] <key_options> : : = KEY_SOURCE = pass_phrase ALRITHM = <algorithm> IDENTITY_VALUE = identity_phrase PROVIDER_KEY_NAME = key_name_in_provider CREATION_DISPOSITION = {CREATE_NEW OPEN_EXISTING } <algorithm> : : = DES TRIPLE_DES TRIPLE_DES_3KEY RC2 RC4 RC4_128 DESX AES_128 AES_192 AES_256 <encrypting_mechanism : : = CERTIFICATE certificate_name PASSWORD = password SYMMETRIC KEY symmetric_key_name ASYMMETRIC KEY asym_key_name SQL Server 보안가이드 61

64 SQL Server 의개인정보암호화 4) 대칭키만들기위구문을참조해서다음과같이데이터암호화에사용할 SECKey01 라는이름의대칭키를만든다. -- 대칭키만들기 CREATE SYMMETRIC KEY SECKey01 WITH ALRITHM = AES_ 암호화알고리즘지정 ENCRYPTION BY PASSWORD = Pa$$w0rd -- 복잡한암호지정 -- 대칭키열기 ( 아직안열린경우 ) OPEN SYMMETRIC KEY SECKey01 DECRYPTION BY PASSWORD = Pa$$w0rd 5) 새로운열에암호화된데이터기록 EncryptedAccountNo 라는이름의새로운열을추가하고, AccountNo 열의기존데이터를암호화해서 EncryptedAccountNo 열에기록한후기존의 AccountNo 열은제거한다. -- 열추가 ALTER TABLE dbo.testcustomer ADD EncryptedAccountNo varbinary(256) -- 새로운열에암호화된데이터저장 UPDATE dbo.testcustomer SET EncryptedAccountNo = EncryptByKey(Key_GUID( SECKey01 ), AccountNo) -- 기존열제거 ALTER TABLE dbo.testcustomer DROP COLUMN AccountNo 62

65 6) 암호화된데이터복호화없이확인우선암호화된데이터를복호화과정없이확인해본다. -- 데이터확인 : 단순확인 SELECT CustomerID, Name, EncryptedAccountNo, , Point FROM dbo.testcustomer 결과는다음과같이정상적인값을확인할수없다. EncryptedAccountNo 열의데이터형식이 varbinary 이므로다음과같이 varchar 형으로변환해서확인해본다. -- 데이터확인 : 형변환확인 SELECT CustomerID, Name, CONVERT(varchar, EncryptedAccountNo) AS AccountNo, , Point FROM dbo.testcustomer 결과는다음과같이정확한값을확인할수없다. SQL Server 보안가이드 63

66 SQL Server 의개인정보암호화 7) 암호화된데이터복호화해서확인복호화함수를사용해암호화된데이터를복호화해서확인한다. -- 대칭키열기 ( 아직안열린경우 ) OPEN SYMMETRIC KEY SECKey01 DECRYPTION BY PASSWORD = Pa$$w0rd -- 복호화함수사용 SELECT CustomerID, Name, CONVERT(varchar, DecryptByKey(EncryptedAccountNo)) AS AccountNo, , Point FROM dbo.testcustomer 결과는다음과같이정상적인값을확인할수있다. 8) 열이름변경만일암호화된데이터를갖는열의이름을기존열의이름으로변경하고싶다면 sp_rename 저장프로시저를사용해변경한다. -- 열이름변경하기 EXEC sp_rename dbo.testcustomer.encryptedaccountno, AccountNo, COLUMN 64

67 9) 암호화된데이터추가암호화된데이터를추가하려는경우다음과같이암호화함수를사용해야한다. -- 대칭키열기 ( 아직안열린경우 ) OPEN SYMMETRIC KEY SECKey01 DECRYPTION BY PASSWORD = Pa$$w0rd -- 데이터추가 INSERT INTO dbo.testcustomer (CustomerID, Name, AccountNo, , Point) VALUES( chikook, N 김치국, EncryptByKey(Key_GUID( SECKey01 ), ), chikook@dbnuri.com, 5500) -- 데이터확인 SELECT CustomerID, Name, CONVERT(varchar, DecryptByKey(AccountNo)) AS AccountNo, , Point FROM dbo.testcustomer 표시되는결과는다음과같다. 10) 테스트완료테스트가완료되었으므로관련개체를모두제거한다. -- 테이블제거 DROP TABLE dbo.testcustomer -- 대칭키제거 DROP SYMMETRIC KEY SECKey01 SQL Server 보안가이드 65

68 SQL Server 의개인정보암호화 1.3 비대칭키를사용한암호화 1) 암호화및복호화방법비대칭키를사용해특정열의데이터를암호화하기위해서는우선비대칭키를만든후관련함수를사용해암호화및복호화를해야한다. 암호화함수 : EncryptByAsymKey() 복호화함수 : DecryptByAsymKey() 2) 연습용테이블만들기우선다음스크립트를사용해연습용테이블을만든다. USE TestDB -- 이미테이블이있으면제거 IF OBJECT_ID( dbo.testcustomer, U ) IS NOT NULL DROP TABLE dbo.testcustomer -- 테이블만들기 SELECT * INTO dbo.testcustomer FROM dbo.customer -- 확인 SELECT * FROM dbo.testcustomer 추가된데이터의내용은다음과같다. 66

69 이제다음과정에따라 TestCustomer 테이블의열중에서 AccountNo 열의데이터를비대칭키를사용해암호화한다. 3) 관련구문비대칭키를만드는 CREATE ASYMMETRIC KEY의전체구문은다음과같다. CREATE ASYMMETRIC KEY Asym_Key_Name [ AUTHORIZATION database_principal_name ] { [ FROM <Asym_Key_Source> ] WITH <key_option> [ ENCRYPTION BY <encrypting_mechanism> ] <Asym_Key_Source> : : = FILE = path_to_strong-name_file EXECUTABLE FILE = path_to_executable_file ASSEMBLY Assembly_Name PROVIDER Provider_Name <key_option> : : = ALRITHM = <algorithm> PROVIDER_KEY_NAME = key_name_in_provider CREATION_DISPOSITION = { CREATE_NEW OPEN_EXISTING } <algorithm> : : = { RSA_512 RSA_1024 RSA_2048 } <encrypting_mechanism> : : = PASSWORD = password SQL Server 보안가이드 67

70 SQL Server 의개인정보암호화 4) 비대칭키만들기위구문을참조해서다음과같이데이터암호화에사용할 SECKey02 라는이름의비대칭키를만든다. -- 비대칭키만들기 CREATE ASYMMETRIC KEY SECKey02 WITH ALRITHM = RSA_ 암호화알고리즘설정 ENCRYPTION BY PASSWORD = Pa$$w0rd -- 복잡한암호지정 5) 새로운열에암호화된데이터기록 EncryptedAccountNo 라는이름의새로운열을추가하고, AccountNo 열의기존데이터를암호화해서 EncryptedAccountNo 열에기록한후기존의 AccountNo 열은제거한다. -- 열추가 ALTER TABLE dbo.testcustomer ADD EncryptedAccountNo varbinary(256) -- 새로운열에암호화된데이터저장 UPDATE dbo.testcustomer SET EncryptedAccountNo = EncryptByAsymKey(AsymKey_ID( SECKey02 ), AccountNo) -- 기존열제거 ALTER TABLE dbo.testcustomer DROP COLUMN AccountNo 6) 암호화된데이터복호화없이확인암호화된데이터를복호화과정없이확인해본다. -- 복호화하지않은데이터확인 SELECT CustomerID, Name, CONVERT(varchar, EncryptedAccountNo) AS AccountNo, , Point FROM dbo.testcustomer 68

71 결과는다음과같이정확한값을확인할수없다. 7) 암호화된데이터복호화해서확인복호화함수를사용해암호화된데이터를복호화해서확인한다. -- 복호화함수사용 SELECT CustomerID, Name, CONVERT(varchar, DecryptByAsymKey(AsymKey_ID( SECKey02 ), EncryptedAccountNo, N Pa$$w0rd )) AS AccountNo, , Point FROM dbo.testcustomer 결과는다음과같이정확한값을확인할수있다. 8) 테스트완료테스트가완료되었으므로관련개체를모두제거한다. -- 테이블제거 DROP TABLE dbo.testcustomer -- 비대칭키제거 DROP ASYMMETRIC KEY SECKey02 SQL Server 보안가이드 69

72 SQL Server 의개인정보암호화 1.4 인증서를사용한암호화 1) 암호화및복호화방법 인증서를사용해특정열의데이터를암호화하기위해서는우선데이터베이스마스터키와인증서를만든후관련함수를사용해암호화및복호화를해야한다. 암호화함수 : EncryptByCert() 복호화함수 : DecryptByCert() 2) 연습용테이블만들기우선다음스크립트를사용해연습용테이블을만든다. USE TestDB -- 이미테이블이있으면제거 IF OBJECT_ID( dbo.testcustomer, U ) IS NOT NULL DROP TABLE dbo.testcustomer -- 테이블만들기 SELECT * INTO dbo.testcustomer FROM dbo.customer -- 확인 SELECT * FROM dbo.testcustomer 추가된데이터의내용은다음과같다. 70

73 이제다음과정에따라 TestCustomer 테이블의열중에서 AccountNo 열의데이터를인증서를사용해암호화한다. 3) 관련구문인증서를만드는 CREATE CERTIFICATE 문의전체구문은다음과같다. CREATE CERTIFICATE certificate_name [ AUTHORIZATION user_name ] { FROM <existing_keys> <generate_new_keys> } [ ACTIVE FOR BEGIN_DIALOG = { ON OFF } ] <existing_keys> : : = ASSEMBLY assembly_name { [ EXECUTABLE ] FILE = path_to_file [ WITH PRIVATE KEY ( <private_key_options> ) ] } { BINARY = asn_encoded_certificate [ WITH PRIVATE KEY ( <private_key_options> ) ] } <generate_new_keys> : : = [ ENCRYPTION BY PASSWORD = password ] WITH SUBJECT = certificate_subject_name [, <date_options> [,...n ] ] <private_key_options> : : = { FILE = path_to_private_key [, DECRYPTION BY PASSWORD = password ] [, ENCRYPTION BY PASSWORD = password ] } { BINARY = private_key_bits [, DECRYPTION BY PASSWORD = password ] [, ENCRYPTION BY PASSWORD = password ] } <date_options> : : = START_DATE = datetime EXPIRY_DATE = datetime SQL Server 보안가이드 71

74 SQL Server 의개인정보암호화 4) 마스터키와인증서만들기위구문을참조해서다음과같이데이터베이스마스터키와데이터암호화에사용할 SECKey03 라는이름의인증서를만든다. -- 마스터키만들기 CREATE MASTER KEY ENCRYPTION BY PASSWORD = Pa$$w0rd -- 복잡한암호지정 -- 인증서만들기 CREATE CERTIFICATE SECKey03 WITH SUBJECT = Test Certificate 5) 새로운열에암호화된데이터기록 EncryptedAccountNo 라는이름의새로운열을추가하고, AccountNo 열의기존데이터를암호화해서 EncryptedAccountNo 열에기록한후기존의 AccountNo 열은제거한다. -- 열추가 ALTER TABLE dbo.testcustomer ADD EncryptedAccountNo varbinary(256) -- 새로운열에암호화된데이터저장 UPDATE dbo.testcustomer SET EncryptedAccountNo = EncryptByCert(Cert_ID( SECKey03 ), AccountNo) -- 기존열제거 ALTER TABLE dbo.testcustomer DROP COLUMN AccountNo 72

75 6) 암호화된데이터복호화없이확인암호화된데이터를복호화과정없이확인해본다. -- 복호화하지않은데이터확인 SELECT CustomerID, Name, CONVERT(varchar, EncryptedAccountNo) AS AccountNo, , Point FROM dbo.testcustomer 결과는다음과같이정확한값을확인할수없다. 7) 암호화된데이터복호화해서확인복호화함수를사용해암호화된데이터를복호화해서확인한다. -- 복호화함수사용 SELECT CustomerID, Name, CONVERT(varchar, DecryptByCert(Cert_ID( SECKey03 ), EncryptedAccountNo)) AS AccountNo, , Point FROM dbo.testcustomer 결과는다음과같이정상적인값을확인할수있다. SQL Server 보안가이드 73

76 SQL Server 의개인정보암호화 8) 테스트완료테스트가완료되었으므로관련개체를모두제거한다. -- 테이블제거 DROP TABLE dbo.testcustomer -- 인증서제거 DROP CERTIFICATE SECKey03 -- 데이터베이스마스터키제거 DROP MASTER KEY 1.5 HASHBYTES 함수를사용한암호화내용작성 1) 암호화방법 HASHBYTES 함수를사용하기위해서는이전의방법과는달리키또는인증서를만들필요가없다. 단지 HASHBYTES 함수를사용해서데이터를암호화해서저장하면된다. 또한복호화함수가없음이이전의다른방법과차이를보인다. 암호화함수 : HASHBYTES() 복호화함수 : 없음 2) 연습용테이블만들기우선다음스크립트를사용해연습용테이블을만든다. USE TestDB -- 이미테이블이있으면제거 IF OBJECT_ID( dbo.testcustomer, U ) IS NOT NULL DROP TABLE dbo.testcustomer -- 테이블만들기 SELECT * 74

77 INTO dbo.testcustomer FROM dbo.customer -- 확인 SELECT * FROM dbo.testcustomer 추가된데이터의내용은다음과같다. 이제다음과정에따라 TestCustomer 테이블의열중에서 AccountNo 열의데이터를비대칭키를사용해암호화한다. 3) 관련구문 HASHBYTES 문과관련된전체구문은다음과같다. HASHBYTES ( <algorithm>, input } ) <algorithm> : : = MD2 MD4 MD5 SHA SHA1 SHA2_256 SHA2_512 SQL Server 보안가이드 75

78 SQL Server 의개인정보암호화 4) 새로운열에암호화된데이터기록 EncryptedPassword라는이름의새로운열을추가하고 HASHBYTES 함수로암호화된데이터를기록한다. -- 열추가 ALTER TABLE dbo.testcustomer ADD EncryptedPassword varbinary(256) -- 새로운열에암호화된데이터저장 UPDATE dbo.testcustomer SET EncryptedPassword = HASHBYTES( SHA2_256, Pa$$w0rd ) 5) 암호화된데이터복호화없이확인암호화된데이터를일반적인방법으로확인해본다. -- 복호화하지않은데이터확인 SELECT CustomerID, Name, AccountNo, , Point, CONVERT(varchar, EncryptedPassword) AS Password FROM dbo.testcustomer 결과는다음과같이정확한값을확인할수없다. 76

79 6) 조건절에서값비교가능복호화함수는없지만다음과같이 WHERE 절에서값이같은지의여부를확인할수있다. SELECT CustomerID, Name, AccountNo, , Point FROM dbo.testcustomer WHERE CustomerID = hong AND EncryptedPassword = HASHBYTES( SHA2_256, Pa$$w0rd ) 결과는다음과같이정확한조건검색결과를보여준다. 7) 테스트완료테스트가완료되었으므로관련개체를모두제거한다. -- 테이블제거 DROP TABLE dbo.testcustomer SQL Server 보안가이드 77

80 SQL Server 의개인정보암호화 2. 데이터베이스암호화 2.1 데이터베이스암호화이해및고려사항 1) 데이터암호화와데이터베이스암호화이전에살펴본특정열에대한데이터암호화방법은정상적인복호화과정없이는해당데이터를조회하지못하도록하기위한방법이다. 이러한데이터암호화방법은암호화되지않은데이터를조회하는것은막을수없으며, 암호화및복호화함수를사용하도록대상쿼리문을모두수정해야한다는부담을가지고있다. SQL Server에서는데이터암호화는별개로데이터베이스수준의암호화방법을제공한다. 데이터베이스수준의암호화를사용하게되면데이터베이스백업도암호화가되기때문에데이터베이스백업이외부로유출된다하더라도복원을할수없게된다. 물론분리된데이터베이스파일이다른서버에연결되는것도원천차단된다. 또한암호화및복호화함수를사용하는것이아니라데이터베이스엔진이암호화및복호화를자동으로수행해주기때문에관련쿼리문을수정할필요가없다. 이러한이유로데이터베이스수준의암호화를투명한데이터암호화 (TDE: Transparent Data Encryption) 라고부르며 Enterprise Edition에서만제공되는기능이다. 참고로 개인정보암호화조치안내서 에보면 TDE 방식에서안전한암호알고리즘을사용하여암호화한다면개인정보보호법에위반되지않음을다음과같이 FAQ 를통해안내하고있다. Q1 공공기관이아닌일반기업입니다. 개인정보처리시스템의 DBMS (DataBase Management System) 에서제공하는 TDE(Transparent Data Encryption) 방식을사용한암호화가개인정보보호법에위배됩니까? 개인정보의안전성확보조치기준 ( 고시 ) 제 7조에따라고유식별정보암호화시안전한알고리즘을사용하도록하고있습니다. TDE 방식에서안전한알고리즘을사용하여암호화한다면법위반사항이아닙니다. Q2 공공기관입니다. 개인정보처리시스템의 DBMS(DataBase Management System) 에서제공하는 TDE(Transparent Data Encryption) 방식을사용한암호화가개인정보보호법에위배됩니까? 78

81 개인정보의안전성확보조치기준 ( 고시 ) 제 7조에따라암호화시안전한알고리즘을사용하도록하고있으므로, TDE 방식에서안전한알고리즘을사용하여암호화한다면법위반은아닙니다. 다만, 공공기관은전자정부법에따라국가정보원이안전성을확인한암호모듈또는제품을우선적용하여야합니다. 자세한사항은 국가정보보안기본지침 을확인하시기바랍니다. Q3 금융기관입니다. 개인정보처리시스템인 DBMS가제공하는 TDE (Transparent Data Encryption) 방식을사용한암호화가개인정보보호법에위배됩니까? 개인정보의안전성확보조치기준 ( 고시 ) 제 7조에따라암호화시안전한알고리즘을사용하도록하고있으므로, TDE 방식에서안전한알고리즘을사용하여암호화한다면법위반은아닙니다. 다만, 전자금융감독규정에서금융기관또는전자금융업자는국가기관의평가 인증을받은장비를사용하도록하고있으므로이를확인하여야합니다. 자세한사항은 전자금융감독규정 을확인하시기바랍니다. 출처 : 2012 년 10 월행정안전부발행 개인정보암호화조치안내서 2) 데이터베이스암호화사용시고려사항데이터베이스암호화는데이터베이스엔진에의해서자동으로수행되는장점이있지만, 암호화와복호화를위해 CPU 사용량이증가된다. 그러므로데이터베이스암호화로인해발생하는성능문제가중요한업무를방해하지않도록정확한성능측정이필요하다. 필요한경우 CPU 등자원증설을검토해야한다. 3) 데이터베이스암호화수행단계데이터베이스수준의암호화를위해서는다음의 4 가지단계가순서적으로수행되어야한다. 1 마스터키만들기 2 인증서만들기 3 데이터베이스암호화키만들기 4 데이터베이스암호화속성설정 이중에서앞의두단계는 master 데이터베이스에서수행해야하며, 나머지두단계는암호화하려는대상데이터베이스에서수행해야한다. SQL Server 보안가이드 79

82 SQL Server 의개인정보암호화 2.2 데이터베이스암호화하기 1) 단계 1: 마스터키만들기데이터베이스암호화를위해서제일먼저 master 데이터베이스에서마스터키를만들어야한다. 이마스터키는이어서만들어지는인증서를보호하는역할을담당한다. 다음과같이마스터키를만든다. -- master 데이터베이스에서수행 USE master -- 마스터키만들기 CREATE MASTER KEY ENCRYPTION BY PASSWORD = Pa$$w0rd -- 복잡한암호지정 2) 단계 2: 인증서만들기다음과같이 master 데이터베이스에서인증서를만든다. 이인증서는앞의과정에서만들어진마스터키로보호되며, 다음과정에서만들어지는데이터베이스암호화키를보호하는역할을담당한다. -- master 데이터베이스에서수행 USE master -- 인증서만들기 CREATE CERTIFICATE TDECert WITH SUBJECT = Server Level Certificate for TDE 3) 단계 3: 데이터베이스암호화키만들기데이터베이스암호화키를만드는 CREATE DATABASE ENCRYPTION KEY 문의전체구문은다음과같다. CREATE DATABASE ENCRYPTION KEY WITH ALRITHM = { AES_128 AES_192 AES_256 TRIPLE_DES_3KEY } ENCRYPTION BY SERVER { 80

83 } [ ; ] CERTIFICATE Encryptor_Name ASYMMETRIC KEY Encryptor_Name 위구문을참조해서다음과같이암호화하고자하는대상데이터베이스에서데이터베이스암호화키를만든다. 이키는앞의과정에서만든인증서로보호되며데이터베이스를암호화할때사용된다. -- 대상데이터베이스에서수행 USE TestDB -- 데이터베이스암호화키 (DEK) 만들기 CREATE DATABASE ENCRYPTION KEY WITH ALRITHM = AES_ 암호화알고리즘지정 ENCRYPTION BY SERVER CERTIFICATE TDECert -- 인증서지정 /* 경고 : 데이터베이스암호화키를암호화하는데사용된인증서가백업되지않았습니다. 인증서와인증서에연결된개인키를즉시백업해야합니다. 인증서를사용할수없게되거나다른서버에서데이터베이스를복원하거나연결해야할경우인증서와개인키의백업본이있어야합니다. 그렇지않으면데이터베이스를열수없습니다. */ 데이터베이스암호화키를만들면위소스하단에포함된경고메시지가표시된다. 데이터베이스암호화키를보호할때사용되는인증서를백업해야한다는내용이다. 데이터베이스를암호화하는경우관련된인증서를필히백업받아서철저히관리되어야한다. 인증서를사용해암호화된데이터베이스의백업으로부터복원하는방법은이후 [2.4 암호화된데이터베이스복원 ] 부분에서다루게된다. SQL Server 보안가이드 81

84 SQL Server 의개인정보암호화 [ 개체탐색기사용 ] 위과정은다음화면과같이 SSMS 에서수행할수도있다. 개체탐색기 대상데이터베이스마우스우측클릭 태스크 (T) 데이터베이스암호화관리 (P) 4) 단계 4: 데이터베이스암호화속성변경 ( 활성화 ) 마지막으로암호화하려는데이터베이스의속성중에서암호화부분을활성화시킨다. -- 데이터베이스암호화속성변경 ALTER DATABASE TestDB SET ENCRYPTION ON 참고로위과정은 [ 단계 3] 의데이터베이스암호화관리화면에서또는다음과같이데이터베이스속성화면에서수행할수도있다. 82

85 5) 데이터베이스암호화과정확인암호화가된또는암호화가진행중인데이터베이스에대한정보는다음과같이확인한다. -- 암호화상태확인 SELECT DB_NAME(database_id) AS db_name, encryption_state FROM sys.dm_database_encryption_keys 위쿼리문의결과로표시되는 encryption_state 값은각각다음과같은의미를가진다. 값설명 데이터베이스암호화키가없고암호화되지않음암호화되지않음암호화진행중암호화됨키변경진행중복호화진행중보호변경진행중데이터베이스암호화키를암호화하는인증서또는비대칭키를변경하고있음 SQL Server 보안가이드 83

86 SQL Server 의개인정보암호화 2.3 데이터베이스백업암호화 지금까지의과정을통해암호화가설정된데이터베이스의백업은자동으로암호화가된다. 그래서다른서버에복원을할수없게된다. 1) 암호화된데이터베이스전체백업만일다음과같이암호화된데이터베이스를전체백업한다고가정한다. -- 데이터베이스전체 (Full) 백업 BACKUP DATABASE TestDB TO DISK = D:\Backup\TestDB_Full.bak -- 백업폴더는미리존재해야함 WITH INIT 2) 암호화된데이터베이스복원시도그리고다음과같이데이터베이스백업파일을다른서버또는동일한서버의다른인스턴스에복원하려고하면소스하단에포함된오류메시지가표시되면서복원이거부된다. USE Master -- 다른서버에서데이터베이스복원시도 RESTORE DATABASE TestDB FROM DISK = D:\Backup\TestDB_Full.bak /* 다중인스턴스환경에서의복원의경우 RESTORE DATABASE TestDB FROM DISK = D:\Backup\TestDB_Full.bak WITH MOVE TestDB TO D:\SmartData\TestDB.mdf, MOVE TestDB_log TO D:\SmartData\TestDB_log.ldf */ /* 메시지 33111, 수준 16, 상태 3, 줄 1 지문이 0x8F61AA8BAD4A1CDA94F63A7BBEBB87FE34DDCB90 인서버인증서을 ( 를 ) 찾을수없습니다. 메시지 3013, 수준 16, 상태 1, 줄 1 RESTORE DATABASE이 ( 가 ) 비정상적으로종료됩니다. */ 84

87 암호화된데이터베이스가분리되어다른서버또는동일서버의다른인스턴스에연결이시도되는경우도동일한오류가발생하면서연결을거부하게된다. 이것이데이터베이스수준의암호화목적중하나이다. 즉유출된데이터베이스파일또는백업파일로부터데이터베이스를복원하지못하게차단한다. 2.4 암호화된데이터베이스복원 만일회사내부에서업무상필요 ( 개발서버에복원또는 SQL Server 재설치후복원 ) 해서다른서버또는동일서버의다른인스턴스에복원을해야하는경우는인증서를백업받아복원을수행해야한다. 1) 개인키를포함해서인증서백업인증서를백업받을때는다음과같이개인키를같이백업해야한다. -- master 데이터베이스에서수행 USE Master -- 개인키를포함해서인증서백업 BACKUP CERTIFICATE TDECert TO FILE = D:\Backup\TDECert.cer WITH PRIVATE KEY (FILE = D:\Backup\TDECert.pvk, ENCRYPTION BY PASSWORD = Pa$$w0rd ) 2) 복원할서버에서마스터키만들고인증서복원그리고다음과같이데이터베이스를복원할새로운서버또는인스턴스에서마스터키를만들고앞의과정에서백업받은인증서를복원해야한다. -- master 데이터베이스에서수행 USE Master -- 마스터키만들기 CREATE MASTER KEY ENCRYPTION BY PASSWORD = Pa$$w0rd -- 백업으로부터인증서만들기 CREATE CERTIFICATE TDECert SQL Server 보안가이드 85

88 SQL Server 의개인정보암호화 FROM FILE = D:\Backup\TDECert.cer WITH PRIVATE KEY (FILE = D:\Backup\TDECert.pvk, DECRYPTION BY PASSWORD = Pa$$w0rd ) 2.5 데이터베이스암호화해제 더이상데이터베이스수준의암호화가필요하지않은경우는다음과정을통해암호화를해제할수있다. 1 데이터베이스암호화속성변경 ( 비활성화 ) 2 데이터베이스암호화키제거 3 인증서제거 4마스터키제거 5 트랜잭션로그완전제거 6 데이터베이스전체백업 1) 데이터베이스암호화비활성화우선다음과같이대상데이터베이스의암호화속성을비활성화시킨다. -- 데이터베이스암호화속성변경 ALTER DATABASE TestDB SET ENCRYPTION OFF 2) 데이터베이스암호화키제거그리고다음과같이해당데이터베이스에포함되어있는데이터베이스암호화키를제거한다. -- 대상데이터베이스에서수행 USE TestDB -- 데이터베이스암호화키제거 DROP DATABASE ENCRYPTION KEY 86

89 3) 인증서제거그리고다음과같이데이터베이스암호화키를보호하고있던인증서를제거한다. 단, 이인증서가다른목적으로사용중이라면제거해서는안되며, 제거될수도없다. -- master 데이터베이스에서수행 USE master -- 인증서제거 ( 불필요한경우만 ) DROP CERTIFICATE TDECert 4) 마스터키제거앞의과정에서인증서를제거했으면이인증서를보호할때사용되었던마스터키를제거한다. 단이마스터키가다른목적으로사용되고있다면제거해서는안된다. -- master 데이터베이스에서수행 USE master -- 마스터키제거 DROP MASTER KEY 5) 트랜잭션로그완전제거마지막으로암호화되었던데이터베이스에포함되어있는트랜잭션로그를모두제거한다. 암호화는해제되었지만기존의트랜잭션로그가암호화되어기록되어있으므로이를모두제거해야한다. 다음과같이복구모델을단순 (Simple) 로변경하고 CHECKPOINT 명령을두세번발생시켜모든로그가제거되게한후복구모델을원래대로변경한다. -- 복구모멜을단순으로변경 ALTER DATABASE TestDB SET RECOVERY Simple -- 6) 해당데이터베이스에대한체크포인트발생시켜로그를완전히삭제 SQL Server 보안가이드 87

90 SQL Server 의개인정보암호화 USE TestDB -- 두세번실행시킴 CHECKPOINT -- 7) 복구모델원래대로변경 -- 복구모델을원래대로변경 ALTER DATABASE TestDB SET RECOVERY Full 6) 데이터베이스백업암호화가제거된데이터베이스는전체백업을다시수행해서보관하도록한다. 88

91 4 SQL Server 의접속기록의위 변조방지 SQL Server 보안가이드 89

92 90

93 4 장. SQL Server 의접속기록의위 변조방지 개인정보의기술적ㆍ관리적보호조치기준 제 5조를보면접속기록의위ㆍ변조방지에대하여다음과같이언급하고있다. 제 5조 : 접속기록의위ㆍ변조방지 ( 감사 ) 1 정보통신서비스제공자등은개인정보취급자가개인정보처리시스템에접속한기록을월 1회이상정기적으로확인 감독하여야하며, 시스템이상유무의확인등을위해최소 6개월이상접속기록을보존 관리하여야한다. 출처 : 방송통신위원회고시제 호 ( ) 개인정보의기술적ㆍ관리적보호조치기준 즉개인정보처리시스템에접속한기록을 6개월이상보존해야하며, 해당기록을월 1회이상정기적으로확인해야한다. SQL Server는 SQL Server 감사기능을사용해서중요데이터에대한접근기록을남길수있으며, 이와연관된다양한관리방법을제공하고있다. 1. SQL Server 감사 1.1 SQL Server 감사에대한이해 SQL Server 감사는중요한데이터에대한액세스를감시하여데이터유출사고가발생한경우그원인을파악하는데중요한근거데이터를확보하게해준다. 데이터유출이발생하지않았다하더라도회사내부의데이터가어떻게액세스가되고있는지의상황을파악할수있는근거데이터가될수도있다. 1) 이전버전의감사방법이전버전에서는다음과같은방법을사용해감사정보를기록할수있었다. SQL Server 프로파일러또는 Trace 기능사용 DML 또는 DDL 트리거사용 SQL Server 보안가이드 91

94 SQL Server 의접속기록의위 변조방지 이중에서프로파일러또는 Trace 기능의경우는서버에부하를상당히주는문제가있어튜닝과같은특수한상황에서주로사용되며, 트리거의경우는 SELECT 문을추적할수없고, 역시트랜잭션처리에성능저하를가져올수있는문제를가지고있어, 일반적인감사용도로는사용할수없다. 2) SQL Server 감사의이점및고려사항 SQL Server 감사는위방법들의문제점과단점을해결해주는감사전용기능으로써제공되는매우유용한기능이다. 특히 SELECT 문또는 EXECUTE 문을추적할수있어가장적절한감사용기능이라할수있다. 하지만너무나많은정보를 SQL Server 감사를통해기록하려고하면이로인한부담이발생할수있다. 많은정보를기록해야하는 SQL Server의부담과함께대량으로기록되어보관되는감사정보를모니터링하는관리자자신에게도큰부담이된다. 3) 감사의범위그렇다면어떤정보를우선적으로감사대상으로선정해야할까? 개인정보의기술적ㆍ관리적보호조치기준해설서 에서는다음과같은예시와함께접속기록항목으로정보주체식별정보, 개인정보취급자식별정보, 접속일시, 접속자정보, 부여된권한유형에따른수행업무등을포함해야한다고설명하고있다. [ 접속기록항목 ( 예시 )] 정보주체식별정보 취급자식별정보 접속일시 접속지 수행업무 홍길동 (HGD) , 15:00: 조회 ( 고객응대 ) 출처 : 2012년 9월방송통신위원회발행 개인정보의기술적ㆍ관리적보호조치기준해설서 그러므로 SQL Server 감사에서도최소한위와같은기록을확인할수있도록구성하여야한다. 1.2 감사와감사사양 SQL Server 감사기능을사용하기위해서는감사와감사사양에대한용어구분이필요하다 1) 감사 SQL Server 감사에서서버감사 ( 보통 [ 감사 ] 라고칭함 ) 는모니터링하고자하는이벤트에대한로그를어디에기록할지정의한것이며감사별로다음중한가지를선택할수있다. 파일 Windows 응용프로그램로그 Windows 보안로그 92

95 이중에서별도의파일로기록을저장하는방법이가장일반적이고효과적이다. Windows 응용프로그램로그또는보안로그에기록하게되면다른 Windows 응용프로그램의로그와같이기록되므로감사기록을개별적으로관리하기가번거롭다. 또한 Windows 보안로그에기록하기위해서는 SQL Server 서비스계정에대한별도의권한을부여해주어야한다. 2) 감사사양감사사양은어떤이벤트에대한정보를감사에기록할지를정의하는것으로다음과같이두가지수준으로구분할수있다. 서버감사사양 서버수준의이벤트에대한감사기록을정의하는것으로로그인실패, 또는전체데이터베이스에대한액세스등이그예가된다. 데이터베이스감사사양 특정데이터베이스수준의이벤트에대한감사기록을정의하는것으로특정테이블또는스키마에포함된개체들에대한 SELECT, 특정프로시저 EXECUTE 등이그예가된다. SQL Server 보안가이드 93

96 SQL Server 의접속기록의위 변조방지 2. 서버감사만들기 SQL Server 감사기능을사용하기위해서는제일먼저서버감사를만들어야한다. SQL Server에서제공하는감사는서버감사이며, 데이터베이스감사는제공되지않는다. 감사와감사사양은다른것임을앞에서이야기했다. 2.1 서버감사만들기 서버감사를만드는 CREATE SERVER AUDIT 문의전체구문은다음과같다. CREATE SERVER AUDIT audit_name { TO { [ FILE (<file_options> [,...n ] ) ] APPLICATION_LOG SECURITY_LOG } [ WITH ( <audit_options> [,...n ] ) ] [ WHERE <predicate_expression> ] } [ ; ] <file_options> : : = { FILEPATH = os_file_path [, MAXSIZE = { max_size { MB GB TB } UNLIMITED } ] [, { MAX_ROLLOVER_FILES = { integer UNLIMITED } } { MAX_FILES = integer } ] [, RESERVE_DISK_SPACE = { ON OFF } ] } <audit_options> : : = { [ QUEUE_DELAY = integer ] [, ON_FAILURE = { CONTINUE SHUTDOWN FAIL_OPERATION } ] [, AUDIT_GUID = uniqueidentifier ] } <predicate_expression> : : = { [NOT ] <predicate_factor> [ { AND OR } [NOT ] { <predicate_factor> } ] [,...n ] } <predicate_factor> : : = event_field_name { = < >! = > > = < < = } { number string } 94

97 [T-SQL 문사용 ] < 기본옵션으로서버감사만들기 > 서버감사는필히 master 데이터베이스에서만들어야한다. 다음과같이가장기본적인방법으로 [ 고객정보감사 ] 와 [ 로그인감사 ] 를만든다. [ 고객정보감사 ] 는고객데이터액세스에대한감사를, [ 로그인감사 ] 는 SQL Server 로그인에대한감사를기록할목적이다. 단, 감사정보가기록될폴더 ( 본서에서는 D:\Audit) 는미리존재하고있어야한다. -- master 데이터베이스에서수행해야함 USE master -- 테이블액세스감사용 CREATE SERVER AUDIT 고객정보감사 TO FILE (FILEPATH = N D:\Audit ) -- 지정된폴더가미리존재해야함 -- 로그인감사용 CREATE SERVER AUDIT 로그인감사 TO FILE (FILEPATH = N D:\Audit ) -- 지정된폴더가미리존재해야함 < 참고 : 다양한옵션으로서버감사만들기 > 참고로다음과같이서버감사를만들때다양한옵션을제공할수있다. 이미앞에서서버감사를만들었으니다음스크립트를수행하지말고참고만하자. 이옵션들에대한설명은인터페이스를사용한방법에서설명하겠다. -- 참고 : 다양한옵션을주어감사만들기 CREATE SERVER AUDIT 고객정보감사 TO FILE ( FILEPATH = N D:\Audit, MAXSIZE = 0 MB, MAX_ROLLOVER_FILES = , RESERVE_DISK_SPACE = OFF ) WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE SQL Server 보안가이드 95

98 SQL Server 의접속기록의위 변조방지 ) < 참고 : Windows 응용프로그램로그에기록되는서버감사만들기 > 만일 Windows 응용프로그램로그에감사정보가기록되게하려면다음과같이하면된다. -- 참고 : Windows 응용프로그램로그에기록하는감사만들기 CREATE SERVER AUDIT 고객정보감사 TO APPLICATION_LOG [ 개체탐색기사용 ] 개체탐색기 보안 감사마우스우측클릭 새감사 (N) 96

99 위화면의항목들의의미는다음과같다. 항목설명 감사이름 감사이름지정 큐지연 ( 밀리초 ) 이벤트가발생한후감사에기록되기까지허용된지연시간 (0으로설정하면실시간으로저장되어서버에부하가가중될수있음 ) 감사로그실패시 계속 : 로그기록을무시하고사용자의작업이정상적으로진행됨 서버종료 : 서버가강제종료됨 실패작업 : 감사대상작업은종료되며나머지작업은정상적으로진행됨 감사대상 파일, 보안로그, 응용프로그램로그중에서선택 파일경로 감사대상이파일인경우파일을저장할폴더지정 감사파일최대제한 감사대상이파일인경우해당됨 최대롤오버파일수파일수에제한을두면최대파일수에도달하면롤오버되면서과거기록이제거되면서새로운로그가기록됨 ( 과거로그유실발생가능 ) 최대파일수파일수에제한을두면최대파일수에도달하면로그기록이실패함 ( 과거로그유실방지 ) 최대파일크기 파일의최대크기지정 디스크공간예약 디스크에공간을미리예약할지지정 SQL Server 보안가이드 97

100 SQL Server 의접속기록의위 변조방지 2.2 서버감사활성화 위과정으로만들어진서버감사는기본적으로비활성화된상태로남는다. 서버감사를사용하기위해서는다음과같이활성화과정을수행해야한다. -- 감사활성화 ALTER SERVER AUDIT 고객정보감사 WITH (STATE = ON) ALTER SERVER AUDIT 로그인감사 WITH (STATE = ON) 3. 데이터베이스감사사양 TestDB 데이터베이스의 Customer 테이블에대한데이터액세스를감사할목적으로데이터베이스감사사양을만든다. 3.1 데이터베이스감사사양만들기 데이터베이스감사사양을만드는 CREATE DATABASE AUDIT SPECIFICATION 문의전체구문은다음과같다. CREATE DATABASE AUDIT SPECIFICATION audit_specification_name { FOR SERVER AUDIT audit_name [ { ADD ( { <audit_action_specification> audit_action_group_name } ) } [,...n] ] [ WITH ( STATE = { ON OFF } ) ] } [ ; ] <audit_action_specification> : : = { action [,...n ]ON [ class : : ] securable BY principal [,...n ] } 98

101 [T-SQL 문사용 ] 위구문을참조해서다음과같이 [ 고객정보감사사양 ] 이라는이름의데이터베이스감사사양을만든다. 이때감사정보가기록될 [ 고객정보감사 ] 를지정하고, 추적하고자하는이벤트, 즉 Customer 테이블에대한 SELECT 문을추가한다. 모든데이터베이스사용자가 Customer 테이블을 SELECT, UPDATE, DELETE 할때기록을남길목적이므로 Public 데이터베이스역할을지정한다. -- 대상데이터베이스에서수행 USE TestDB CREATE DATABASE AUDIT SPECIFICATION 고객정보감사사양 FOR SERVER AUDIT 고객정보감사 -- 기록할대상감사지정 ADD (SELECT ON OBJECT : : dbo.customer BY Public) -- 대상동작또는동작그룹지정 WITH (STATE = ON)-- 즉시활성화또는비활성화지정 [ 개체탐색기사용 ] 개체탐색기 데이터베이스 대상데이터베이스 보안 데이터베이스감사사양마우스우측클릭 새데이터베이스감사사양 (N) SQL Server 보안가이드 99

102 SQL Server 의접속기록의위 변조방지 3.2 데이터액세스테스트 앞의과정에서만든 [ 고객정보감사사양 ] 에의해서 [ 고객정보감사 ] 에 Customer 테이블에대한 SELECT 및 UPDATE 이력이기록되는지확인하기위해다음과같이다양한쿼리문을수행한다. 이때 SQL Server 감사의특징을확인하기위해조건에해당하는데이터가없는 SELECT 문, 뷰, 저장프로시저호출등도포함시킨다. USE TestDB -- 테이블전체조회 SELECT * FROM dbo.customer -- 데이터변경 UPDATE dbo.customer SET = hankook@sqlroad.com WHERE CustomerID = han -- 대상건이없는조회 SELECT * FROM dbo.customer WHERE CustomerID = jangrae -- 관련뷰조회 SELECT * FROM dbo.vw_customer_top_2 -- 관련저장프로시저호출 EXECUTE = hong -- 대상건이없는저장프로시저호출 EXECUTE = jangrae 100

103 3.3 감사정보확인 앞의과정에서수행된쿼리문이감사에기록이되었는지확인해본다. [T-SQL 문사용 ] 우선다음과같이 sys.fn_get_audit_file 함수를사용해서감사로그의내용을확인할수있다. 대상감사파일의경우는 D:\Audit\ 고객정보 * 와같이파일이름의일부만지정해도된다. SELECT event_time AS EventTime, session_id AS SessionID, server_principal_name AS User, object_id AS ObjectID, object_name AS TableName, statement AS ActionDetail(Statement) FROM sys.fn_get_audit_file ( D:\Audit\ 고객정보 *, DEFAULT, DEFAULT) WHERE action_id <> AUSC 결과는다음과같다. 결과에서보듯 Customer 테이블에대해서앞에서수행된모든쿼리문이감사에기록이된다. 조건절을만족하는데이터가없다하더라도기록되며, 테이블을직접액세스하지않더라도해당테이블과연관된뷰또는저장프로시저등의개체를통한데이터액세스도모두감사대상이된다. 참고로 UPDATE문은대상데이터를찾아변경하므로내부적인 SELECT시에도기록이되며 UPDATE 시에도기록이되어두개의로그가표시된다. 또한데이터베이스감사사양에서는접속한클라이언트의 IP 주소는로그에기록이되지않는다. 하지만이후에살펴볼서버감사사양을기록하면클라이언트의 IP 주소를확인할수있다. SQL Server 보안가이드 101

104 SQL Server 의접속기록의위 변조방지 [ 개체탐색기사용 ] 개체탐색기 보안 감사 고객정보감사마우스우측클릭 감사로그보기 (V) 3.4 감사정보테이블에기록하기 파일로저장된감사로그를테이블로저장하고자한다면 SELECT INTO 문을사용하면된다. 다음과같이 sys.fn_get_audit_file 함수를사용해감사파일의내용을조회한결과를 dbo.audit_001 테이블로저장한다. SELECT INTO 문은쿼리문의결과를새로운테이블을만들면서기록하는기능을가지므로대상테이블이이미존재하면오류가발생한다. SQL Server 에이전트를사용한자동화기능을활용하면일별감사로그를테이블로기록하는것을충분히자동화할수있다. -- 테이블에기록하기 SELECT event_time AS EventTime, session_id AS SessionID, server_principal_name AS User, object_id AS ObjectID, object_name AS TableName, statement AS ActionDetail(Statement) INTO dbo.audit_ 대상테이블지정 ( 새로만들어짐 ) 102

105 FROM sys.fn_get_audit_file ( D:\Audit\ 고객정보 *, DEFAULT, DEFAULT) WHERE action_id <> AUSC 4. 서버감사사양 앞의과정에서살펴본데이터베이스감사사양은특정데이터베이스에대한이벤트를감사할수있으며, 서버수준의이벤트를감사하기위해서는서버감사사양을만들어야한다. 4.1 서버감사사양만들기 서버감사사양을만드는 CREATE SERVER AUDIT SPECIFICATION 문의전체구문은다음과같다. CREATE SERVER AUDIT SPECIFICATION audit_specification_name FOR SERVER AUDIT audit_name { { ADD ( { audit_action_group_name } ) } [,...n] [ WITH ( STATE = { ON OFF } ) ] } [ ; ] [T-SQL 문사용 ] 위구문을참조해다음과같이 [ 로그인감사사양 ] 이라는이름의서버감사사양을만든다. 감사가기록된대상으로앞의과정에서만들었던 [ 로그인감사 ] 를지정하고모든로그인을감사하기위해서 SUCCESSFUL_LOGIN_GROUP을대상동작으로지정한다. USE master -- master 데이터베이스에서수행 CREATE SERVER AUDIT SPECIFICATION 로그인감사사양 FOR SERVER AUDIT 로그인감사 ADD (SUCCESSFUL_LOGIN_GROUP) WITH (STATE = ON) SQL Server 보안가이드 103

106 SQL Server 의접속기록의위 변조방지 [ 개체탐색기사용 ] 개체탐색기 보안 서버감사사양마우스우측클릭 새서버감사사양 (N) 4.2 감사로그확인 로그인에대한감사가제대로수행되는지확인하기위해감사가설정된 SQL Server에몇번로그인을해본다. 그리고다음과같이 sys.fn_get_audit_file 함수를사용해감사로그의내용을확인한다. 참고로로그인기록에는클라이언트의 IP 주소가포함된추가정보가 XML 형태로포함되어있으며, 다음과같이 XQuery를사용해 IP 주소만을추출할수있다. WITH XMLNAMESPACES(DEFAULT _data ) SELECT event_time AS EventTime, session_id AS SessionID, server_principal_name AS User, CAST(additional_information AS xml).value( (action_info/address/text())[1], varchar(255) ) AS ClientIPAddress FROM sys.fn_get_audit_file ( D:\Audit\ 로그인 *, DEFAULT, DEFAULT) WHERE action_id <> AUSC 104

107 결과는다음과같다. 만일앞에서살펴본데이터액세스에대한감사와로그인에대한감사가같이수행된경우다음과같이두정보를연결해서필요한결과를얻을수있다. WITH XMLNAMESPACES(DEFAULT _data ) SELECT event_time AS EventTime, session_id AS SessionID, server_principal_name AS User, object_id AS ObjectID, object_name AS TableName, statement AS ActionDetail(Statement), t2.clientipaddress FROM sys.fn_get_audit_file ( D:\Audit\ 고객정보 *, DEFAULT, DEFAULT) AS t1 CROSS APPLY ( SELECT TOP (1) CAST(additional_information AS xml).value( (action_info/address/text())[1], varchar(255) ) AS ClientIPAddress FROM sys.fn_get_audit_file ( D:\Audit\ 로그인 *, DEFAULT, DEFAULT) WHERE server_principal_sid = t1.server_principal_sid AND session_id = t1.session_id ) AS t2 WHERE action_id <> AUSC 결과는다음과같다. SQL Server 보안가이드 105

108 SQL Server 의접속기록의위 변조방지 4.3 감사에서필터링사용하기 SQL Server 2012부터는서버감사에필터링기능이추가되었다. 예를들어앞의 [ 로그인감사 ] 에는모든로그인에대한내용이기록된다. 서버감사필터링기능을사용해 sa 계정에대한로그인만기록되게할수있다. 참고로원래 SQL Server 감사기능은 Enterprise Edition 에서제공되는기능이지만, SQL Server 2012 에서는 Standard Edition에서서버감사사양을사용할수있다. 서버감사사양을사용해특정데이터베이스또는특정개체에대한감사만을수행하고싶다면서버감사의필터링기능을사용해원하는개체에대한감사만기록되게설정할수있다. 감사에필터조건추가 다음과같이 [ 로그인감사 ] 를비활성화한후 sa 계정에대한로그인만을감사하도록서버감사에조건을추가한후 [ 로그인감사 ] 를다시활성화한다. USE master ALTER SERVER AUDIT 로그인감사 WITH (STATE = OFF) ALTER SERVER AUDIT 로그인감사 WHERE server_principal_name = sa ALTER SERVER AUDIT 로그인감사 WITH (STATE = ON) 106

109 서버감사에조건을추가하는것은다음과같이서버감사속성화면에서수행할수있다. 5. 감사및감사사양제거 더이상감사및감사사양이필요하지않다면다음의순서로진행하면된다. 1 서버및데이터베이스감사사양비활성화 2 서버및데이터베이스감사사양제거 3서버감사비활성화 4서버감사제거 5.1 감사사양비활성화및제거 다음과같이지금까지사용했던서버감사사양과데이터베이스감사사양을비활성화한후제거한다. USE master -- 서버감사사양비활성화 SQL Server 보안가이드 107