SQL Server의 애플리케이션에서 사용하는 계정 추가

이전 고객 상담 사례에서는 많은 고객 애플리케이션이 SA 계정을 직접 사용하여 SQL Server에 연결했습니다. 데이터베이스 관리가 조금 더 엄격한 경우 애플리케이션에 이러한 권한을 부여해서는 안 됩니다. 일반적으로 애플리케이션에서는 추가, 삭제, 수정 및 쿼리만 수행하면 되고 DDL 작업은 거의 수행하지 않아도 됩니다. 따라서 "최소 권한 할당"의 원칙이 적용됩니다. "를 따라 계정을 구성해야 합니다. 필요한 권한을 부여하세요.

애플리케이션의 경우 일반적으로 읽기 권한, 쓰기 권한, 저장 프로시저 실행 권한에 대한 최소 권한이 부여됩니다. SQL 인젝션으로 인한 데이터베이스 정보 유출을 방지하기 위해서는 정의 보기 권한을 거부하는 것도 고려해야 합니다. 단, 정의 보기 권한이 거부되면 대량 삽입이 실패하므로 주의할 필요가 있습니다. 전체 권한 정의는 다음과 같습니다.

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(&#39;&#39;&#39;+@IsWrite+&#39;&#39;&#39;<>&#39;&#39;rwv&#39;&#39;) begin deny view definition to &#39; + @loginName + &#39;; end else begin grant view definition to &#39; + @loginName + &#39;; end&#39; 
EXEC (@DynamicSQL)

이 저장 프로시저는 애플리케이션이 연결하는 데 필요한 로그인 이름을 생성하는 데 사용됩니다. 사용자 또는 로그인 이름이 있으면 이 단계를 건너뜁니다. 이 저장 프로시저를 사용하는 예는 다음과 같습니다.

EXEC sp_CreateUser 'UserName','rw','DatabaseNam'
EXEC sp_CreateUser 'tesefx','r','Test','0xE39CA97EBE03BB4CA5FF78E50374EEBB'

위 실행의 첫 번째 줄은 다음과 같습니다. 표준 계정을 생성합니다. UserName이라는 계정은 DatabaseNam 라이브러리에 대한 읽기 및 쓰기 권한을 부여하고 생성된 GUID 암호를 반환합니다. 두 번째 저장 프로시저에서는 네 번째 매개 변수인 sid를 사용하여 로그인 이름을 생성합니다. AlwaysOn 또는 미러링 환경에서는 양쪽 로그인 이름의 SID가 동일해야 하므로 이 경우 SID를 사용하여 로그인 이름을 생성하는 방법을 제공합니다.