Azure SQL Database 및 Azure SQL Data Warehouse에 대한 액세스 제어 및 권한 부여

 

·       Version : Azure SQL

 

Azure SQL Database 및 Azure SQL Data Warehouse 방화벽 규칙이 구성된 후에는 관리자 계정 중 하나로, 데이터베이스 소유자로 또는 데이터베이스의 데이터베이스 사용자로 SQL Database 및 SQL Data Warehouse에 연결할 수 있다.

·       방화벽 규칙 설정 : https://docs.microsoft.com/ko-kr/azure/sql-database/sql-database-firewall-configure?WT.mc_id=DP-MVP-4039834

 

관리자로 작동하는 계정에는 두 가지가 (서버 관리자 및 Active Directory 관리자)이 있습니다. SQL 서버에 대해 이러한 관리자 계정을 식별하려면 Azure Portal을 열고 SQL Server 또는 SQL Database의 [속성] 탭으로 이동한다.

 

 

·       Server Admin login은 Azure SQL 서버를 만들 때 서버 관리자 로그인을 지정해야 한다. SQL 서버는 master 데이터베이스에 로그인으로 해당 계정을 만든다. 이 계정은 SQL Server 인증(사용자 이름 및 암호)을 사용하여 연결되며 하나만 생성할 수 있다. 서버 관리자에 대한 암호를 다시 설정하려면 Azure Portal로 이동하여 SQL Server를 클릭하고 목록에서 서버를 선택한 다음, 암호 재설정을 클릭한다.

·       Azure Active Directory admin은 하나의 Azure Active Directory 계정, 개인 또는 보안 그룹 계정을 관리자로 구성할 수도 있다. Azure AD 관리자를 구성하는 것은 선택 사항이지만, Azure AD 계정을 사용하여 SQL Database에 연결하려면 Azure AD 관리자를 반드시 구성해야 한다.

 

Server Admin login과 Azure Active Directory admin 관리자 계정에는 다음과 같은 특징이 있다.

·       서버에서 모든 SQL Database에 자동으로 연결할 수 있는 유일한 계정이다. (사용자 데이터베이스에 연결하려면 다른 계정은 데이터베이스의 소유자이거나 사용자 데이터베이스에 사용자 계정이 있어야 한다.)

·       계정은 dbo 사용자로 사용자 데이터베이스에 연결하고 사용자 데이터베이스에서 모든 권한을 갖는다. (사용자 데이터베이스의 소유자는 또한 dbo 사용자로 데이터베이스에 접속한다.)

·       master 데이터베이스에 dbo 사용자로 접속할 수 없으며 master에서는 제한된 사용 권한을 갖는다.

·       SQL 데이터베이스에서 sysadmin 역할의 멤버가 아니다.

·       데이터베이스, 로그인, master의 사용자 및 서버 수준 IP 방화벽 규칙을 만들고 변경하고 삭제할 수 있다.

·       dbmanager 및 loginmanager 역할에 멤버를 추가하고 제거할 수 있다.

·       sys.sql_logins 시스템 테이블을 볼 수 있다.

 

서버 수준 방화벽이 제대로 구성되면 SQL 서버 관리자 및 Azure Active Directory 관리자가 SQL Server Management Studio 또는 SQL Server Data Tools와 같은 클라이언트 도구를 사용하여 연결할 수 있다. 최신 도구만 모든 특징 및 기능을 제공한다. 다음 다이어그램에서는 두 명의 관리자 계정에 대한 일반적인 구성을 보여 준다. 서버 수준 방화벽에서 열려 있는 포트를 사용하면 관리자가 모든 SQL Database에 연결할 수 있다.

 

[Serve Level Administrative Roles]

Database creators

dbmanager 역할의 멤버는 새 데이터베이스를 만들 수 있다. 이 역할을 사용하려면 master 데이터베이스에 사용자를 만든 다음 해당 사용자를 dbmanager 데이터베이스 역할에 추가해야 한다. 데이터베이스를 만들려면 사용자가 master 데이터베이스의 SQL Server 로그인을 기반으로 한 사용자이거나 Azure Active Directory 사용자를 기반으로 한 포함된 데이터베이스 사용자여야 한다. 아래 스크립트를 사용하여 생성할 수 있다.

USE master

GO

 

CREATE LOGIN Mary WITH PASSWORD = '<strong_password>';

 

CREATE USER [mike@contoso.com] FROM EXTERNAL PROVIDER; -- To create a user with Azure Active Directory

CREATE USER Ann WITH PASSWORD = '<strong_password>'; -- To create a SQL Database contained database user

CREATE USER Mary FROM LOGIN Mary;  -- To create a SQL Server user based on a SQL Server authentication login

 

ALTER ROLE dbmanager ADD MEMBER Mary;

ALTER ROLE dbmanager ADD MEMBER [mike@contoso.com];

 

Login managers

Loginmanager 멤버는 master 데이터베이스에 새 로그인을 만들 수 있다. 원한다면 동일한 단계(로그인 및 사용자 만들기, 사용자를 login manager 역할에 추가)를 완료하여 사용자가 master에서 새 로그인을 만들 수 있도록 한다. 일반적으로 Microsoft는 로그인 기반 사용자를 사용하는 대신 데이터베이스 수준에서 인증하는 포함된 데이터베이스 사용자를 사용할 것을 권장하므로 로그인이 필수는 아니다.

 

[Non-administrator users]

일반적으로 비관리자 계정은 master 데이터베이스에 액세스할 필요가 없다. 비관리자 계정은 CREATE USER(Transact-SQL) 문을 사용하여 데이터베이스 수준에서 포함된 데이터베이스 사용자를 생성한다. 사용자는 Azure Active Directory 인증 포함 된 데이터베이스 사용자 (Azure AD 인증용 환경을 구성한 경우) 또는 SQL Server 인증 포함 된 데이터베이스 사용자 또는 SQL Server 기반 SQL Server 인증 사용자 일 수 있다. 아래 예시 스크립트를 사용하여 계정을 생성할 수 있다.

CREATE USER Mary FROM LOGIN Mary;

CREATE USER [mike@contoso.com] FROM EXTERNAL PROVIDER;

 

GRANT ALTER ANY USER TO Mary;

 

--db_owner 역할 멤버로 설정

ALTER ROLE db_owner ADD MEMBER Mary;

 

--Azure SQL Data Warehouse 경우 아래 실행

EXEC sp_addrolemember 'db_owner', 'Mary';

 

데이터베이스 수준 방화벽이 제대로 구성되었으면 데이터베이스 사용자는 SQL Server Management Studio 또는 SQL Server Data Tools와 같은 클라이언트 도구를 사용하여 연결할 수 있다. 다음 다이어그램에서는 일반적인 비관리자 액세스 경로를 보여준다.

 

이 외에도 그룹 및 역할, 권한을 정의 할 수 있으며, SQL Database에는 개별적으로 부여하거나 거부할 수 잇는 100개가 넘는 권한이 있다. 대부분 권한은 중첩되어 사용된다. 자세한 내용은 참고자료의 링크를 확인할 수 있도록 한다.

 

[참고자료]

·       (영어) https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins?WT.mc_id=DP-MVP-4039834

·       (한국어) https://docs.microsoft.com/ko-kr/azure/sql-database/sql-database-manage-logins?WT.mc_id=DP-MVP-4039834

 

 

 

2019-11-08 / Sungwook Kang / http://sungwookkang.com/

 

 

Azure SQL Database, Azure SQL Data Warehouse, Azure 방화벽, Azure Database 방화벽, Azure Security, Controlling SQL Database Access, DB 접근, DB 액세스

저작자표시 비영리 변경금지

'SQL Server > SQL on Azure' 카테고리의 다른 글

Azure SQL Database 및 Azure SQL Data Warehouse IP 방화벽 규칙  (0) 2019.11.07
Azure SQL Database 보안 기능 개요  (0) 2019.10.17
Azure SQL Database의 서버리스 구매 모델  (0) 2019.10.15
Azure SQL에서 선택할 수 있는 다양한 배포 옵션  (0) 2019.10.11
Azure SQL Database의 vCore 모델과 DTU 모델에서 마이그레이션  (0) 2019.10.10

기본 추적을 사용한 SQL Server 스키마 변경사항 캡처

 

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

 

SQL Server에서 스키마 변경이 발생하였을 때 이를 캡처하고 확인할 수 있는 방법에 대해서 알아본다.

 

기본적으로 SSMS에서는 스키마 변경 기록 보고서를 제공한다. 이 보고서에서는 DDL 작업에 대한 내용과 시간, 로그인 사용자, 사용자 이름 등 유용한 정보가 표시되지만 SQL Server가 시작된 이후의 내용만 확인할 수 있다.

 

 

 

 

SQL Server가 다시 시작되더라도 변경 내용을 히스토리로 관리할 수 있도록 사용자 테이블에 변경 내역을 저장하는 방법에 대이다. 우선 변경 내용을 저장할 테이블을 생성한다.

-- create table to hold DDL history

CREATE TABLE sw_test.[dbo].[DDL_History](

    [database_name] [nvarchar](256) NULL,

    [start_time] [datetime] NULL,

    [login_name] [nvarchar](256) NULL,

    [user_name] [nvarchar](256) NULL,

    [application_name] [nvarchar](256) NULL,

    [ddl_operation] [nvarchar](40) NULL,

    [object] [nvarchar](257) NOT NULL,

    [type_desc] [nvarchar](60) NULL

) ON [PRIMARY]

GO

 

아래 스크립트를 실행하면 변경된 내역을 위에 생성한 사용자 테이블로 저장한다.

USE [master]

GO

--Declare variables

SET NOCOUNT ON;

DECLARE @dbid INT;

DECLARE @dbname VARCHAR(100);

DECLARE @execstr VARCHAR(4000);

-- Declare a cursor.

DECLARE dbs CURSOR FOR

SELECT database_id,name from sys.databases where name not in ('master','tempdb');

 

-- Open the cursor.

OPEN dbs;

-- Loop through all the tables in the database.

FETCH NEXT

FROM dbs

INTO @dbid,@dbname;

WHILE @@FETCH_STATUS = 0

BEGIN;

 

-- Check default trace for any DDL in specific database

SELECT @execstr='if (select convert(int,value_in_use) from sys.configurations where name = ''default trace enabled'') = 1

begin

declare @d1 datetime;

declare @diff int;

declare @curr_tracefilename varchar(500);

declare @base_tracefilename varchar(500);

declare @indx int ;

declare @temp_trace table (

obj_name nvarchar(256)

, obj_id int

, database_name nvarchar(256)

, start_time datetime

, event_class int

, event_subclass int

, object_type int

, server_name nvarchar(256)

, login_name nvarchar(256)

, user_name nvarchar(256)

, application_name nvarchar(256)

, ddl_operation nvarchar(40)

);

 

select @curr_tracefilename = path from sys.traces where is_default = 1 ;

set @curr_tracefilename = reverse(@curr_tracefilename)

select @indx = PATINDEX(''%\%'', @curr_tracefilename)

set @curr_tracefilename = reverse(@curr_tracefilename)

set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + ''\log.trc'';

 

insert into @temp_trace

select ObjectName

, ObjectID

, DatabaseName

, StartTime

, EventClass

, EventSubClass

, ObjectType

, ServerName

, LoginName

, NTUserName

, ApplicationName

, ''temp''

from ::fn_trace_gettable( @base_tracefilename, default )

where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID = '+cast(@dbid as varchar)+' ;

 

update @temp_trace set ddl_operation = ''CREATE'' where event_class = 46;

update @temp_trace set ddl_operation = ''DROP'' where event_class = 47;

update @temp_trace set ddl_operation = ''ALTER'' where event_class = 164;

 

select @d1 = min(start_time) from @temp_trace

set @diff= datediff(hh,@d1,getdate())

set @diff=@diff/24;

 

        insert into sw_test.dbo.DDL_History (database_name,start_time,login_name,user_name,application_name,ddl_operation,object,type_desc)

        select database_name,start_time,login_name,user_name,application_name,ddl_operation,s.name+''.''+o.name as "object",o.type_desc

        from @temp_trace tt inner join

         '+@dbname+'.sys.objects o on tt.obj_id=o.object_id inner join

             '+@dbname+'.sys.schemas s on s.schema_id=o.schema_id

     where object_type not in (21587) -- don''t bother with auto-statistics as it generates too much noise

and start_time > (select case when max(start_time) is NULL then ''1900-01-01'' else max(start_time) end from sw_test.dbo.DDL_History)

end'

 

EXEC (@execstr)

 

FETCH NEXT

FROM dbs

INTO @dbid,@dbname;

END;

-- Close and deallocate the cursor.

CLOSE dbs;

DEALLOCATE dbs;

 

 

위 스크립트를 주기적으로 실행하도록 설정하여 DDL 변경 사항에 대해서 이력관리를 할 수 있도록 한다.

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4057/capture-sql-server-schema-changes-using-the-default-trace/

 

2015-10-21 / 강성욱 / http://sqlmvp.kr

 

MS SQL, SQL Server, 스키마 변경, DDL 캡처, DB 보안, DB 접근, DDL 사용자 확인, DB 변경 이력 관리

'SQL Server > SQL Server Tip' 카테고리의 다른 글

확장이벤트를 사용한 실행 계획 캡처  (0) 2015.10.29
테이블 외래키 트리 확인 및 데이터 삭제하기  (0) 2015.10.26
SQL Server 2016 JSON 출력에 NULL 값 포함하기  (0) 2015.10.15
SQL Server 특정 테이블의 모든 컬럼에서 문자열 찾기  (0) 2015.10.05
SQL Server 임시 테이블 특성  (0) 2015.09.30

+ Recent posts

티스토리툴바