SQL

GRANT CREATE VIEW TO [UserName];

GRANT CREATE PROCEDURE TO [UserName];

GRANT ALTER ON SCHEMA::[dbo] TO [UserName];

GRANT EXECUTE ON [procedure_Name] TO [UserName];

우선 해당 실행계획을 보면 다음과 같습니다.

처음에 튜닝 할 당시에 이게 뭐하는 실행계획인지 잘 몰랐습니다. 조인 시에 Miss? / 인덱스 오류? 이런 저런 생각을 하였는데 정말 이유는 간단했습니다. 

조인의 조건자가 정말 없습니다. 그런데 상식적으로 조인을 걸고 ON 절을 해주지 않으면 조인이 안되지 않나? 이런 생각을 했는데... 잘 됩니다. 음...없다기 보다는

같은걸 2번 쓰거나 엉뚱한(?)것을 입력 했을때 쿼리가 잘 실행됩니다. 

그렇다면 뭘로 조인을 할까요? 그래서 테스트를 해봤습니다. 

준비한 테이블은 골키퍼 축구 선수들의 트레이닝 강의에 관한 내용입니다. 발 설계 및 발 쿼리니 이 자료는 참고만 부탁드립니다. 

테이블은 간단합니다. 4개의 테이블이 존재하며, 각각 선수정보(info) / 트레이닝항목(Class) / 수강현황(Register_info) / 코치(Teacher) 

개인적으로 수강현황은 N:N의 내용을 해소하기 위한 테이블로 만들었습니다. 한 트레이닝을 여러명이 들을수 있으며, 여러명이 여러개의 트레이닝을 받을 수 있기에...

Step 1 - 테이블 만들기

- 인덱스를 보고 이건 뭐야?라는 분들이 계실텐데 실행계획에서 NL조인에 X표기를 보기위한 것이니 너무 신경 쓰지 않으시길 바랍니다. ^^....

- 테이블은 위에 언급처럼 총 4개를 만듭니다. 선수정보/트레이닝항목/수강현황/코치 입니다. 

USE StudyDB

GO



create table info

(

User_key int identity(1,1)

,ID varchar(10)

,Name varchar(8)

,Hp char(11)

constraint PK_info primary key clustered (User_key)

)

go



create table Class

(

Class_key int identity(1,1)

,Class_name varchar(20)

,Class_code int

,teacher_key int

constraint PK_Class primary key clustered (Class_key)

)

go



create table Register_info

(

register_key int identity(1,1)

,User_key int

,Class_key int

,register_regdate datetime

,flag char(1)

constraint PK_Register_info primary key clustered (register_key)

)

go

create nonclustered index NC_1 on Register_info(User_key)

create nonclustered index NC_2 on Register_info(Class_key)

go



create table Teacher

(

teacher_key int

,teacher_name varchar(8)

,Hp char(11)

,teacher_class varchar(30)

constraint PK_Teacher primary key clustered (teacher_key)

)

go

Step 2 - 데이터 입력

- 이제 몇건의 데이터를 넣어보겠습니다.

insert into [dbo].[info]( ID, Name, Hp)  values ('AAAA','데헤아','01012341234')

insert into [dbo].[info]( ID, Name, Hp)  values ('BBBB','노이어','01023143532')

insert into [dbo].[info]( ID, Name, Hp)  values ('CCCC','카시야스','01033313643')

insert into [dbo].[info]( ID, Name, Hp)  values ('DDDD','체흐','01012346643')

insert into [dbo].[info]( ID, Name, Hp)  values ('EEEE','부폰','01023535036')

go

select * from [info]

go



insert into [dbo].[Class](Class_name, Class_code, teacher_key) values ('피지컬',1001,1)

insert into [dbo].[Class](Class_name, Class_code, teacher_key) values ('골키퍼',1002,2)

go

select * from [Class]

go



insert into [dbo].[Teacher] ([teacher_key],teacher_name, Hp, teacher_class) values(1,'무리뉴','01035530455','골키퍼 전문코치')

insert into [dbo].[Teacher] ([teacher_key],teacher_name, Hp, teacher_class) values(2,'클롭','01035340455','체력 전문코치')

go

select * from [Teacher]

go



insert into [dbo].[Register_info] ( User_key, Class_key, register_regdate, flag) values (1,1,getdate(),'Y')

insert into [dbo].[Register_info] ( User_key, Class_key, register_regdate, flag) values (1,2,getdate(),'Y')

insert into [dbo].[Register_info] ( User_key, Class_key, register_regdate, flag) values (2,1,getdate(),'Y')

insert into [dbo].[Register_info] ( User_key, Class_key, register_regdate, flag) values (3,2,getdate(),'Y')

go

select * from [Register_info]

go

Step 3 - 정상적인 조인

- 여기서 우리는 Select를 통해서 어떤 선수가 어떠한 트레이닝을 받고 있는지 확인 하고자 합니다.

select I.User_key,I.Name,C.Class_name

from info as I

inner join Register_info as R

on I.User_key = R.User_key

inner join Class as C

on R.Class_key = C.Class_key

order by User_key asc

- 실행계획에 아무런 지장이 없습니다. 

- 왜? on절도 정확히 명시되어있고, 정확한 Key로 묶었습니다.

- 그럼 선수가 받는 트레이닝은 무엇이며, 그 트레이닝의 담당자는 누구인지도 확인해 보겠습니다.

select I.User_key,I.Name,C.Class_name,T.teacher_name

from info as I

inner join Register_info as R

on I.User_key = R.User_key

inner join Class as C

on R.Class_key = C.Class_key

inner join Teacher as T

on C.teacher_key = T.teacher_key

order by User_key asc

- 역시 별다른 특이점이 없습니다. 왜? ON절을 정확히 명시했으니... 

- 그럼이제 NL 조인 부분에 X를 나타나게 해보겠습니다.

Step 4 - 조인 조건자 없음 

- 아까랑 똑같이 하돼 약간의 ON의 변화를 주겠습니다.

- 쿼리에서 표시한 부분의 ON절을 보면 2번째 Inner Join부분과 ON절이 똑같습니다. 그럼 Teacher 테이블은 어디갔어? ON절에 없잖아?

  뭐랑 조인하지? 하겠지만 조인이 됩니다.

select I.User_key,I.Name,C.Class_name,T.teacher_name

from info as I

inner join Register_info as R

on I.User_key = R.User_key

inner join Class as C

on R.Class_key = C.Class_key

inner join Teacher as T

on R.Class_key = C.Class_key -- 문제가 되는 부분

order by User_key asc

- NL 조인 부분에 X표시가 생겼습니다. 

  이 경고는 말그대로 조건자가 없음을 말합니다. 그렇다면 이것이 왜 문제가 되는가? 이것이 핵심입니다.

  바로 데이터가 틀리게 나오기 때문입니다. 우리가 바로 위에서 했던 정상 쿼리문과 결과를 비교해보겠습니다.

- 비교된 결과를 보면 다음과 같습니다.

- 그림을 통해서 확실히 보면 다음과 같습니다.

- 결과를 보면 이상한 점을 볼 수 있습니다. 

  각각의 데이터가 2배로 들어났습니다. 데이터도 이상합니다. 데헤아는 골키퍼,피지컬 1번씩 듣는데 2번씩 듣도록 되어있고, 노이어 / 카시야스는 수업이 총 1회씩인데

  골키퍼 2번 / 피지컬 2번씩 되어 있습니다. 이것은 무엇을 의미 할 까요?

- 3번째 Inner 조인까지 정확히 되었으나, 4번째 Teacher 테이블을 조인 시 Cross 조인을 하는 것임을 알 수 있습니다.

- 결과적으로 이것은 잘못된 데이터죠. 

  그렇다면 언제 이러한 실수를 많이 저지를 까요?

Step 5 - 우리는 언제 이러한 실수를 많이 하는가?

- 에이 누가 쿼리를 그렇게 짜요? 할 수 있습니다. 그런데 제가 현재 회사에서 튜닝을 하면 20건 중에 1건 정도는 이러한 결과를 보여줍니다. 왜 일까요?

- 개발자 분들은 주로 Where 조건을 이용해서 Join을 합니다. ON절을 잘 안쓰더군요.(물론 캐바캐입니다.)

  Where 조건에 넣음으로써 쿼리에서 결과가 맞다고 판단하고 그냥 넘어가는 것이죠. Where 조건으로 위의 내용을 재현하면 다음과 같습니다.

select I.User_key,I.Name,C.Class_name

from info as I

,  Register_info as R

,  Class as C

,  Teacher as T

where  I.User_key = R.User_key

and R.Class_key = C.Class_key

and R.Class_key = C.Class_key

order by User_key asc

흔히들 Where 조건에 기재하면서 Where 조건에 조건을 잘못 입력하는 경우가 있습니다. 

위의 조건 처럼 사용하면 실수를 많이 범합니다. 하지만... 조인이 잘되니 아 ! 이상이 없구나 하고 넘기는 경우가 많죠.

이러한 실수를 방지하기 위해서 꼭 ON절을 이용한 조인을 해야합니다. 뭐.. 꼭은 아니지만 권고하는 것이죠.

감사합니다.

DB 보안 설정

- 인증 방법에 따른 차이점

- 윈도우 인증과 로그인 인증의 차이점

1. 윈도우 인증은 SQL Server로 하여금 운영체제의 보안 기능 (암호 암호화(encryption), 암호 사용 기간, 암호의 최소/최대 길이 제한) 이 사용 가능합니다.

2. 윈도우 인증은 트러스트된 연결을 사용하여 기존 윈도우의 연결 방식에 의존한다.

3. 윈도우 인증은 액티브 디렉터리 사용자 및 로컬 사용자 인증이 가능합니다.

- 계정 관리

1. DB 최소권한의 유저로 운영하라.

2. 사용하지 않는 저장된 프로시저와 기능들은 제거하거나 관리자에게 제한된 접근권한을 주어라.

3. 퍼미션을 변경하고, 공개된 시스템 객체에 접근을 제거 하라.

4. 모든 사용자 계정의 패스워드를 강화 시켜라

- SQL Injection

1.저장된 프로시저를 사용할 때 매개변수화 된 API를 이용하라.

모든 입력 값 검증은 일반적인 루틴을 이용하고, 최소한의 권한을 DB 사용자 에게 적용하라.

2.프로시저로 변경 하고 동적 쿼리를 사용하지 않는다.

3.데이터베이스와 연동을 하는 스크립트의 모든 파라미터들을 점검하여 사용자의 입력 값이 SQL injection을 발생시키지 않도록 수정한다.

4.사용자 입력이 SQL injection을 발생시키지 않도록 사용자 입력 시 특수문자(' " / \ ; : Space -- +등)가 포함되어 있는지 검사하여 허용되지 않은 문자열이나 문자가 포함된 경우에는 에러로 처리한다.

5.SQL 서버의 에러 메시지를 사용자에게 보여주지 않도록 설정한다.

공격자는 리턴 되는 에러 메시지에 대한 분석을 통하여 공격에 성공할 수 있는 SQL Injection 스트링을 알아낼 수 있다. 따라서 SQL 서버의 에러 메시지를 외부에 제공하지 않도록 한다.

6.웹 애플리케이션이 사용하는 데이터베이스 사용자의 권한을 제한한다.

가능하면 일반 사용자 권한으로는 모든 system stored procedures에 접근하지 못하도록 하여 웹 애플리케이션의 SQL Injection 취약점을 이용하여 데이터베이스 전체에 대한 제어권을 얻거나 데이터베이스를 운용중인 서버에 대한 접근이 불가능하도록 한다.

7. 위험한 확장 저장 프로시저들을 제거한다. (확장 프로시저를 생성 할수 있는 dll도 같이 삭제)

(xp_cmdshell, Xp_regread, xp_regwrite , Xp_makewebtask ,Xp_enumdsn)

1. 인증

윈도우 인증

  윈도우의 인증 방법만을 사용한 인증

사용자가 SQL 서버에 연결할 때는 별도의 로그인 절차 없이 윈도우 계정 정보로 SQL 서버에 연결한다.

SQL 서버 MASTER 데이터베이스 안에 있는 syslogin 테이블이 모든 윈도우 사용자 계정이나 그룹 계정에 관한 정보들을 SQL 서버 로그인 계정으로 가지고 있다.

윈도우의 액티브 디렉토리 서비스에 지원되는 단일 로그인 기능을 사용할 수도 있다.

SQL 서버 인증

  운영체제와는 별개의 아이디 패스워드를 가지고 있으면서 사용자의 인증을 관리하는 방식

  운영체제의 강력한 보안체계를 전혀 활용할 수 없다는 단점이 있다.

혼합 인증

  윈도우, SQL 서버 인증 방식을 동시에 사용 하는 방식

  2가지의 장점을 잘 조화하여 사용 하는 방식

2. 로그인 과 유저

로그인 : SQL 서버에 접속한다는 의미

유저: 데이터베이스를 사용하기 위한

로그인 관련정보는 syslogins 테이블에 저장

유저의 관련정보는 sysusers 테이블에 저장

SQL 서버 설치시 기본적으로 만들어지는 로그인 계정

- Sa: System Administrator, sql 서버와 모든 데이터베이스에서 사용 가능한 모든 권한 소유

- BUILTIN/Administrator : Adminstrator 그룹으로 SQL 서버에 접속하는 사용자들을 위한 윈도우 인증 방법으로 접속할 때의 최고의 권한을 가지는 계정이다. (윈도우를 서버로 사용할 때만 등록되어 있다.)

- 새로운 로그인 생성

1. SP

sp_addlogin [ @loginame = ] 'login'

    [ , [ @passwd = ] 'password' ]  -- 빈암호 사용을 자제해야함 Default NULL

    [ , [ @defdb = ] 'database' ]  - Default master

    [ , [ @deflanguage = ] 'language' ] Default NULL,  서버의 현재 기본 언어

    [ , [ @sid = ] sid ] Default Null

    [ , [ @encryptopt= ] 'encryption_option' ]

2. CREATE

CREATE LOGIN login_name { WITH <option_list1> | FROM <sources> }

<sources> ::=

    WINDOWS [ WITH <windows_options> [ ,... ] ]

    | CERTIFICATE certificateName

    | ASYMMETRIC KEY asym_key_name

<option_list1> ::=

    PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]

    [ , <option_list2> [ ,... ] ]

<option_list2> ::=

    SID = sid

    | DEFAULT_DATABASE = database 

    | DEFAULT_LANGUAGE = language

    | CHECK_EXPIRATION = { ON | OFF}

    | CHECK_POLICY = { ON | OFF}

    | CREDENTIAL = credential_name

<windows_options> ::=   

    DEFAULT_DATABASE = database

    | DEFAULT_LANGUAGE = language

  SQL 서버 인증 방식

USE [master]

GO

CREATE LOGIN [SA_TEST] WITH PASSWORD=N'1235_111', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

GO

  윈도우 인증 방식

USE [master]

GO

CREATE LOGIN [TAEK\TaekSu] FROM WINDOWS WITH DEFAULT_DATABASE=[test], DEFAULT_LANGUAGE=[한국어]

GO

USE [test]

GO

CREATE USER [TAEK\TaekSu] FOR LOGIN [TAEK\TaekSu]

GO

- 유저 생성

모든 데이터베이스 마다 자동적으로 만들어지는 유저는 dbo 하나 밖에 없다.

모든 데이터베이스에 공통적으로 생성해주어야 하는 유저가 있다면 model 데이터베이스 안에 원하는 유저를 생성해주면 된다.

SP

sp_adduser [ @loginame = ] 'login'

        [ , [ @name_in_db = ] 'user' ]

        [ , [ @grpname = ] 'role' ]

CREATE

CREATE USER user_name    [ { { FOR | FROM }

      {

        LOGIN login_name

        | CERTIFICATE cert_name

        | ASYMMETRIC KEY asym_key_name

      }

      | WITHOUT LOGIN

    ]

    [ WITH DEFAULT_SCHEMA = schema_name ]

동일한 SP에 대해 2000, 20005 , 2008의 처리 방식이 다름.

SYSTEM 테이블

master.dbo.syslogins

master.dbo.syslanguages

master.dbo.sysxlogins

sys.schemas

sys.database_permissions

3. 역할 ROLE

- 시스템 정의 역할

시스템 정의 역할은 SQL 서버에서 미리 정의해 놓은 역할.

- 서버역할

서버 역할은 SQL 서버에서 로그인 계정들을 그룹화 하고 정해진 권한을 부여하기 위해 사용

서버 역할은 사용자가 새로운 역할을 추가하거나 기존의 역할에 설정되어 있는 권한을 변경할 수 없다.

역할 설명

Bulkadmin BULK INSERT 문을 실행

Dbcreator 데이터베이스 생성, 삭제, 변경, 복원 가능

Diskadmin 디스크 파일 관리

Processadmin 프로세스 종료 가능

Securityadmin 로그인 및 속성 관리, 데이터베이스 수준의 사용 권한 허가 및 거부 등 가능

Serveradmin 서버 차원의 구성 옵션 변경 가능, 서버 종료 가능

Setupadmin 링크드 서버 추가 및 제거, 일부 시스템 저장 프로시저 실행 가능

sysadmin 서버의 모든 작업 수행 가능

SP_ADDSRVROLEMEMBER 시스템 저장 프로시저를 사용하는 문법

Sp_addsrvrolemember [ @loginame = ] 'login'

          , [ @rolename = ] 'role'

- 데이터베이스 역할

DB role이라고 부르며 각 데이터베이스 안에서 유저에 부여하는 것이다.

권한 변경이 불가능한 고정된 역할과, 권한을 변경할 수 있고 모든 유저가 속해 있는 public 역할이 있다.

역할 설명

Db_accessadmin 윈도우 로그인, 그룹 및 sql 서버 로그인 관리

Db_backupadmin 데이터베이스 백업 관리

Db_datareader 테이블 조회 권한 관리

Db_datawriter 테이블 데이터 변경 가능

Db_ddladmin 모든 DDL 명령 실행 가능

Db_denydatareader 테이블 조회 구너한을 제거

Db_denydatawriter 테이블 데이터 변경 권한을 제거

Db_owner 데이터베이스 내 모든 권한 사용

Db_securityadmin 사용 권한 관리

Public 데이터베이스 사용자가 가지는 가장 최소한의 권리

SP_ADDROLEMEMBER

Sp_addrolemember [ @rolename = ] 'role' ,

        [ @membername = ] 'security_account'

SYSTEM TABLE , DBCC

2000 sysusers

2005 sys.database_principals

dbcc auditevent

- 사용자 정의 역할

동일한 권한을 가진 유저 그룹을 만들 수 있게 해주는 것으로서 실제로 db role의 일부분이

라고 할 수 있다.

SP

p_addrole [ @rolename = ] 'role' [ , [ @ownername = ] 'owner' ]

CREATE

USE [test]

GO

CREATE ROLE [MyRole] - Role 생성

GO

USE [test]

GO

EXEC sp_addrolemember N'MyRole', N'SA_TEST' - 데이터베이스 역할 생성

GO

use [test]

GO

GRANT ALTER ON [dbo].[a] TO [MyRole] - 권한 생성

GO

use [test]

GO

GRANT SELECT ON [dbo].[c] TO [MyRole] WITH GRANT OPTION

GO

use [test]

GO

GRANT VIEW DEFINITION ON [dbo].[d] TO [MyRole]

GO

Db_decurityadmin이나 db_owner 역할을 가진 유저만 추가 가능

SSMS , sp_addrole, sp_droprole 로 추가가 가능

- 애플리케이션 역할

애플리케이션 역할 역시 db Role의 일부분이라고 볼 수 있다.

특정한 응용 프로그램에 따라 사용할 수 있는 권한을 제한하는 것.

주의사항: 한번 설정된 애플리케이션 역할은 해당 세션이 끝날 때까지 계속 유효하며, 일반 역할이나 유저에 부여된 모든 권한이나 역할은 해당 세션에서 모두 무시된다.

SP 활용

sp_addapprole [ @rolename = ] 'role' , [ @password = ] 'password'

CREATE APPLICATION ROLE application_role_name

    WITH PASSWORD = 'password' [ , DEFAULT_SCHEMA = schema_name ]

USE [test]

GO

CREATE APPLICATION ROLE [AppRoleTest] WITH PASSWORD = N'1234'

GO

use [test]

GO

GRANT EXECUTE ON [sys].[sp_ActiveDirectory_SCP] TO [AppRoleTest]

GO

use [test]

GO

GRANT INSERT ON [dbo].[c] TO [AppRoleTest]

GO

EXEC sp_setapprole 'AppRoleTest','1234';

SETUSER [ 'username' [ WITH NORESET ] ]

데이터베이스에 대한 ALTER ANY APPLICATION ROLE 권한이 필요합니다.

보안 카탈로그 뷰

http://msdn.microsoft.com/ko-kr/library/ms178542.aspx

보안 저장 프로시저

http://msdn.microsoft.com/ko-kr/library/ms182795.aspx

4. 데이터베이스 암호화

SQL 서버 2000까지의 암호화

- 문서화되지 않은 PWDEncrypt 함수로 복호화 되지 않는 단 방향 해시 함수로, 해당 값의 비교를 위한 PWDCompare 함수를 따로 지원한다.

DECLARE @Ciphertext varbinary(20);

DECLARE @Plaintext varchar(20);

SET @Plaintext = 'Hello'

set @Ciphertext = PWDENCRYPT(@Plaintext)

SELECT [원문] = @Plaintext , [암호문] = @ciphertext

SQL 서버 2005 암호화 방식

- 내장된 암호화 함수가 제공되고 이 함수 (MD2,MD4,MD5SHA,SHA1) 지원 (단방향 해시)

DECLARE @Ciphertext varbinary(20);

DECLARE @Plaintext varchar(20);

SET @Plaintext = 'Hello'

set @Ciphertext = HashBytes('SHA1',@Plaintext)

SELECT [원문] = @Plaintext , [암호문] = @ciphertext

- ENCRYPTBYPASSPHRASE 패스워드를 가지고서 키를 암호화 하는 방식

DECLARE @CreditCardNo varchar(20);

SET @Creditcardno = '1234567890'

DECLARE @PASS nvarchar(128);

SET @PASS = '암호화문자입니다.'

DECLARE @Enctext varbinary(8000)

set @Enctext = EncryptByPassPhrase(@Pass,@creditCardNo)

DECLARE @Dectext varbinary(8000)

set @Dectext = DecryptByPassPhrase(@Pass,@Enctext)

SELECT @creditCardNo 카드번호,

  @PASS 암호화문자,

  @Enctext 암호화,

  @Dectext,

  CONVERT(VARCHAR,@Dectext) 복호화

복호화에 반환되는 데이터는 VarBinary 형태임에 주의하여 문자열로 변환 하여야 한다.

SQL SERVER 2008

TDE (Transparent Data Encrypt)

http://msdn.microsoft.com/ko-kr/library/bb934049.aspx

http://optimizer.tistory.com/48

5. 보안 이슈

1. Windows Server, SQL Server Patch

2. JS/Spida 1443포트를 사용하는 SQL 서버에 sa 계정의 패스워드가 NULL인 경우 감염되는 바이러스이다.

JS/Spida, JS.Spida, JS_SQLSPIDA, JScript/SQLSpida, Worm, SQLsnake, Digispid로 불리고 5월 말에 주로 행동했던 것은 이 웜 바이러스의 변종인 JS/Spida.B이다.

3. 윈도우 인증모드라고 하여도 SA 패스워드를 NULL로 남겨두지 않는다.

4. DBMS 포트를 기본 1433에서 다른 포트로 변경하거나 필터링 한다.

5. SA 패스워드를 잘 보관한다. ASP 혹은 ASA 파일들, Connect.inc와 같은 데이터베이스 연결 문자열을 담아주는 파일들, 레지스트리에 패스워드를 넣어놓는 것은 sa의 패스워드를 노출할 위험이 있는 것이다. 암호화 되거나 컴포넌트와 같은 컴파일 된 파일에 보관하도록 한다.

6. 위험한 확장 저장 프로시저들을 제거하라.

- Xp_cmdshell (쉘 상의 모든 명령들을 수행할 수 있다.)

USE master

GO

IF OBJECT_ID('[dbo].[xp_cmdshell]') IS NOT NULL BEGIN

  EXEC sp_dropextendedproc 'xp_cmdshell'

END

GO

-

- Xp_regread, xp_regwrite (레지스트리를 읽거나 쓰기가 가능)

- Xp_makewebtask (데이터베이스 내의 정보들을 인터넷에 게시할 수 있다.)

- Xp_enumdsn (DNS 정보들을 보고자 하는 경우 사용)

- Sp_dropextendedproc 시스템 프로시저로 제거 할수 있다. Ex) sp_dropextendedproc 'xp_cmdshell'

1) master database

·  확장 프로시저 퍼미션 삭제

·  REVOKE EXECUTE ON xp_regread FROM public

·  REVOKE EXECUTE ON xp_instance_regread FROM public GO

·  sp_runwebtask 접근을 통한 web task 실행 권한 획득 방지

·  REVOKE EXECUTE ON dbo.sp_runwebtask FROM public GO

2) msdb database

·  Agent Job 접근을 통한 권한 확대 방지

·  REVOKE EXECUTE ON sp_add_job FROM public

·  REVOKE EXECUTE ON sp_add_jobstep FROM public

·  REVOKE EXECUTE ON sp_add_jobserver FROM public

·  REVOKE EXECUTE ON sp_start_job FROM public

·  mswebtasks 테이블을 통한 web task 관리 권한 획득 방지

·  REVOKE ALL ON dbo.mswebtasks FROM public

·  DTS packages 를 통한 administrator 패스워드 유출 방지

·  REVOKE EXECUTE ON sp_enum_dtspackages FROM public

·  REVOKE EXECUTE ON sp_get_dtspackage FROM public

·  SQL Agent Password 노출 방지

·  REVOKE EXECUTE ON sp_get_sqlagent_properties FROM public

  프로시저들의 Drop후에도 dll 파일은 남아 있으니 아래 쿼리를 실행하여 제거한다.

·  dbcc xp_cmdshell(free)

·  dbcc xp_dirtree(free)

·  dbcc xp_regdeletekey(free)

·  dbcc xp_regenumvalues(free)

·  dbcc xp_regread(free)

·  dbcc xp_regwrite(free)

·  dbcc sp_makewebtask(free)

·  dbcc sp_adduser(free)

다음으로 SQL Injection을 유발시킬 수 있는 시스템 DB상에 존재하는 프로시저들의 EXCUTE권한을 DENY하겠다.  해당 프로시저들을 통해 시스템의 정보를 조회하고 변경할 수 있으니 필수적으로 DENY해야 할 권한들이다.

·  DENY  EXECUTE  ON [master].[dbo].[xp_subdirs] TO [guest] CASCADE

·  DENY  EXECUTE  ON [master].[dbo].[xp_dirtree] TO [guest] CASCADE

·  DENY  EXECUTE  ON [master].[dbo].[xp_availablemedia] TO [guest] CASCADE

·  DENY  EXECUTE  ON [master].[dbo].[xp_regwrite] TO [guest] CASCADE

·  DENY  EXECUTE  ON [master].[dbo].[xp_regread] TO [guest] CASCADE

·  DENY  EXECUTE  ON [master].[dbo].[xp_regaddmultistring] TO [guest] CASCADE

·  DENY  EXECUTE  ON [master].[dbo].[xp_regdeletekey] TO [guest] CASCADE

·  DENY  EXECUTE  ON [master].[dbo].[xp_regdeletevalue] TO [guest] CASCADE

·  DENY  EXECUTE  ON [master].[dbo].[xp_regremovemultistring] TO [guest] CASCADE

·  DENY  EXECUTE  ON [master].[dbo].[xp_regaddmultistring] TO [guest] CASCADE

마지막으로 일반 User의 물리적 파일 접근을 제한하기 위해 아래 쿼리를 실행한다.

·  DENY  EXECUTE  ON [master].[dbo].[xp_fileexist] TO [guest] CASCADE

·  DENY  EXECUTE  ON [master].[dbo].[xp_fixeddrives] TO [guest] CASCADE

·  DENY  EXECUTE  ON [master].[dbo].[xp_getfiledetails] TO [guest] CASCADE

7. SQL 서버 에러 로그를 자주 검사하고 잘 보관해라.

6. SQL Injection

1. 저장 프로시저를 작성하여 Spooky Loing 문제를 해결 할 수 있다.

2. 저장된 프로시저를 사용할 때 매개변수화 된 API를 이용하라. 모든 입력 값 검증은 일반적인 루틴을 이용하고, 최소한의 권한을 DB 사용자 에게 적용하라.

3. 프로시저로 변경 하고 동적 쿼리를 사용하지 않는다.

4. 데이터베이스와 연동을 하는 스크립트의 모든 파라미터들을 점검하여 사용자의 입력 값이 SQL injection을 발생시키지 않도록 수정한다.

5. 사용자 입력이 SQL injection을 발생시키지 않도록 사용자 입력 시 특수문자(' " / \ ; : Space -- +등)가 포함되어 있는지 검사하여 허용되지 않은 문자열이나 문자가 포함된 경우에는 에러로 처리한다.

6. SQL 서버의 에러 메시지를 사용자에게 보여주지 않도록 설정한다. 공격자는 리턴 되는 에러 메시지에 대한 분석을 통하여 공격에 성공할 수 있는 SQL Injection 스트링을 알아낼 수 있다. 따라서 SQL 서버의 에러 메시지를 외부에 제공하지 않도록 한다.

7. 웹 애플리케이션이 사용하는 데이터베이스 사용자의 권한을 제한한다. 가능하면 일반 사용자 권한으로는 모든 system stored procedures에 접근하지 못하도록 하여 웹 애플리케이션의 SQL Injection 취약점을 이용하여 데이터베이스 전체에 대한 제어권을 얻거나 데이터베이스를 운용중인 서버에 대한 접근이 불가능하도록 한다.

Sys.object의 public 권한 제거

7.  서버를 견고하게 운영하기

1. DB 최소권한의 유저로 운영하라.

2. 사용하지 않는 저장된 프로시저와 기능들은 제거하거나 관리자에게 제한된 접근

권한을 주어라.

3. 퍼미션을 변경하고, 공개된 시스템 객체에 접근을 제거 하라.

4. 모든 사용자 계정의 패스워드를 강화 시켜라

5. 미리 승인된 서버의 링크를 제거 하라.

6. 사용하지 않는 네트워크 프로토콜을 제거하라.

7. 신뢰할 수 있는 네트워크,웹 서버, 백업 서버만 접근을 허용하라.

출처: https://taeksu.tistory.com/entry/SQL-Server-로그인-및-보안 [아ㅏㅏㅏㅏㅏㅏㅏㅏㅏ 졸려]