SQL Server 安全篇——SQL Server 安全模型(2)——实例级别安全性

2018-01-24 10:32:30来源:http://blog.csdn.net/dba_huangzj/article/details/79033859作者:黄钊吉的博客人点击

分享

接上文 http://blog.csdn.net/dba_huangzj/article/details/79030680 实例级别安全性包含创建和管理登录名、凭证(credentials)和服务器角色。相对的安全主体在实例级别包含数据库、端点和AlwaysON Availability Groups。本文将讨论登录名、服务器角色和凭证。


登录名:

从SQL Server 2012开始,在包含数据库中,可以让用户直接连到数据库,而不需要在实例层面验证。SQL Server支持两种实例级别验证模式:


Windows 身份验证:只勾选这个时,用户只能使用Windows本地账号或者域账号去连接SQL Server。SQL Server的账号与这个Windows账号关联,Windows主体的SID(security identifier)会存储在master库中。


混合身份验证:也称SQL Server和Windows 身份验证模式,见下图,在这种模式下,Windows身份验证依旧生效。同时可以创建SQL 登录名,这种登录名具有名字、密码和SID,并存储在master库中。我们最常用的莫过于sa。使用这种方式,用户可以不具有任何Windows权限,即可登录到SQL Server。



混合身份验证比Windows身份验证略微不安全,因为可以直接绕过Windows域的保护,只要直到账号密码即可直接登录,同时在数据库审计层面也不方便(假设所有人都用sa登录,出了问题很难查找谁做的)。所以最好使用Windows身份验证。在一些情况下,可以考虑使用混合身份验证:


遗留应用系统中需要。
从域外访问,如从Linux服务器访问。但是在SQL On Linux版本中,这个限制则不是非常必要。
安全审计要求。
创建登录名:
Windows身份验证:

在使用T-SQL命令创建登录名时,如果创建Windows身份验证,则语法很直接,因为不需要输入密码。必要重要的WITH选项有两个:


DEFAULT_DATABASE:代表这个登录名登录成功后的默认打开的数据库。个人习惯一般是DBA为tempdb,开发人员直接指向开发库,默认是master,过去本人经常写错代码在master库,如果是批量代码,删起来就很恶心。
DEFAULT_LANGUAGE:如果不指定登录名的默认语言,那么会使用实例上的默认语言,语言看似不重要,但是在特别是日期格式的使用过程有非常严重的影响,并且不容易发现。作为良好的管理,应该制定统一的规范

Windows身份验证的登录名创建案例:


创建一个登录名,使用域名/账号名格式:cartersecuresafe/Pete。默认数据库为master,默认语言为英式英语。


USE master
GO
CREATE LOGIN [cartersecuresafe/Pete]
FROM WINDOWS
WITH DEFAULT_DATABASE=master, DEFAULT_LANGUAGE=British ;
GO
SQL Server身份验证: 如果创建SQL Server身份验证,则WITH选项会更多:

PASSWORD:指定原始密码,以明文显示。
PASSWORD HASHED :指定登录所使用的初始密码的哈希表示形式。
SID:指定登录名的SID。
DEFAULT_DATABASE/DEFAULT_LANGUAGE :与Windows身份验证一样。
CHECK_POLICY:指定登录的密码必须符合组策略或本地安全策略中强制执行的 Windows 用户的相同要求 (如长度和复杂性)。
CHECK_EXPIRATION:指定登录的密码将按照组策略或本地安全策略强制执行的 Windows 用户密码 过期 策略的规定过期。仅在指定了 CHECK_POLICY,时这个选项才有效。
MUST_CHANGE:指定用户在首次登录时是否必须修改密码。

例子如下:


由于使用了CHECK_POLICY=ON,所以密码复杂度要足够强。


USE master
GO
CREATE LOGIN Danni
WITH PASSWORD='C0mplexPa$$w0rd',
DEFAULT_DATABASE=master, CHECK_EXPIRATION=OFF, CHECK_POLICY=ON ;
GO
实例间登录名迁移:

即把一个实例的登录名迁移到另外一个实例,这个在过去意义不大,但是随着多种高可用技术的产生及新版本的发布,把现有实例的内容迁移到新的实例变得越来越频繁,但是如果登录名很多,迁移过程就很痛苦,并且会出现很多遗漏。


常见的需要迁移的场景有DR(灾难恢复)方案中,需要预先创建账号,在故障切换时,可以减少停机时间。对于Windows身份验证,只要都在同一个域,问题不大,虽然SID存储在master库,但是安全主体本身,是由Windows自己管理的。你可以预先后者在需要时,在新实例上直接创建,不需要任何修改。


但是使用SQL Server身份验证,即使相同的账号密码,SID都是不一样的,而SQL Server在权限检查时依赖的是SID,所以即使账号密码相同,对SQL Server来说还是不同的登录名。此时会出现“孤儿账号/孤立账号”的现象。对此,可以使用ALTER USER 命令并使用WITH LOGIN选项(替换以前的sp_change_users_logins存储过程)来修复。


比如:



USE AdventureWorks2016CTP3
GO
ALTER USER Danni WITH LOGIN = Danni ;
但是毕竟是补救,更好的办法是预先创建。在创建时使用WITH SID选项,下面一个SQLCMD脚本可以用来迁移Danni账号,注意需要使用SQLCMD模式:把ProdInstance1中的Danni账号迁移到DRInstance1中。

:CONNECT CARTERSECURESAFE/ProdInstance1
DECLARE @SQL NVARCHAR(MAX) ;
SET @SQL = (SELECT 'CREATE LOGIN '
+ name
+ ' WITH PASSWORD = ''C0mplexPassw0rd'', SID = 0x'
+ CONVERT(NVARCHAR(64), SID, 2)
FROM sys.sql_logins
WHERE Name = 'Danni') ;
:CONNECT CARTERSECURESAFE/DRInstance1
EXEC(@SQL) ;
不过这个方式会使得密码以明文形式存储,略微不安全,而且如果DBA不知道某个账号的密码,也无法进行。为此,可以使用sys.sql_logins视图中的password_hash列来脚本化登录名的密码。不过这里使用HASHBYTES()()函数来产生登陆名的hash值。如下脚本,这个脚本产生DDL命令用于把所有启用的SQL 登录名脚本化并对密码哈希化:

DECLARE @password NVARCHAR(MAX) = 'C0mplexPa$$w0rd' ;
DECLARE @salt VARBINARY(4) = CRYPT_GEN_RANDOM(4) ;
DECLARE @hash VARBINARY(1000) ;
DECLARE @SQL NVARCHAR(MAX) ;
SET @hash = (SELECT 0x0200 + @salt + HASHBYTES('SHA2_512', CAST(@password
AS VARBINARY(MAX)) + @salt)) ;
SET @SQL = (SELECT 'CREATE LOGIN '
+ Name
+ ' WITH PASSWORD = '
+ CONVERT(NVARCHAR(1000), @hash, 1)
+ ' HASHED, SID = 0x'
+ CONVERT(NVARCHAR(64), SID, 2)
FROM sys.sql_logins
WHERE is_disabled = 0
FOR XML PATH('')) ;
SELECT @SQL ;
这里使用最高级别算法SHA2_512来加密,代码中的CRYPT_GEN_RANDOM()函数,使用Windows CAPI来产生一个加密随机数,本机直接执行的结果如下:




除此之外,还可以使用SSIS来迁移,具体见
迁移 SQL Server 登录名使用数据迁移助手 及
How to transfer logins and passwords between instances of SQL Server 和
Transfer SQL Server Jobs Task and Transfer SQL Server Logins Task in SSIS
服务器角色:

SQL Server提供了一系列内建服务器角色。这些角色满足常用需求,可以直接对登录名授予对应角色,则自动继承实例层面的对应权限。这些内建角色也称为固定服务器角色(fixed server roles)。不可修改和删除,只能对其添加或移除登录名。




固定服务器角色


角色
描述


sysadmin
实例层面最高权限。


bulkadmin
允许成员执行BULK INSERT命令,常用于执行ETL过程的服务账号。


dbcreator
可以在实例中创建数据库,一旦创建辛苦,登录名自动成为数据库的拥有者(Onwer),并且可以对这个库进行任何操作。但不代表可以对其他现有非该登录创建的库有权限。


diskadmin
允许成员可以在SQL Server中管理备份设备。


processadmin
通过T-SQL或者SSMS来停止实例。也可以kill掉运行中的进程。


public
所有登录都默认添加到public角色。不可去除。


securityadmin
成员可以管理实例级别的登录名,比如可以把登录名添加到除sysadmin外的服务器角色,或者对实例级别资源如端点授权。但是不能对数据库层面的用户进行授权。


serveradmin
包含了diskadmin和processadmin角色,外加启动和停止实例,但是成员如果使用SHUTDOWN T-SQL命令,也能关闭服务(注意关闭跟停止,如果使用NOWAIT选项,则关闭时不需要运行CHECKPOINT),同时成员还可以修改端点和查看所有实例元数据。


setupadmin
其成员可以创建和管理linked server(链接服务器)


从SQL Server 2012开始,还可以进行自定义服务器角色,用于补充固定服务器角色的权限。比如可以创建一个叫AvailabilityRole的服务器角色用于部署AlwaysON 可用性组,并且授权这个组具有:


修改任意Availability Group
修改任意端点
创建Availability Group
创建端点

这些对于AlwaysOn来说必须的组件。


也可以创建一个专用的初级DBA服务器角色,去除部分重要且不容易掌握的功能的控制权限。下面是演示例子,注意这里使用了GRANT命令,T-SQL中,有三类DCL(控制定义语言):GRANT、DENY、REVOKE。


GRANT:允许安全性主体访问安全对象。可以通过WITH选项使其授权的主体具有授权给其他主体的权限。
DENY:与GRANT相对,拒绝访问指定安全对象。并且总会覆盖GRANT规则。即优先级比GRANT高。
REVOKE:GRANT可以理解新增权限,那么REVOKE则可以理解为移除权限。但是如果登录名已经通过服务器角色授权,则直接取消登录本身的权限将不会起效。需要对登录名或服务器角色使用DENY,或者把登录名移除出服务器角色。

CREATE SERVER ROLE AVAILABILITYROLE AUTHORIZATION [CarterSecureSafe/
SQLAdmin] ;
GO
GRANT ALTER ANY AVAILABILITYROLE GROUP TO AVAILABILITYROLE ;
GRANT ALTER ANY ENDPOINT TO AVAILABILITYROLE ;
GRANT CREATE AVAILABILITYROLE GROUP TO AVAILABILITYROLE ;
GRANT CREATE ENDPOINT TO AVAILABILITYROLE ;
GO

然后添加Danni这个登录名到AvailabilityRole角色中:


ALTER SERVER ROLE AvailabilityRole ADD MEMBER Danni ;
GO
凭证:

凭据用于提供访问 SQL Server 实例外部的资源的能力。SQL登录名可以使用凭证访问操作系统层面的资源,SQL Server Agent 代理账号可以使用品种去访问SQL Server Agent子系统如PowerShell或者CmdExec。另外在云计算盛行的今天,凭证可以用于把备份放到Azure中,实现更安全更廉价甚至更具扩展性的架构。


当用于访问操作系统层面资源时,凭证通常记录Windows安全性主体的ID和密码。如果用于备份到Azure,那么凭证会记录Azure存储账号(Azure storage account)的名字和私有密钥。比如下面演示:


CREATE CREDENTIAL URLBackupCredential
WITH IDENTITY = 'CarterSecureSafeStorageAcc'
,SECRET ='/Ydfg/SGdTgJNpVFl992sBv7Bp1gyL61I33wNrTMHGBDdtVcx97F5f6SC5uDi59FeY2/IjxyqsuLU2xrkrNAGT==' ; 创建一个凭证用于备份到Azure中,使用的存储账号叫“CarterSecureSafeStorageAcc”。

下一文介绍:SQL Server 安全篇——SQL Server 安全模型(3)——数据库级别安全性


相关文章

    无相关信息

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台