在之前客戶咨詢案例中，很多客戶應用程序連接sql server直接用的就是sa帳號。如果對數據庫管理稍微嚴格一點的話，就不應該給應用程序這種權限，通常應用程序只需要進行增刪改查，而很少有ddl操作，因此配置帳號時應該遵循“最小權限分配”的原則僅僅賦予所需的權限。
??? 對于應用程序來說，最小的權限通常就是就是給予讀權限，寫權限和執行存儲過程權限。由于為了防止sql注入導致的數據庫信息泄漏，則還需要考慮拒絕帳號的查看定義權限，但值得注意的是，如果拒絕了查看定義的權限，則Bulk Insert會失敗。完整的權限定義如下：
ALTER?ROLE?[db_datareader]?ADD?MEMBER?用戶名 ALTER?ROLE?[db_datawriter]?ADD?MEMBER?用戶名 grant?execute?to?用戶名 deny?view?definition?to?用戶名
?? 在SQL Server中，實例級別的是登錄名，而數據庫級別的才是用戶名，登錄名在創建完成后可映射到具體的庫。因此我寫了一個完整的腳本，同時創建登錄名，用戶，以及賦予對應的權限，腳本如下：
--創建用戶的存儲過程，? --示例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為只讀權限，rw為讀寫權限，rwv為讀寫加View?Definition權限')? IF?EXISTS?(?SELECT?name? FROM?sys.syslogins? WHERE?name?=?@loginName?)? BEGIN? PRINT?N'登錄名已存在，跳過創建登錄名步驟'? 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'登錄名已創建，密碼為:'+@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'已經使用SID創建登錄名:'+@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)? --切換數據庫上下文? SET?@DynamicSQL?=?N'Use?['?+?@DatabaseName?+?'];?'?+?'IF?EXISTS(SELECT?name?FROM?sys.database_principals?WHERE?name='''+@loginName+''')?Begin?Print(''用戶名已存在，跳過創建用戶名的步驟'')?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)
?? 該存儲過程用于創建應用程序連接SQL Server所需的登錄名，用戶以及對應權限，當用戶或登錄名存在時還會跳過該步驟，使用該存儲過程的示例如：?
EXEC?sp_CreateUser?'UserName','rw','DatabaseNam' EXEC?sp_CreateUser?'tesefx','r','Test','0xE39CA97EBE03BB4CA5FF78E50374EEBB'
??? 上述執行的第一行是創建一個標準的帳號，賬戶名UserName，賦予對DatabaseNam的庫的讀寫權限，并返回生成的GUID密碼。第二個存儲過程是使用第四個參數sid創建登錄名，由于在AlwaysOn或鏡像的環境中，兩端登錄名需要有相同的SID，因此提供了在該情況下使用SID創建登錄名的辦法。
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
