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

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

請(qǐ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ù)中心，作為主站點(diǎn)。創(chuàng)建1個(gè)Database為MyDB。

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

注意：我們?cè)谙聢D中創(chuàng)建的服務(wù)器管理員賬戶leizhang，是屬于sysadmin。

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

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

我們預(yù)先創(chuàng)建在MyDB數(shù)據(jù)庫(kù)里，創(chuàng)建一個(gè)數(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)

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

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

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

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

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

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

3.站點(diǎn)信息如下：

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

主站點(diǎn)leizhangsqlsvrsh.database.chinacloudapi.cn China East MyDB Basic讀寫(xiě)

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

第一部分，我們?cè)贑hina East主站點(diǎn)：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庫(kù)下，右鍵New Query。如下圖：

在Master庫(kù)下，我們點(diǎn)擊執(zhí)行下面的T-SQL語(yǔ)句：

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

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

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

3.如果我們需要在只讀站點(diǎn)China North，創(chuàng)建只讀用戶，請(qǐng)?jiān)贛aster庫(kù)下執(zhí)行以下的語(yǔ)句：

否則請(qǐng)忽略步驟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如上圖的截圖。這個(gè)sid我們?cè)谥蛔x站點(diǎn)leizhangsqlsvrbj，創(chuàng)建只讀賬戶會(huì)用到。

4.在MyDB庫(kù)下，右鍵New Query。如下圖：

在MyDB庫(kù)下，我們點(diǎn)擊執(zhí)行下面的T-SQL語(yǔ)句：

CREATE USER readonlyuser FROM LOGIN readonlylogin;

EXEC sp_addrolemember'db_datareader','readonlyuser';

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

5.然后新開(kāi)啟一個(gè)SSMS，以readonlylogin只讀賬戶登錄：

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

(2)User Name:readonlylogin

(3)Password:1231!#ASDF!a

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

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

第二部分，我們?cè)贑hina North只讀站點(diǎn)：leizhangsqlsvrbj.database.chinacloudapi.cn，創(chuàng)建只讀用戶。

1.我們以sysadmin賬戶，登錄China North只讀站點(diǎn)：

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

(2)User Name:leizhang

(3)Password:略

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

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

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

3.我們?cè)谥蛔x站點(diǎn)的Master庫(kù)里，執(zhí)行下面的T-SQL語(yǔ)句

--注意SID=后面沒(méi)有單引號(hào)或者雙引號(hào)

--請(qǐng)把第一部分的，步驟3中的sid值復(fù)制過(guò)來(lái)

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

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

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

(2)User Name:readonlylogin

(3)Password:1231!#ASDF!a

注意：在Connection Properties的Database選擇MyDB