|
SQL Server 数据库管理中，账户安全是数据库安全的一道重要屏障。除对密码复杂度有严格要求外，周期性修改账户密码，是保证数据库安全的一个重要举措。如下给出修改登陆账户/包含用户密码的脚本： --更换登陆帐户密码 USE master GO ALTER LOGIN Jack WITH PASSWORD='I8@^mV7(Tyz0Jov',CHECK_POLICY=ON,CHECK_EXPIRATION=OFF --修改包含用户密码 USE test GO ALTER USER Jack WITH PASSWORD='I8@^mV7(Tyz0Jov' 但对于生产数据库，这样直接修改原账户的密码是存在问题的。因为我们不能做到数据库和应用同时修改密码，这样是会影响业务的正常运行，而导致不必要的损失。而较为合理的做法是创建新账户，赋予原账户相同的权限，替换原账户。下面将使用《SQL Server 登陆账户权限克隆——sp_DBA_LoginClone 升级版》、《克隆SQL Server 用户及权限》两篇文章中叙述的SQL Server登陆账户、SQL Server 数据库用户克隆的方式给出新账户的创建及权限赋予脚本。 为更方便的给创建新账户提供随机密码，这里首先给出生成随机密码的脚本，如下： --创建字符集表，并插入生成密码的字符 USE master CREATE TABLE AsciiCode(id INT IDENTITY(1,1),characters CHAR(1)) DECLARE @i INT=33 WHILE @i<127 BEGIN IF @i NOT IN(34，39) --密码中不能有单引号和双引号 INSERT INTO AsciiCode(characters) SELECT CHAR(@i) SET @i=@i+1 END GO --创建生成随机密码的存储过程 USE master GO CREATE PROC pwdGenerate( @num smallint --密码长度 ,@pwd VARCHAR(50) OUTPUT ) AS BEGIN DECLARE @i SMALLINT=1 SET @pwd='' WHILE @i<=@num BEGIN SELECT TOP 1 @pwd=@pwd+characters FROM master.dbo.AsciiCode --WHERE id=RAND()*94 ORDER BY NEWID() SET @i=@i+1 END --RETURN @pwd END GO 替换账户信息 接下来确定需要替换的账户、新账户，并使用上面的脚本生成密码，如下： USE master GO --创建新旧帐户密码对应表 CREATE TABLE login_create ( id INT IDENTITY(1,1) ,newLoginName sysname NULL ,loginName sysname NULL ,pwd VARCHAR(50) ,dbName sysname NULL ,userName sysname NULL ,newUserName sysname NULL ) ----插入需要替换的账户(一般针对生产环境使用的帐户、用户)、新帐户、密码(密码可以由存储过程pwdGenerate生成) INSERT INTO login_create(loginName) SELECT name FROM sys.sql_logins WHERE name NOT IN ( 'NT SERVICE\MSSQLSERVER', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\SQLSERVERAGENT','##MS_PolicyTsqlExecutionLogin##','##MS_PolicyEventProcessingLogin##' ) AND is_disabled=0 --select * from login_create --TRUNCATE TABLE login_create UPDATE login_create SET newLoginName=loginName+'_V1' --生成新帐户的密码 DECLARE @id INT=1,@maxNum INT DECLARE @pwd VARCHAR(50) SELECT @maxNum=MAX(id) from login_create WHILE @id<=@maxNum BEGIN EXEC pwdGenerate 15,@pwd OUTPUT UPDATE login_create SET pwd=@pwd WHERE id=@id SET @id=@id+1 END GO 注意：login_create中，对于登陆账户，DBName、userName、newUserName字段为NULL；包含用户newLoginName、loginName为空。 克隆账户用户 使用前文中的过程sp_DBA_LoginPermissionsClone、sp_DBA_userPermisionsClone，生成新账户的创建脚本，及权限赋予脚本。注意执行下面脚本时需要先在master库下创建sp_DBA_LoginPermissionsClone、sp_DBA_userPermisionsClone两过程，并标记为系统过程。 USE master GO --生成新帐户的创建脚本，并生成克隆原帐户权限的赋予脚本 SET nocount ON DECLARE @newLoginName SYSNAME ,@loginName SYSNAME ,@pwd CHAR(15) ,@userName sysname ,@dbName sysname ,@newUserName sysname ,@sql NVARCHAR(max) DECLARE logins CURSOR FOR SELECT newLoginName,loginName,pwd,dbName,userName,newUserName FROM login_create OPEN logins FETCH NEXT FROM logins INTO @newLoginName, @loginName, @pwd,@dbName,@userName,@newUserName --循环登陆账户 WHILE @@FETCH_STATUS = 0 BEGIN if @loginName is not null begin EXEC dbo.sp_DBA_LoginPermissionsClone @loginName,@newLoginName,@pwd,@sql OUTPUT PRINT @sql set @userName=@loginName end --循环数据库，生成创建数据用户脚本及赋予用户权限脚本 DECLARE @databaseName SYSNAME DECLARE databaseNames CURSOR FOR SELECT name FROM sys.sysdatabases OPEN databaseNames FETCH NEXT FROM databaseNames INTO @databaseName WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = N'select @i=1 from ' + QUOTENAME(@databaseName,'[') + N'.sys.sysusers where name=''' + @userName + '''' DECLARE @j INT= NULL EXEC sp_executesql @sql, N'@i int output', @i = @j OUTPUT IF ( @j IS NULL ) --数据库不存在用户，回到开始进行下次循环 BEGIN FETCH NEXT FROM databaseNames INTO @databaseName CONTINUE END IF @userName IS NULL SET @userName=@loginName IF @newUserName IS NULL SET @newUserName=@newLoginName IF ( @j = 1 ) BEGIN if (@dbName is not null and @dbName=@databaseName) or @dbName is null EXEC dbo.sp_DBA_userPermisionsClone @databaseName,@userName,@newUserName,@newLoginName,@pwd END FETCH NEXT FROM databaseNames INTO @databaseName END CLOSE databaseNames DEALLOCATE databaseNames FETCH NEXT FROM logins INTO @newLoginName, @loginName, @pwd,@dbName,@userName,@newUserName END CLOSE logins DEALLOCATE logins 注意：对于包含用户，如果存在涉及到跨库的包含用户，需要单独创建，具体参加《SQL Server 部分包含数据库》。 生成完脚本后，及时备份创建脚本或者执行生成的脚本。将新、旧账户对应关系及密码导出到本机加密文件中，更新密码字段为NULL。同时检查附近时点是否有对该库进行备份的作业，确保新账户密码没有备份到数据库备份文件中。 UPDATE login_create SET pwd=NULL 监控账户替换情况 新账户创建完成后，需要将旧、新账户及密码发相关人员替换（开发或者IT），开启就账户连接监控。我们可以执行使用查询连接的脚本，查看哪些旧账户还没有被替换掉： --查询指定连接数据库账户的IP SELECT distinct client_net_address,loginame,hostname,program_name FROM sys.dm_exec_connections conn join master..sysprocesses procs on conn.session_id=procs.spid where loginame IN(SELECT loginName from login_create) 但对于哪些非频繁连接的账户，使用上面的查询并不一定能捕获到，最好的方式是使用触发器来监控： --创建登陆信息日志表 CREATE TABLE [dbo].[Login_Monitor]( [Spid] [int] NULL, [Posttime] [datetime] NULL, [Servername] [varchar](30) NULL, [Hostname] [varchar](30) NULL, [Loginame] [varchar](30) NULL, [Logintype] [varchar](20) NULL, [ClientHost] [varchar](20) NULL, [Stat] [int] NULL ) use master GO --================================== -- 服务器登监控账户信息 --================================== CREATE TRIGGER [LoginMonitor] ON all server WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN DECLARE @Data XML DECLARE @PostTime NVARCHAR(24) DECLARE @Spid NVARCHAR(6) DECLARE @LoginName NVARCHAR(100) DECLARE @HostName NVARCHAR(100) DECLARE @ServerName NVARCHAR(20) DECLARE @LoginType NVARCHAR(20) DECLARE @ClientHost NVARCHAR(20) DECLARE @IP NVARCHAR(20) DECLARE @STAT INT = 0 DECLARE @IPint BIGINT=0 SET @Data = EVENTDATA() SET @HostName = HOST_NAME() SET @PostTime = @Data.value('(/EVENT_INSTANCE/PostTime)[1]', 'NVARCHAR(24)') SET @Spid = @Data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(6)') SET @LoginName = @Data.value('(/EVENT_INSTANCE/LoginName)[1]','NVARCHAR(100)') SET @ServerName = @Data.value('(/EVENT_INSTANCE/ServerName)[1]','NVARCHAR(100)') SET @ClientHost = @Data.value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(100)') SET @LoginType = @Data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(100)') SET @IP = @ClientHost IF @ClientHost<>'<local machine>' begin IF @LoginName in( SELECT loginName from login_create) BEGIN INSERT INTO [Login_Monitor] VALUES(@SPID, @posttime,@ServerName,@hostname, @loginname,@LoginType,@IP,@STAT) END END END 经过一定周期的监控，确定所有账户已经替换后，接下来可以禁用原账户了。 禁用原账户 --生成禁用登陆帐户脚本 SELECT 'ALTER LOGIN '+QUOTENAME(loginName,']') +'DISABLE' FROM login_create 生成脚本后直接执行即可。 清理禁用的旧账户 通过观察，没有账户有报错后，就可以清理旧的停用账户了。因为删除登陆账户时，没有办法直接关联删除其对应在各数据库中的用户，下面使用循环将停用旧登陆账户及对应各数据库中的用户删除，脚本如下： --删除停用的帐户，及各数据库中对应的用户 DECLARE @user AS VARCHAR(200) DECLARE @databaseName AS VARCHAR(200) DECLARE @sql AS VARCHAR(800) --循环删除停用的用户 DECLARE curUsers CURSOR FOR SELECT QUOTENAME(loginName, ']') FROM login_create OPEN curUsers FETCH NEXT FROM curUsers INTO @user WHILE @@FETCH_STATUS = 0 BEGIN DECLARE databaseNameCur CURSOR FOR SELECT name FROM sys.databases WHERE state = 0 OPEN databaseNameCur FETCH NEXT FROM databaseNameCur INTO @databaseName WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'use ' + @databaseName + ';' SET @sql =@sql + 'drop user ' + @user BEGIN TRY EXEC(@sql) PRINT @sql END TRY BEGIN CATCH PRINT '数据库' + @databaseName + '中不存在用户' + @user END CATCH FETCH NEXT FROM databaseNameCur INTO @databaseName END CLOSE databaseNameCur DEALLOCATE databaseNameCur USE master SET @sql = 'drop login ' + @user PRINT @sql EXEC(@sql) FETCH NEXT FROM curUsers INTO @user END CLOSE curUsers DEALLOCATE curUsers (责任编辑：IT) |