SQL数据库权限分配及访问探讨

上周，有位朋友给我提出了这样的需求：区分用户访问数据库的权限。顺便总结了下有如下要求：某个用户查询所有数据库的权限某个用户只有备份数据库的
权限给一个用户只能查看指定数据库的权限给一个用户只有某个表的权限要进行以上任务，首先我们先了解下数据库的权限相关的内容主体“主体”
是可以请求SQLServer资源的实体。与SQLServer授权模型的其他组件一样，主体也可以按层次结构排列。主体的影
响范围取决于主体定义的范围(Windows、服务器或数据库)以及主体是否不可分或是一个集合。例如，Windows登录名就是一个不
可分主体，而Windows组则是一个集合主体。每个主体都具有一个安全标识符(SID)。Windows级别的主体Windows域登
录名Windows本地登录名SQLServer-级的主体SQLServer登录名服务器角色数据库级的主体数据库用户数据库角色
应用程序角色SQLServersa登录名SQLServersa登录名是服务器级的主体。默认情况下，该登录名是在安装实例时创
建的。public数据库角色每个数据库用户都属于public数据库角色。当尚未对某个用户授予或拒绝对安全对象的特定权限时，则该
用户将继承授予该安全对象的public角色的权限。INFORMATION_SCHEMA和sys每个数据库都包含两个实体：INFO
RMATION_SCHEMA和sys，它们都作为用户出现在目录视图中。这两个实体是SQLServer所必需的。它们不是主
体，不能修改或删除它们。基于证书的SQLServer登录名名称由双井号(##)括起来的服务器主体仅供内部系统使用。下列主体
是在安装SQLServer时从证书创建的，不应删除。##MS_SQLResourceSigningCertificate###
#MS_SQLReplicationSigningCertificate####MS_SQLAuthenticatorCertif
icate####MS_AgentSigningCertificate####MS_PolicyEventProcessingLo
gin####MS_PolicySigningCertificate####MS_PolicyTsqlExecutionLogin
##guest用户每个数据库包括一个guest。授予guest用户的权限由对数据库具有访问权限，但在数据库中没有用户帐户的用户继
承。不能删除guest用户，但可通过撤消该用户的CONNECT权限将其禁用。可以通过在master或tempdb以外的任何数据
库中执行REVOKECONNECTFROMGUEST来撤消CONNECT权限。客户端和数据库服务器根据定义，客户端和数据库
服务器是安全主体，可以得到保护。在建立安全的网络连接前，这些实体之间可以互相进行身份验证。SQLServer支持Kerber
os身份验证协议，该协议定义客户端与网络身份验证服务交互的方式。创建数据库用户SQL2016中支持11种用户类型：用户基于登录名在
master这是最常见的用户类型。基于登录名基于的WindowsActiveDirectory帐户的用户CREATEUSE
R[Contoso\Fritz];基于Windows组的登录名的用户。CREATEUSER[Contoso\Sales];
基于使用SQLServer身份验证的登录名的用户。CREATEUSERMary;在数据库进行身份验证的用户建议以帮助使你的
数据库可移植性。始终允许在SQLDatabase。中包含的数据库中只允许存在SQLServer。基于无登录名的Wind
ows用户的用户CREATEUSER[Contoso\Fritz];基于无登录名的Windows组的用户。CREATEUSE
R[Contoso\Sales];中的用户SQLDatabase或SQL数据仓库基于AzureActiveDirec
tory的用户。CREATEUSER[Contoso\Fritz]FROMEXTERNALPROVIDER;拥有密码的包
含数据库用户。(在中不可用SQL数据仓库。)CREATEUSERMaryWITHPASSWORD=''
'';基于Windows主体通过Windows组登录名进行连接的用户基于无登录名但可通过Windows组中的成员身份连接到数据库
引擎的Windows用户的用户CREATEUSER[Contoso\Fritz];基于无登录名但可通过其他Windows组中的
成员身份连接到数据库引擎的Windows组的用户。CREATEUSER[Contoso\Fritz];无法进行身份验证的用户
这些用户无法登录到SQLServer或SQLDatabase。没有登录名的用户。不能登录，但可以被授予权限CREATE
USERCustomAppWITHOUTLOGIN;基于证书的用户。不能登录，但可以被授予权限，也可以对模块进行签名。C
REATEUSERTestProcessFORCERTIFICATECarnationProduction50;基于非对称
密钥的用户。不能登录，但可以被授予权限，也可以对模块进行签名。CREATEUserTestProcessFROMASYM
METRICKEYPacificSales09;下面的图片显示了创建数据库用户需要的选项的含义：创建用户可以使用界面完成：也可
以使用T-SQL来进行创建--创建登录名：Test密码是：''123456''.CREATELOGINTestWITHPAS
SWORD=''123456'';GO上面说完了用户，下面说下数据库的角色和权限服务器级别的权限SQLServer提供服务器级
角色以帮助你管理服务器上的权限。这些角色是可组合其他主体的安全主体。服务器级角色的权限作用域为服务器范围。(“角色”类似于W
indows操作系统中的“组”。)SQLServer提供了九种固定服务器角色。无法更改授予固定服务器角色的权限。从SQ
LServer2012开始，您可以创建用户定义的服务器角色，并将服务器级权限添加到用户定义的服务器角色。你可以将服务器级主体
(SQLServer登录名、Windows帐户和Windows组)添加到服务器级角色。固定服务器角色的每个成员都可以将其
他登录名添加到该同一角色。用户定义的服务器角色的成员则无法将其他服务器主体添加到角色。下表显示了服务器级的固定角色及其权限下表显示
了固定数据库角色及其能够执行的操作。所有数据库中都有这些角色。无法更改分配给固定数据库角色的权限无法更改分配给固定数据库角色的权
限。下图显示了分配给固定数据库角色的权限：SQL2016有一些数据库的特殊权限msdb角色msdb数据库中包含下表显示的特殊用
途的角色。使用RServicesSQLServer(从SQLServervNext开始)安装RServices时，其
他数据库角色可用于管理包下面讲如何实现文章前面说的需求：给某个用户查询所有数据库的权限给某个用户只有备份数据库的权限给一个用户只有
指定数据库的权限给一个用户只有某个表的权限给某个用户查询所有数据库的权限创建一个用户USE[master]GOCREATE
LOGIN[Test1]WITHPASSWORD=N''password@123''使用Test1连接数据库实例可以看到数据库列表
，但是无法访问数据库，赋予test1对FinaceDemo的读取权限USE[FinaceDemo]GOCREATEUSE
R[Test1]FORLOGIN[Test1]ALTERROLE[db_datareader]ADDMEMBER
[Test1]GO这样就可以给test1用户对finacedemo的读取权限但是test1没有写入权限这样就可以单独对tes
t1赋予数据库的读取权限进行查看操作。给某个用户只有备份数据库的权限Test1对于finacedemo无备份权限赋予备份权限AL
TERROLE[db_backupoperator]ADDMEMBER[Test1]给一个用户只有指定数据库的权限我们需
要Test1只能看到FinanceDemo，其他所有数据库都不能看到执行下面脚本USE[master]DenyVIEWa
nyDATABASETOTest1;go运行后的效果Test1连接后看不到任何数据库执行：ALTERAUTHORIZA
TIONONDATABASE::FinanceDemoTOtest1完成后结果：Test1能查看到赋予权限的数据库给一个用户
只有某个表的权限创建测试用户test3USE[master]GOCREATELOGIN[Test3]WITHPASS
WORD=N''password@123''-----赋予test2可以登录testDBUSE[testdb]GOCREAT
EUSER[Test3]FORLOGIN[Test3]GO赋予test3对于t2表的update和select权限g
rantupdateondbo.t2totest3grantselectondbo.t2totest3usetestDB查看test3用户获得的权限execsp_helprotect@username=''test3''可以看到用户test3拥有了t2的select和update权限执行selectfromt2执行插入操作失败。以上介绍了对数据库权限细致的管理，更加详细的控制可以参考technet上面的信息。权限管理非常复杂，以上只是做了简单的介绍。需要更加详细的内容，需要自己去研究。在technet上可以找到更加详细的信息。