SQL

인덱스 삭제 시 체크해야 할 리스트

* SQL Server 기준으로 작성하였습니다.

1. 쿼리 힌트에 인덱스가 사용되지는 않았는지 확인

2. 쿼리 실행계획이 변경될 가능성이 있는지 확인

1. 기존 쿼리가 삭제될 인덱스를 사용하는 경우 실행계획이 변경되게 되고 이로인해 이슈가 될 수 있는지 확인합니다.

3. 인덱스에 접근하는 쿼리가 있는지 확인

1. dm_db_index_usage_stats 의 동적뷰에 마지막 접근 시간이 기록됩니다. 해당 뷰를 조회해서 인덱스가 지금도 사용되고 있는지 확인합니다.

4. ★ 인덱스를 바로 삭제하려고 하지마십시오.

1. rename 으로 인덱스 명을 변경하여 삭제와 같은 상황만 만들어 둡니다.

  유사 시 빠르게 복원이 가능하도록 준비하고 있어야 합니다.

  ( 인덱스 재생성 시, 테이블 Lock 이 걸리며 데이터가 많은 경우 인덱스 생성 시간도 오래걸립니다. )

생성과 다르게 삭제는 위험도가 높은 작업입니다.

서비스에 영향이 없는지 꼼꼼한 체크가 필요합니다.

-- 인덱스에대해카운터가되었는지확인

-- Index_id NULL 인경우는인덱스를타지않은경우, NULL 이아닌경우는인덱스를탄경우

select object_name( idx.object_id) as 'table',

idx.name as 'index',

idx.index_id as 'id',

idx.type_desc as 'type',

icol.column_id,

--col.name,

usg.user_seeks,

usg.last_user_seek,

usg.user_scans,

usg.last_user_scan,

usg.user_lookups,

usg.last_user_lookup

from sys.indexes idx inner join sys.dm_db_index_usage_stats usg

on usg.object_id = idx.object_id and idx.index_id = usg.index_id and usg.database_id = db_id()

left join sys.index_columns icol on icol.index_id = usg.index_id and icol.object_id = idx.object_id

and usg.index_id IS null

and idx.type_desc not in ('heap','clustered')

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-로그인-및-보안 [아ㅏㅏㅏㅏㅏㅏㅏㅏㅏ 졸려]

AlwaysOn 가용성 그룹 구성에서 패치를 적용하는 방법?

보조 복제본이 하나 인 가용성 그룹에 대해 단계별로 선택하겠습니다.

1. 시스템 상태 (또는 SQL 서비스가 중지 된 VMware 스냅 샷)를 사용하여 최신 OS 백업, 모든 데이터베이스를 최근에 백업하고 기본 노드에서 checkdb를 성공적으로 완료했는지 확인하십시오. {이것은 필수는 아니지만 "Ouch"순간을 피하기 위해}

2. 기본 복제본 (SQL1)으로 작동하는 노드에서 장애 조치 모드를 수동으로 변경하십시오.

3. 보조 복제본 (SQL2)에서 영향을받는 데이터베이스를 새로 고치고 대시 보드에서 모든 것이 녹색인지 확인하십시오.

4. SQL2에 패치 (CU의 서비스 팩)를 적용하십시오.

5. 사용 가능한 모든 패치가 적용될 때까지 Windows Update 및 / 또는 소프트웨어 업데이트를 반복하십시오. 모든 패치와 패치 후 재부팅 및 구성 작업이 완료 될 때까지 패치 단계를 진행하지 마십시오.

6. 패치가 적용되었고 클러스터가 정상이며 AlwaysOn 가용성 그룹이 작동하는지 다시 확인하십시오.

7. 동기화 상태가 SYNCHRONIZED인지 확인하십시오.

8. 가용성 그룹을 보조 복제본으로 장애 조치 (SQL2)하십시오.

9. 동기화 상태가 동기화 될 때까지 보조 복제본 (이전 기본 = SQL1)에서 영향을받는 데이터베이스를 새로 고칩니다.

10. SQL1에 패치 (CU의 서비스 팩)를 적용하십시오.

11. 사용 가능한 모든 패치가 적용될 때까지 Windows Update 및 / 또는 소프트웨어 업데이트를 반복하십시오. 모든 패치와 패치 후 재부팅 및 구성 작업이 완료 될 때까지 패치 단계를 진행하지 마십시오.

12. 패치가 적용되었고 클러스터가 정상이며 AlwaysOn 가용성 그룹이 작동하는지 다시 확인하십시오.

13. 동기화 상태가 SYNCHRONIZED인지 확인하십시오.

14. 가용성 그룹을 기본 노드로 페일 오버합니다 (SQL1로 돌아 가기).

15. 장애 조치 모드를 지금 자동으로 변경하십시오 (b 단계에서 변경됨).

계획대로 진행되지 않으면 a) 단계를 수행 했으므로 수행해야 할 작업을 알 수 있습니다.

Always On 가용성 그룹

고객이 엔지니어로서 미션 크리티컬 애플리케이션을 사용하는 경우 항상 HA (고 가용성) 및 (DR) 재해 복구에 중점을 둡니다. 따라서 "Always On"은 고 가용성 및 재해 복구를 달성하기위한 단순화 된 통합 솔루션입니다. 가용성 그룹은 여러 다른 기능과 함께 여러 활성 보조 노드뿐만 아니라 여러 데이터베이스 장애 조치를 지원하는 새로운 개념입니다.

이미지 출처 : https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server

간단히 말해서 고객 데이터를 보호하기 위해 여러 개의 SQL Server와 동기화 된 데이터베이스가 있습니다. 우리는 이미 소프트웨어가 요구 사항 및 새로운 기능 추가와 일치하기 위해 항상 업그레이드 및 패치가 필요하다는 것을 알고 있으며, 프로덕션 환경에서도 독립형 SQL Server를 쉽게 업그레이드 할 수 있습니다. 가용성 그룹의 경우 까다 롭습니다. 따라서 중단 시간없이 SQL Server 인스턴스를 업그레이드하는 데 도움이되는 단계를 제공하려고 생각했습니다.

업계에는 여러 유형의 가용성 그룹 토폴로지가 있습니다. 다음은 몇 가지 예입니다.

• 원격 보조 복제본이있는 가용성 그룹

• 장애 조치 클러스터 인스턴스 노드가있는 가용성 그룹

• 보조 복제본이 여러 개인 가용성 그룹 (복제본 중 하나에 대한 비동기 커밋)

간단한 토폴로지를 보자. 이 다이어그램은 고객 설정을 설명합니다.

업그레이드 단계

• SQL Server에서 AG 속성으로 이동

   

   

  

   

  

   

자동 장애 조치를 피하려면 가용성 모드를 "비동기"로 변경하십시오.

이러한 단계는 SSMS (SQL Management Studio)에 연결하여 기본 복제본 (노드)에서 수행해야합니다.

• 두 번째 단계는 보조 복제본을 업그레이드하는 것입니다. [2014 년에서 2016 년으로 업그레이드 할 수 있습니다]

• 이제 기본 모드에서 가용성 모드를 "동기"로 다시 변경하십시오.

• AG의 대시 보드에서 동기화 상태가 "동기화"로 표시 될 때까지 기다리십시오.

• 기본 노드를 교환하려면 장애 조치를 수행하십시오.

• 현재 보조 노드를 업그레이드하십시오.

• 아래의 resume 명령을 실행하십시오.

ALTER DATABASE  database_name  SET HADR 이력서

유용한 링크

• https://msdn.microsoft.com/en-us/library/dn178483.aspx

• https://blogs.msdn.microsoft.com/sqlalwayson/tag/sql-server-2012/

• https://msdn.microsoft.com/en-us/library/mt735149.aspx

SELECT  user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage]

, migs.last_user_seek 

, mid.[statement] AS [Database.Schema.Table] 

, mid.equality_columns 

, mid.inequality_columns 

, mid.included_columns 

, migs.unique_compiles 

, migs.user_seeks 

, migs.avg_total_user_cost 

, migs.avg_user_impact

FROM   sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )

        INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK )

           ON migs.group_handle = mig.index_group_handle

        INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK )

           ON mig.index_handle = mid.index_handle

WHERE   mid.database_id = DB_ID()

ORDER BY index_advantage DESC

-- 인덱스에대해카운터가되었는지확인

-- Index_id NULL 인경우는인덱스를타지않은경우, NULL 이아닌경우는인덱스를탄경우

SELECT object_name( idx.object_id) as 'table',

idx.name as 'index',

idx.index_id as 'id',

idx.type_desc as 'type',

icol.column_id,

--col.name,

usg.user_seeks,

usg.last_user_seek,

usg.user_scans,

usg.last_user_scan,

usg.user_lookups,

usg.last_user_lookup

FROM sys.indexes idx inner join sys.dm_db_index_usage_stats usg

ON usg.object_id = idx.object_id and idx.index_id = usg.index_id and usg.database_id = db_id()

LEFT JOIN sys.index_columns icol on icol.index_id = usg.index_id and icol.object_id = idx.object_id

AND usg.index_id IS null

AND idx.type_desc not in ('heap','clustered')

동적 데이터 마스킹 (DDM)SQL Server 2016에 추가 된 유용한 기능입니다. DDM을 사용하면 인증되지 않은 사용자의 민감한 데이터를 숨길 수 있습니다. 가장 중요한 것은 데이터 마스킹과 암호화는 데이터를 보호하는 두 가지 방법입니다. 동적 데이터 마스킹은 여러 가지 전략을 사용하여 데이터를 숨 깁니다. SQL Server에서 저장된 데이터를 암호화 기능으로 수정하지 않으면 도움이됩니다.

다이내믹 데이터 마스킹은 헬스 케어, 뱅킹 도메인에서 광범위하게 사용되어 권한이없는 엔티티의 데이터 기밀성을 유지하기위한 엄격한 조치를 취할 수 있습니다. DDM을 통해 우리는 응용 프로그램 코드 및 쿼리를 수정하지 않고 기존 저장된 데이터를 보호.

사례 연구로서 개발자는신원, 연락처, 신용 카드 번호 및 재무 기록과 같은 고객의 건강 및 은행 기록이있는 생산 데이터에 노출됩니다. 타사 영업 담당자는 자신의 제품을 마케팅하는 데 사용할 수있는 고객의 보험 내역을 알고 있어야합니다.

따라서 동적 데이터 마스킹은 기밀 데이터를 일반 텍스트 형식으로 무단 사용자에게 노출하는 것을 제한 읽기 권한이 있어도.

마스킹 기능을 테스트 할 새 사용자 작성

동적 데이터 마스킹 쿼리를 진행하기 전에. 우리는 새로운 사용자를 만들 것입니다 MaskedTestUser 갖는 것 고르다 아래에 생성 된 테이블에 대한 권한 dbo 사용자. 마스크 된 열 기능을 사용하여 dbo 사용자 아래에 테이블을 만들고 MaskedTestUser를 사용하여 쿼리하여 마스크 된 데이터를 봅니다. MaskedTestUser는 실제 데이터에 액세스 할 수없는 권한이없는 사용자라고 가정합니다.

DROP USER IF EXISTS MaskedTestUser;

CREATE USER MaskedTestUser WITHOUT LOGIN;

SQL Server에서 마스킹 기능 및 지원되는 데이터 형식

SQL Server는 서로 다른 네 가지를 통합했습니다민감한 데이터를 마스크하는 기능. XML, varbinary 및 hierarchyid와 같은 일부 특수 데이터 유형과 함께 문자열, 숫자 및 날짜와 같은 가능한 모든 데이터 유형과 작동하도록 설계되었습니다.

기능과 사용법을 이해하기 위해 각각을 살펴 보겠습니다.

1. 태만

데이터의 전체 마스킹 기본 마스킹 기능을 사용하여 달성 할 수 있습니다. 가능한 모든 데이터 유형과 호환 SQL Server에서. 마스크 된 데이터는 마스크 된 열의 데이터 유형에 따라 표시됩니다. 문자열 데이터 유형은 IdentityNumber 및 Phone 열과 같은 XXXX 문자를 사용하여 표시됩니다. 숫자 데이터 유형은 0으로 표시됩니다. 날짜는 DateOfBirth 열과 같은 기본 날짜 '1900-01-01'로 표시됩니다.

데모 목적으로 샘플 데이터를 마스킹 열로 채 웁니다.

DROP TABLE IF EXISTS DefaultMaskTest;

CREATE TABLE DefaultMaskTest

(

ID       INT              IDENTITY (1,1) PRIMARY KEY NOT NULL

,DefaultMask_Varchar  VARCHAR(255) MASKED WITH (FUNCTION = 'default()') NULL

,DefaultMask_Char    CHAR(1)         MASKED WITH (FUNCTION = 'default()') NOT NULL

,DefaultMask_Bit      BIT MASKED WITH (FUNCTION = 'default()') NOT NULL

,DefaultMask_Date    DATE MASKED WITH (FUNCTION = 'default()') NOT NULL

,DefaultMask_DateTime DATETIME MASKED WITH (FUNCTION = 'default()') NOT NULL

,DefaultMask_Time    TIME MASKED WITH (FUNCTION = 'default()') NOT NULL

,DefaultMask_Integer  BIGINT MASKED WITH (FUNCTION = 'default()') NOT NULL

,DefaultMask_Decimal  DECIMAL(9,2) MASKED WITH (FUNCTION = 'default()') NOT NULL

,DefaultMask_XML      XML MASKED WITH (FUNCTION = 'default()') NOT NULL

);

GO

INSERT INTO DefaultMaskTest

(

DefaultMask_Varchar, DefaultMask_Char, DefaultMask_Bit, DefaultMask_Date, DefaultMask_DateTime, DefaultMask_Time

, DefaultMask_Integer, DefaultMask_Decimal, DefaultMask_XML

)

VALUES

(

'Chetan Sharma', 'M', 1, '2020-06-12', '2021-06-12 12:23:32:543', '08:12:46:342'

, 5282991, 45628.39,'<root>Tech-Recipes</root>'

);

다음 쿼리는 MaskedTestUser 및 dbo 사용자에게 마스크 된 데이터가 표시되는 방법을 보여줍니다.

--Drop & Create User - MaskedTestUser

DROP USER IF EXISTS MaskedTestUser;

CREATE USER MaskedTestUser WITHOUT LOGIN;

--Query table using dbo user

SELECT * FROM DefaultMaskTest;

--Grant SELECT permission to MaskedTestUser

GRANT SELECT ON DefaultMaskTest TO MaskedTestUser;

--Query table using MaskedTestUser

EXECUTE AS USER = 'MaskedTestUser';

SELECT * FROM DefaultMaskTest;

--Revert user impersonation to dbo user

REVERT;

2.부분

부분 기능 문자열 데이터 유형에서만 작동. 데이터의 부분 마스킹에 문자가 거의 없음문자열의 시작 또는 끝에서 기본 'XXXX'문자를 사용하는 대신 맞춤 문자열을 사이에 삽입 할 수 있습니다. 위의 예에서 CreditCard 열은 partial (2,“XXXXXXX”, 0) 방법을 사용하여 마스킹되어 처음 두 문자 만 표시하고 사용자 정의 가능한 X 문자를 추가합니다.

데모 목적으로 샘플 데이터를 마스킹 열로 채 웁니다.

DROP TABLE IF EXISTS PartialMaskTest;

CREATE TABLE PartialMaskTest

(

ID          INT IDENTITY(1,1) PRIMARY KEY NOT NULL

,PartialMask_Varchar  VARCHAR(255) MASKED WITH (FUNCTION = 'partial(1, "XXXX",1)') NOT NULL

,PartialMask_Nvarchar NVARCHAR(255)MASKED WITH (FUNCTION = 'partial(2, "ABCDEFG",3)') NOT NULL

);

GO

INSERT INTO PartialMaskTest

(

PartialMask_Varchar

,PartialMask_Nvarchar

)

VALUES

(

'I am on tech-recipes.com'

,'SQL Server keep rocking'

);

다음 쿼리는 MaskedTestUser 및 dbo 사용자에게 마스크 된 데이터가 표시되는 방법을 보여줍니다.

--Drop & Create User - MaskedTestUser

DROP USER IF EXISTS MaskedTestUser;

CREATE USER MaskedTestUser WITHOUT LOGIN;

--Query table using dbo (owner) user

SELECT * FROM PartialMaskTest;

--Grant SELECT permission to MaskedTestUser

GRANT SELECT ON PartialMaskTest TO MaskedTestUser;

--Query table using MaskedTestUser

EXECUTE AS USER = 'MaskedTestUser';

SELECT * FROM PartialMaskTest;

--Revert user impersonation to dbo user

REVERT;

3.이메일

이메일, 중요한 기능을 고려하여 이메일 ID를 마스킹하는 특정 기능인 이메일. 이메일 기능은 문자열 데이터 유형에서만 작동합니다. 에 이메일 ID 및 도메인 이름 마스킹 ‘[이메일 보호]’ 첫 문자를 그대로 유지하고 도메인 이름을 .COM으로 변경합니다. 고객 테이블의 EmailId 열이에서 마스크되었습니다. [이메일 보호] 에 [이메일 보호]. 부분 기능조차도 이메일 기능 사용을 다음과 같이 복제 할 수 있습니다 부분적 (1, '[이메일 보호]’, 0).

이메일 기능이 작동하는지 확인하기 위해 샘플 데이터를 마스킹 열로 채 웁니다.

DROP TABLE IF EXISTS EmailMaskTest;

CREATE TABLE EmailMaskTest

(

ID   INT IDENTITY (1,1) PRIMARY KEY NOT NULL

,EmailMask VARCHAR(255) MASKED WITH (FUNCTION = 'email()') NOT NULL

);

GO

INSERT INTO EmailMaskTest

(

EmailMask

)

VALUES ('[email protected]'),

('[email protected]'),

('[email protected]');

다음 쿼리는 MaskedTestUser 및 dbo 사용자에게 마스크 된 데이터가 표시되는 방법을 보여줍니다.

--Drop & Create User - MaskedTestUser

DROP USER IF EXISTS MaskedTestUser;

CREATE USER MaskedTestUser WITHOUT LOGIN;

--Query table using dbo (owner) user

SELECT * FROM EmailMaskTest;

--Grant SELECT permission to MaskedTestUser

GRANT SELECT ON EmailMaskTest TO MaskedTestUser;

--Query table using MaskedTestUser

EXECUTE AS USER = 'MaskedTestUser';

SELECT * FROM EmailMaskTest;

--Revert user impersonation to dbo user

REVERT;

4.무작위

마스킹 숫자 데이터 유형 열 원래 값에 난수를 사용합니다. 정의 된 범위 사이에서 난수를 생성 할 수 있습니다. 고객 테이블의 급여 열이 랜덤 (1,10) – 1에서 10 사이의 난수 만 생성합니다. 우리는 다음을 사용하여 소수 범위를 정의 할 수 있습니다 무작위 (0.1,0.75).

DROP TABLE IF EXISTS RandomMaskTest;

CREATE TABLE RandomMaskTest

(

ID     INT IDENTITY (1,1) PRIMARY KEY NOT NULL

,RandomMask_INT     INT MASKED WITH (FUNCTION = 'random(1,999)') NOT NULL

,RandomMask_BIGINT  BIGINT MASKED WITH (FUNCTION = 'random(1000,2000)') NOT NULL

,RandomMask_DECIMAL DECIMAL(9,2) MASKED WITH (FUNCTION = 'random(1.1,10.5)') NOT NULL

);

GO

INSERT INTO RandomMaskTest

(

RandomMask_INT

,RandomMask_BIGINT

,RandomMask_DECIMAL

)

VALUES

(33405691, 401204193524, 311531.56);

다음 쿼리는 MaskedTestUser 및 dbo 사용자에게 마스크 된 데이터가 표시되는 방법을 보여줍니다.

--Drop & Create User - MaskedTestUser

DROP USER IF EXISTS MaskedTestUser;

CREATE USER MaskedTestUser WITHOUT LOGIN;

--Query table using dbo (owner) user

SELECT * FROM RandomMaskTest;

--Grant SELECT permission to MaskedTestUser

GRANT SELECT ON RandomMaskTest TO MaskedTestUser;

--Query table using MaskedTestUser

EXECUTE AS USER = 'MaskedTestUser';

SELECT * FROM RandomMaskTest;

--Revert user impersonation to dbo user

REVERT;

마스킹 기능 및 데이터 유형 호환성

마스킹 기능은 지원되는 데이터와 작동유형 만. 호환되지 않는 데이터 형식으로 마스킹 기능을 사용하려고하면 SQL Server에서 다음 오류가 발생합니다. 문자 데이터 유형에 임의의 함수를 사용하려고 할 때. 임의 함수는 숫자 데이터 유형과 만 호환됩니다.

16003 메시지, 수준 16, 상태 0, 줄 21열 'IdentityNumber'의 데이터 유형은 데이터 마스킹 기능 'random'을 지원하지 않습니다.</br>

데이터베이스에서 마스크 된 열 쿼리

추가 sys.masked_columns 뷰는 데이터베이스의 모든 마스크 열을 포함합니다. 이를 사용하여 마스크 된 열 이름, 마스킹 기능 및 관련 테이블 이름을 쿼리 할 수 있습니다.

SELECT  OBJECT_NAME(mc.object_id) as TableName

,mc.name as ColumnName

,TYPE_NAME(system_type_id) as DataType

,mc.is_masked as IsMasked

,mc.masking_function as MaskingFunction

FROM    sys.masked_columns as mc

WHERE  mc.is_masked = 1;

테이블 문을 만들기 위해 다중 마스킹 기능 추가

우리는 이미 유용한 예제를 통해 각 마스킹 기능을 자세히 살펴 보았습니다. 요구 사항에 따라 모든 마스킹 기능이 포함 된 테이블을 만들고 출력을 봅시다.

사용자 역할 및 권한 이해는중대한. MaskedTestUser는 실제 데이터를 볼 수있는 권한이 없으므로 마스크 된 데이터가 그에게 표시되지만 권한이있는 사용자 및 고객 테이블 소유자 인 dbo는 사용 가능한 모든 데이터를 일반 텍스트로 볼 수 있습니다.</br>

Use tempdb;

DROP TABLE IF EXISTS Customer;

CREATE TABLE Customer

(

Id INT IDENTITY(1,1)

,DateOfBirth DATE MASKED WITH (FUNCTION = 'default()') NOT NULL

,EmailId VARCHAR(255) MASKED WITH (FUNCTION = 'email()') NOT NULL

,IdentityNumber VARCHAR(11)  MASKED WITH (FUNCTION = 'default()') NOT NULL

,Phone VARCHAR(11) MASKED WITH (FUNCTION = 'default()') NOT NULL

,Salary INT MASKED WITH (FUNCTION = 'random(1,9)') NOT NULL

,CreditCard VARCHAR(20)    MASKED WITH (FUNCTION = 'partial(2,"XXXXXX",0)') NOT NULL

);

INSERT INTO Customer (DateOfBirth, EmailId, IdentityNumber, Phone, Salary, CreditCard)

VALUES ('1985-10-28', '[email protected]', 'BZVLPE1258Q', '98564533213', 85000, '9764-3451-0916-1047');

다음 쿼리는 MaskedTestUser 및 dbo 사용자에게 마스크 된 데이터가 표시되는 방법을 보여줍니다.

--Drop and Create MaskedTestUser

DROP USER IF EXISTS MaskedTestUser;

CREATE USER MaskedTestUser WITHOUT LOGIN;

--Query table using dbo user

SELECT * FROM Customer;

--Grant SELECT permission to MaskedTestUser

GRANT SELECT ON Customer TO MaskedTestUser;

--Query table using MaskedTestUser

EXECUTE AS USER = 'MaskedTestUser';

SELECT * FROM Customer;

--Revert user impersonation to dbo user

REVERT;

테이블의 기존 열에 마스킹 기능 추가

ALTER TABLE Customer DROP COLUMN IF EXISTS AccountNumber;

ALTER TABLE Customer

ADD AccountNumber INT;

ALTER TABLE Customer

ALTER COLUMN AccountNumber ADD MASKED WITH (FUNCTION = 'random(1000,5000)');

개요

동적 데이터 마스킹은 권한이 없는 사용자로부터 민감한 데이터를 숨기는 데 유용합니다. 데이터 기밀성이 비즈니스에 중요한 경우에 사용할 수 있습니다.

• Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

인덱스를 리빌드하면 통계가 업데이트 될까? 이 질문에 많은 사람들은 "YES"라고 답할 것이다. 사실은 모든 통계를 업데이트를 하지 않는다. 인덱스를 리빌드 하는경우 해당 인덱스와 관련된 통계만 업데이트 된다.

위에서 볼 수 있듯이 모든 통계는 인덱스 리빌드 작업을 통해서 업데이트 된다. 때로는 비 인덱스 통계도 매우 중요하다. 비인덱스 통계는 자동 또는 수동으로 생성된 관련 통계를 의미한다. 임계값이 큰 테이블의 경우 수동 통계 업데이트가 필요할 수 있다. 추적플래그 2371이 도움이 될 수 있다.

• Statistical maintenance functionality (autostats) in SQL Server :

http://support.microsoft.com/ko-kr/kb/195565

• Changes to automatic update statistics in SQL Server – traceflag 2371 :

http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx

실습을 통해 모든 통계를 업데이트 하지 않는 경우를 확인 할 수 있다.

[샘플 데이터 생성]

[통계 정보 확인]

[인덱스 리빌드 진행]

통계 정보를 확인해 보면 t1_index1의 통계는 최근 업데이트가 반영된 것을 확인 할 수 있으며 __WA_Sys_00000002_34C8D9D1 은 업데이트가 되지 않은 것을 확인 할 수 있다.

[참고자료]

http://blogs.msdn.com/b/psssql/archive/2015/03/06/does-rebuild-index-update-statistics.aspx

출처: https://sungwookkang.com/1062 [Data Science Lab]