블로그 이미지
정홍주
Azure에 대한 내용뿐만 아니라 새로운 트렌드로 빅데이터, BI, SharePoint, 앱 등의 내용을 다룹니다.

calendar

        1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31

Notice

2015.10.23 08:00 Microsft Azure/SQL Databases

 

Azure SQL Database Security

 

Azure SQL Database 에서 1014일에 발표된 보안 강화에 대한 내용을 정리해보겠습니다.

아래 링크를 확인해보면 SQL Server Azure SQL Database는 다른 DBMS보다는 취약점이 제일 적게 나타났습니다.

https://azure.microsoft.com/ko-kr/blog/microsoft-azure-sql-database-provides-unparalleled-data-security-in-the-cloud-with-always-encrypted/

 

 

위 링크에 소개된 Azure SQL Database 보안 내용과 보안에 대한 10월 업데이트 내용입니다.

l  Always Encrypted

https://msdn.microsoft.com/en-us/library/mt163865.aspx?wt.mc_id=WW_CE_DM_OO_BLOG_NONE

l  Transparent Data Encryption

https://msdn.microsoft.com/library/dn948096.aspx?wt.mc_id=WW_CE_DM_OO_BLOG_NONE

l  Azure Active Directory (Azure AD) 인증

https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/?wt.mc_id=WW_CE_DM_OO_BLOG_NONE

l  Row-Level Security(행 수준 잠금)

https://msdn.microsoft.com/library/dn765131?wt.mc_id=WW_CE_DM_OO_BLOG_NONE

l  동적 데이터 마스킹(Dynamic Data Masking)

https://azure.microsoft.com/en-us/documentation/articles/sql-database-dynamic-data-masking-get-started/?wt.mc_id=WW_CE_DM_OO_BLOG_NONE

l  Azure SQL Database 감사

https://azure.microsoft.com/en-us/documentation/articles/sql-database-auditing-get-started/?wt.mc_id=WW_CE_DM_OO_BLOG_NONE

 

 

위 내용 중에서 대부분 블로그에 올라가 있으며 Azure Active Directory (Azure AD) 인증, Always Encrypted 내용을 다루어보겠습니다.

 

 

posted by 정홍주
2015.09.25 08:00 Microsft Azure/고급 분석

 

Azure HDInsight 평가판

 

앞에서 HDInsight 등에 대한 정보를 알려드렸는데 HDInsight를 테스트하려면 먼저 Azure를 구독해야 합니다. Azure 구독을 위해서는 신용정보 확인이 필요해서 번거로울 수도 있습니다.

아래 링크를 확인해보시면 별도로 Azure 구독을 하지 않고 7일동안 Hadoop 클러스터를 테스트할 수 있습니다.

https://azure.microsoft.com/ko-kr/blog/try-hadoop-on-azure-hdinsight-7/

 

아래 정보를 위 링크에 있는 메일로 보내면 4 노드의 클러스터를 7일동안 평가판으로 이용할 수 있습니다.

First and last name

Company/organization

Work email address

Workload desired: Hadoop (Hive/Pig/MapReduce), HBase, Storm or Spark

Brief one line description stating intended use for trial period (ex. Learning hive, comparing to on-prem, building PoC)

 

평가판 7일이 끝나고 나면 설문조사를 링크를 받게 됩니다. 필요하다면 간단히 이메일로 신청하여 테스트해볼 수 있으니 참고하십시오.

 

posted by 정홍주
2015.09.25 08:00 Microsft Azure/고급 분석

 

HDInsight Sample – Sensor Data Analysis

 

Hive Editor 를 통해 수집된 데이터로부터 Hive 쿼리를 통해 Hive 테이블을 생성하고 이를 Excel 2013에서 액세스해서 시각화 하는 예제를 알아보겠습니다. HDInsight Query Console 에 보면 Sensor Data Analysis 예제를 확인할 수 있으며 Building 데이터와 HAV (냉난방기) 데이터를 수집하여 Hive 분석하는 내용을 아래 그림처럼 설명해주고 있습니다.

먼저 데이터를 살펴보아야 할 것 같은데요, 일단 데이터는 이미 HDinsight에 연결된 저장소 계정의 컨테이너에 저장되어 있습니다. 데이터를 한번 열어보면 아래와 같은 CSV로 구성되어 있습니다. 전세계 빌딩에서 온도 정보가 수집된 데이터로 보시면 됩니다.

데이터를 수집하였으며 Hive 테이블로 EXTERNAL 테이블을 생성해보겠습니다. 아래와 같은 HIVE 쿼리 구문을 생성합니다. 저장소의 ‘,’로 구분된 CSV 파일로부터 hvac 라는 테이블을 생성합니다. Building 테이블도 마찬가지로 생성합니다.

DROP TABLE IF EXISTS hvac;

CREATE EXTERNAL TABLE hvac(date STRING, time STRING, targettemp BIGINT,

    actualtemp BIGINT,

    system BIGINT,

    systemage BIGINT,

    buildingid BIGINT)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

STORED AS TEXTFILE LOCATION

'wasb://hbddemo@hbd.blob.core.windows.net/HdiSamples/SensorSampleData/hvac/';

 

위에서 생성한 Hvac 테이블로부터 분석에서 원하는 로직을 통해 데이터를 가공하거나 그룹핑, 필터링 등을 수행할 수 있습니다. 여기서는 temp_diff, extremetemp 라는 열을 통해 ‘HOT, COLD, NORMAL’에 대한 정보가 추가되는 hvac_temperatures 테이블을 생성합니다.

DROP TABLE IF EXISTS hvac_temperatures;

CREATE TABLE hvac_temperatures AS

SELECT *, targettemp - actualtemp AS temp_diff,

    IF((targettemp - actualtemp) > 5, 'COLD',

    IF((targettemp - actualtemp) < -5, 'HOT', 'NORMAL')) AS temprange,

    IF((targettemp - actualtemp) > 5, '1', IF((targettemp - actualtemp) < -5, '1', 0)) AS extremetemp

FROM hvac;

그리고 hvac_temperatures 테이블과 Building 테이블을 조인하여 국가, 빌딩년도 등의 정보를 가진 hvac_building 테이블을 생성합니다.

 

쿼리를 하나씩 실행해도 되고 한꺼번에 쿼리를 실행해도 됩니다.

생성된 Hive 테이블을 SELECT 하는 쿼리를 실행하여 결과를 확인해도 되며 예제에서처럼 Hive ODBC Driver 를 통해 연결하여 Excel에서도 시각화하여 확인이 가능합니다. Hive ODBC Driver를 설치하고 아래와 같이 클러스터 주소와 사용자 계정, 비밀번호를 입력하여 DSN을 설정합니다.

Excel 2013PowerPivot 에서 외부 데이터 가져오기를 ODBC를 통해 진행하면 아래 그림과 같이 HDInsightHive 테이블을 확인할 수 있습니다.

피벗차트 또는 피벗테이블을 통해 시각화를 진행할 수 있습니다.

 

간략히 HDInsight Query Console 에서 Hive Query를 실행하여 시각화하는 내용까지 살펴보았습니다.

 

 

posted by 정홍주
TAG 정홍주
2015.08.28 08:00 Microsft Azure/고급 분석

Microsoft Azure HDInsight 쿼리 콘솔

 

이번 글에서는 Microsoft AzureHDInsight에서 쿼리 콘솔을 간략히 살펴보겠습니다. Azure 관리포털에서 HDInsight를 생성하였다면 이름을 클릭하여 대시보드 메뉴 아래에서 쿼리 콘솔 메뉴를 클릭합니다.

그러면 인증 창이 나타나는데 인증에 필요한 계정과 비밀번호는 HDInsight를 생성시 입력한 관리자 계정과 비밀번호에 대한 정보를 입력하면 됩니다. 그러면 Azure의 웹 사이트 로그와 저장소에 대한 분석을 할 수 있는 솔루션과 기본적으로 제공되는 샘플 데이터를 위한 솔루션을 볼 수 있습니다.

샘플 데이터를 위한 솔루션에서 적절한 것을 선택하고 설명대로 따라가면 Hive 등의 결과를 바로 확인할 수 있습니다.

Hive 쿼리에 익숙하여 직접 Hive 쿼리를 입력하고 작업을 제출할 수 있는 Hive Editor도 볼 수 있습니다.

제출된 작업의 기록을 확인하여 결과, 오류 등을 확인할 수 있습니다.

HDFS에서 파일을 직접 확인할 수 있는 File Browser 가 있습니다. 유용하게 사용할 수 있습니다.

최근에 추가된 내용으로 Hadoop 의 데이터 노드 등에 대한 정보를 확인하고 YarnApp에 대한 정보를 확인할 수 있습니다.

 

쿼리 콘솔을 통해 다양한 작업 등을 손쉽게 진행할 수 있습니다.

다음 글에서는 샘플 데이터를 위한 솔루션에서 센서 데이터 분석을 Hive 를 통해 진행해보겠습니다.

 

 

'Microsft Azure > 고급 분석' 카테고리의 다른 글

Azure HDInsight 평가판  (0) 2015.09.25
HDInsight Sample – Sensor Data Analysis  (0) 2015.09.25
Microsoft Azure HDInsight 쿼리 콘솔  (0) 2015.08.28
HDInsight Hadoop – Word Count(2)  (0) 2015.08.14
HDInsight Hadoop – Word Count(1)  (0) 2015.08.07
HDInsight - Hadoop 시작  (0) 2015.08.07
posted by 정홍주
2015.08.14 08:00 Microsft Azure/고급 분석

 

HDInsight Hadoop – Word Count(2)

 

앞에서 Hadoop Command 창에서 Word Count를 실행해보았습니다. 사실 Hadoop에 연결해야 하는 번거로움이 있기는 합니다. Microsoft AzurePowerShell을 통해 원격에서도 Word Count 등의 MapReduce 작업을 실행할 수 있습니다. Azure PowerShellhttp://azure.microsoft.com/ko-kr/ 의 다운로드 링크를 통해 설치할 수 있습니다.

아래는 PowerShell ISE를 통해 진행합니다.

 

l  Word Count

# 변수 선언

$subscriptionName =Azure subscription name"

$clusterName = "HDInsight cluster name"

 

# Azure 로그온, 구독 선택

Import-AzurePublishSettingsFile "G:\HDInsight\Azure.publishsettings"

Select-AzureSubscription $subscriptionName

 

 

# MapReduce 작업 (한줄로 실행)

$wordCountJobDefinition = New-AzureHDInsightMapReduceJobDefinition

-JarFile "wasb:///example/jars/hadoop-mapreduce-examples.jar"

-ClassName "wordcount"

-Arguments "wasb:///example/data/gutenberg/davinci.txt", "wasb:///example/data/WordCountOutput"

 

# 작업 제출 (한줄로 실행)

$wordCountJob = Start-AzureHDInsightJob -Cluster $clusterName

-JobDefinition $wordCountJobDefinition | Wait-AzureHDInsightJob -WaitTimeoutInSeconds 3600 

 

 

# 작업 결과 확인

Get-AzureHDInsightJobOutput -Cluster $clusterName -JobId $wordCountJob.JobId -StandardError

 

Word CountPowerShell hadoop-mapreduce-examples.jar 파일을 통해 davinci.txt 파일을 처리한 결과입니다. MapReduce 작업 결과를 확인할 수 있습니다.

 

 

 

실제 출력 파일이 생성된 것을 관리 포털의 저장소, 컨테이너에서 확인 가능합니다.

 

 

l 출력 결과 문자열 확인

 

출력 결과 파일을 다운로드하여 cat 명령으로 특정 문자를 검색할 수 있습니다. 저장소에 있는 출력 파일을 다운로드하여 결과를 화면에 표시해줍니다.

 

# 변수 선언

$subscriptionName =Azure subscription name"

$clusterName = "HDInsight cluster name"

$storageAccountName = "Azure storage account name"

$containerName = "Blob storage container name

 

# 저장소 계정 정보

$storageAccountKey = Get-AzureStorageKey $storageAccountName | %{ $_.Primary }

$storageContext = New-AzureStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storageAccountKey 

 

# 작업 결과 다운로드 (한줄로 실행)

Get-AzureStorageBlobContent -Container $ContainerName

-Blob example/data/WordCountOutput/part-r-00000 -Context $storageContext -Force

 

cat ./example/data/WordCountOutput/part-r-00000 | findstr "there"

 

아래는 위 PowerShell의 결과입니다.

 hadoop-mapreduce-examples.jar MapReduce 함수는 아래 링크를 참고할 수 있습니다.

https://azure.microsoft.com/ko-kr/documentation/articles/hdinsight-develop-deploy-java-mapreduce/

매번 작업에 jar 파일을 생성하기에는 제한적이며 보다 더 쉽게 구성할 수 있도록 Hive, Pig 등을 제공하고 있습니다. 다음 글에서는 Hive를 통해 MapReduce 작업을 살펴 보겠습니다.

 

 

posted by 정홍주
2015.08.07 08:30 Microsft Azure/고급 분석

HDInsight Hadoop – Word Count(1)

 

Hadoop 클러스터를 원격으로 연결하여 Hadoop Command 에서 아래와 같은 명령어로 Word Count MapReduce 작업을 실행하여 결과를 확인해보겠습니다.

MapReduce 작업에 대한 설명은 아래 링크를 확인할 수 있습니다.

https://azure.microsoft.com/ko-kr/documentation/articles/hdinsight-use-mapreduce/

 

MapReduce 관련 작업을 실행하기 위해 반드시 Hadoop Command 에서 실행해야 하는 것은 아닙니다. Azure PowerShell을 통해 Hadoop 클러스터가 아닌 원격에서 실행할 수 도 있습니다.

아래 구문을 하나씩 복사하여 실행할 수 있습니다. Hadoop 버전을 확인하여 진행하면 됩니다.

cd %hadoop_home%

hadoop fs -mkdir /user

hadoop fs -mkdir /user/demo

hadoop fs -copyFromLocal C:\apps\dist\examples\data\gutenberg\davinci.txt /user/demo

hadoop fs -ls /user/demo

hadoop jar C:\apps\dist\hadoop-2.4.0.2.1.15.1-0001\share\hadoop\mapreduce\hadoop-mapreduce-examples-2.4.0.2.1.15.1-0001.jar wordcount /user/demo/*.txt /user/demo/WordCount_Output

hadoop fs -cat /user/demo/WordCount_Output/part-r-00000 | findstr "there"

 

처음은 user 아래에 demo 라는 폴더를 생성합니다.

hadoop fs -mkdir /user/demo

 

WordCount 할 수 있는 원본 파일을 복사해옵니다. 원본 파일은 examples 폴더에 들어가 있습니다.

hadoop fs -copyFromLocal C:\apps\dist\examples\data\gutenberg\davinci.txt /user/demo

 

파일을 확인하기 위해 ls 명령어로 확인해봅니다.

hadoop fs -ls /user/demo

 

이제 jar 파일을 통해 Wordcount 라는 클래스를 통해 Map 함수와 Reduce 함수를 수행하고 출력은 WordCount_Output 이라는 폴더 아래에 생성하도록 jar 명령어를 실행합니다. 마찬가지로 jar 파일은 examples 폴더에 들어가 있습니다. 출력 결과를 보면 Map 작업과 Reduce 작업을 확인할 수 있습니다.

hadoop jar C:\apps\dist\hadoop-2.4.0.2.1.15.1-0001\share\hadoop\mapreduce\hadoop-mapreduce-examples-2.4.0.2.1.15.1-0001.jar wordcount /user/demo/*.txt /user/demo/WordCount_Output

 

결과 파일이 생성되어 있고 내부 내용을 보기 위해 cat, findstr 명령어를 실행합니다.

hadoop fs -cat /user/demo/WordCount_Output/part-r-00000 | findstr "there"

 다음 글에서는 PowerShell을 통해 Word CountHadoop 클러스터에서 실행해보겠습니다.

posted by 정홍주
TAG 정홍주
2015.08.07 08:00 Microsft Azure/고급 분석

 

HDInsight - Hadoop 시작

 

요번 글에서는 Microsoft Azure에서 HDInsightHadoop 을 만들어보겠습니다. 빅 데이터의 핵심이라고 할 수 있는 부분인 HadoopWindows Server에 구성하는 내용입니다.

관리 포털로 이동하여 새로 만들기에서 HDInsight에서 Hadoop을 선택합니다. 사전에 저장소를 생성해야 합니다.

 

시간이 약 30분이내로 흐르면 생성이 완료되어 실행 중으로 메뉴가 바뀌게 됩니다.

 

이름을 클릭하여 구성 메뉴로 이동합니다. 아래 쪽을 보면 원격 사용 메뉴를 확인할 수 있습니다.

 

원격 사용 메뉴를 클릭하여 RDP 사용자 이름과 암호, 만료날짜를 입력합니다.

 

시간이 지나면 연결 메뉴가 활성화 됩니다. 연결 메뉴를 클릭하여 위에서 입력한 RDP 사용자 이름과 암호로 로그온 합니다.

 

로그온하면 Windows Server 2012 R2 에서 바탕 화면이 바로 가기를 볼 수 있습니다.

 

Hadoop Command 을 열고 아래 명령어를 입력하면 Hadoop 버전을 알 수 있습니다.

hadoop version

 다음 글에서는 Hadoop Command 창에서 Word Count를 실행해보겠습니다.

 

'Microsft Azure > 고급 분석' 카테고리의 다른 글

HDInsight Hadoop – Word Count(2)  (0) 2015.08.14
HDInsight Hadoop – Word Count(1)  (0) 2015.08.07
HDInsight - Hadoop 시작  (0) 2015.08.07
Big Data와 Microsoft Azure HDInsight  (0) 2015.07.19
Azure Machine Learning - 데모  (0) 2015.05.02
HDInsight에서 HBase 지원  (0) 2014.06.16
posted by 정홍주
TAG 정홍주
2015.07.19 20:54 Microsft Azure/고급 분석

 Big DataMicrosoft Azure HDInsight

 

Big DataMicrosoft Azure HDInsight에 대한 내용으로 연세대학교 원주캠퍼스에서 특강을 했습니다. 3월에는 캡스톤 평가에도 참석하였으며 IoT 관련하여 8월에 다시 방문할 예정입니다.

 

데이터 과학자에 대한 내용을 강조했으며, Big DataHadoop 내용이 의공학과 관련하여 학생들과 업체에 많은 도움이 되었으면 합니다.

 

의료관련 빅 데이터는 액세스가 어려웠지만 심평원 링크를 참고하여 직접 방문하거나 원격으로 액세스 할 수 있습니다.

http://opendata.hira.or.kr/home.do

 

빅데이터 전략센터에 대한 링크도 참고할 수 있습니다.

https://kbig.kr/

 

 

'Microsft Azure > 고급 분석' 카테고리의 다른 글

HDInsight Hadoop – Word Count(1)  (0) 2015.08.07
HDInsight - Hadoop 시작  (0) 2015.08.07
Big Data와 Microsoft Azure HDInsight  (0) 2015.07.19
Azure Machine Learning - 데모  (0) 2015.05.02
HDInsight에서 HBase 지원  (0) 2014.06.16
HDInsight 3.0 미리보기  (0) 2014.03.01
posted by 정홍주
TAG 정홍주
2015.07.10 08:00 Microsft Azure/SQL Databases

SQL Database V12 – 투명한 데이터 암호화(Transparent Data Encryption)

 

이번 글에서는 Azure SQL Database V12에서 투명한 데이터 암호화(TDE)를 지원하는 내용을 살펴보겠습니다. 보다 더 자세한 정보는 아래 링크를 참고할 수 있습니다.

https://msdn.microsoft.com/library/0bf7e8ff-1416-4923-9c4c-49341e208c62.aspx

 

Azure SQL Database V12의 새로운 기능에 대한 내용은 아래 링크를 참고할 수 있습니다.

https://azure.microsoft.com/ko-kr/documentation/articles/sql-database-v12-whats-new/

 

투명한 데이터 암호화(TDE) 설정하는 방법은 여러 가지 방법이 있습니다.

l  미리보기 포털

l  T-SQL 구문

l  PowerShell 스크립트

 

투명한 데이터 암호화(TDE) 설정은 미리보기 포털을 통해서 액세스 해보겠습니다.

1.     https://portal.azure.com 로 이동하여 V12 데이터베이스로 이동합니다.

2.     해당 데이터베이스 메뉴에서 모든 설정 메뉴를 클릭하면 투명한 데이터 암호화 메뉴를 볼 수 있습니다.
 

3.     미리 보기 조건을 수락해야 사용할 수 있습니다. 미리 보기 조건을 클릭하고 체크하고 확인을 클릭합니다.
   

4.     데이터 암호화 설정 메뉴를 선택하고 저장을 클릭하면 됩니다.   

5.     암호화 진행 메뉴를 살펴볼 수 있습니다.   

6.     암호화가 완료되면 아래 그림처럼 암호화됨상태를 확인할 수 있습니다.   

7.     T-SQL로 암호화 상태를 확인할 수 있습니다. sys.dm_database_encryption_keys 뷰의 encryption_state 값을 통해서 확인이 가능합니다. 뷰의 자세한 정보는 아래 링크를 참고할 수 있습니다.

SELECT * FROM  sys.dm_database_encryption_keys

 

https://msdn.microsoft.com/ko-kr/library/bb677274.aspx

  

 

암호화 되어 있는 데이터베이스의 경우 포털 이미지에 열쇠 표시로 나타납니다.

추가로 T-SQL 구문으로는 아래와 같은 구문으로 투명한 데이터 암호화(TDE) 설정이 가능합니다.

 

-- database encryption key 생성

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE ##MS_TdeCertificate##;

 

-- 암호화 설정

ALTER DATABASE [AdventureWorksLT] SET ENCRYPTION ON;

GO

 

 

투명한 데이터 암호화(TDE) 설정은 지리적 복제, 특정 시점 복원, 삭제된 데이터베이스로부터 복원, 데이터베이스 복사 등에서도 설정이 상속되어 복호화 등의 설정은 필요 없습니다.

그래서 간단하게 다른 서버로 데이터베이스를 복사하여 설정을 확인해보았습니다. 

 

그러나 Bac 로 내보내기하여 다시 가져오기할 경우는 암호화되어 내보내는 것은 아니라고 설명이 되어 있어 테스트를 해보았습니다. 암호화되어 있지 않은 것으로 나타납니다.

 

Bac 파일 같은 경우 로컬로 복사할 수도 있어 다른 서버에 복원할 경우 복원이 된다는 것이 생각과는 다르며 Azure TDE 미리보기와 로컬 TDE와 차이가 있습니다. 이점은 고려해야 할 사항입니다.

 

posted by 정홍주
TAG TDE, 정홍주
2015.07.08 08:00 Microsft Azure/SQL Databases

 

SQL Database V12 – 행 수준 보안(Row-Level Security)

 

이번 글에서는 Azure SQL Database V12에서의 행 수준 보안(Row-Level Security)에 대한 내용을 살펴보려 합니다. 행 수준 보안(RLS)의 경우 SQL Server 2014에서 지원되는 기능으로 사용자별로 SELECT 결과값이 다르게 나오게 하는 것으로 사전 설정된 행 필터에 따라 결과를 표시하므로 사용자마다 다른 결과값을 보게 됩니다.

 

Azure SQL Database V12의 새로운 기능에 대한 내용은 아래 링크를 참고할 수 있습니다.

https://azure.microsoft.com/ko-kr/documentation/articles/sql-database-v12-whats-new/

 

행 수준 보안(RLS)에 대한 내용은 아래 링크를 참고할 수 있습니다. 행 수준 보안을 적용하게 하려면 먼저 사용자별로 연결이 설정되어야 합니다. 일반적인 단일 사용자로만 들어오게 되면 행 수준 보안은 의미가 없으며 여러 사용자별로 실행된다면 행 수준의 액세스를 제어할 수 있습니다.

https://msdn.microsoft.com/library/dn765131.aspx

 

위 링크의 예제를 활용하여 Azure SQL Database V12에서 행 수준 보안을 아래와 같이 적용해보았습니다. 위 링크의 경우 행 수준별 사용자를 한명만 할당하게 되는데 이는 여러 명이 해당 행을 접근할 수 있는 경우라면 비효율적이라 역할 기반으로 수정해서 적용해보았습니다.

 

 

-- 사용자를 생성합니다, Sales Region 별 사용자를 생성해서

-- 지역별 행 수준 보안을 적용해보겠습니다.

CREATE USER Manager WITHOUT LOGIN;

CREATE USER SalesUS WITHOUT LOGIN;

CREATE USER SalesEU WITHOUT LOGIN;

CREATE USER SalesKR WITHOUT LOGIN;

GO

 

-- 한 행당 사용자 한명으로 할당하지 않고 역할 기반을 구성하기 위한

-- 사용자 테이블을 생성합니다.

CREATE TABLE SalesRep

(ID INT IDENTITY PRIMARY KEY,

UserName sysname

)

GO

-- 사용자 여러 명을 하나의 역할로 묶어주는 역할 테이블을 생성합니다.

CREATE TABLE RLSRole

(ID INT IDENTITY PRIMARY KEY

, RoleName sysname

, SalesRepID INT REFERENCES SalesRep(ID)

)

GO

 

INSERT SalesRep VALUES(N'Manager')

INSERT SalesRep VALUES(N'SalesUS')

INSERT SalesRep VALUES(N'SalesEU')

INSERT SalesRep VALUES(N'SalesKR')

 

GO

INSERT RLSRole VALUES(N'ManagerGroup',1)

INSERT RLSRole VALUES(N'USGroup',2)

INSERT RLSRole VALUES(N'EUGroup',3)

INSERT RLSRole VALUES(N'KRGroup',4)

 

-- 최종 결과에서 다시 INSERT 해서 결과를 확인할 구문입니다.

--  INSERT RLSRole VALUES(N'EUGroup',4)

 

GO

 

-- 실제 데이터 값을 가지고 있는 테이블을 생성합니다.

-- RoleName 이라는 열을 행 수준 보안에서 사용할 것입니다.

CREATE TABLE Sales

    (

    OrderID int,

    RoleName sysname,

    Product varchar(10),

    Qty int

    );

 

GO

INSERT Sales VALUES

(1, 'USGroup', 'Valve', 5),

(2, 'USGroup', 'Wheel', 2),

(3, 'EUGroup', 'Valve', 4),

(4, 'EUGroup', 'Bracket', 2),

(5, 'KRGroup', 'Wheel', 5),

(6, 'KRGroup', 'Seat', 5);

 

SELECT * FROM Sales;

 

-- 테이블에 SELCT 권한을 부여합니다.

GRANT SELECT ON Sales TO Manager;

GRANT SELECT ON Sales TO SalesUS;

GRANT SELECT ON Sales TO SalesEU;

GRANT SELECT ON Sales TO SalesKR;

 

 

CREATE SCHEMA Security;

GO

-- 사용자 함수를 생성합니다. 매개변수는 역할 이름이며

-- 역할테이블과 사용자테이블을 조인하여 현재 사용자 정보로 필터링하는 함수입니다.

-- Manager 일 경우 전체가 반환되도록 되어 있으며

-- 비지니스 로직을 적용하여 행 수준 보안을 구성하기 위한 함수 입니다.

CREATE FUNCTION Security.fn_securitypredicate

(@RoleName AS sysname)

    RETURNS TABLE

WITH SCHEMABINDING

AS

    RETURN SELECT 1 AS fn_securitypredicate_result

             FROM dbo.RLSRole AS r INNER JOIN dbo.SalesRep AS s

             ON r.SalesRepID =s.ID

WHERE (@RoleName =r.RoleName AND s.UserName= USER_NAME() )

                           OR ( USER_NAME() = 'Manager');

 

 

 

-- 행 수준 보안의 핵심이며 ADD FILTER PREDICATE 정책을 Sales 테이블에 적용하는 구문입니다.

-- 행 필터는 위에서 생성한 함수이며 Sales 테이블의 역할 이름을 매개변수로 전달합니다.

CREATE SECURITY POLICY SalesFilter

ADD FILTER PREDICATE Security.fn_securitypredicate(RoleName)

ON dbo.Sales

WITH (STATE = ON);

 

-- EXECUTE AS 구문을 통해서 테스트합니다.

EXECUTE AS USER = 'SalesUS';

SELECT * FROM Sales;

REVERT;

 

/*

1            USGroup             Valve     5

2            USGroup             Wheel    2

*/

 

EXECUTE AS USER = 'SalesEU';

SELECT * FROM Sales;

REVERT;

/*

3            EUGroup             Valve     4

4            EUGroup             Bracket  2

*/

 

EXECUTE AS USER = 'SalesKR';

SELECT * FROM Sales;

REVERT;

/*

5            KRGroup             Wheel    5

6            KRGroup             Seat       5

*/

 

 

EXECUTE AS USER = 'Manager';

SELECT * FROM Sales;

REVERT;

/*

1            USGroup             Valve     5

2            USGroup             Wheel    2

3            EUGroup             Valve     4

4            EUGroup             Bracket  2

5            KRGroup             Wheel    5

6            KRGroup             Seat       5

*/

 

 

 

 

--  INSERT RLSRole VALUES(N'EUGroup',4)

-- 구문을 실행하여 SalesKR 사용자는 EUGroup 역할에도 포함하게 합니다.

-- EUGroup 역할에 속하기 때문에 실행 결과는 유럽 지역까지 결과에 나타납니다.

 

EXECUTE AS USER = 'SalesKR';

SELECT * FROM Sales;

REVERT;

/*

3            EUGroup             Valve     4

4            EUGroup             Bracket  2

5            KRGroup             Wheel    5

6            KRGroup             Seat       5

*/

 

 

-- 아래는 수준 보안을 해제합니다.

-- 테이블이나 함수의 스키마를 변경하려면 해제해야 합니다.

ALTER SECURITY POLICY SalesFilter

WITH (STATE = OFF);

 

 

행 수준 보안을 적용하게 하려면 먼저 사용자별로 연결이 설정되어야 합니다. 또한 사용자 함수를 기반으로 하기 때문에 데이터 건수가 많은 테이블의 경우 성능에 대한 테스트도 필요할 것으로 보입니다.

이번 글을 통해 Azure SQL Database V12에서도 RLS 를 지원한다는 것을 간략히 살펴보았습니다.

 

 

posted by 정홍주
TAG RLS, 정홍주