上周， 有位朋友给我提出了这样的需求：区分用户访问数据库的权限。顺便总结了下有如下要求：
某个用户查询所有数据库的权限 某个用户只有备份数据库的权限 给一个用户只能查看指定数据库的权限 给一个用户只有某个表的权限
要进行以上任务，首先我们先了解下数据库的权限相关的内容 主体 “主体” 是可以请求SQL Server资源的实体。 与SQLServer授权模型的其他组件一样，主体也可以按层次结构排列。 主体的影响范围取决于主体定义的范围（Windows、服务器或数据库）以及主体是否不可分或是一个集合。 例如，Windows登录名就是一个不可分主体，而Windows组则是一个集合主体。 每个主体都具有一个安全标识符(SID)。 Windows级别的主体 Windows域登录名 Windows本地登录名 SQL Server-级的主体 SQL Server登录名 服务器角色 数据库级的主体 数据库用户 数据库角色 应用程序角色 SQLServer sa登录名 SQL Server sa登录名是服务器级的主体。 默认情况下，该登录名是在安装实例时创建的。 public数据库角色 每个数据库用户都属于public数据库角色。 当尚未对某个用户授予或拒绝对安全对象的特定权限时，则该用户将继承授予该安全对象的public角色的权限。 INFORMATION_SCHEMA和sys 每个数据库都包含两个实体： INFORMATION_SCHEMA和sys，它们都作为用户出现在目录视图中。这两个实体是SQL Server所必需的。 它们不是主体，不能修改或删除它们。 基于证书的SQL Server登录名 名称由双井号(##)括起来的服务器主体仅供内部系统使用。 下列主体是在安装SQL Server时从证书创建的，不应删除。 ##MS_SQLResourceSigningCertificate## ##MS_SQLReplicationSigningCertificate## ##MS_SQLAuthenticatorCertificate## ##MS_AgentSigningCertificate## ##MS_PolicyEventProcessingLogin## ##MS_PolicySigningCertificate## ##MS_PolicyTsqlExecutionLogin## guest用户 每个数据库包括一个guest。 授予guest用户的权限由对数据库具有访问权限，但在数据库中没有用户帐户的用户继承。不能删除guest用户，但可通过撤消该用户的CONNECT权限将其禁用。 可以通过在master或tempdb以外的任何数据库中执行REVOKE CONNECTFROM GUEST来撤消CONNECT权限。 客户端和数据库服务器 根据定义，客户端和数据库服务器是安全主体，可以得到保护。 在建立安全的网络连接前，这些实体之间可以互相进行身份验证。SQLServer支持Kerberos身份验证协议，该协议定义客户端与网络身份验证服务交互的方式。 创建数据库用户 SQL2016中支持11种用户类型： 用户基于登录名在master这是最常见的用户类型。 基于登录名基于的Windows Active Directory帐户的用户 CREATE USER [Contoso\Fritz]; 基于Windows组的登录名的用户。CREATE USER [Contoso\Sales]; 基于使用SQLServer身份验证的登录名的用户。CREATE USER Mary; 在数据库进行身份验证的用户建议以帮助使你的数据库可移植性。
基于无登录名的Windows用户的用户 CREATEUSER [Contoso\Fritz]; 基于无登录名的Windows组的用户。CREATE USER [Contoso\Sales]; 中的用户SQLDatabase或SQL数据仓库 基于Azure Active Directory的用户。CREATE USER [Contoso\Fritz] FROMEXTERNAL PROVIDER; 拥有密码的包含数据库用户。(在中不可用SQL数据仓库。)CREATE USER Mary WITHPASSWORD = '********'; 基于Windows主体通过Windows组登录名进行连接的用户 基于无登录名但可通过Windows组中的成员身份连接到数据库引擎的Windows用户的用户 CREATE USER [Contoso\Fritz]; 基于无登录名但可通过其他Windows组中的成员身份连接到数据库引擎的Windows组的用户。CREATE USER [Contoso\Fritz]; 无法进行身份验证的用户这些用户无法登录到SQL Server或SQL Database。 没有登录名的用户。 不能登录，但可以被授予权限 CREATE USER CustomAppWITHOUT LOGIN; 基于证书的用户。 不能登录，但可以被授予权限，也可以对模块进行签名。CREATE USERTestProcess FOR CERTIFICATE CarnationProduction50; 基于非对称密钥的用户。 不能登录，但可以被授予权限，也可以对模块进行签名。CREATE User TestProcessFROM ASYMMETRIC KEY PacificSales09; 下面的图片显示了创建数据库用户需要的选项的含义： 创建用户可以使用界面完成： 也可以使用T-SQL来进行创建 --创建登录名：Test密码是：'123456'. CREATELOGINTest WITH PASSWORD = '123456'; GO
上面说完了用户，下面说下数据库的角色和权限 服务器级别的权限 SQL Server提供服务器级角色以帮助你管理服务器上的权限。 这些角色是可组合其他主体的安全主体。服务器级角色的权限作用域为服务器范围。 （“角色”类似于Windows操作系统中的“组”。） SQL Server提供了九种固定服务器角色。 无法更改授予固定服务器角色的权限。 从SQL Server 2012开始，您可以创建用户定义的服务器角色，并将服务器级权限添加到用户定义的服务器角色。 你可以将服务器级主体（SQL Server登录名、Windows帐户和Windows组）添加到服务器级角色。 固定服务器角色的每个成员都可以将其他登录名添加到该同一角色。用户定义的服务器角色的成员则无法将其他服务器主体添加到角色。 下表显示了服务器级的固定角色及其权限 下表显示了固定数据库角色及其能够执行的操作。 所有数据库中都有这些角色。无法更改分配给固定数据库角色的权限 无法更改分配给固定数据库角色的权限。 下图显示了分配给固定数据库角色的权限： SQL 2016有一些数据库的特殊权限 msdb角色 msdb数据库中包含下表显示的特殊用途的角色。 使用R Services SQL Server（从SQL Server vNext开始） 安装R Services时，其他数据库角色可用于管理包 下面讲如何实现文章前面说的需求： 给某个用户查询所有数据库的权限 给某个用户只有备份数据库的权限 给一个用户只有指定数据库的权限 给一个用户只有某个表的权限 给某个用户查询所有数据库的权限 创建一个用户
USE [master] GO CREATE LOGIN [Test1]WITH PASSWORD=N'password@123' 使用Test1连接数据库实例 可以看到数据库列表， 但是无法访问数据库， 赋予test1对FinaceDemo的读取权限
USE [FinaceDemo] GO CREATE USER [Test1] FOR LOGIN [Test1] ALTER ROLE [db_datareader] ADD MEMBER [Test1] GO 这样就可以给test1用户对finacedemo的读取权限 但是test1 没有写入权限 这样就可以单独对test1赋予数据库的读取权限进行查看操作。 给某个用户只有备份数据库的权限 Test1对于finacedemo无备份权限 赋予备份权限 ALTER ROLE [db_backupoperator] ADD MEMBER [Test1] 给一个用户只有指定数据库的权限 我们需要Test1只能看到FinanceDemo，其他所有数据库都不能看到 执行下面脚本
USE [master] Deny VIEW any DATABASE TO Test1; go 运行后的效果 |
注册
登录
