在之前客戶咨詢案例中，很多客戶應(yīng)用程序連接sql server直接用的就是sa帳號。如果對數(shù)據(jù)庫管理稍微嚴格一點的話，就不應(yīng)該給應(yīng)用程序這種權(quán)限，通常應(yīng)用程序只需要進行增刪改查，而很少有ddl操作，因此配置帳號時應(yīng)該遵循“最小權(quán)限分配”的原則僅僅賦予所需的權(quán)限。
??? 對于應(yīng)用程序來說，最小的權(quán)限通常就是就是給予讀權(quán)限，寫權(quán)限和執(zhí)行存儲過程權(quán)限。由于為了防止sql注入導(dǎo)致的數(shù)據(jù)庫信息泄漏，則還需要考慮拒絕帳號的查看定義權(quán)限，但值得注意的是，如果拒絕了查看定義的權(quán)限，則Bulk Insert會失敗。完整的權(quán)限定義如下：
ALTER?ROLE?[db_datareader]?ADD?MEMBER?用戶名 ALTER?ROLE?[db_datawriter]?ADD?MEMBER?用戶名 grant?execute?to?用戶名 deny?view?definition?to?用戶名
?? 在SQL Server中，實例級別的是登錄名，而數(shù)據(jù)庫級別的才是用戶名，登錄名在創(chuàng)建完成后可映射到具體的庫。因此我寫了一個完整的腳本，同時創(chuàng)建登錄名，用戶，以及賦予對應(yīng)的權(quán)限，腳本如下：
--創(chuàng)建用戶的存儲過程，? --示例EXEC?sp_CreateUser?'UserName','rw','DatabaseName'? --EXEC?sp_CreateUser?'tesefx','r','Test','0xE39CA97EBE03BB4CA5FF78E50374EEBB'? CREATE?PROC?sp_CreateUser? @loginName?VARCHAR(50)?,? @IsWrite?VarCHAR(3)?,? @DatabaseName?VARCHAR(50),? @Sid?VARCHAR(100)?='1'? AS? PRINT('示例：EXEC?sp_CreateUser?''UserName'',''rw'',''DatabaseName''')? PRINT('示例：EXEC?sp_CreateUser?''UserName'',''rwv'',''DatabaseName'',''0xE39CA97EBE03BB4CA5FF78E50374EEBB''')? PRINT('r為只讀權(quán)限，rw為讀寫權(quán)限，rwv為讀寫加View?Definition權(quán)限')? IF?EXISTS?(?SELECT?name? FROM?sys.syslogins? WHERE?name?=?@loginName?)? BEGIN? PRINT?N'登錄名已存在，跳過創(chuàng)建登錄名步驟'? END? ELSE? BEGIN? DECLARE?@CreateLogin?NVARCHAR(1000)? DECLARE?@pwd?VARCHAR(50)? PRINT?@Sid? SET?@pwd=NEWID()? IF(@sid='1')? BEGIN? SET?@CreateLogin?=?'CREATE?LOGIN?['?+?@loginName?+?']?WITH?PASSWORD=N'''? +?@Pwd? +?''',?DEFAULT_DATABASE=[master],?CHECK_EXPIRATION=OFF,?CHECK_POLICY=OFF;'? PRINT?N'登錄名已創(chuàng)建，密碼為:'+@pwd? END? ELSE? BEGIN? SET?@CreateLogin?=?'CREATE?LOGIN?['?+?@loginName?+?']?WITH?PASSWORD=N'''? +?@Pwd? +?''',?DEFAULT_DATABASE=[master],?CHECK_EXPIRATION=OFF,?CHECK_POLICY=OFF,sid='+@Sid+';'? PRINT?N'已經(jīng)使用SID創(chuàng)建登錄名:'+@loginName? END? EXEC?(@CreateLogin)? --DECLARE?@sidtemp?NVARCHAR(50)? --SELECT?@sidtemp=sid?FROM?sys.server_principals?WHERE?name=@loginName? --PRINT(N'登錄名為:'+@loginName+N'?SID為:?0x'+CONVERT(VARCHAR(50),?@sidtemp,?2)?)? END? DECLARE?@DynamicSQL?NVARCHAR(1000)? --切換數(shù)據(jù)庫上下文? SET?@DynamicSQL?=?N'Use?['?+?@DatabaseName?+?'];?'?+?'IF?EXISTS(SELECT?name?FROM?sys.database_principals?WHERE?name='''+@loginName+''')?Begin?Print(''用戶名已存在，跳過創(chuàng)建用戶名的步驟'')?end?else?begin?CREATE?USER?['? +?@loginName?+?']?FOR?LOGIN?'?+?@loginName?+?'?end;IF?('''? +?@IsWrite? +?'''=''rw''?or?'''? +?@IsWrite? +?'''=''rwv'')?BEGIN?ALTER?ROLE?[db_datareader]?ADD?MEMBER?'?+?@loginName? +?';ALTER?ROLE?[db_datawriter]?ADD?MEMBER?'?+?@loginName? +?';?END?ELSE?BEGIN?ALTER?ROLE?[db_datareader]?ADD?MEMBER?'? +?@loginName?+?';? ALTER?ROLE?db_datawriter?DROP?MEMBER?'? +?@loginName?+?'? ;End;grant?execute?to?'?+?@loginName?+?';? if('''+@IsWrite+'''''rwv'')?begin?deny?view?definition?to?'?+?@loginName?+?';?end?else?begin?grant?view?definition?to?'?+?@loginName?+?';?end'? EXEC?(@DynamicSQL)
?? 該存儲過程用于創(chuàng)建應(yīng)用程序連接SQL Server所需的登錄名，用戶以及對應(yīng)權(quán)限，當用戶或登錄名存在時還會跳過該步驟，使用該存儲過程的示例如：?
EXEC?sp_CreateUser?'UserName','rw','DatabaseNam' EXEC?sp_CreateUser?'tesefx','r','Test','0xE39CA97EBE03BB4CA5FF78E50374EEBB'
??? 上述執(zhí)行的第一行是創(chuàng)建一個標準的帳號，賬戶名UserName，賦予對DatabaseNam的庫的讀寫權(quán)限，并返回生成的GUID密碼。第二個存儲過程是使用第四個參數(shù)sid創(chuàng)建登錄名，由于在AlwaysOn或鏡像的環(huán)境中，兩端登錄名需要有相同的SID，因此提供了在該情況下使用SID創(chuàng)建登錄名的辦法。
