Azure SQL Database如何創(chuàng)建只讀用戶

本文將介紹如何在Azure SQL Database創(chuàng)建只讀用戶。

請先按照筆者之前的文章：Azure SQL Database(24)使用新管理界面，創(chuàng)建跨數(shù)據(jù)中心標(biāo)準(zhǔn)地域復(fù)制(Standard Geo-Replication)

預(yù)先準(zhǔn)備以下內(nèi)容：

1.在Azure China East數(shù)據(jù)中心，作為主站點。創(chuàng)建1個Database為MyDB。

2.在Azure China North數(shù)據(jù)中心，創(chuàng)建只讀數(shù)據(jù)庫。MyDB為只讀數(shù)據(jù)庫。

注意：我們在下圖中創(chuàng)建的服務(wù)器管理員賬戶leizhang，是屬于sysadmin。

3.當(dāng)我們配置完服務(wù)器防火墻。訪問主站點：leizhangsqlsvrsh.database.chinacloudapi.cn，輸入相關(guān)的登錄信息。

我們就可以訪問上面創(chuàng)建的數(shù)據(jù)庫了。

我們預(yù)先創(chuàng)建在MyDB數(shù)據(jù)庫里，創(chuàng)建一個數(shù)據(jù)表：

CREATE TABLE dbo.new_employees(

[ID][int]IDENTITY(1,1)NOT NULL,

[firstname][nvarchar](50)NOT NULL,

[lastname][nvarchar](50)NOT NULL,

[gender][datetime]NOT NULL)

但是請注意上圖中：服務(wù)器管理員賬戶leizhang，畢竟是屬于sysadmin組的。

如果我們使用ETL抽取Azure SQL Database數(shù)據(jù)的時候，提供leizhang這個sysadmin組的用戶是非常不安全的。

最安全的方式，是提供只讀用戶。

我們這里需要做兩種只讀用戶：

1.在China East主站點，創(chuàng)建只讀用戶

2.在China North只讀站點，創(chuàng)建只讀用戶

3.站點信息如下：

類型SQL Server Name物理位置DB Name定價層權(quán)限

主站點leizhangsqlsvrsh.database.chinacloudapi.cn China East MyDB Basic讀寫

異地復(fù)制站點leizhangsqlsvrbj.database.chinacloudapi.cn China North MyDB Basic只讀

第一部分，我們在China East主站點：leizhangsqlsvrsh.database.chinacloudapi.cn，創(chuàng)建只讀用戶。

1.按照下面的信息登錄：

Server Name:leizhangsqlsvrsh.database.chinacloudapi.cn

UserName:leizhang

password：略

注意，使用SQL Server Management Studio(SSMS)，選擇Options。

在Connection Properties，我們選擇default。

2.在Master庫下，右鍵New Query。如下圖：

在Master庫下，我們點擊執(zhí)行下面的T-SQL語句：

創(chuàng)建新的Login readonlylogin并設(shè)置密碼。

CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a';

執(zhí)行成功后，就可以在Logins里面，查看到新的readonlylogin。如下圖：

3.如果我們需要在只讀站點China North，創(chuàng)建只讀用戶，請在Master庫下執(zhí)行以下的語句：

否則請忽略步驟3.

SELECT[name],[sid]FROM[sys].[sql_logins]WHERE[type_desc]='SQL_Login'

SELECT[name],[sid]FROM[sys].[database_principals]WHERE[type_desc]='SQL_USER'

執(zhí)行結(jié)果，如下：

我們可以看到，readonlylogin賬戶的sid如上圖的截圖。這個sid我們在只讀站點leizhangsqlsvrbj，創(chuàng)建只讀賬戶會用到。

4.在MyDB庫下，右鍵New Query。如下圖：

在MyDB庫下，我們點擊執(zhí)行下面的T-SQL語句：

CREATE USER readonlyuser FROM LOGIN readonlylogin;

EXEC sp_addrolemember'db_datareader','readonlyuser';

執(zhí)行成功后，我們可以看到，在主站點:leizhangsqlsvrsh的數(shù)據(jù)庫MyDB下，有新的用戶reareadonlyuser。如下圖：

5.然后新開啟一個SSMS，以readonlylogin只讀賬戶登錄：

(1)Server Name:leizhangsqlsvrsh.database.chinacloudapi.cn

(2)User Name:readonlylogin

(3)Password:1231!#ASDF!a

注意Connection Properties，我們要選擇MyDB。如下圖：

6.以readonlylogin只讀賬戶登錄后，我們執(zhí)行插入操作，會發(fā)現(xiàn)插入失敗。如下圖：

第二部分，我們在China North只讀站點：leizhangsqlsvrbj.database.chinacloudapi.cn，創(chuàng)建只讀用戶。

1.我們以sysadmin賬戶，登錄China North只讀站點：

(1)Server Name:leizhangsqlsvrbj.database.chinacloudapi.cn

(2)User Name:leizhang

(3)Password:略

注意：在Connection Properties，我們選擇default。

2.登錄后，我們發(fā)現(xiàn)在只讀站點：leizhangsqlsvrsh.database.chinacloudapi.cn的數(shù)據(jù)庫MyDB下，已經(jīng)把主站點的只讀用戶同步過來。如下圖：

但是上圖的藍色區(qū)域里，logins只能leizhang這個sysadmin組的用戶。并沒有readonlylogin信息

3.我們在只讀站點的Master庫里，執(zhí)行下面的T-SQL語句

--注意SID=后面沒有單引號或者雙引號

--請把第一部分的，步驟3中的sid值復(fù)制過來

CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a',SID=0x01060000000000640000000000000000EB29A4EE8E1A75459AAC0219BC67BB33

4.執(zhí)行成功后，我們重新打開SQL Server Management Studio(SSMS)。登錄只讀站點eizhangsqlsvrbj，信息如下：

(1)Server Name:leizhangsqlsvrbj.database.chinacloudapi.cn

(2)User Name:readonlylogin

(3)Password:1231!#ASDF!a

注意：在Connection Properties的Database選擇MyDB