레이블이 MSSQL인 게시물을 표시합니다. 모든 게시물 표시

2012-11-23

scripts] ms sql server total size check

'// sql server
'// Displays the total size of a SQL Server database
'//

strDBServerName = "database server name"
strDBName = "ScriptingGuysTestDB"

Set objSQLServer = CreateObject("SQLDMO.SQLServer")
objSQLServer.LoginSecure = True
objSQLServer.Connect strDBServerName

Set objDB = objSQLServer.Databases(strDBName)
WScript.Echo "Total Size of Data File + Transaction Log of DB " & strDBName & ": " & objDB.Size & "(MB)"

2012-11-03

DB/MSSQL] convert/ cast 함수

mssql의 convert()/cast() 함수

convert(), cast() 는 둘다 데이터를 표현할때 변환해서 보여주는 함수로 cast()는 ansi sql을 지원한다.

convert(datatype[length], expression, [style])

cast(expression as datatype)
   expression : 표현식, 테이블의 필드, 문자열 등이 올 수 있다.
   datatype : sql에서 지원하는 데이터타입을 말한다. int, char(), varchar() 등등...
   style : expression이 datetime 형식일 경우 주로 사용되는 것으로 각 나라의 표현 형식을 지정할 때 사용한다.
한국의 경우 주로 121, 21이 사용되는 경우가 많다.

예제) titles 테이블에서 제목과 판매일자를 가지고 온다.

SELECT SUBSTRING(title, 1, 25) AS Title, CAST(ytd_sales AS char(2))
FROM titles
WHERE type = ''trad_cook''

DB / MSSQL ] 인덱스를 이용한 조건검색

인덱스의 유무에 따라서 데이터를 찾는 성능의 차이가 천차만별이 될 수도 있으나 또한 너무 남용을 하게 되면 데이터의 크기보다 인덱스의 크기가 커지는 배보다 배꼽이 더 큰경우가 발생하는 경우도 있으니 유의해서 사용을 해야된다.

인덱스는 테이블에서 어느 하나의 필드, 혹은 필드들의 조합으로 생성을 할 수 있다.
이 생성된 인덱스를 사용하게 하는 것이 쿼리 작성자의 능력이다.

이제 조건 검색에서 주의할 사항을 알아보자.

1. 조건검색시 ''='' 은 모든 인덱스검색중 최우선 순위를 가진다.

2. 조건 검색시 좌변인덱스는 가공하지 말고, 우변을 가공해서 조건을 생성할 것.
    단, 의도적인 인덱스 사용을 막고자 하는 경우는 예외.

3. 조건 검색시 부정의 표현을 사용하지 말고 긍정표현 및 범위 조건을 사용할것.
     다시말해, ''not'', ''<>'', ''or'' 이런 조건들은 될 수 있으면 긍정적인 표현으로 바꾸어서 사용할 것.
     ''<>'' => exists로 대체, ''not'' A => not (A) 대체
4. 인덱스가 있는 곳과 없는곳의 테이블을 조인하여 결과 검색시, 인덱스가 없는곳의 데이터가 먼저 읽혀지고 다음에 인덱스가 있는 테이블의 데이터가 읽혀진다.

5. 가급적 조건에 ''Null'', ''Not Null'' 을 없애기 위해 테이블 설계시, 결합인덱스의 구성컬럼이 된다면 Not Null로, 또한 입력조건값으로 자주 사용되는 것이면 Not Null로 설계를 하여서 조건검색시 Not Null, Null이란 조건을 가급적 없앤다.

위의 사항만을 유념해서 검색을 하더라도 select 쿼리 수행 시간을 줄일 여지가 많이 있다.

===========================================================================

쿼리문의 속도 향상 방법.

부분범위처리를 사용한다.

부분범위처리란

- 조건을 만족하는 전체집합이 아닌 일부분만을 Access 하게 하는것
- Data량이 많아도 퍼포먼스에는 지장이 없고, 오히려 향상을 가져올 수도 있다.
- 인덱스나 클러스트를 적절히 활용하여 sort를 대체가능하다.
- table은 액세스하지 않고 index만 사용하도록 유도
- exists를 활용한다.
- query를 이원화 하여 일부분씩 scan하도록 유도

위의 것들을 조합하여 활용하면 쿼리문을 작성하여 대량의 데이터를 처리할 경우에도 무리없이 원하는 속도를 얻을 수 있을 것이다.

2012-09-17

DBMS/SQL] 쿼리문의 속도 향상 시리즈.1

부분범위처리를 사용한다.

부분범위처리란
   - 조건을 만족하는 전체집합이 아닌 일부분만을 Access 하게 하는것
   - Data량이 많아도 퍼포먼스에는 지장이 없고, 오히려 향상을 가져올 수도 있다.
   - 인덱스나 클러스트를 적절히 활용하여 sort를 대체가능하다.
   - table은 액세스하지 않고 index만 사용하도록 유도
   - exists를 활용한다.
   - query를 이원화 하여 일부분씩 scan하도록 유도

위의 것들을 조합하여 활용하면 쿼리문을 작성하여 대량의 데이터를 처리할 경우에도 무리없이
원하는 속도를 얻을 수 있을 것이다.

DBMS/SQL]인덱스를 이용한 조건검색

인덱스란 데이터베이스에서 데이터를 빠르게 찾을 수 있게 도와주는 도구로 흔히 책의 차례에 비유된다.

인덱스의 유무에 따라서 데이터를 찾는 성능의 차이가 천차만별이 될 수도 있으나 또한 너무 남용을 하게 되면 데이터의 크기보다 인덱스의 크기가 커지는 배보다 배꼽이 더 큰경우가 발생하는 경우도 있으니 유의해서 사용을 해야된다.

인덱스는 테이블에서 어느 하나의 필드, 혹은 필드들의 조합으로 생성을 할 수 있다.

이 생성된 인덱스를 사용하게 하는 것이 쿼리 작성자의 능력이다.

이제 조건 검색에서 주의할 사항을 알아보자.

1. 조건검색시 ''='' 은 모든 인덱스검색중 최우선 순위를 가진다.

2. 조건 검색시 좌변인덱스는 가공하지 말고, 우변을 가공해서 조건을 생성할 것.
단, 의도적인 인덱스 사용을 막고자 하는 경우는 예외.

3. 조건 검색시 부정의 표현을 사용하지 말고 긍정표현 및 범위 조건을 사용할것.
다시말해, ''not'', ''<>'', ''or'' 이런 조건들은 될 수 있으면 긍정적인 표현으로 바꾸어서 사용할 것.
''<>'' => exists로 대체, ''not'' A => not (A) 대체

4. 인덱스가 있는 곳과 없는곳의 테이블을 조인하여 결과 검색시, 인덱스가 없는곳의 데이터가 먼저 읽혀지고 다음에 인덱스가 있는 테이블의 데이터가 읽혀진다.

5. 가급적 조건에 ''Null'', ''Not Null'' 을 없애기 위해 테이블 설계시, 결합인덱스의 구성컬럼이 된다면 Not Null로, 또한 입력조건값으로 자주 사용되는 것이면 Not Null로 설계를 하여서 조건검색시 Not Null, Null이란 조건을 가급적 없앤다.

DBMS/MSSQL]새로운 SQL 잘라내기 공격 및 대처 방법

새로운 SQL 잘라내기 공격 및 대처 방법

이 기사에서 다루는 내용:

식별자 및 문자열 구분
유용한 T-SQL 함수
잘라내기 및 수정 공격
버그 및 취약점 찾기

이 기사에서 사용하는 기술:
SQL Server

SQL 주입을 이용하는 공격은 방화벽과 침입 검색 시스템을 통과해서 데이터 계층을 손상시킬 수 있다는 점 때문에 많은 관심을 끌었습니다. 기본 코드 패턴을 보면 1차 또는 2차 주입 모두 문을 생성할 때 신뢰할 수 없는 데이터를 사용한 경우에 발생하는 다른 주입 문제와 비슷합니다. 대부분의 개발자는 백 엔드에서 매개 변수가 있는 SQL 쿼리를 저장 프로시저와 함께 사용하여 웹 프런트 엔드의 취약점을 완화하고 있지만, 사용자 입력 기반의 DDL(데이터 정의 언어) 문을 생성하는 경우 또는 C/C++로 작성된 응용 프로그램의 경우에는 동적으로 생성된 SQL을 여전히 사용하고 있습니다.

이 기사에서는 구분 문자가 이스케이프된 코드일지라도 SQL 문을 수정하거나 SQL 코드를 주입할 수 있는 몇 가지 새로운 아이디어에 대해 설명합니다. 구분 식별자와 SQL 리터럴을 생성하는 유용한 방법을 몇 가지 살펴본 다음 응용 프로그램을 보호하는 데 도움이 될 수 있도록 공격자가 SQL 코드를 삽입할 때 사용하는 새로운 방법을 설명합니다.

식별자 및 문자열 구분

SQL Server™에는 테이블, 뷰 및 저장 프로시저와 같은 SQL 개체를 고유하게 식별하는 SQL 식별자와 데이터를 나타내는 리터럴 문자열이라는 두 가지 문자열 변수가 있습니다. SQL 식별자를 구분하는 방법은 데이터 문자열을 구분하는 방법과는 다릅니다. 이러한 데이터 변수를 사용해야 하는 동적 SQL을 생성하는 유용한 방법을 살펴보겠습니다.

SQL 개체 이름에 키워드가 사용되거나 개체 이름에 특수 문자가 들어 있는 경우 구분 식별자를 사용해야 합니다. my_dbreader라는 이름의 로그인을 삭제한다고 가정해 봅시다. 이 경우 다음 문을 실행하여 작업을 수행할 수 있습니다.

DROP LOGIN my_dbreader

키워드이기도 한 DROP을 이름으로 사용하는 로그인을 삭제하려는 경우에는 어떻게 해야 합니까? 다음 SQL 문을 사용하면 SQL Server에서 잘못된 구문 오류가 반환됩니다.

DROP LOGIN DROP

my][dbreader라는 이름의 로그인을 삭제하려는 경우에는 어떻게 합니까? 이 경우에도 잘못된 구문 오류가 반환됩니다.
두 예제 모두 로그인 이름이 키워드이거나 로그인 이름에 특수 문자가 들어 있기 때문에 SQL Server에서 SQL 문에 있는 개체 이름을 식별할 수 있도록 시작 및 끝 표시를 입력해야 합니다.

큰따옴표나 대괄호를 SQL 식별자의 구분 기호로 사용할 수 있지만 연결 기반 설정인 QUOTED_IDENTIFIER 설정을 사용하도록 설정한 경우에는 큰따옴표만 사용할 수 있습니다. 복잡하지 않도록 하기 위해 항상 대괄호를 사용하는 것도 좋은 방법입니다.
로그인 이름인 DROP을 삭제하기 위해 다음과 같이 대괄호를 사용하여 SQL 문을 생성할 수 있습니다.

DROP LOGIN [DROP]

그러나 다음 문은 어떻게 처리되겠습니까?

DROP LOGIN [my][dbreader]

이와 같은 특별한 경우에는 로그인 이름인 my][dbreader에 구분 문자가 들어 있으므로 SQL에서는 대괄호로 둘러싸인 [my]를 로그인 이름으로 간주합니다. 로그인 이름 뒤에 오는 [dbreader]는 올바른 SQL 문이 아니기 때문에 구문 오류가 발생합니다. 오른쪽 대괄호를 하나 더 사용하여 오른쪽 대괄호를 이스케이프하면 이 문제를 해결할 수 있습니다. 따라서 다음 문을 실행하면 SQL Server에서 로그인 my][dbreader가 삭제됩니다.

DROP LOGIN [my]][dbreader]

이스케이프 메커니즘은 간단히 오른쪽 대괄호를 두 번 표시하는 것입니다. 왼쪽 대괄호를 포함한 다른 어떤 문자도 변경할 필요가 없습니다.
구분 리터럴을 사용하는 것은 구분 SQL 식별자를 사용하는 것과 비슷하지만 사용해야 하는 구분 문자가 다르다는 데 기본적으로 차이가 있습니다. 비슷한 규칙을 사용하여 구분 문자열 리터럴을 만들기 전에 몇 가지 예제를 살펴보겠습니다.
암호가 P@$$w0rd인 로그인 이름 dbreader를 만든다고 가정해 봅시다. 이 경우 다음 SQL 문을 사용할 수 있습니다.

CREATE LOGIN [dbreader] WITH PASSWORD = 'P@$$w0rd'

이 문에서 P@$$w0rd는 작은따옴표로 구분된 문자열 데이터이므로 SQL에서 문자열의 시작과 끝이 인식됩니다. 그러나 문자열 데이터에 작은따옴표가 들어 있다면 어떻게 되겠습니까? 문이 유효하지 않기 때문에 SQL Server에서 오류가 발생합니다.

CREATE LOGIN [dbreader] WITH PASSWORD = 'P@$$'w0rd'

유효한 SQL 문을 만들려면 문자열에 있는 모든 작은따옴표를 이스케이프해야 합니다.

CREATE LOGIN [dbreader] WITH PASSWORD = 'P@$$''w0rd'

이 문을 실행하면 SQL Server에서 암호가 P@$$'w0rd인 로그인 dbreader가 만들어집니다.
큰 따옴표를 구분 기호로 사용할 수도 있지만 앞에서 설명했듯이 이 방법의 성공 여부는 전적으로 QUOTED_IDENTIFIER 설정의 사용 여부에 달려 있습니다. 결과적으로 항상 작은따옴표를 문자열 리터럴의 구분 기호로 사용하는 것이 좋습니다.

T-SQL 함수

지금까지 살펴본 것처럼 식별자와 문자열을 다루는 규칙은 비교적 간단하며 문자열을 미리 알고 있으면 수동으로 구분할 수 있습니다. 그러나 사용자 입력 기반의 동적 T-SQL 문을 생성할 경우에는 어떻겠습니까? 자동으로 이 작업을 수행할 수 있는 방법이 있어야 합니다. 구분 문자열을 준비하는 데 도움이 되는 QUOTENAME과 REPLACE라는 2개의 T-SQL 함수를 사용할 수 있습니다.
QUOTENAME은 입력 문자열을 유효한 식별자로 만들기 위해 구분 기호가 추가된 유니코드 문자열을 반환합니다. QUOTENAME 함수는 다음 구문을 사용합니다.

QUOTENAME ( 'string' [ , 'delimiter' ] )

QUOTENAME의 인수는 구분할 문자열과 구분 기호로 사용할 한 문자로 된 문자열입니다. 구분 기호로 대괄호, 작은따옴표 또는 큰따옴표를 사용할 수 있습니다.
이 함수는 주로 구분 SQL 식별자를 사용하기 위한 것이므로 SQL Server에서 nvarchar(128) 형식인 sysname만 받습니다. 또한 이 함수를 사용하여 구분 SQL 리터럴 문자열을 준비할 수 있지만 인수 길이 제한 때문에 128자 이하의 문자열에만 사용할 수 있습니다. 즉, 이러한 제한 때문에 REPLACE 함수를 사용하게 됩니다. 그림 1에서는 sp_addlogin이 QUOTENAME을 사용하여 구분된 로그인 이름 및 암호 문자열을 만드는 방법을 보여 줍니다. 그림에서 볼 수 있듯이 @loginname과 @passwd가 모두 sysname이므로 QUOTENAME 함수를 사용하여 구분 SQL 식별자와 구분 리터럴을 준비할 수 있습니다. 따라서 @loginname = 'my[]dbreader'와 @passwd = 'P@$$''w0rd'가 전달된다고 하더라도 QUOTENAME이 구분 문자를 올바르게 이스케이프하므로 SQL 주입 기회가 발생하지 않습니다.

create login [my[]]dbreader] with password = 'P@$$''w0rd'

Figure 1 QUOTENAME으로 문자열 구분

create procedure sys.sp_addlogin @loginame sysname ,@passwd
sysname = Null ,@defdb sysname = ‘master’ ,@deflanguage sysname = Null
,@sid varbinary(16) = Null ,@encryptopt varchar(20) = Null AS -- SETUP
RUNTIME OPTIONS / DECLARE VARIABLES -- -- some code ---- set @exec_stmt =
‘create login ‘ + quotename(@loginame, ‘[‘) if @passwd is null select
@passwd = ‘‘ if (@encryptopt is null) set @exec_stmt = @exec_stmt + ‘
with password = ‘ + quotename(@passwd, ‘‘‘‘) else -- some code GO

REPLACE 함수는 지정된 문자열을 모두 지정된 대체 문자열로 바꿉니다. QUOTENAME과는 달리 받는 인수에 대한 길이 제한은 없습니다.

REPLACE ( 'string1' , 'string2' , 'string3' )

REPLACE는 3개의 문자열을 받습니다. 즉, string1은 편집할 식이고 string2는 바꿔야 하는 string1 내의 항목이며 string3은 string2 대신 사용할 항목입니다. 모든 문자열 식은 문자 또는 이진 데이터일 수 있습니다.
구분 SQL 리터럴을 준비하기 위해 REPLACE를 사용하여 작은따옴표 수를 2배로 만들 수 있습니다. 그러나 이 경우 시작 및 끝 작은따옴표를 사용하는 것처럼 구분 기호를 수동으로 추가해야 합니다. 그림 2에서는 sp_attach_single_file_db에서 이 함수를 사용하여 이스케이프된 물리적 파일 이름을 준비하는 방법을 보여 줍니다. @physname은 nvarchar(260)이므로 QUOTENAME을 구분 리터럴을 준비하는 데 사용할 수 없습니다. 바로 이 점 때문에 REPLACE를 사용하는 것입니다. 따라서 작은따옴표가 있는 문자열을 전달한다고 하더라도 SQL 문을 수정하거나 SQL 코드를 주입할 수 없습니다.

Figure 2 REPLACE로 문자열 구분

create procedure sys.sp_attach_single_file_db @dbname sysname,
@physname nvarchar(260) as declare @execstring nvarchar (4000) -- some
code -- select @execstring = ‘CREATE DATABASE ‘ + quotename( @dbname ,
‘[‘) + ‘ ON (FILENAME =‘ + ‘‘‘‘ + REPLACE(@physname,N’’’’,N’’’’’’) +
‘‘‘‘ + ‘ ) FOR ATTACH’ EXEC (@execstring) -- some code -- GO

SQL 주입 취약점

이제 현재 암호의 유효성을 확인한 후 사용자 계정의 암호를 변경하는 저장 프로시저를 살펴보겠습니다(그림 3 참조).

Figure 3 암호 변경

CREATE PROCEDURE sp_setPassword @username varchar(25), @old
varchar(25), @new varchar(25) AS DECLARE @command varchar(100) SET
@command= ‘update Users set password=‘‘‘ + @new + ‘‘‘ where username=‘‘‘
+ @username + ‘‘‘ AND password=‘‘‘ + @old + ‘‘‘‘ EXEC (@command) GO

저장 프로시저를 간단하게 살펴보면 작은따옴표에 대해 이스케이프된 매개 변수가 없어서 SQL 주입 공격에 취약하다는 것을 알 수 있습니다. 공격자는 몇 가지 특정 인수를 전달하여 SQL 문을 다음과 같이 수정할 수 있습니다.

update Users set password='NewP@ssw0rd' where username='admin'
--' and password='dummy'

결과적으로 실제 암호가 없어도 admin 계정(또는 알려진 계정)의 암호가 설정됩니다. T-SQL에서는 REPLACE 또는 QUOTENAME을 사용하여 이 코드를 수정할 수 있습니다. 그림 4에서는 REPLACE 함수를 사용하여 수정한 코드를 보여 줍니다.

Figure 4 REPLACE를 사용하여 주입 방지

CREATE PROCEDURE sp_setPassword @username varchar(25), @old
varchar(25), @new varchar(25) AS -- Declare variables. DECLARE @command
varchar(100) -- Construct the dynamic SQL SET @command= ‘update Users
set password=‘‘‘ + REPLACE(@new, ‘‘‘‘, ‘‘‘‘‘‘) + ‘‘‘‘ + ‘ where
username=‘‘‘ + REPLACE(@username, ‘‘‘‘, ‘‘‘‘‘‘) + ‘‘‘‘ + ‘ AND password =
‘‘‘ + REPLACE(@old, ‘‘‘‘, ‘‘‘‘‘‘) + ‘‘‘‘ -- Execute the command. EXEC
(@command) GO

그림에서 볼 수 있는 것처럼 REPLACE는 매개 변수에 있는 모든 작은따옴표 수를 2배로 만듭니다. 따라서 공격자가 동일한 인수를 전달하는 경우 다음과 같은 문이 만들어집니다.

update Users set password='NewP@ssw0rd' where
username='admin''--' and password='dummy'

따라서 일반적인 SQL 주입 문제에 취약하지 않게 됩니다.

잘라내기를 통한 수정

앞에서 본 저장 프로시저를 자세히 살펴보면 @command 변수의 길이 제한이 100자임을 알 수 있습니다. 그러나 25자로 된 각 변수에 대한 REPLACE 함수는 모든 문자가 작은따옴표일 경우 50자를 반환할 수 있습니다. SQL Server 2000 SP4 및 SQL Server 2005 SP1에서는 변수의 버퍼가 충분하지 않으면 데이터가 자동으로 잘립니다. 공격자는 이러한 기회를 틈타 명령 문자열을 자를 수 있습니다.

이 예제에서 누군가가 username='username' 식의 바로 뒤에 있는 명령을 자를 수 있다면 알려진 사용자 계정의 현재 암호를 알지 못하더라도 해당 암호를 변경할 수 있습니다.

웹 응용 프로그램에 administrator라는 이름의 사용자가 있다는 것을 공격자가 알고 있다고 가정합니다. 이 경우 모든 사용자 계정이 대상이 될 수 있습니다. 명령이 너무 길어서 적절하게 잘리도록 하기 위해서는 공격자가 41자 길이의 새 암호를 제공해야 합니다. 즉, 전체 길이가 100자인 명령에서 27자는 update 문에 사용되고 17자는 where 절에 사용되고 13자는 "administrator"에 사용되며 2자는 새 암호를 둘러싸는 작은따옴표에 사용되기 때문에 암호에는 41자가 필요합니다.

공격자는 새 암호로 25자만 전달할 수 있습니다. 그러나 REPLACE 함수에 의해 2배가 되는 작은따옴표를 전달하면 이 문제를 해결할 수 있습니다. 따라서 공격자는 작은따옴표 18개, 대문자 1개, 기호 1개 및 소문자 2개를 전달하여 where username='administrator' 식의 바로 뒤에 있는 명령을 자를 수 있습니다. 공격자가 @new 매개 변수에 대해 ''''''''''''''''''!Abb1을 전달하고 username 매개 변수에 대해 administrator를 전달하면 @command는 다음과 같이 됩니다.

update Users set password=
'''''''''''''''''''''''''''''''''''''!Abb1' where
username='administrator'

그림 5에서는 REPLACE 대신 QUOTENAME을 사용합니다. 이전 예제에서는 개발자가 사용자 이름, 새 암호 및 기존 암호에 대해 작은따옴표를 구분 문자로 추가하지만 이 예제에서는 QUOTENAME 함수를 사용해서 작은따옴표를 추가한다는 점이 이전 예제와 이 예제의 유일한 차이점입니다. 사용자가 제공한 데이터는 변경되지 않기 때문에 이전 예제에 사용한 것과 동일한 공격 문자열을 이 예제에 사용할 수 있습니다. 그림 6에서는 동일한 기능을 수행하는 중간 계층 응용 프로그램에서 작성된 간략한 C/C++ 함수 버전을 보여 줍니다. 이 함수도 동일한 공격에 취약합니다.

Figure 6 C++의 잘라내기 문제점

DWORD ChangePassword(char* psUserName, char* psOld, char*
psNew) { char* psEscapedUserName = NULL; char* psEscapedOldPW = NULL;
char* psEscapedNewPW = NULL; char szSQLCommand[100]; HRESULT hr=0; //
Input Validation ... // Calculate and allocate the new buffer with
length // userdatalen*2 + 1 // Escape all single quotes with double
quotes ... //Construct the query hr = StringCchPrintf(szSQLCommand,
sizeof(szSQLCommand)/sizeof(char), "Update Users set password=‘%s’ where
username=‘%s’" "AND password=‘%s’, psEscapedNewPW, psEscapedUserName,
psEscapedOldPW); if (S_OK != hr) { // handle error cases } // Execute
and return }

Figure 5 QUOTENAME을 사용하여 주입 방지

CREATE PROCEDURE sp_setPassword @username varchar(25), @old
varchar(25), @new varchar(25) AS -- Declare variables. DECLARE @command
varchar(100) -- In the following statement, we will need 43 characters
-- to set an administrator password without knowing its current
password. -- 100 - 26 - 16 - 15 = 43 (26 for update stmt, 16 for where
clause, -- 15 for ‘administrator’). But @new only takes 25 characters,
which we -- can get around by using single quotes. So one can pass the
following -- parametes and set admin password. @new = 18 single quotes, 1
Capital -- letter, 1 symbol, 2 small case letters, 1 digit -- @username
= administrator -- @command becomes -- update Users set
password=‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘‘!Abb1’ -- where username=‘administrator’ SET
@command= ‘update Users set password=‘ + QUOTENAME(@new,’’’’) + ‘ where
username=‘ + QUOTENAME(@username,’’’’) + ‘ AND password = ‘ +
QUOTENAME(@old,’’’’) -- Execute the command. EXEC (@command) GO

잘라내기를 통한 SQL 주입

그림 7에서는 개별 변수를 고정적으로 사용하는 동일한 코드의 여러 변형을 보여 줍니다. 이 코드에서는 이스케이프된 문자열을 개별 변수에 저장하고 @command의 버퍼는 전체 문자열을 저장할 수 있을 정도로 큽니다. @escaped_username, @escaped_oldpw 및 @escaped_newpw는 varchar(25)로 선언되었지만 @username, @old 및 @new의 모든 문자가 25개의 작은따옴표 문자일 경우 50자를 저장해야 합니다. 이 경우 이스케이프된 문자로 구성된 문자열이 잘릴 가능성이 있습니다.

Figure 7 개별 변수를 사용하여 주입 방지

CREATE PROCEDURE sp_setPassword @username varchar(25), @old
varchar(25), @new varchar(25) AS -- Declare variables. DECLARE
@escaped_username varchar(25) DECLARE @escaped_oldpw varchar(25) DECLARE
@escaped_newpw varchar(25) DECLARE @command varchar(250) SET
@escaped_username = REPLACE(@username, ‘‘‘‘, ‘‘‘‘‘‘) SET @escaped_oldpw =
REPLACE(@old, ‘‘‘‘, ‘‘‘‘‘‘) SET @escaped_newpw = REPLACE(@new, ‘‘‘‘,
‘‘‘‘‘‘) SET @command = ‘update Users set password=‘‘‘ + @escaped_newpw +
‘‘‘‘ + ‘ where username=‘‘‘ + @escaped_username + ‘‘‘‘ + ‘ AND password
= ‘‘‘ + @escaped_oldpw + ‘‘‘‘ EXEC (@command) GO

공격자는 123...n'(여기서 n은 24번째 문자)을 새 암호로 제공하고 @escaped_newpw를 123...n'으로 만든 후(REPLACE 함수에서 반환된 두 번째 작은따옴표 문자가 잘림) 다음과 같은 마지막 쿼리를 만들 수 있습니다. 이 경우 공격자는 username 필드를 통해 코드를 주입하여 공격할 수 있습니다.

update users set password='123...n'' where username='<SQL
Injection here using Username>

이 코드 패턴의 경우 기존 SQL을 단순히 자르는 것에 그치지 않고 SQL 코드를 삽입할 가능성이 열려 있기 때문에 더 위험합니다.

그림 8에서는 REPLACE 대신 QUOTENAME 함수를 사용하는 동일한 코드의 변형을 예제로 보여 줍니다. QUOTENAME은 구분 기호를 추가하므로 페이로드는 다르지만 여전히 SQL 주입 공격에는 취약합니다.

Figure 8 개별 변수를 통해 QUOTENAME 사용

ALTER PROCEDURE sp_setPassword @username varchar(25), @old
varchar(25), @new varchar(25) AS -- Declare variables. DECLARE
@quoted_username varchar(25) DECLARE @quoted_oldpw varchar(25) DECLARE
@quoted_newpw varchar(25) DECLARE @command varchar(250) -- In the
following statements, all the variables can only hold -- 25 characters,
but quotename() will return 52 characters when all -- the characters are
single quotes. SET @quoted_username = QUOTENAME(@username, ‘‘‘‘) SET
@quoted_oldpw = QUOTENAME(@old, ‘‘‘‘) SET @quoted_newpw =
QUOTENAME(@new, ‘‘‘‘) -- By passing the new password as 123...n where n
is 24th character, -- @quoted_newpw becomes ‘123..n -- Observe carefully
that there is no trailing single quote as it gets -- truncated. -- So
the final query becomes something like this -- update users set
password=‘123...n where username=‘ <SQL Injection -- here using
Username> SET @command= ‘update Users set password=‘ + @quoted_newpw +
‘ where username=‘ + @quoted_username + ‘ AND password = ‘ +
@quoted_oldpw EXEC (@command) GO

이 코드에서는 구분 문자열을 개별 변수에 저장하고 @command의 버퍼는 전체 명령 문자열을 저장할 수 있을 정도로 큽니다. 이전 예제와 마찬가지로 따옴표 붙은 변수 @quoted_username, @quoted_oldpw 및 @quoted_newpw에 문제가 있습니다. 이러한 변수는 varchar(25)로 선언되었지만 @username, @old 및 @new의 모든 문자가 25개의 작은따옴표 문자일 경우 52자가 필요합니다. 또한 QUOTENAME은 시작 및 끝 구분 기호를 추가합니다. 이 경우 공격자는 구분 문자로 구성된 문자열을 자를 기회를 얻게 됩니다.

공격자는 123...n(여기서 n은 24번째 문자)을 새 암호로 제공하고 @escaped_newpw를 '123...n으로 만든 후(QUOTENAME 함수가 시작 작은따옴표를 추가함) 다음과 같은 마지막 쿼리를 만들 수 있습니다. 이 경우 공격자는 username 필드를 통해 코드를 주입하여 공격할 수 있습니다.

update users set password='123...n where username=' <SQL
Injection here using Username>

그림 9에서는 이 코드와 동일한 기능을 수행하면서 C/C++로 작성된 간단한 코드를 보여 줍니다. 이 코드 또한 동일한 공격 방식에 취약합니다.

Figure 9 C++의 변수 잘라내기 문제

DWORD ChangePassword(char* psUserName, char* psOld, char*
psNew) { char szEscapedUserName[26]; char szEscapedOldPW[26]; char
szEscapedNewPW[26]; char szSQLCommand[250]; // Input Validation //
Escape User supplied data Replace(psUserName, "’", "’’",
szEscapedUserName, sizeof(szEscapedUserName)); Replace(psPassword, "’",
"’’", szEscapedOldPW, sizeof(szEscapedOldPW)); Replace(psPassword, "’",
"’’", szEscapedNewPW, sizeof(szEscapedNewPW)); // Construct the query
StringCchPrintf(szSQLCommand, sizeof(szSQLCommand)/sizeof(char), "Update
Users set password=‘%s’ where username=‘%s’" "AND password=‘%s’,
szEscapedNewPW, szEscapedUserName,szEscapedOldPW); // Execute and return
}

여기에서는 설명을 위해 T-SQL 코드를 사용하기는 했지만 실제로는 DML(데이터 조작 언어) 코드가 포함된 대부분의 응용 프로그램이 이러한 문제에 취약하지 않으므로 DML 문에 동적 SQL을 사용할 필요는 없습니다.

그림 10에서는 사용자 입력에 따라 동적 DDL 문을 생성하는 예제를 보여 줍니다. 앞서 살펴본 다른 예제와 마찬가지로 다음 문에는 잘라내기 문제가 있습니다.

set @escaped_oldpw = quotename(@old, '''') set @escaped_newpw =
quotename(@new, '''')

공격자는 @new = '123...'을 전달하여 이러한 문을 공격할 수 있습니다. 여기서 127번째 문자(작은따옴표 아님)는 @old = '; SQL Injection'을 시작하고 다음과 같은 SQL 문이 만들어집니다.

alter login [loginname] with password = '123... old_password =
'; SQL Injection

Figure 10 동적 DDL 문 만들기

create procedure sys.sp_password @old sysname = NULL, -- the
old (current) password @new sysname, -- the new password @loginame
sysname = NULL -- user to change password on as -- SETUP RUNTIME OPTIONS
/ DECLARE VARIABLES -- set nocount on declare @exec_stmt nvarchar(4000)
declare @escaped_oldpw sysname declare @escaped_newpw sysname set
@escaped_oldpw = quotename(@old, ‘‘‘‘) set @escaped_newpw =
quotename(@new, ‘‘‘‘) set @exec_stmt = ‘alter login ‘ +
quotename(@loginame) + ‘ with password = ‘ + @escaped_newpw + ‘
old_password = ‘ + @escaped_old exec (@exec_stmt) if @@error <> 0
return (1) -- RETURN SUCCESS -- return (0) -- sp_password

저장 프로시저에서 이러한 문제가 쉽게 발생할 수 있는 것처럼 보이기는 하지만 모든 저장 프로시저가 보안에 취약한 것은 아닙니다. 다음 내용을 주의 깊게 검토할 필요가 있습니다.

SQL Server에서는 모든 저장 프로시저가 기본적으로 호출자의 컨텍스트에서 실행됩니다. 따라서 프로시저에 SQL 주입 문제가 있다고 하더라도 프로시저에 대한 실행 권한을 갖고 있는 악의적 로컬 사용자가 자신의 권한을 높일 수 없기 때문에 주입된 코드는 해당 사용자의 컨텍스트에서 실행됩니다. 그러나 EXECUTE AS 기능을 통해 소유자 또는 다른 특정 사용자가 실행할 수 있는 내부 유지 관리 스크립트가 있는 경우에는 호출자가 다른 사용자 컨텍스트에서 코드를 실행하여 호출자의 권한을 해당 사용자의 권한으로 높일 수 있습니다.

모든 잘라내기 문제는 명백히 버그이지만 반드시 보안 취약점이라고 할 수는 없습니다. 그러나 향후 누가 이러한 문제점을 발견하여 악용할 가능성은 있기 때문에 문제를 해결해 두는 것이 좋습니다.

SQL 코드의 주입 취약점을 완화하기 위해 취할 수 있는 다른 방법이 있습니다. 첫 번째는 저장 프로시저에서 DML 문에 대해 동적 SQL을 사용하지 않는 것입니다. 불가피하게 동적 SQL을 사용해야 한다면 sp_executesql을 사용하십시오. 두 번째는 이 기사의 예제에서 설명한 것처럼 버퍼 길이를 올바르게 계산해야 합니다. 마지막으로 C/C++ 코드의 경우 문자열 연산 반환 값을 확인하여 문자열이 잘렸는지 여부를 확인합니다. 문자열이 잘렸으면 실패한 것입니다. 취할 수 있는 단계에 대한 요약을 보려면 "취약점 검색 방법" 보충 기사를 참조하십시오.

잘라내기를 통한 주입 검색

잘라내기를 이용한 SQL 주입 문제를 자동화된 도구로 검색하려면 잘라내기 공격의 가능성을 남기는 모든 코드 패턴을 잘 파악하고 있어야 합니다. 서로 다른 문자열 데이터를 사용하여 개별 특정 코드 패턴에 적용할 수 있습니다. 다음 시나리오에서는 n이 입력 버퍼의 길이라고 가정합니다.

QUOTENAME 구분 문제를 검색하기 위해 먼저 QUOTENAME(또는 C/C++ 응용 프로그램의 경우 비슷한 함수)을 사용해서 구분 식별자 또는 리터럴을 준비했고, 구분된 문자열 버퍼 크기가 2*n + 2보다 작다고 가정합니다. 구분된 문자열 버퍼 길이가 n일 경우 이러한 문제를 검색하려면 비구분 문자로 구성된 긴 문자열을 전달합니다. 후행 구분 기호는 잘리고 다른 입력 변수를 사용하여 주입할 수 있는 기회가 생깁니다.

구분된 버퍼 길이가 홀수일 때 이러한 문제를 검색하려면 작은따옴표(또는 오른쪽 대괄호나 큰따옴표) 문자로 구성된 긴 문자열을 전달합니다. QUOTENAME은 모든 구분 기호 수를 2배로 만들고 시작 구분 문자를 추가하는 반면, 이스케이프된 문자열 버퍼에는 홀수 개의 문자만을 저장할 수 있으므로 후행 구분 기호는 잘립니다.

구분된 버퍼 길이가 짝수일 때 이러한 문제를 검색하려면 1', 1'', 1''', 1''''과 같이 각 반복에 대해 작은따옴표(또는 오른쪽 대괄호)의 수를 늘리는 방식으로 문자열을 전달합니다. QUOTENAME은 모든 작은따옴표 수를 2배로 만들기 때문에 시작 구분 기호와 1이 포함된 짝수 개의 작은따옴표가 들어 있는 문자열이 반환되어 짝수 개의 문자를 받게 됩니다. 결과적으로 후행 구분 기호는 잘립니다.

또한 REPLACE(또는 C/C++ 응용 프로그램의 경우 유사한 함수)를 사용하여 이스케이프된 문자열을 준비하고 이스케이프된 문자열 버퍼 크기가 2*n보다 작은 경우에도 이러한 문제를 검색할 수 있습니다. 이스케이프된 문자열 버퍼 길이가 n과 같을 때 이러한 문제를 검색하려면 1', 12', 123' 및 123...n'과 같이 각 반복에 대해 입력 문자열의 길이를 늘리는 방식으로 문자열을 전달합니다. 이 경우 올바른 길이를 입력하면 REPLACE 함수에 의해 마지막 작은따옴표 문자가 2배로 됩니다. 이스케이프된 문자열 변수에는 충분한 버퍼 공간이 없으므로 마지막 작은따옴표가 잘린 채로 저장되어 전달되기 때문에 SQL 문을 수정할 수 있는 기회가 발생합니다.

이스케이프된 버퍼 길이가 홀수일 때 REPLACE를 사용하여 이러한 문제를 검색하려면 ', '', ''' 및 ''''...'와 같이 길이가 길어지는 작은따옴표 문자로 구성된 문자열을 전달하거나 작은따옴표 문자로 구성된 긴 문자열을 전달합니다. 이 경우 REPLACE는 모든 작은따옴표 수를 2배로 만듭니다. 그러나 버퍼 크기가 홀수이기 때문에 마지막 작은따옴표가 잘리면서 SQL 문을 수정할 수 있는 기회가 발생합니다.

이스케이프된 버퍼 길이가 짝수일 때 이러한 문제를 검색하려면 1', 1'', 1''', 1''''과 같이 각 반복에 대해 작은따옴표(또는 오른쪽 대괄호)의 수를 늘리는 방식으로 문자열을 전달합니다. 맨 앞의 1을 제외한 반환 값에는 짝수 개의 문자가 포함되므로 전체 반환 값의 문자 수는 홀수가 됩니다. 그러나 버퍼 길이가 짝수이기 때문에 후행 작은따옴표가 잘리면서 SQL 문을 수정할 수 있는 기회가 발생합니다.

취약점 검색 방법

코드 검토 사용 코드 검토를 수행할 때 다음과 같은 방법을 사용하여 SQL 문의 문제를 검색할 수 있습니다.

1차 또는 2차 SQL 주입 검색

동적 SQL 문을 실행하는 데 사용한 API를 확인합니다.
동적 SQL 문에 사용된 데이터에 대해 데이터 유효성 검사가 수행되었는지 검토합니다.
데이터 유효성 검사가 수행되지 않은 경우 데이터의 구분 문자(문자열 리터럴의 경우 작은따옴표, SQL 식별자의 경우 오른쪽 대괄호)가 이스케이프되었는지 검토합니다.

잘라내기를 통한 SQL 수정 문제 검색

마지막 동적 SQL 문을 저장하는 데 사용된 버퍼 길이를 검토합니다.
입력이 최대값을 초과하고 SQL 문을 저장하는 데 사용할 버퍼가 충분히 큰 경우 SQL 문을 저장하는 데 필요한 최대 버퍼를 계산합니다.
QUOTENAME 또는 REPLACE 함수의 반환 값에 특히 주의합니다. 이러한 함수는 입력 데이터의 길이가 n자일 때 모든 입력 문자가 구분 문자이면 2*n + 2 또는 2*n을 반환합니다.
C/C++ 응용 프로그램의 경우 SQL 문을 준비하는 데 사용한 StringCchPrintf와 같은 API의 반환 값에 대해 버퍼 부족 오류가 발견되었는지 확인합니다.

잘라내기를 통한 SQL 주입 문제 검색

구분 문자열 또는 이스케이프된 문자열을 저장하는 데 사용된 버퍼 길이를 검토합니다.
n이 입력 문자열의 길이이면 QUOTENAME의 반환 값을 저장하는 데 2*n + 2가 필요하고 REPLACE의 반환 값을 저장하는 데 2*n이 필요합니다.
C/C++ 응용 프로그램의 경우 REPLACE에 상응하는 함수의 반환 값에 대해 버퍼 부족 오류가 발견되었는지 확인합니다.

블랙 박스 메서드 사용

자동화 도구나 지능형 퍼저(Fuzzer)가 있는 경우에는 다음과 같은 방법을 사용하여 SQL 문의 문제를 검색할 수 있습니다.

SQL 주입 문제 검색

작은따옴표를 입력 데이터로 보내서 사용자 입력이 동적 SQL 문에서 문자열 리터럴로 관리 및 사용되지 않는 경우를 검색합니다.
오른쪽 대괄호(] 문자)를 입력 데이터로 사용하여 정리되지 않은 사용자 입력이 SQL 식별자의 일부분으로 사용되는 경우를 검색합니다.

잘라내기 문제 검색

버퍼 오버런 검색을 위해 문자열을 보내는 것처럼 긴 문자열을 보냅니다.

잘라내기를 통한 SQL 수정 문제 검색

작은따옴표 문자(또는 오른쪽 대괄호나 큰따옴표)로 구성된 긴 문자열을 보냅니다. 이 경우 REPLACE 및 QUOTENAME 함수의 반환 값이 최대값을 초과하게 되어 SQL 문을 저장하는 데 사용되는 명령 변수가 잘릴 수 있습니다.

필자소개

Bala Neerumalla는 Microsoft의 보안 소프트웨어 개발자이며 응용 프로그램 보안 취약점 조사를 전문적으로 담당하고 있습니다.

출처 : 마이크로소프트

제공 : DB포탈사이트 DBguide.net

DBMS/MSSQL] SQL 성능을 높이는 5가지 방법

응용 프로그램이 더 빠르게 실행되도록 하기 위해서는 여기 저기를 조금씩 손보기만 하면 됩니다. 문제는 어떻게 손보는가에 있죠! 조만간 응용 프로그램의 SQL 쿼리가 여러분이 의도한 방식대로 응답하지 않는 상황에 직면하게 될 것입니다. 원하는 데이터를 반환하지 않거나 아니면 너무 길어서 적합하지 않습니다. SQL이 보고서나 엔터프라이즈 응용 프로그램의 속도를 떨어뜨려 엄청난 시간 동안 기다려야 하는 상황이 발생하면 사용자는 그리 즐거울 수 없을 것입니다. 부모님이 자녀가 귀가 시간을 어긴 이유를 듣고 싶어하지 않듯 사용자 역시 쿼리가 그렇게 오래 걸리는 이유를 알고 싶어하지 않습니다. (“엄마, 죄송해요. LEFT JOIN을 너무 많이 사용했네요.”) 사용자는 응용 프로그램이 신속히 응답하고 보고서가 분석 데이터를 즉시 반환하기를 원합니다. 저 역시도 웹 서핑 중 한 페이지를 로드하는데 10초(사실 5초 정도) 이상이 걸리면 참을 수가 없어집니다.
이러한 문제를 해결하기 위해서는 그 문제의 원인을 찾아 내는 것이 중요합니다. 그렇다면 어디부터 시작해야 할까요? 문제의 원인은 일반적으로 데이터베이스 디자인과 그 데이터베이스를 액세스하는 쿼리에 있습니다. 이번 달 컬럼에서는 SQL Server 기반 응용 프로그램의 성능이나 확장성을 향상시키는데 사용할 수 있는 네 가지 테크닉을 살펴 보겠습니다. 그리고 LEFT JOIN과 CROSS JOIN 사용 및 IDENTITY 값 검색도 살펴 보겠습니다. 마술같은 해결책은 없다는 것을 기억하십시오. 데이터베이스와 쿼리를 조정하려면 시간이 걸리고 분석과 함께 수차례의 테스팅이 필요합니다. 여기 제시된 테크닉은 증명이 된 것이지만 사용자 응용 프로그램에 따라 더 잘 실행되는 테크닉과 그렇지 않은 테크닉이 있을 수 있습니다.

INSERT에서 IDENTITY 반환 T
가장 궁금한 문제 즉, SQL INSERT를 실행한 후 어떻게 IDENTITY 값을 검색하는지부터 살펴 보겠습니다. 문제는, 그 값을 검색하는 쿼리를 어떻게 작성하는지가 아니라 언제 어디서 작성하는가 입니다. SQL Server에서, 활성 데이터베이스 연결에서 가장 최신 SQL 문 실행에 의해 만들어진 IDENTITY 값을 검색하는 문은 다음과 같습니다.

          SELECT @@IDENTITY

이 SQL은 강력하지가 않으므로 가장 최근의 SQL 문이 INSERT가 아니거나 INSERT SQL이 아닌 다른 연결에 대해 이 SQL을 실행한다면 예상하는 값을 얻지 못할 것이라는 사실을 명심해야 합니다. IDENTITY를 검색하려면 다음과 같이 INSERT SQL 직후에 동일한 연결에서 이 코드를 실행해야 합니다.

          INSERT INTO Products (ProductName) VALUES ('Chalk')

          

          SELECT @@IDENTITY

단일 연결에서 Northwind 데이터베이스에 대해 이러한 쿼리를 실행하면 Chalk라는 신제품에 대한 IDENTITY 값이 반환될 것입니다. 따라서 ADO를 사용하는 Visual Basic 응용 프로그램에서 다음 명령문을 실행할 수 있습니다.

          Set oRs = oCn.Execute("SET NOCOUNT ON;INSERT INTO Products _

          (ProductName) VALUES ('Chalk');SELECT @@IDENTITY")

          

          lProductID = oRs(0)

이 코드는 그 쿼리에 대한 행 카운트를 반환하지 않도록 SQL Server에 알리고 INSERT 문을 실행하며 그 새 행에 대해 만들어진 IDENTITY 값을 반환합니다. SET NOCOUNT ON 문은 반환된 Recordset에 새 IDENTITY 값이 들어 있는 한 행과 열이 있다는 것을 뜻합니다. 이 문이 없으면 (INSERT 문이 데이터를 반환하지 않으므로) 빈 Recorset가 반환되며 그 다음 반환되는 두 번째 Recordset에 IDENTITY 값이 들어 있습니다. 따라서 INSERT가 Recordset를 반환하도록 할 생각이 아니었던 경우에는 특히나 당황스러울 수 있습니다. 이러한 상황은, SQL Server는 행 카운트(즉, 영향을 받는 행)를 확인하고 그 카운트를 Recordset 표시로 해석하기 때문에 발생합니다. 따라서 올바른 데이터는 두 번째 Recordset로 밀려납니다. ADO에서 NextRecordset 메서드를 사용하면 이 두 번째 Recordset를 확인할 수 있지만 이 Recordset이 반환되는 첫 번째이자 유일한 값이라면 훨씬 쉽고 효율적일 것입니다.
이 테크닉이 작업을 실행하긴 하지만 SQL 문에 추가 코드가 필요합니다. 동일한 결과를 얻을 수 있는 또 다른 방법은 다음 코드에서 볼 수 있는 것처럼 INSERT 앞에 SET NOCOUNT ON 문을 사용하고 그 테이블의 FOR INSERT 트리거에 SELECT @@IDENTITY 문을 넣는 것입니다. 이렇게 하면 그 테이블에 대한 어떤 INSERT 문이나 자동으로 IDENTITY 값을 반환하게 됩니다.

          CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS 

              SELECT @@IDENTITY 

          GO

이 트리거는 Product 테이블에 INSERT가 실행될 때만 발생하므로 성공적인 INSERT 후에는 언제나 IDENTITY를 반환합니다. 이 테크닉을 사용하면 응용 프로그램 내 어디서나 동일한 방식으로 IDENTITY 값을 검색할 수 있습니다.

인라인 값 VS. 임시 테이블
종종 쿼리는 GROUP BY 후 표준 쿼리를 실행해야만 수집할 수 있는 다른 데이터에 데이터를 조인해야 하는 경우가 있습니다. 예를 들어 가장 최근 주문 5건에 대한 정보를 반환하고 싶다면 먼저 그 최근 주문 5건이 무엇인지부터 알아야 합니다. 이 주문은 주문 ID를 반환하는 SQL 쿼리를 사용하면 검색할 수 있습니다. 이 데이터는 임시 테이블에 저장될 수 있으며 그런 다음 Product 테이블로 조인되어 그 주문에 대해 판매된 제품 수량을 반환합니다.

          CREATE TABLE #Temp1 (OrderID INT NOT NULL, _

                               OrderDate DATETIME NOT NULL)

          

          INSERT INTO #Temp1 (OrderID, OrderDate)

          SELECT     TOP 5 o.OrderID, o.OrderDate

          FROM Orders o ORDER BY o.OrderDate DESC

          

          SELECT     p.ProductName, SUM(od.Quantity) AS ProductQuantity

          FROM     #Temp1 t 

              INNER JOIN [Order Details] od ON t.OrderID = od.OrderID

              INNER JOIN Products p ON od.ProductID = p.ProductID 

          GROUP BY p.ProductName

          ORDER BY p.ProductName

          

          DROP TABLE #Temp1

이 SQL 일괄 처리는 임시 테이블을 만들어 그 테이블에 데이터를 입력하고 다른 데이터를 조인한 다음 그 임시 테이블을 삭제합니다. 이 쿼리는 I/O가 많으므로 임시 테이블 대신 인라인 뷰를 사용하도록 다시 작성할 수 있습니다. 인라인 뷰는 간단하게 말하면 FROM 절에서 조인될 수 있는 쿼리입니다. 따라서 임시 테이블의 tempdb에서 많은 I/O 와 디스크 액세스를 허비하는 대신 인라인 뷰를 사용해서도 동일한 결과를 얻을 수 있습니다.

          SELECT p.ProductName, 

              SUM(od.Quantity) AS ProductQuantity

          FROM     (

              SELECT TOP 5 o.OrderID, o.OrderDate

              FROM     Orders o 

              ORDER BY o.OrderDate DESC

              ) t 

              INNER JOIN [Order Details] od ON t.OrderID = od.OrderID

              INNER JOIN Products p ON od.ProductID = p.ProductID 

          GROUP BY

              p.ProductName

          ORDER BY

              p.ProductName

이 쿼리는 이전 쿼리보다 효율적일 뿐만 아니라 더 짧습니다. 임시 테이블은 많은 리소스를 소비합니다. 따라서 데이터를 다른 쿼리에 조인시키기만 하면 되는 경우에는 인라인 뷰를 사용하여 리소스를 보존하는 방법을 시도해 보는 것이 좋을 것입니다.

LEFT JOIN과 NULL 피하기
물론, LEFT JOIN을 실행하고 NULL 값을 사용해야 할 때가 있습니다. 하지만 모든 경우에 항상 그래야 하는 것은 아닙니다. SQL 쿼리를 구성하는 방식을 변경하면 실행 시간이 몇 분이 걸리는 보고서를 단 몇 초 만에 실행되는 보고서로 만들 수 있습니다. 그리고 응용 프로그램이 원하는 방식으로 쿼리의 데이터를 변경해야 하는 경우도 종종 있습니다. TABLE 데이터 형식은 리소스 사용량을 줄여주지만 쿼리에는 최적화할 수 있는 부분이 여전히 많이 남아 있습니다. SQL에서 일반적으로 사용되는 아주 유용한 기능은 LEFT JOIN입니다. 이 기능은 첫 번째 테이블의 모든 행과 두 번째 테이블의 일치하는 모든 행, 그리고 첫 번째 테이블의 행과 일치하지 않는 두 번째 테이블의 모든 행을 검색하는데 사용할 수 있습니다. 예를 들어 모든 Customer와 그 주문을 반환하고 싶다면 LEFT JOIN은 주문을 한 Customer와 주문을 하지 않은 Customer를 표시할 것입니다.
이 도구는 지나치게 남용될 수 있습니다. LEFT JOIN은 NULL(존재하지 않음) 데이터에 대해 데이터를 일치시키는 작업을 하므로 부담이 큽니다. 이 실행을 피할 수 없는 경우도 있지만 그럴 경우 부담이 커집니다. LEFT JOIN은 INNER JOIN보다 부담이 더 크므로 LEFT JOIN을 사용하지 않도록 쿼리를 다시 작성할 수 있다면 부담이 크게 줄어들 수 있습니다(그림 1의 다이어그램 참조).

그림 1 쿼리

-1){ thisSrc = this.src; ns = thisSrc.lastIndexOf('='); ne = thisSrc.length; orgImg = thisSrc.substr(ns + 1, ne); this.src = './dnImage/' + orgImg;}" src="http://tfile.nate.com/download.asp?FileID=7514955" localfile="yes" height="160" width="109">
그림 1 쿼리

LEFT JOIN을 사용하는 쿼리의 속도를 높이기 위해서는 TABLE 데이터 형식을 만들고 첫 번째 테이블(LEFT JOIN의 왼쪽에 있는 테이블)에서 모든 행을 삽입한 다음 두 번째 테이블의 값을 사용하여 그 TABLE 데이터 형식을 업데이트합니다. 이 테크닉은 2단계 프로세스이지만 표준 LEFT JOIN과 비교하면 시간을 크게 줄일 수 있습니다. 자신의 응용 프로그램에 맞는 최고 성능의 쿼리를 얻을 때까지 각각에 대해 서로 다른 테크닉을 시도하여 시간을 측정해 보는 것이 좋습니다.
쿼리 속도를 테스트할 때는 여러 번 실행해서 평균을 구하는 것이 좋습니다. 쿼리(또는 저장 프로시저)는 SQL Server 메모리의 프로시저 캐시에 저장될 수 있으므로 처음에는 더 오래 걸리지만 계속 시도할수록 점점 더 짧아집니다. 이 외에도 쿼리 실행 중 동일한 테이블에 대해 다른 쿼리가 실행되고 있을 수도 있습니다. 그렇게 되면 다른 쿼리가 테이블을 잠궜다가 해제하는 동안 사용자의 쿼리는 기다려야 할 수도 있습니다. 예를 들어 다른 사람이 테이블의 데이터를 업데이트하는 동안 그 테이블에 대해 쿼리를 실행하면 그 업데이트가 실행되는 동안에는 쿼리 실행에 더 많은 시간이 걸릴 수 있습니다.
LEFT JOIN으로 인한 감속을 피할 수 있는 가장 쉬운 방법은 가능한 한 많이 LEFT JOIN을 중심으로 데이터베이스를 디자인하는 것입니다. 예를 들어, 한 제품에 카테고리가 있을 수도 있고 없을 수도 있다고 가정해 봅시다. 제품 테이블에 그 카테고리의 ID가 저장되어 있는데 특정 제품에 대한 카테고리가 존재하지 않는다면 그 필드에는 NULL 값을 저장할 수 있습니다. 그런 다음 LEFT JOIN을 실행하여 모든 제품과 그 카테고리를 얻습니다. “No Category” 값을 가진 카테고리를 만들 수 있으므로 NULL 값을 허용하지 않도록 외래 키 관계를 지정합니다. 이렇게 하면 이제 INNER JOIN을 사용하여 모든 제품과 그 카테고리를 검색할 수 있습니다. 추가 데이터로 인해 작업이 더 많아진 것처럼 보일 수도 있지만 SQL 일괄 처리에서 부담이 큰 LEFT JOIN을 제거할 수 있으므로 아주 유용한 테크닉입니다. 데이터베이스의 보드 전체에 이 개념을 사용하면 처리 시간이 크게 줄어듭니다. 불과 몇 초도 사용자에게는 많은 것을 의미하며 온라인 데이터베이스 응용 프로그램을 액세스하는 사용자가 많을 경우 이 시간은 더욱 길어진다는 것을 기억하십시오.

Cartesian 제품을 현명하게 사용하기
이 팁의 경우에는 일반적인 의견과 달리, 특정한 경우 Cartesian 제품을 사용하도록 주장합니다. Cartesian 제품(CROSS JOIN)은 많은 비난을 받았으며 개발자들은 종종 이 제품을 절대 사용하지 말 것을 권고 받습니다. 많은 경우 이 제품은 부담이 너무 커서 효과적이지 않습니다. 하지만 SQL의 여느 도구와 마찬가지로 이 도구 역시 적절히 사용하기만 하면 유용합니다. 예를 들어, 그 달에는 주문을 하지 않은 고객에 대해서조차 매달 데이터를 반환할 쿼리를 실행하고 싶다면 Cartesian 제품이 아주 편리할 것입니다. 그림 2 (영문)의 SQL이 바로 그렇습니다.
이 방법이 마법처럼 여겨지지는 않겠지만 Customer에서 Orders로의 표준 INNER JOIN을 실행하여 월별로 묶고 판매를 요약하면 해당 고객이 주문을 한 월만을 반환 받게 된다는 점을 생각해 보십시오. 그렇게 되면 고객이 제품을 주문하지 않은 달에 대해서는 0 값을 반환 받지 못합니다. 월별 판매가 표시된 고객별 그래프를 만들려고 한다면 시각적으로 식별할 수 있도록 월 판매가 0인 달도 포함된 그래프를 그리고 싶을 것입니다. 그림 2 (영문)의 SQL을 사용하면 Order 테이블에 비판매 관련 행이 없으므로 이 데이터는 판매 금액이 0인 달은 건너뜁니다(발생하지 않은 것은 저장하지 않는 것으로 가정됩니다).
그림 3 (영문)의 코드는 더 길지만, 판매가 없는 달에 대해서도 모든 판매 데이터를 받는다는 목표를 달성할 수 있습니다. 먼저, 지난 해 모든 달의 목록을 받아서 첫 번째 TABLE 데이터 형식 테이블(@tblMonths)에 넣습니다. 그러면 이 코드는 그 기간 동안 판매를 한 모든 고객의 회사명 목록을 받아 또 다른 TABLE 데이터 형식 테이블(@tblCus-tomers)에 넣습니다. 이 두 테이블은 실제 판매 수치를 제외하고는 결과 집합을 만드는데 필요한 기본 데이터를 모두 저장합니다.
첫 번째 테이블에는 모든 달(12행)이 표시되며 그 기간 동안 판매를 한 모든 고객은 두 번째 테이블(81)에 표시됩니다. 모든 고객이 지난 12달 동안 매달 제품을 구입하지는 않았으므로 INNER 또는 LEFT JOIN을 실행하면 매달 모든 고객을 반환하지는 않으며 고객이 뭔가를 구입한 달과 그 고객만을 반환합니다.
Cartesian 제품은 모든 달에 대해 모든 고객을 반환할 수 있습니다. Cartesian 제품은 기본적으로 첫 번째 테이블에 두 번째 테이블을 곱하므로 첫 번째 테이블의 행 수 곱하기 두 번째 테이블의 행수가 들어 있는 행 집합이 만들어 집니다. 따라서 Cartesian 제품은 @tblFinal 테이블에 972행을 반환합니다. 마지막 단계는 이 날짜 범위동안 개별 고객에 대한 월간 총 판매량을 사용해 @tblFinal 테이블을 업데이트하고 최종 행 집합을 선택하는 것입니다.
Cartesian 제품은 리소스 집약적이므로 진짜 이 제품이 필요한 것이 아니라면 CROSS JOIN을 주의해서 사용하는 것이 좋습니다. 예를 들어, 제품과 카테고리에 CROSS JOIN을 실행한 다음 WHERE 절, DISTINCT 또는 GROUP BY를 사용하여 대부분의 행을 필터링하는 경우 INNER JOIN을 사용하면 훨씬 효과적인 방식으로 동일한 결과를 얻을 수 있습니다. 월간 판매일이 표시된 그래프를 로드하고자 하는 경우처럼 모든 가능성에 대해 데이터가 반환되기를 원하는 경우 Cartesian 제품은 아주 유용할 수 있습니다. 하지만 대부분의 경우 INNER JOIN이 훨씬 더 효율적이므로 다른 용도로는 사용하지 말아야 합니다.

기타
다음은 SQL 쿼리의 효율성을 높이는데 도움이 될 수 있는 다른 몇몇 일반적인 테크닉입니다. 모든 판매 담당자를 지역별로 묶은 다음 그 판매액을 집계하려고 한다고 가정합니다. 단, 데이터베이스에서 활성으로 표시된 판매 담당자만을 원한다고 합니다. 이 판매 담당자를 지역별로 묶은 다음 HAVING 절이나 WHERE 절을 사용하여 활성 상태가 아닌 판매 담당자를 제거할 수 있습니다. WHERE 절에서 이 작업을 하면 그룹화해야 하는 행의 수가 줄어들므로 HAVING 절을 사용하는 것보다 훨씬 효과적입니다. HAVING 절에서 행 기반 기준을 필터링하면 이 쿼리는 WHERE 절에서 제거되었을 데이터를 그룹화합니다.
효율성을 높이기 위한 또 다른 트릭은 GROUP BY 절을 사용하는 대신 DISTINCT 키워드를 사용하여 고유한 데이터 행 목록을 찾아내는 것입니다. 이 경우 DISTINCT 키워드를 사용하는 SQL이 더 효과적입니다. GROUP BY는 집계 함수 (SUM, COUNT, MAX 등)를 계산해야 하는 경우를 위해 남겨 두십시오. 이 외에도, 쿼리가 항상 고유한 행을 반환하는 경우에는 IDSTINCT 키워드를 사용하지 마십시오. 이러한 경우 DISTINCT 키워드는 오버헤드만 가중시킬 뿐입니다.
다양한 테크닉을 사용하여 쿼리를 최적화하고 특정한 비즈니스 규칙을 구현할 수 있다는 것을 보았습니다. 문제는 몇몇 테크닉을 시도하여 그 성능을 비교해 보아야 한다는 것입니다. 가장 중요한 것은 테스트를 하고 또 해야 한다는 것입니다. 이 컬럼의 다음 호에서는 데이터베이스 디자인, 우수한 인덱싱 실행 기준, 그리고 SQL Server 보안 패러다임을 포함한 SQL Server 개념을 살펴 보겠습니다.

Johnny 에게 질문이나 의견 있으시면 mmdata@microsoft.com으 로 메일을 보내십시오.

Johnny Papa는 노스캐롤라이나 주 Raleigh에 있는 MJM Investigations의 정보 기술 담당 부사장으로 Professional ADO 2.5 RDS Programming with ASP 3.0(Wrox, 2000)을 저술했으며 업계 컨퍼런스에서 발표하는 모습을 자주 볼 수 있습니다. 연락처는mmdata@microsoft.com입 니다.

2012-09-16

DBMS/MSSQL]SQL Server 2005의 최신보안 기능을 이용한 해커 저지하기

해커 주의

SQL Server 2005의 최신보안 기능을 이용한 해커 저지하기

Don Kiely

이 기사는 SQL Server 2005의 프리릴리즈 버전에 근거합니다. 여기에 실린 모든 정보는 변경될 수 있습니다.

This article discusses:

세밀한 사용 권한과 최소 권한 원칙
메타데이터 가시성 제어
사용자와 스키마 분리
데이터베이스 실행 컨텍스트와 암호화

This article uses the following technologies:
SQL Server 2005, 보안

Code download available at:
SQLServerSecurity.exe (115KB)

지금은 여러분의 데이터베이스와 클라이언트 응용프로그램에 대해 더욱 더 적대적인 세상입니다. 공격자들은 매일 여러분의 귀중한 데이터를 손상시키기 위해 새롭고 영리한 공격을 개발합니다. 다행스럽게 SQL Server 2005은 철저한 방어와 최소 권한 같은 원칙을 전적으로 다루는 강력한 새로운 보안 기능들을 제공합니다. SQL Server 온라인 설명서에서 설명된 것처럼 Microsoft는 변화하는 보안 상황에 높을 보안을 유지하는 것에 관한 보다 나은 보안 도구와 문서를 제공하면서, 공격 표면적을 줄이고 SQL Server와 데이터베이스를 안전하게 배포하는 것을 쉽게 하는 많은 보안 전략을 구현했습니다.

이 문서에서는 개발자 관점에서 SQL Server2005내에서 가장 흥미로운 보안 기능 향상에 대해서 알아볼 것입니다. 저자는 TechNet Magazine (영문)의 2005년 봄호에서 관리자 보안 기능들에 대해서 다루었습니다. 그러나 종단점 인증과 서버에서 실행하는 관리 코드의 보안 컨텍스트에 대한 지원 같은 수많은 개발자 기능 향상이 있습니다. 저자는 핵심 관계형 데이터베이스 엔진에만 집중할 것입니다. Reporting Service나 Analysis Service 같은 지원 시스템은 전체기사를 할애할만한 가치가 있는 자체 보안 기반구조를 가지고 있습니다.

사용 권한

SQL Server 2000과 그 이전 버전은 사용자, 로그인 및 다른 대리 사용자(principal) 에게 권한을 할당하는 합리적인 스키마를 가지고 있습니다. 그러나 이런 버전의 SQL Server에 대한 락 다운(lock down)을 시도할 때 여러분은 강력한 보안을 구현하기 위해 날카로운 해부용 메스라기 보다는 상당히 뭉툭한 도구를 가지고 작업을 하고 있습니다. 여러분은 종종 사용자에게 사용자가 필요로 하는 권한 뿐만 아니라 사용자가 필요로 하지 않은 다른 많은 권한을 가지는 고정 역할을 사용자에게 할당해야만 합니다. 이것은 개개의 사용자나 다른 대리 사용자가 더도 말고 덜도 말고 딱 필요한 권한만 가진다는 최소 권한 원칙의 심각한 위반입니다.

SQL Server 2005에서는 부여할 수 있는 권한이 이전 버전보다 훨씬 한정되었습니다. 사실상 어떤 개체든 실질적인 대리 사용자에 수여할 수 있는 다양한 권한을 가지고 있습니다. SQL Server는 여전히 서버와 데이터베이스 수준에서 역할을 많이 사용합니다만 사용자 요구가 특정한 자원에 대한 제한된 액세스일 때 더 이상 사용자를 역할에 추가할 필요가 없습니다.

SQL Server 2005의 보안 용어에서, 대리 사용자(principal)는 보호되는 자원에 액세스하는 요청을 할 수 있고 이것에 액세스 하기 위해서 권한이 부여 되는 어떤 개인, 그룹(역할), 또는 프로세스입니다. 이전 버전의 SQL Server에서처럼 대리 사용자는 Windows내에 정의 할 수 있고 하거나 일치하는 Windows 대리 사용자 없이도 SQL Server 로그인을 기반으로 할 수 있습니다. 그림 1 에서는 고정 서버 역할과 고정 데이터베이스 역할을 제외한 SQL Server 2005 대리 사용자 계층구조를 보여주며 어떻게 로그인과 데이터베이스 사용자 보안 개체에 매핑될 수 있는지를 보여줍니다. 이전 버전에서처럼 SQL Server 2005에서 대리 사용자의 영향이 미치는 범위는 이것의 정의 범위 에 달려 있습니다. 따라서 Windows 수준 대리 사용자는 SQL 서버 수준 대리 사용자를 포함하고 SQL 서버 수준 대리 사용자는 데이터베이스 수준 대리 사용자를 포함합니다. 모든 데이터베이스 사용자는 자동적으로 고정 공용(Public) 역할에 속합니다.

또한 주목할만한 것은 개체는 사용 권한을 부여하고 거부함으로서 보호될 수 있다는 것입니다. 그림 2은 SQL Server 2005의 보호할 수 있는 개체 목록입니다. 서버 수준에서 서버로부터 와 서버로의 통신 채널을 제어하기 위한 네트워크 종단점뿐만 아니라 데이터베이스, 바인딩, 역할, 및 로그인을 보호할 수 있습니다. 데이터베이스와 스키마수준에서는 생성할 수 있는 모든 개체는, 특히 데이터베이스 개체들을 포함하기 위해 사용되는 스키마 개체, 사실상 보호할 수 있습니다.

고정 서버 역할들과 고정 데이터베이스 역할들은 SQL Server 2000와 비교해서 변경되지 않았습니다. 그래서 사용자나 응용프로그램이 전부 또는 대부분의 정의된 사용 권한을 요구할 때 이런 미리 정의된 사용 권한 묶음을 여전히 이용할 수 있습니다. 그러나 최소 권한 원칙하에서는 여러분은 대리 사용자에게 초과 권한을 부여하는 역할을 사용하기를 원하지 않을 것입니다. 비록 대리 사용자에 필요한 권한을 알아내고 사용 권한을 할당하는 것은 약간의 일을 더하게 되지만 이것은 좀 더 안전한 데이터베이스 환경일 수 있습니다.

SQL Server에서 사용 가능한 사용 권한의 숫자에 대해 알기 위해서, builtin_permissions 카타로그 뷰를 볼 수 있습니다:

          SELECT * FROM sys.fn_builtin_permissions(default)

4월의 CTP에서는 모든 사용 권한을 나타내는 180행 이상의 행을 반환합니다; 여기서는 좀 더 중요한 몇 가지에 대해서 설명할 것입니다.

CONTROL사용 권한은 권한을 허가 받든 사용자(grantee)에게, 보호할 수 있는 개체에 사용 권한을 수여하는 능력을 포함하여, 소유권을 수여합니다. 계층구조내의 한 레벨에 있는 보호할 수 있는 개체가 CONTROL 권한을 가지고 있다는 것은 포함된 모든 개체에 같은 권한이 있다는 것을 뜻합니다.

ALTER ANY 엔터티 사용 권한은 엔터티 형태의 어떤 개체에 생성(create), 대체(alter), 및 삭제(drop)하는 권한을 수여합니다. 예를 들어 서버 수준의 ALTER ANY DATABASE 사용권한은 권한을 허가 받은 사용자가 어떤 데이터베이스든 대체할 수 있게 합니다. 특정한 데이터베이스 범위내의 ALTER ANY ASSEMBLY 사용 권한은 권한을 허가 받은 사용자가 어떤 어셈블리든 변경할 수 있도록 합니다.

IMPERSONATE ON 로그인 또는 IMPERSONATE ON 사용자는 권한을 허가 받은 사용자에게 특정한 로그인이나 데이터베이스 사용자를 가장할 수 있게 해서 가장된 보안 자격 증명을 사용하여 어떤 연산이든 수행됩니다. 이 사용 권한은 EXECUTE AS 기능(이 기사의 뒤에서 설명하는)에서는 중요합니다.

SQL Server 2005는 보호할만한 개체에 권한을 할당하고 거부하기 위해서 잘 알려진 GRANT, DENY,및 REVOKE 스키마를 여전히 사용합니다. GRANT 구문은 권한 부여 영역과 대리 사용자가 다른 대리 사용자에게 권한를 부여할 수 있는지 와 같은 새로운 권한 옵션을 포함하도록 확장되었습니다. 데이터베이스 간의 사용자 권한은 허용되지 않습니다. 데이터베이스 간의 사용자 권한 부여를 위해서, 각각의 데이터베이스에 복제 사용자를 만들고 각각의 데이터베이스 사용자에게 권한을 할당해야 합니다. 이전 버전의 SQL Server와 같이 응용프로그램 역할 활성화는 역할이 활성화되는 기간동안 다른 권한을 보류됩니다.

특정한 권한 부여는 함축을 통해서 다른 사용 권한을 옮길 수 있습니다. SQL Server 온라인 설명서에는 sys.fn_builtin_permissions 카테고리 뷰로부터 계층구조 목록을 모으고 각각 사용 권한의 깊이를 식별하는 ImplyingPermissions 사용자 정의 함수를 위한 T-SQL이 있습니다. 개체와 사용 권한 형태를 전달해서 사용 권한의 계층 구조 목록을 생성하기 위해 아래 구문을 실행 합니다:

          SELECT * FROM master.dbo.ImplyingPermissions('schema', 'alter')

SQL Server 2005내에서 단지 얼마나 세밀한 사용 권한이 가능한지를 평가하는 것은 처음에는 어려울 것입니다. 서버와 대표적인 데이터베이스내의 사용 가능한 대리 사용자의 숫자와 형태 그리고 보호할 수 있는 개체의 숫자를 고려하십시오(검토하십시오). 또한 사용 가능한 사용 권한과 포함되고 내포한 사용 권한의 숫자를 고려하십시오. 응용프로그램 생성은 좀더 자세한 분석 보안 요구사항에 대한 상세한 분석과 모든 개체의 사용권한을 주의 깊은 제어를 필요로 합니다. 그럼에도 불구하고 여전히 부여할 수 있는 권한을 가지지 않는 동작들이 있습니다. 예를 들면 TRUNCATE TABLE은 테이블에 단지 테이블의 행을 삭제하는 것 이상의 능력을 수여하는 ALTER 사용 권한을 필요로 한다.

카탈로그 뷰와 메타데이터 가시성

SQL Server 2005의 고도로 세밀한 권한 스키마의 장점은 현대 데이터베이스 엔진이 필요로 하는 메타데이터에 대한 보다 나은 보호를 할 수 있다는 것입니다. SQL Server는 SQL-92 명세에 정의 되어 있는 INFORMATION_SCHEMA 뷰, 서버와 데이터베이스 수준의 시스템 테이블, 그리고 시스템에 내장된 막대한 숫자의 저장 프로시저를 통해서 강력히 지원되는 리치(rich) 메타데이터를 가지고 있습니다. 시스템 안정성에 커다란 위험이 있지만 시스템 테이블을 수정해서 서버와 서버의 데이터베이스의 내재하는 상태 조차도 변경할 수 있습니다. Microsoft는 언제든지 내재하는 시스템 테이블을 변경할 수 있어서 이것을 사용한 것은 본인의 책임임을 끊임없이 상기시킵니다.

SQL Server 2005에서 Microsoft는 시스템 전체의 모든 메타데이터를 표시하는 새로운 카탈로그 뷰 집합을 만들었습니다. 새로운 sys 스키마는 메타데이터 뷰를 캡슐화 합니다. 카타로그 뷰들은 읽기 전용이며 이것은 이전 버전의 SQL Server에서 가능했던 해킹을 제거합니다. 많은 뷰들이 원하는 정보를 쉽게 찾기 위해서 예전 시스템 테이블의 이름과 같은 이름을 사용합니다. 예를 들어 sysobjects 메타데이터 sys.objects 뷰를 통해서 표시됩니다. 뷰 정의는 물리적으로 이 정의에 직접적으로 액세스하는 방법이 없는 알려지지 않은 (undocumented) 위치에 숨겨져 있습니다. (서버에 해킹을 해서 이것을 찾지 않으면) 그럼에도 불구하고 메시지는 명백합니다: 서버와 데이터베이스의 모든 데이터를 얻기 위해 카타로그 뷰를 이용하십시오. 그리고 이전 해킹에 대해서는 잊어버리십시오. 저장 프로시저, T-SQL, 및 그 밖의 SQL Server를 구성하고 조정(tweaking)하는 공식적인 방법을 준수하십시오. 이것은 모두 보안과 안정성을 위해서 입니다.

보안 관점에서 메타데이터를 표시하기 위해서 뷰를 사용하는 것의 장점은 카탈로그 뷰로부터 반환되는 데이터가 데이터를 요청하는 사용자 컨텍스트의 보안에 따라 필터된다는 것입니다. 예를 들어, 과거에는 데이터베이스내의 sysobject 테이블에 액세스하는 권한을 가지고 있는 사용자(대부분의 사용자는 많은 응용프로그램 내에서 합니다.)가 테이블에 대한 쿼리를 실행할 수 있고 데이터베이스에 있는 모든 사용가능한 개체를, 이 사용자가 각각의 개체에 대한 액세스가 있던 없던 상관없이, 볼 수 있었습니다. SQL Server 2005에서는 테이블을 보기 위해서는 테이블에 대한 SELECT 권한 같은 최소한의 액세스를 가지고 있어만 합니다. 만약 사용자가 데이터베이스에 대한 액세스를 가지고 있지만 이 데이터베이스 개체에 대해서는 액세스 없으면, sys.objects에 대한 쿼리는 아무런 결과 행을 반환하지 않습니다.

이것이 어떤 식으로 동작하는지 보기 위해서 다음의 코드를 관리자로 실행하십시오:

          USE master

          CREATE LOGIN User1 WITH password = 'myPassword'

          

          

          -- 여러분이 선택한 데이터베이스를 사용하십시오

          

          USE AdventureWorks

          CREATE USER User1

          

          EXECUTE AS LOGIN = 'User1'

          SELECT * FROM sys.objects

          

          -- 사용자 db내에 사용권한이 없기 때문에 아무런 행이 반환되지 안습니다.

          

          REVERT

코드는 서버에 대한 User1 로그인을 생성하는 것으로 시작해서 로그인한 사용자와 매핑 되는 사용자를 예제 AdventureWorks 데이터베이스에 추가합니다. 그리고는 실행 컨텍스트를 User1으로 바꾸고 sys.objects로부터 데이터를 검색합니다. User1은 데이터베이스에 대한 액세스는 가지고 있지만 어떤 개체에 대한 권한도 없기 때문에 카타로그 뷰로부터 아무것도 반환되지 않습니다. REVERT 구문은 관리자로 실행 컨텍스트를 복귀시킵니다.

데이터가 반환되기 위해서는 두개의 권한을 사용자에게 부여해야만 합니다. 다음 코드는 Contact 테이블에 대한 SELECT 권한과 dbo.uspGetBillOfMaterials 저장 프로시저에 대한 EXECUTE 권한을 부여합니다.

          -- 테이블과 저장 프로시저에 대한 권한을 부여 하십시오 

          

          GRANT SELECT ON Person.Contact TO User1

          GRANT EXECUTE ON dbo.uspGetBillOfMaterials TO User1

          

          -- User1으로 다시 실행하십시오

          

          EXECUTE AS LOGIN = 'User1'

          SELECT * FROM sys.objects

          REVERT

User1으로 카타로그 뷰를 실행했을 때, sys.objects 카타로그 뷰로부터 Contact 테이블와 uspGetBillOfMaterials 저장 프로시저의 메타데이터뿐만 아니라 테이블 제약 조건과 트리거 같은 연관된 개체를 포함하는 아홉개의 행을 얻게 됩니다. 이것은 개체 계층구조의 한 수준에 권한을 부여하면 연관된 권한이 자식 개체에 부여되는 방법을 보여줍니다.

예상한 것처럼, sysadmins와 sa는 시스템 카타로그 뷰에서 서버에 있는 모든 것을 볼 수 있으며 데이터베이스 소유자는 데이터베이스의 모든 것을 볼 수 있습니다. 사용 권한 기반 필터링은 sp_help와 sp_helpdb 같은 별개의 개체에 대한 정보를 보여주는 시스템 저장 프로시저에도 적용합니다. 이런 시스템 저장 프로시저는 시스템 카타로그 뷰를 읽기 때문에, 프로시저를 실행하는 대리 사용자의 권한에 따라 필터링됩니다. 그렇지만 메타데이터 가시성의 한계가 아직까지는 모든 메타데이터 함수에 적용되지 않았기 때문에(OBJECTPROPERTY같은) 주의해야만 합니다.

대부분의 예전 시스템 테이블, 저장 프로시저, 및 뷰들은(모두 읽기 전용 뷰로 표시되는 것을 제외하고) 여전히 사용하는 가능합니다. 이것들은 이전 버전 호환을 위한 것이며 어떤 SQL Server 2005의 새로운 기능도 나타내지 않습니다.

사용자/스키마 분리

ANSI SQL-99 명세는 데이터베이스 스키마를 개체의 단일 네임스페이스로 구성하는 단일 대리 사용자가 소유한 데이터베이스 개체의 집합으로 정의 합니다. 스키마는 테이블, 뷰, 저장 프로시저, 함수, 타입, 트리거 같은 데이터베이스 개체를 포함하며 이것은 개체를 그룹화하는 간편한 방법이며 그래서 데이터베이스가 서로 다른 소유자 밑에서 개체 이름과 그룹 개체를 재사용할 수 있습니다.

SQL Server 2000에서는 그림 3의 제일 윗 부분에서 보는 것 같이 소요자와 스키마를 구별할 수 없었습니다. 만약 관리자가 데이터베이스 내에 Fred라는 이름의 사용자를 생성하면 Fred라는 이름의 스키마를 자동으로 생성하고 Fred 사용자 밑에 숨깁니다. Fred 사용자가 SQL Server에 로그인하고 Table1 테이블을 생성하면 테이블 이름은 Fred.Table1이 됩니다. Fred가 만드는 Fred.StoredProcedure와 Fred.View1같은 다른 개체에 대해서도 마찬가지 입니다. Fred가 데이터베이스의 소유자이면 Fred1 개체가 대신 생성되고 dbo 스키마의 일부가 됩니다.

Fred가 퇴사하고 George가 Fred의 일을 맡은 때 같은 개체의 소유권 변경이 필요할 때 문제가 발생합니다. 시스템 관리자는 Fred가 가지고 있는 모든 개체의 소유권을 변경해야만 합니다. Fred.Table1를 참조하는 T-SQL이나 클라이언트 응용프로그램 코드는 George가 테이블의 소유권을 획득한 후에 George.Table1로 변경되어야 합니다. Fred가 소유하고 있는 개체의 수와 얼마나 많은 응용프로그램이 내부에 이 이름을 포함하고 있는가에 따라서 이것은 복잡해질 수 있습니다.

SQL Server 2005는 이 문제를 해결하며 그림 3의 아래 부분에서 보는 것과 같이 사용자와 스키마를 분리함으로써 SQL-99스키마를 좀 더 엄격하게 구현합니다. 새로운 DDL 구문을 이용해서 Fred라는 이름의 사용자를 만들 때 SQL Server는 더 이상 자동으로 같은 이름의 스키마를 생성하지 않습니다. 대신에 명시적으로 스키마를 생성하고 소유권을 할당해야만 합니다. 모든 데이터베이스 개체가 MySchema 스키마 (처음에 Fred가 소유하는)에 포함되어 있기 때문에 스키마의 소유권을 George로 바꿈으로써 스키마의 모든 개체의 소유권을 바꾸는 것은 간단해 집니다. 각각의 사용자는 이 사용자에 할당되는 기본 스키마를 가지고 있어서 스키마 참조 없이 이름으로 참조되는 어떤 개체든 기본 스키마 내에 있다고 여겨집니다. 그래서 그림 3의 아래 부분에서 사용자 Fred가 기본 스키마로 MySchema를 가지고 있으면 테이블을 MySchema.Table1나 또는 간단히 Table1로 참조할 수 있습니다. George는 아마도 이름에 연관된 기본 스키마가 없어서 테이블을 참조 할 때 MySchema.Table1로 참조해야만 할 겁니다. 지정된 기본 스키마가 없는 사용자는 기본적으로 dbo를 갖습니다.

그림 4 는 이것이 어떻게 작동하는지를 보여줍니다. 코드는 Carol이라는 이름의 로그인을 만드는 것으로 시작하고 CREATE TABLE 권한을 가지는 Pub 데이터베이스의 Carol이라는 이름의 사용자를 생성합니다. 코드를 실행하는 보안 컨텍스트는 Carol로 바꿔고 Carol 테이블 생성을 시도 합니다. 이 시도는 “지정된 스키마 이름 ’dbo’가 존재하지 않거나 이것을 이용할 권한이 없습니다.”라는 에러와 함께 실패합니다. 이 경우의 문제점은 Carol이 dbo 스키마에 개체를 생성할 권한이 없다는 것입니다. 기본 스키마 지정이 되지 않았기 때문에 CREATE TABLE 구문은 dbo.table1생성을 시도합니다.

이 코드는 이 코드를 실행하고 CarolSchema을 생성하고 Carol에게 스키마의 소유권을 수여한 signed in 한 admin 사용자로 복귀합니다. 그리고 Carol의 보안 컨텍스트 내에서 다시 실행되고 다시 테이블 생성을 시도합니다. 이 시도는 같은 에러 메시지를 가지고 실패합니다. Carol이 그녀의 재량권에 스키마를 가지고 있기 때문인데 이것은 SQL Server가 기본적으로 이것을 사용할 것이라는 것을 의미하지 않습니다. 결과적으로 이용할 사용 권한을 가지고 있는 개체 이름과 스키마를 명시적으로 사용할 때 CarolSchema.table1을 성공적으로 생성할 수 있습니다. 두 번째 테이블 생성 시도의 실패는 CarolSchema 이 존재한 이후에, 아래 보는 바와 같이 사용자 Carol를 생성할 때나 사용자에 기본 설정을 추가함으로, Carol 기본 스키마를 설정해서 성공할 수 있습니다.

          CREATE USER Carol FOR LOGIN Carol WITH DEFAULT_SCHEMA = CarolSchema

          -- or

          ALTER USER Carol WITH DEFAULT_SCHEMA = CarolSchema

실행 컨텍스트

SQL Server는 데이터에 접근하는 코드를 실행하는 사용자가 적절한 권한을 가지고 있다는 것을 보증하는 강력히 지원되는 소유권 연결(chaining)을 가지고 있습니다. 코드를 호출하는 사용자가 실행 권한을 가지고 있고 그리고, 예를 들어, 코드의 소유자가 액세스한 두 개의 테이블과 뷰의 소유자이기만 하면, 더 이상의 권한이 검사되지 않고 호출자는 요청된 데이터를 받습니다. 만약 소유권 체인이 손상(예를 들면 코드의 소유자가 참조되는 뷰를 소유하고 있지 않은경우)되면 호출자의 보안 컨텍스트를 검사합니다.

만약 호출자가 뷰에 액세스하는 권한이 있다면, 데이터는 반환됩니다. 만약 호출자가 뷰에 액세스 하는 권한을 가지고 있지 않다면, 데이터는 반환되지 않을 것입니다. 이것이 SQL Server 200와 이전 버전이 작동하는 방법이었습니다. 데이터에 대한 액세스를 프로시저나 함수를 통해서 사용자에게 수여할 때 이런 방법은 좋습니다. 소유권 연결은 약간의 제한이 있습니다. 한가지는 데이터 조작 작업에만 적용되고 동적 SQL에는 아닙니다.

그러나 호출자의 권한을 데이터 액세스의 유효성 검사에 사용하는 것을 원하지는 않을 것입니다. 데이터를 보호하기 위해 주의 깊게 디자인된 프레임워크의 일부분으로써, 권한을 검사하기 위해서 다른 사용자의 보안 컨텍스트를 사용하는 저장 프로시저를 생성하고 싶다면 어떻게 할 것인가? 실행 컨텍스트의 개념을 소개하는 SQL Server 2005이전에는 어떤 쉬운 선택 사항들이 없었습니다. 이제 저장 프로서저, 데이터 조작 트리거, 및 사용자 정의 함수(인라인 테이블 값을 제외하고)를 정의 할 때, EXECUTE AS 절을 이용하여 SQL Server가 개체와 프로시저에 의해 참조되는 개체와 데이터에 대한 액세스의 유효성 검사를 위해 어떤 사용자의 권한을 사용할 것인가를 지정할 수 있습니다. 예를 들어 코드 생성자와 관련된 권한을 데이터에 액세스하는데 항상 사용하도록 지정할 수 있습니다. 다음은 지정된 사용자 권한을 가지고 코드를 실행하는 예제입니다. 이 경우에 실행 컨텍스트가 ec의 컨텍스트이기 때문에 사용자 ec는 반듯이 titles 테이블에 대한 SELECT 권한을 가지고 있어야 합니다:

          USE pubs

          

          CREATE LOGIN ec WITH PASSWORD = 'ecpassword'

          CREATE USER ec FOR LOGIN ec

          

          CREATE PROCEDURE GetTitlesEC(@Table varchar(40))

          WITH EXECUTE AS 'ec'

          AS

              EXECUTE('SELECT * FROM ' + quotename(@Table))

          GO

SQL Server 2005는 네 가지의 실행 컨텍스트 옵션을 제공합니다. EXECUTE AS CALLER는 코드 모듈의 호출자 컨텍스트내에서 실행되도록 지정합니다. 따라서 호출자는 모듈의 실행 권한뿐만 아니라 모든 내제하는 개체의 액세스 권한을 가지고 있어야 합니다. SQL Server는 끊어진 소유권 체인에 대해서만 권한을 검사합니다. 그래서 만약 코드의 소유자가 내제하는 개체도 소유하면, 모듈의 실행 권한만 검사됩니다. 이것은 이전 버전 호환성을 보장하기 위한 기본 실행 컨텍스트입니다.

EXECUTE AS 'user_name'는 지정된 사용자의 보안 컨텍스트내에서 코드가 실행되는 것을 지정합니다. 이것은 소유권 체인화에 의지하고 싶지 않고 대신에 코드를 실행할 필요한 권한을 가진 사용자를 생성하기를 원한다면 주목할 만한 옵션입니다.

EXECUTE AS SELF는 모듈이 모듈을 생성하고 대체하는 사용자의 보안 컨텍스트 아래 실행되는 것을 지정하기 위한 간단한 표기법입니다. SQL Server는 SELF가 아니라 모듈과 연관된 실제 사용자 이름을 저장합니다.

EXECUTE AS OWNER는 보안 컨텍스트가 모듈의 현재 소유자의 컨텍스트라는 것을 지정합니다. 만약 소유자가 지정되지 않으면 포함하는 스키마의 소유자의 컨텍스트가 사용됩니다. 소유자는 지정된 사용자(역할이 아닌)의 싱글턴(singleton) 계정이어야 합니다. 모듈 자제를 변경하지 않고 모듈의 소유자를 변경할 수 있기를 원할 때 주목할 만한 옵션입니다.

코드의 실행 컨텍스트를 변경하는 데는 약간의 제한이 있습니다. 모듈의 생성자는 반드시 특정한 사용자를 위한 IMPERSONATE 권한이 있어야 합니다. 여러분 자신을 가장하기 위해 이 권한을 가질 필요는 없습니다. 또한 특정 사용자를 더 이상 사용하지 않기 위해서 모든 모듈의 실행 컨텍스트를 변경하기 전까지 데이터베이스에서 이 사용자를 삭제할 수 없습니다. 소유권 연결은 모듈 내에서 동적 SQL 실행에는 적용하지 않습니다. SQL Server 2000고 마찬가지로 실행 컨텍스트는 동적 SQL에 의해 액세스된 내제하는 개체의 권한을 가지고 있어야 합니다.

암호화

귀중한 자원에 대한 최선의 보호는 보안을 겹겹이 하는 것 입니다 ? 철저한 방어로 알려진 원칙. 공격자는 상품에 도달하기 전에 계층을 연달아 통과해야 합니다. 해커는 빈번하게 네트워크 보안을 손상시켜왔고 그 다음엔 컴퓨터 보안을 그 다음엔 데이터베이스를 손상시켜서 귀중한 데이터에 자유로운 액세스를 획득해 왔습니다.

SQL Server에 암호화된 데이터는 방어의 마지막 선입니다. 비록 공격자가 데이터베이스 액세스를 성공적으로 획득했다 하더라고 데이터를 해독 해야만 합니다. 요즘의 강력한 암호화 알고리즘이라면 해독 키 없이는 공격자는 거의 넘을 수 없는 도전에 직면하게 됩니다. 물론 암호화는 결코 공짜가 아닙니다. 여러분은 아마도 상품 카탈로그 같은 공개적으로 접근가능한 데이터를 포함해서 SQL Server내에 저장되어 있는 모든 데이터를 암호화 하고 싶은 유혹에 빠져들 수도 있을 겁니다. 그러나 암호화는 프로세서 집중적이기 때문 성능상의 막대한 타격을 초래할 수 있습니다. 암호화나 해독에 요구되는 처리 주기 테이블의 하나의 짧은 문자열을 1000만행으로 증대시켜서 쉽게 서버를 굴복시킬 수 있습니다. 보호가 필요한 수준을 고려해서 필드를 보호하는 것이 비용을 지출을 초래할 만한 가치가 있는 때만 암호화를 사용하십시오.

SQL Server 2005는 다양한 키 형태과 암호화 알고리즘을 제공합니다. 대층 키에는 RC4, RC2, DES군, 그리고 AES군 알고리즘를 사용할 수 있습니다. 비대층 키로 RSA를 제공하고 증명서로는 인터넷 엔지니어링 테스크 포스팀의 X.509 V1 표준을 사용합니다.

암호화의 가장 어려운 부분은 키 관리(기밀사항을 비밀에 붙이는것)입니다. 만약 공격자가 데이터를 암호화하는데 사용한 대칭키를 획득하면 데이터에 액세스 할수 있고 거리낌없이 데이터를 변경할 수 있습니다. SQL Server 2005는 서버내의 별개의 범위에 다양한 형태의 키를 보호하기 위해 암호화 개체의 계층구조를 사용해서(그림 5에 서 보듯이) 여러분이 직접 키를 관리하거나 SQL Server가 여러분의 위해 키를 관리하도록 할 수 있습니다.

그림 5 의 제일 위는 SQL Server의 모든 키와 인증서의 부모인 서비스 마스터 키입니다. 서비스 마스터 키(서버의 모든 키를 직간접적으로 암호화 하는 대칭키)는 SQL Server를 설치할 때 자동으로 생성됩니다. 만약 이것이 손상되면, 공격자는 결과적으로 모든 데이터베이스내의 모든 키를 해독(crack)해 낼 수 있습니다. 이런 이유 때문에 이것은 윈도우 내의 Data Protection API(DPAPI)에 의해 보호됩니다. SQL Server가 운영중인 서비스 계정 이름을 이용해서 이것이 액세스할 수 있습니다.

비록 그림 6 에서 보이는 T-SQL문을 이용하여 키를 파일에 덤프하고, 키를 재생성, 파일에서 키를 복원하는 다소간의 관리 작업을 수행 할 수는 있지만 SQL Server가 여러분을 위해 서비스 마스터 키 관리를 책임집니다.

대부분의 경우 키에 어떤 변화도 원하지 않거나 필요하지 않지만, ALTER SERVICE MASTER KEY구문은 복구 옵션과 DPAI에서 키를 암호화하는데 사용하 서비스 계정을 변경하는 옵션을 가지고 있고 있습니다.

데이터베이스의 범위 내에서 데이터베이스 마스터 키는 모든 키와 인증서와 데이터베이스 데이터의 루트 암호화 개체입니다. 각각의 데이터베이스는 단일 마스터 키를 가질 수 있습니다: 만약 보조 키를 만들려고 시도한다면 에러가 발생할 것 입니다.

사용자가 제공하는 패스워드를 가지고 CREATE MASTER KEY T-SQL 사용하기 전에 CREATE MASTER KEY T-SQL를 통해 데이터베이스 마스터 키를 생성해야만 합니다:

          CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'WeZ#6hv*XHq#akAEaqcr7%CUP3aQ'

결과적으로 생성되는 키는 triple DES로 암호화되고 두 번 저장됩니다. 첫 번째로 저장되는 장소는 sys.symmetric_keys 데이터베이스 테이블에 저장되며 제공되는 패스워드에 의해 암호화되고, 두 번째로는 master 데이터베이스의 sys.databases 테이블에 저장되며 서비스 마스터 키를 이용하여 암호화됩니다. 이 복제 저장소는 마스터 키가 자동으로 열릴 수 있게합니다.

데이터베이스에서 현재의 마스터 키를 분리해 내서 다른 서버로 이동하는 것는 문제를 발생시킵니다. 왜냐하면 새로운 서버의 서비스 마스터 키가 서로 다르기 때문입니다. ALTER MASTER KEY는 이전 서버의 서비스 마스터 키에 의한 암호화를 해제하는 옵션을 가지고 있으며 이렇게 한 뒤에 새로운 서버에 추가합니다. 이렇게 하지 않으면 마스터 키를 사용하기 전에 항상 명시적으로 열어야만 합니다.

데이터베이스 마스터 키가 존재하면 필요한 암호화에 따라 세가지 형태의 키를 만드는데 사용할 수 있습니다.

비대칭 키는 공개키 공개키와 개인키의 쌍을 가지는 공개키 암호화에 사용됩니다.
대칭 키는 데이터를 암호화하고 해독할 때 같은 키를 사용하는 공유되는 비밀에 사용됩니다.
인증서는 기본적으로 공개키에 대한 래퍼입니다.

그림 5는 이 키들과 인증서 다른 키나 데이터를 암호화 하는데 사용되는 방법을 보여줍니다. 비대칭 키는 대칭 키와 데이터를 암호화 할 수 있으며, 대칭 키는 다른 대칭 키와 데이터를 암호화 할 수 있으며, 인증서는 대칭 키와 데이터를 암호호 할 수 있습니다. 만약 여러분이 직접 키 관리를 다루고 싶다면 대칭 키는 사용자 지정 패스워드를 이용해서 생성될 수 있습니다. 물론 결국에는 이 모든 키는 데이터베이스 마스터 키를 이용해서 암호화 됩니다.

인증서는 문서, 이메일, 파일 서명에 관해 얘기할 때 전자 서명의 컨텍스트에서 사용되는 같은 종류의 개체입니다. 인증서 개체는 비대칭 암호화에 사용되는 공개키를 이것이 생성하는 방법에 대한 풍부한 옵션을 가지고 감쌉니다. 그러나 SQL Server모든 자세한 사항을 책임지기 때문에 다른 환경에서 인증서를 생성하기 위해 요구되는 단계를 숨깁니다.

SQL Server가 자체적인 사용을 위해서 인증서를 만들게 할 수 있으며 Microsoft 인증 서버나 VeriSign과 같은 다른 신뢰된 인증 기관이 발행한 인증서를 가져올게 할 수 있습니다. 옵션은 개인 키를 암호화하는데 사용하기 위한 패스워드 설정이나 SQL Server 데이터베이스 마스터 키를 사용하도록 하는 설정뿐만 아니라 생성일과 만료일을 설정을 포함합니다. 인증서를 파일부터 가져오거나 서명된 실행 파일이나 탑재된 .NET 어셈블리로부터 인증서를 읽을 수 있습니다. 다음의 코드는 두개의 인증서를 만드는데 User1과 연관된 만료되지 는 하나와 User2을 위한 2005년에 만료되는 다른 하나입니다:

          -- User1과 관련된 데이터베이스 마스터 키로 암호화된 인증서를 생성하십오  

          

          -- database master key. 데이터베이스 마스터 키는 이미 존재해야 합니다. 

          

          

          CREATE CERTIFICATE User1Certificate

              AUTHORIZATION User1 WITH subject = 'Certificate For User1'

          GO

          -- 제한된 User2를 위한 인증서를 생성하십시오 

          

          CREATE CERTIFICATE User2Certificate

              AUTHORIZATION User2 WITH subject = 'Certificate For User2',

              EXPIRY_DATE = '12/31/2005',

              ENCRYPTION_PASSWORD = 'q%dsabciJ&#QZk#wM5G!WB36z5m7'

인증서가 존재하면 암호화를 지원하는 새로운 T-SQL 함수 EncryptByCert를 이용하여 데이터를 암호화할 수 있습니다. 다음과 같은 Customer라는 이름의 테이블이 있다고 생각해 봅시다

          CREATE TABLE Customer (

              CustId int, 

              name nvarchar(30), 

              City varchar(20), 

              CreditCardType varbinary(300),

              CreditCardNumber varbinary(300), 

              Notes varbinary(4000))

          GO

암호화된 데이터는 바이너리라서 테이블은 이것을 담기 위해 varbinary 필드를 사용합니다. 암호화된 데이터는 디지털 인증을 포함하기 때문에 필드 길이는 간단한 문자열 “Visa”와 크레디트 카드 번호를 담지만 다소간 커야 합니다. 여기서는 이 테이블에 데이터 행을 삽입하는 방법을 보여줍니다.

          INSERT INTO Customer VALUES (1, 'Don Kiely', 'Fairbanks',

              EncryptByCert(Cert_ID('User1Certificate'), 'Visa'),

              EncryptByCert(Cert_ID('User1Certificate'), '1234-5678-8765-4321'),

              EncryptByCert(Cert_ID('User1Certificate'), 

                  'This customer is a real flake. Don''t trust him!'))

EncryptByCert는 Cert_ID 함수(여러분이 인증서에 명명함 이름을 이용하는)를 가지고 얻을 수 있는 인증서 ID와 암호화되는 데이터의 두개의 인수를 가집니다.

테이블에서 데이터를 select할 때 암호화한 데이터의 장점을 볼 수 있습니다. SQL Server Management Studio 에서 SELECT * FROM Customer를 실행하면 그림 7에 보이는 결과를 반환합니다. INSERT문은 Encrypt함수에 대해 다중 호출를 사용하기 때문에 각각의 필드가 다른 메소드를 이용하여 암호화될 수 있습니다. SELECT 문의 일부분으로 데이터를 해도하기 위해서는 아래에 보이는 DecryptByCert를 이용하십시오:

To decrypt the data as part of a SELECT statement, use DecryptByCert as shown here:

          SELECT CustID, Name, City,

              CONVERT(VARCHAR, 

                  DecryptByCert(Cert_ID('User1Certificate'),

                      CreditCardType)) AS CardType,

              CONVERT(VARCHAR, 

                  DecryptByCert(Cert_ID('User1Certificate'),

                      CreditCardNumber)) AS CardNumber,

              CONVERT(VARCHAR, 

                  DecryptByCert(Cert_ID('User1Certificate'),Notes)) AS Notes

          FROM Customer

DecryptByCert는 varbinary 데이터를 반환하기 때문에 일반적으로 다른 데이터 타입(여기서는 varchar)으로 변화해야 합니다. 이것은 그림 8에서 보는 바와 같이 일반 텍스트로 반환합니다.

비대칭 키를 가지고 수행하는 암호화의 매우 일반적인 형식은 공개 키 암호화라 불립니다. SQL Server 2005내의 비대칭 키는 512, 1,024,또는 2,048 bit 크기의 키를 가지는 RSA 알고리즘을 사용합니다. 지정된 패스워드나 데이터베이스 마스터 키를 가지고 생성된 개인 키를 암호화할 수 있습니다. 또한 디스크 파일, 실행 파일, 메모리에 로드된 .NET 어셈블리에서 키를 가져올 수 있습니다. 다음 코드는 키를 생성하는 서로 다른 두 가지 방법을 보여줍니다. 첫 번째는 사용자에 의해 관리되는 시크릿(secret)를 이용하고 두 번째는 SQL Server에 의해 관리되는 데이터베이스 마스터 키를 이용합니다:

          -- 사용자 제공 패스워드에 의해 보호되는  

          

          -- 개인 키를 가지고 비대칭 키를 만드십시오.

          

          CREATE ASYMMETRIC KEY User1AsymmetricKey

              AUTHORIZATION User1

              WITH ALGORITHM = RSA_2048

              ENCRYPTION BY PASSWORD = 'AVeryVerySecretPassword'

          

          -- 데이터베이스 마스터키에 의해서 보호되는 다른 하나를 만드십시오 

          

          CREATE ASYMMETRIC KEY User2AsymmetricKey

              AUTHORIZATION User1

              WITH ALGORITHM = RSA_2048

데이터베이스에서 키가 사용하는 하면 데이터를 암호화 하기 위해서 EncryptByAsymKey 함수를 사용할 수 있습니다. EncryptByCert 함수와 비슷하게 첫 번째 인수는 AsymKey_ID 함수를 사용해서 얻을 수 있는 여러분이 사용하고자 하는 키의 ID입니다. 비록 그림 9 에서는 이런 접근 방법이 사용하지만 레코드의 모든 암호화되는 필드에 같은 키를 사용할 필요는 없습니다.

각각의 열의 데이터를 해독하기 위해서 데이터 암호화에 사용된 비대칭 키의 이름을 가지고 AsymKey_ID 함수를 한번 더 이용하여 DecryptByAsymKey 함수를 사용하십시오. 그림 10 에서는 User1AsymmetricKey가 패스워드를 가지고 생성됩니다. 따라서 DecryptByAsymKey 함수 호출의 세 번째 필드에 패스워드를 전달해야 합니다. varbinary 데이터를 사람이 읽을 수 있는 형태로 변환하기 위해서 CONVERT함수를 사용해야 합니다.( 그림 10참조)

데이터베이스 마스터 키를 가지고 생성되는 User2AsymmetricKey(그림 11참고)를 가지고 데이터를 암호화 하기 위해서는, SQL Server가 키를 관리하기 때문에 어떤 자격 증명도 전달할 필요가 없습니다. 바라건대 SQL Server가 여러분을 위해 키를 관리하도록 하는 것이 매운 쉽다는 것을 보기 바랍니다.

만약 SQL Server이 비대칭 키 쌍을 생성하면 다른 사람에게 전송하기 위한 공개키를 sys.asymmetric_keys 카타로그 뷰의 public_key 필드에서 구할 수 있습니다. 다음 코드는 생성하는 각각의 비대칭 키 쌍과 연관된 공개 키를 반환합니다:

          SELECT Name, Public_Key FROM sys.asymmetric_keys

인증서와 비대칭 키는 SQL Server데이터를 위한 고성능의 암호화를 제공합니다. 특히 데이터베이스 외부로 데이터를 옮기고 그 도중에 데이터를 보호해야 할 때 고성능의 암호화를 제공합니다. 그러나 서버에 있는 데이터를 보호해야하고 다른 위치에서도 걱정 없으려면 대칭 키는 좋은 선택입니다. 데이터는 안전하게 저장되어 있고 서버에서 해독되고, 클라이언트에 보내고 인증된 사용자에게 보여지는 것은 상당히 일반적인 데이터베이스 시나리오입니다. 대칭 키는 다른 형태의 암호화보다 프로세싱 싸이클이 덜 필요합니다. 이것은 데이터베이스 응용프로그램에 아주 적절하기 때문에 SQL Server내의 대층키 암호화는 아주 유연합니다.

대칭 키 생성은 인증서와 비대칭 키를 생성하는 것과 유사합니다. CREATE SYMMETRIC KEY 구문은 여러 옵션을 가지고 있습니다. 한 옵션은 데이터베이스에 저장될 때 키 자체가 암호화되는 방법입니다. 그 림 5을 참조하여 인증서, 비대칭 키, 사용자 지정 패스워드, 또는 다른 대칭 키를 사용하는 대칭 키를 보호할 수 있습니다. 그 외의 옵션은 사용된 압축 알고리즘, 키를 생성하기 위해 사용자가 제공한 패스워드를 이용할 것인가, 및 선택적인 ID 문구 을 포함한다. 이 구문은 암호화된 데이터에 임시 키를 태그(tag)로 다는데 사용되는 GUID를 생성합니다. SQL Server는 DES, triple DES, 및 AES을 비롯해서 가장 널리 사용되는 대부분의 대칭 키 알고리즘을 지원합니다.

대칭 키를 생성하고 이 키를 직접 관리할 것인지SQL Server가 관리할 것인가를 고려할 때 선택적인 패스워드를 어떻게 보호할 것인가에 대한 고려가 중요합니다. 키를 암호화 하기 위해 패스워드를 지정하면, 생성된 대칭 키를 가지고 데이트를 암호화하는데 사용하려고 선택한 알고리즘이 뭐던지 상관없이, triple DES가 패스워드로부터 키를 이끌어 내는데 사용됩니다. 키를 보호하는 패스워드를 보호하는데 사용된 것보다 더 강력한 암호화를 데이터를 위해 사용하는 것은 가능합니다.

그림 12 은 대칭 키를 생성하는 많은 옵션중 몇 가지를 보여 줍니다. 첫 번째 예제는 현재 있는 인증서에 의해 triple DES를 가지고 암호화한 키를 생성하고 User1 소유권을 결과 키에 부여합니다. 대칭 키와 다르게 인증서를 사용하기 위해서는 명시적으로 인증서를 열지 않아도 된다는 것에 주의하십시오. 두 번째 예제는 비슷한 대칭 키를 만듭니다. 그러나 인증서 대신, 사용자는 제공되는 패스워드를 사용하여 키를 관리하도록 선택했습니다. 세 번째 예제에서는 AUTHORIZATION 절을 생략해서 dbo가 키를 소유하고 RC4 알고리즘을 사용합니다. 이 예제는 새로운 대칭 키를 보호하기 위해서 현재 있는 비대칭 키를 사용합니다. 마지막 예제는 dbo가 키를 소유하는 또 다른 대칭 키를 생성합니다. 그러나 이것 자체는 다른 대칭 키에 의해서 보호됩니다. 새로운 키를 암호화 하기 위해서 현재 있는 대칭 키를 이용하기 전에 현재 있는 키를 명시적으로 열어야 합니다.

대칭 키를 가지고 암호화된 데이터를 삽입하고 select하는 것은 인증서와 비대칭 키를 이용하는 것과 이용하는 메소드와 비슷합니다만 예제 코드 다운로드(MSDN Magazine 웹 싸이트에서 다운 가능)좀더 복잡합니다. 코드는 특정한 키를 검색하기 위한 Key_GUID 함수와 함께 데이터를 전환하기 위해서 EncryptByKey 와 DecryptByKey 함수를 이용합니다. 데이터베이스 마스터 키에 의해 암호화된 키 같은 키를 생성할 수 없기 때문에 대칭 키를 사용하기 전에 반드시 명시적으로 이 대칭 키를 열어야 합니다. 예제에서는 GenericSymmetricKeySym 키를 생성하고 사용합니다. 이 키는 대칭 키User1SymmetricKeyCert에 의해 보호되기 때문에 사용하고자 하는 키를 보호하는 대칭 키를 먼저 열어야 합니다. 이것은 어느 때든지 데이터베이스에서 대칭 키를 다중으로 열 수 있다는 것을 설명합니다. SQL Server는 어떤 키가 데이터를 암호화하는데 사용되었는지를 알고 있습니다: DecryptByKey 함수 내에서 키를 지정하지 않아도 됩니다.

SQL Server 2005 암호화 구현의 흥미 있는 장점 한가지는 사용자가 그들의 데이터만 보도록 하는 방법을 제공한다는 것입니다. 이 기사의 여러 예제에서 보인 것처럼 AUTHORIZATION 절을 사용해서 키나 인증서의 소유권을 다른 사용자에게 데이터를 보기 위해서 권한을 수여할 수 있는 사용자에게 줄 수 있습니다.

결론

SQL Server 2005는 데이터베이스 엔진내의 보안을 위한 전례가 없는 수준의 지원을 했습니다. 이것은 안전한 서버에 있는 데이터를 액세스 할 수 있는 안전한 응용프로그램을 만드는데 사용할 풍부한 기능 집합을 개발자에게 제공합니다. 높은 입자성 권한은 최소 권한의 보안 원칙을 실천하도록 하고 사용자에게 일을 수행하는데 필요한 권한만을 부여 하고 자만하는 권한의 이점을 이용하는 공격을 막습니다. SQL Server 2005의 이런 보안 기능 향상은 여러분의 데이터에 대한 오늘날의 세련된 공격을 극복해 내는데 필요한 모든 도구를 제공합니다.

Don Kiely, MVP, MCSD, 는 책임 기술 컨설턴트이며 보안, 특히 분산 Windows Forms과 ASP.NET 응용프로그램의 개발 내에서의 보안에, 에 중점을 두고 있는 개발자입니다. 그는 또한 기술에 관한 저술, 컨퍼런스 발표, 강연을 합니다. Don에게 연락하려면 donkiely@computer.org.