SQL Server的安全模块
身份验证
- 创建登录名
- 修改和删除登陆名
用户管理
角色管理
- 类型
  - 服务器角色
    - 服务器级的固定角色以及其权限
  - sp_addsrvrolemember
- 数据库角色
  - 固定数据库角色
    - 数据库级的固定角色及其权限
  - 自定义数据库角色
权限管理

简单来说就是用账号密码登陆，每个账号密码有不同的权限。

SQL Server的安全模块

1. SQL Server登陆

想要链接SQL Server服务器实例，必须拥有相应的登陆账户和密码。SQL Server的身份认证系统验证用户是否拥有有效的登陆账户和密码。从而决定是否允许该用户连接到指定的SQL Server服务器实例了。

2. 数据库用户

通过身份认证后，用户可以连接到SQL Server实例，但是这并不意味着用户可以访问指定服务器上的所有数据库，每个SQL Server数据库中，都存在一组SQL Server用户账户。登陆账户要访问到指定数据库，就要将自身映射到数据库的某个用户账户上，从而获得访问数据库的权限。一个登陆账户可以对应多个用户账户。

3. 权限

权限规定了用户在指定数据库中能进行的操作

4. 角色

类似于Windows的用户组，角色可以对用户进行分组管理。可以对角色赋予数据库访问权限，此权限将应用于角色中的每个用户

身份验证

分为两种Windows身份验证和SQL Server身份验证。
Windows身份验证通过本机Windows的账户进行登陆，必须由于管理员在SQL Server中创建与Windows账户对应的SQL Server账号。
SQL Server身份验证就是输入账号密码登陆

创建登录名

1.使用SSMS创建登录名

查看登录名

右键登录名——新建登录名

2.使用存储过程sp_addlogin创建SQL Server身份验证的登录账户

sp_addlogin '登陆名称'
[,'登陆密码'][,'默认数据库'][,'默认语言']

3.使用存储过程sp_grantlogin创建Windows身份验证模式登陆账户

sp_grantlogin '登陆名称'

登陆名称指定要添加的Windows用户或者组的名称，Windows组合用户必须用Windows域名限定。格式为“域名\用户名”

4. 使用CREATE LOGIN语句创建登录名

CREATE LOGIN可以创建4种类型的登陆名：SQL Server登陆名，Windows登陆名，证书映射登陆名和非对称密钥登陆名。

CREATE LOGIN <登陆名> {WITH <option_list>|FROM <sources>}

option_list：新建登录名的选项设置 sources：新建登录名的来源（例如windows登陆，证书或者非对称密钥等）

option_list的语法

PASSWORD='密码' [HASHED] [MUST_CHANGE] 
[,<option_list2>[,...n]]

PASSWORD：指定登陆密码，仅适用于SQL Server登陆名。 HASHED：指定密码已经经过哈希运算。如果未选择此选项，则密码存储到数据库之前，进行哈希运算 MUST_CHANGE：指首次登陆时必须修改密码 option_list2：指定更多选项

option_list2的语法

SID=sid
|DEFAULT_DATABASE=<默认数据库>
|DEFAULT_LANGUAGE=<默认语言>
|CHECK_EXPIRATION={NO|OFF}
|CHECK_POLICY={ON|OFF}
[CREDENTIAL=credential_nam]

SID：仅适用于SQL Server登录名 CHECK_EXPIRATION：仅适用于SQL Server登录名，指定是否对此登陆名实施强制密码过期策略。默认值为OFF CHECK_POLICY：仅适用于SQL Server登录名，指定此对应的登录名强制实施运行SQL Server的计算机的Windows密码策略。默认为ON CREDENTIAL：映射到SQL Server登陆名的证书名称。该证书必须存在服务器中。

sources的语法

WINDOWS [WITH <windows_option>[,...n]]
|CERTIFICATE cert_name
|ASYMMETRIC KEY asym_key_name

WINDOWS：指定将登录名映射到Windows登录名 CERTIFICATE：指定将与此登录名关联的证书名称。此证书必须已经存在于master数据库中。 ASYMMETRIC KEY：指定将与此登录名关联的非对称密钥的名称。此密钥必须已经存在于master数据库中。 windows_option：指定Windows登陆名的更多选项

windows_option的语法

DEFAULT_DATABASE=<默认数据库>
|DEFAULT_LANGUAGE=<默认语言>

修改和删除登陆名

图形页面，有手就行。

sp_denylogin

用于阻止Windows用户或用户组连接到SQL Server实例。

sp_denylogin '用户或用户组名'

只能用于Windows账户一起使用，“用户或者用户组名”，格式为“域名\用户名” 无法用于通过sp_addlogin添加的SQL Server登陆。 sp_denylogin和sp_grantlogin是对应的两个存储过程，他们可以互相反转对方的效果。

sp_revokelogin

用于删除SQL Server中使用sp_denylogin或者sp_grantlogin创建的Windows身份认证模式的登录名。

sp_revokelogin '用户或用户组名'

sp_password

用户修改SQL Server登陆的密码

sp_password '旧密码','新密码','登录名'

sp_droplogin

用于删除SQL Server登陆账户

sp_droplogin '登陆名称'

用户管理

类型

在SQL Server中有两类账户：一类是登陆服务器的登陆账户，称为登陆名，另一类是使用数据库的用户账号，称为数据库用户。

登录名

指能登录到SQL Server 服务器的账号，属于服务器的层面，虽然登录名能够登录到SQL Server 服务器，但是并不表明一定可以访问数据库，登录名只有成为数据库用户后才能访问数据库。

数据库用户

在一个数据库中唯一标识的一个用户，用户对数据库的访问权限以及对数据库对象的所有关系都是通过数据库用户来控制的。一般的，登录名和用户名是相同的，方便操作，登录名和数据库用户也可以不同名，而且一个登录名可以关联不同的数据库的多个数据库用户。但每一个登录名在一个数据库中只能有一个数据库用户。

总之，登录名是属于服务器的层面，而数据库用户则属于数据库的层面。一般情况下，数据库用户与登录名使用相同的名称。

默认用户

每新建一个数据库，SQL Server服务器就会自动在新建的数据库中创建4个数据库用户

dbo

dbo用户对应的SQL Server的登录名为sa。

guest

在数据库中发现guest用户图标上有个被禁用的图标。guest用户是不能添加或者删除的，删除后一刷新，guest用户又出现了。guest用户只能启用/禁用。可以通过撤销该用户的CONNECT权限将其禁用，也可通过授予该用户的CONNECT权限将其启用。可以通过master或者tempdb以外的任何数据库中执行REVOKE CONNECT FROM GUEST来撤销其连接（CONNECT）权限，即禁用了guest用户，也可以通过在master或tempdb以外的任何数据库中执行GRANT CONNECT TO GUEST来授予其连接（CONNECT）权限，即启用了guest用户。

INFORMATION_SCHEMA和sys

每个数据库都包含这两个实体。他们都作为用户出现在目录视图中。这两个实体是SQL Server所必须的。他们不是主体，不能修改或删除。

创建数据库用户

CREATE USER

CREATE USER <用户名>
[{FOR|FROM} {LOGIN <登陆名>}|WITHOUT LOGIN]
[WITH DEFAULT_SCHEMA=<架构名>]

WITH DEFAULT_SCHEMA=<架构名>：指定服务器为此数据库用户解析对象名称时将搜索的第一个架构 WITHOUT LOGIN：指定不应将用户映射到现有登录名。如果使用LOGIN子句，则创建用户与登录名相关联。

sp_grantdbaccess

使用sp_grantdbaccess存储过程可以将SQL Server登陆和Windows用户指定为当前数据库用户，并使其能够被授予在数据库中执行活动的权限。

sp_grantdbaccess '登陆名'[,'数据库用户名']

修改和删除数据库用户

ALTER USER修改用户

ALTER USER <用户名>
WITH <set_item>[,..n]

set_item如下

NAME=<新用户名>|DEFAULT_SCHEMA=<架构名>

sp_revokedbaccess删除用户

sp_revokedbaccess '数据库用户名'

DROP USER删除用户

DROP USER '数据库用户名'

角色管理

角色是用来指定权限的一种数据库对象，每个数据库都有自己的角色对象，可以为每个角色设置不同的权限。角色概念类似于Windows操作系统的组。在SQL Server中系统已经创建了多个角色。只要吧数据库用户直接设置为某个角色的成员，那个该用户就会继承这个角色的权限。

类型

服务器角色

服务器角色权限作用于为服务器的范围。提供固定服务器角色时，为了方便使用和向后兼容，应尽可能分配更具的权限。SQL Server提供了9种固定服务器角色，无法更改授权固定服务器角色的权限。

服务器级的固定角色以及其权限

服务器角色	允许权限
bulkadmin（大容量插入操作者）	可以执行BULK INSERT语句，以用户指定的格式将数据文件加载到数据表或视图中
dbcreator（数据库创建者）	可以创建，更改，和还原任何数据库
diskadmin（磁盘管理员）	可以管理数据库在磁盘中的文件
processadmin（进程管理员）	可以终止在数据库引擎实例中运行的进程
securityadmin（安全管理员）	可以管理登录名及其属性
serveradmin（服务管理员）	可以更改服务器范围的配置选项和关闭服务器
setupadmin（安装管理员）	可以添加和删除连接服务器，并可以执行某些系统存储过程
sysadmin（系统管理员）	可以在数据库引擎中执行任何活动
public	拥有服务器中登陆名所有默认权限

sp_addsrvrolemember

可以使用sp_addsrvrolemember为现有的服务器添加一个用户名

sp_addsrvrolemember '用户名','服务器角色'

数据库角色

固定数据库角色

固定数据库角色是由SQL Server在数据库级别定义的角色，并存在于每个数据中。db_owner和db_securityadmin数据库角色的成员可以管理固定数据库角色身份，但是只有db_owner数据库角色的成员能够向db_owner固定数据库角色中添加成员。

数据库级的固定角色及其权限

数据库角色	允许权限
db_accessadmin	可以为Windows登陆账户，Windows组合SQL Server登陆账户添加或删除数据库访问权限
db_backupoperator	可以备份该数据库
db_datareader	可以读取所在用户表中的数据
db_datawriter	可以在所有用户表中添加，更改或删除数据
db_ddladmin	可以在数据库中运行任何DDL命令
db_denydatawriter	不能添加，修改或删除数据库内用户表中的任何数据
db_denydatareader	不能读取数据库内用户表中的任何数据
db_owner	可以执行数据库的所有配置和维护活动，删除数据库
db_securityadmin	可以修改角色成员身份和管理权限
public	拥有数据库用户的所有默认权限

自定义数据库角色

如果固定用户角色不能满足用户特定的需要，还可以创建一个自定义的数据库角色。在创建数据库角色时，需要先给该角色指派权限，然后将用户指派给该角色，用户将继承该角色指派的任何权限。

USE <要添加角色的数据库>
sp_addrole '角色名'

权限管理

在SQL Server中，不同的数据库用户具有不同的数据库访问权限。用户要对其数据库进行访问操作时，必须获得相应的操作授权。

安全对象

“安全对象”是服务器、数据库和数据库包含的对象。每个安全对象都拥有一组权限，可对这些权限进行配置以减少SQL Server外围应用。
安全对象是SQL Server 数据库引擎授权系统控制对其进行访问的资源。通过创建可以为自己设置安全性的名为“范围”的嵌套层次结构，可以将某些安全对象包含在其他安全对象中。安全对象范围有服务器、数据库和架构。
(1)服务器。包含以下安全对象：端点、登录名和数据库。
(2)数据库。包含以下安全对象：数据库用户、角色、应用程序角色、程序集、消息类型、路由、服务、远程服务绑定、全文目录、证书、非对称密钥、对称密钥、约定和架构。
(3)架构。包含以下安全对象：类型、XML 架构集合和对象。其中，对象类的成员有聚合、约束、函数、过程、队列、统计信息、同义词、表和视图。

主体

在SQL Server 中,“主体”是可以访问受保护资源且能获得访问资源所需权限的任何个人(个体)、组或进程。主体是可以请求SQL Server 资源的实体。与SQL Server 授权模型的其他组件一样，主体也可以按层次结构排列。主体的影响范围取决于主体定义的范围(Windows、服务器或数据库)及主体是否不可分或是一个集合(例如，Windows 登录名就是一个不可分主体，而 Windows组则是一个集合主体)。这样, Windows级别的主体就比SQL Server级别的主体拥有更大的影响范围，而后者的影响范围又大于数据库级别的主体。
每个主体都具有一个安全标识符(Ssno)。SQL Server 主体的层次结构如下，但不包括固定服务器和数据库角色，还显示了将登录和数据库用户映射为安全对象的方法。
(1) Windows级别的主体：Windows域登录名、Windows本地登求名和 Windows组。
(2)SQL Server级的主体：SQL Server登录名(包括映射为 Windows登录的 SQL Server登录、映射为证书的SOL Server登录和映射为不对称密钥的SQL Server登录).
(3)数据库级的主体：数据库用户(包括映射为SQL Server 登录的数据库用户、映射为Windows登录的数据库用户、映射为证书的数据库用户和映射为不对称密钥的数据库用户)、数据库角色、应用程序角色和公共角色。

架构

在SQL Server 2005 及更高版本中，架构就是数据库对象(如类型、XML 架构集合、数据表、视图、存储过程、函数、聚合函数、约束、同义词、队列、统计信息)的容器，它是单个主体所拥有的所有数据库对象的集合，而且该主体形成了对象的一个命名空间。它的功能与.NTE Framework和 XML中的命名空间函数非常类似，该函数可将对象进行分组，以便数据库能够重用对象名称，如允许在一个数据库中同时存在 dbo.Customer 和db_owner.Customer两个表。

架构的特点

架构与用户是分离的

作为数据库主体，用户拥有架构，而对象则包含在架构中。架构也是SOL Server安全对象的一部分。
数据库对象的引用由4部分组成：服务器名.数据库名.架构名.对象名。由此可以看出，数据库从属于SQL Server 服务器，架构从属于数据库，这些实体是嵌套在一起的。服务器是最外面的框，而架构是最里面的框。架构包含特定的安全对象，但是它不包含其他框。

架构中每个对象的名称都必须是唯一的

在SQL Server 2000和早期版本中，数据库可以包含一个名为“架构”的实体，但此实体实际上是数据库用户。现在在SQL Server 中，架构既是一个容器，又是一个命名空间。
任何用户都可以拥有架构，并且架构的所有权可以转移。

架构只能有一个所有者，但一个用户可以不拥有架构，也可以拥有多个架构

从SQL Server 2005开始，每个用户都拥有一个默认架构。可以使用CREATE USER 或 ALTER USER 的 DEFAULT_scHEMA 选项设置和更改默认架构。
如果未定义DEFAULT_scHEMA，则数据库用户将使用 dbo作为默认架构。该默认架构是服务器解析DML 或DDL语句中指定的未限定的对象名称时搜索的架构。
因此，当引用的对象包含在默认架构中时，不需要指定架构名。例如，如果 table_name包含在默认架构中，则语句SELECT * FROM table_name可以成功执行。若要访问非默认架构中的对象，则必须至少指定一个由两部分构成的标识符(schema_name.object name，即架构名.表名)。引用架构范围内的对象的所有DDL和 DML 语句都必须符合此要求。

权限的类别

对象权限

对象权限用于决定用户对数据库对象进行数据处理和执行存储过程等操作的权利，数据库对象包括表、视图、存储过程等。
对象权限的具体内容如下：

应用于表或视图：是否允许执行SELECT、DELETE、INSERT、UPDATE 和REFERENCES语句。
应用于表或视图的字段：是否允许执行SELECT 和 UPDATE 语句。
应用于存储过程和函数：是否允许执行EXECUTE语句。
SQL Server主要对象权限
| 权限 | 适用对象 | | —- | —- | | SELECT | 同义词，表和列，表值函数和列，视图和列 | | VIEW CHANGE TRACKING | 表，架构 | | UPDATE | 同义词，表和列，视图和列 | | REFERENCES | 标量函数和聚合函数，Service Broker队列，表和列，表值函数和列，视图和列 | | INSERT | 同义词，表和列，视图和列 | | DELETE | 同义词，表和列，视图和列 | | EXECUTE | 过程，标量函数和聚合函数，同义词 | | RECEIVE | Service Broker队列 | | VIEW DEFINTION | 过程，Service Broker队列，标量函数和聚合函数，同义词，表，表值函数，视图。 | | ALTER | 过程，标量函数和聚合函数，Service Broker队列，表，表值函数，视图 | | TAKE OWNERSHIP | 过程，标量函数和聚合函数，同义词，表，表值函数，视图 | | CONTROL | 过程，标量函数和聚合函数，Service Broker队列，同义词，表，表值函数，视图 |

语句权限

语句权限(也称为系统权限)用于创建数据库或数据库中对象所涉及的操作权利，其适用于语句自身，而不适用于数据库中定义的特定对象。
语句权限允许用户在数据库里执行管理操作，如创建数据库、删除数据库、创建用户账户、删除用户、删除和修改数据库对象、修改对象的状态、修改数据库的状态以及其他会对数据库造成重要影响的操作。
下面是一些SQL Server常见的语句权限。

BACKUP DATABASE：备份数据库
BACKUP LOG：备份数据库日志
CREATE DATABASE：创建数据库
CREATE TABLE：在数据库中创建表
CREATE PROCEDURE：在数据库中创建存储过程
CREATE VIEW：在数据库中创建视图
CREATE RULE：在数据库中创建规则
CREATE DEFAULT：在数据库中创建默认对象
CREATE FUNCTION：创建函数

隐含权限
隐含权限是指系统定义而不需要授权就有的权限，例如，sysadmin固定服务器角色成员自动集成在SQL Server安装中进行操作或查看的全部权限。
数据库对象所有者以及服务器固定成员均具有隐含权限，可以对所有者的对象执行一切活动。例如，拥有表的用户可以查看、添加或删除数据，更改表定义，或控制允许其他用户对表进行操作的权限。

权限管理的操作
在3种权限中，隐含权限是系统定义，是不能进行设置修改的，因而权限的设置管理实际上是针对对象权限和语句权限进行的，权限可由数据库所有者和角色来进行管理。
权限所涉及的操作如下：
授予（GRANT）：允许用户或角色对一个对象实施某种操作或执行某种语句
撤销（REVOKE）：不允许用户或角色对一个对象实施某种操作或执行某种语句，或收回曾经授予的某种权限，这与授予权限正好相反。
拒绝（DENY）：拒绝用户访问某个对象，或删除以前授予的权限，停用其他角色继承的权限，确保不继承更高级别角色的权限。
授权GRANT
```
GRANT {ALL [PRIVILEGES]}
|permission [(column [,...n])][,...n]
[ON [class ::] securable] TO principal [,...n]
[WITH GRANT OPTION][AS principal]
```
语法比较复杂，有关意义参考《Micrisoft SQL Server联机丛书》

撤销REVOKE

REVOKE [GRANT OPTION FOR]
{
    [ALL [PRIVILEGES]]
  |permission [(column [,...n])][,...n]
}
[ON [class ::] securable]
{TO | FROM} principal [,...n]
[CASCADE] [AS principal]

拒绝DENY

DENY {ALL [PRIVILEGES]}
|permission [(column [,...n])][,...n]
[ON [class ::] securable] TO principal [,...n]
[CASCADE] [AS principal]

数据库安全性管理