6.1SQL Server 安全机制和登录账户管理
6.1.1 SQL Server 安全机制
登录账户（Login）:登录账户是账户标识符，用来控制对任何 SQL Server 系统的访问权限。SQL Server 只有在首先验证指定的登录账户有效后，才能完成连接
数据库用户（User）:用户是在特定的数据库内创建，并关联一个登录账户。当创建一个用户时，必须为他指定一个与之相关联的登录账户
账户（Account）:数据库用户仅仅是一个数据库对象，而账户则特指由用户管理器所创建的 Windows 账户（Windows 系统）
6.1.2 SQL Server 身份验证模式
使用SSMS设置验证模式
- 启动 SSMS，并连接到 SQL Server 中的数据库。在“对象资源管理器”窗口中，右击连接的 SQL Server服务器名称，系统弹出快捷菜单
- 执行弹出菜单中的【属性】命令，打开“服务器属性”对话框
- 在服务器属性对话框中，选择“安全性”，进入其“安全性”设置页面，在其“服务器身份验证”选项中可选择 SQL Server 的验证模式，还可设置其他有关选项，设置完成后单击“确定”按钮即可
6.1.3 创建登录账户
使用 T-SQL 命令创建登录账户
语法：create login login_name from windows
示例：if suser_ID('csc\emp1')is not null drop login [csc\emp1] go create login [csc\emp1] from windows go
创建 SQL Server 登录账户
语法：create login login_name with password='password' , default_database=db_name
示例：if suser_ID(' emp1')is not null drop login emp1 go create login emp1 with password='abc123' go 创建名称为 emp1 的 SQL Server 登录账户，该账号的密码为 abc123
if suser_ID('emp1')is not null drop login emp1 go create login emp1 with password='abc123',default_database=lingju go 创建名称为 emp1 的 SQL Server 登录账户，该账号的密码为 abc123，默认数据库 lingju
6.1.4 维护登录账户
使用 SSMS 维护登录账户
- 启动 SSMS，在“对象资源管理器”窗口中，展开“安全性”节点，右击欲修改的“登录名”节点（例如emp1），系统弹出快捷菜单，执行【重命名】命令
- 输入新的登录名即完成登录名的更名
6.1.4 维护登录账户
使用 T-SQL 命令维护登录账户
示例：
alter login emp1 with name= emp2 修改登录名 emp1 为 emp2
alter login emp2 with password='123456' 修改登录名 emp2 的新密码为 123456
alter login emp2 disable 禁用登录名 emp2 登录 SQL Server
alter login emp2 enable 启用登录名 emp2 登录 SQL Server
drop login emp2 删除登录名 emp2
6.2数据库用户管理
6.2.1 数据库用户概要
创建登录账户后，用户只能连接 SQL server 服务器而已，还没有访问某个具体数据库的权限，还不能操纵数据库中的数据
用户要拥有访问数据库的权限，还必须将登录账户映射到数据库用户。用户是通过登录账户与数据库用户的映射关系取得对数据库的实际访问权的
数据库用户是数据库级的主体，是登录名在数据库中的映射，是在数据库中执行操作和活动的行动者
6.2.2 创建数据库用户
使用 SSMS 创建数据库用户
启动 SSMS，在“对象资源管理器”窗口中，点击数据库（例如，lingju），依次展开“安全性”-“用户”，右击弹出快捷菜单，执行【新建用户】命令，打开“数据库用户－新建”对话框
在“用户类型”下拉框中选中“带登录名的 SQL 用户”， 在“用户名”文本框中输入新建数据库用户的名称；通过“登录名”文本框后面的“…”按钮选择该数据库用户所关联的 SQL Server 登录账号；通过“默认架构”文本框后面的“…”按钮可设置该用户的默认架构
使用 T-SQL 命令创建数据库用户
语法：create user user_name from login login_name [with default_schema=schema_name]
示例：use lingju go if user_id('user_emp')is not null drop user user_emp go create user user_emp from login emp with default_schema=schema_emp go
6.2.3 架构管理
SQL Server 架构是形成单个命名空间的数据库实体的集合。SQL Server 架构被单个负责人（可以是用户或角色）所拥有并构成唯一命名空间，它是数据库级的安全对象，也是 SQL Server 强调的特点
架构是数据库对象的容器，也是分离数据库用户和安全对象的有效方法
SQL Server 内置了 12 个预定义的架构，它们与内置数据库用户和角色具有相同的名称。这些架构主要用于向后兼容性。如果不需要与固定数据库角色具有相同名称的架构，则可以删除它们。但不能删除下列架构
- dbo
- guest
- sys
- information_schema
使用 SSMS 创建架构
- 启动 SSMS，在“对象资源管理器”窗口中，点击数据库（例如，lingju），依次展开“安全性”-“架构”，右击弹出快捷菜单，执行【新建架构】命令，打开“架构－新建”对话框
- 在“架构名称”文本框中输入新建架构的名字，此处为 schema_emp；通过“架构所有者”文本框后面的“…”按钮添加该架构的所有者，此处选择架构的所有者是 user_emp
- 架构名称和架构所有者的输入信息完成之后，点击“确定”按钮则完成了 schema_emp 架构的创建
创建架构包括定义架构名称和定义架构的所有者（数据库用户），另外在定义架构的同时，也可以定义该架构所拥有的表、视图，同时也可授予权限
示例：
use lingju go if schema_id('schema_emp')is not null drop schema schema_emp go create schema schema_emp go
use lingju go if schema_id('schema_emp')is not null drop schema schema_emp go create schema schema_emp authorization user_emp go
定义名称为 schema_emp 的架构，指定该架构的所有者为 user_emp，并创建该架构所拥有的数据库对象，此处为表 employee
示例：use lingju go if schema_id('schema_emp')is not null drop schema schema_emp go create schema schema_emp authorization user_emp create table employee( empID int primary key, empname varchar(20) ) go
将 dbo 架构拥有的 area 表转移到 schema_emp 架构中
示例：use lingju go if schema_id('schema_emp')is not null alter schema schema_emp transfer dbo.area go
alter authorization on schema::schema_emp to dbo
6.2.4 维护数据库用户
将 lingju 数据库中的 user_emp 数据库用户更名为 user_emp1
示例：use lingju go if user_id('user_emp')is not null alter user user_emp with name=user_emp1 go
use lingju go if user_id('user_emp')is not null alter user user_emp with default_schema=dbo go
use lingju go if user_id('myemp')is not null alter user myemp with login=mgr go
6.3数据库角色管理
6.3.1 SQL Server 角色概要
在 SQL Server 中，角色是为了方便权限管理而设置的管理单位
角色将数据库中的不同用户集中到不同单元（角色）中，并以单元为单位进行权限管理，该单元的所有用户都具有该权限，即角色的成员继承了角色的权限，大大减少了管理员的工作量
6.3.2 固定服务器角色
固定服务器角色是服务器级别的主体，它们独立于各个数据库，其作用范围是整个服务器
固定服务器角色的功能描述
- sysadmin ：系统管理员，拥有操作 sql server 服务器的所有权限
- serveradmin ：服务器管理员，拥有修改端点、资源、服务器状态等权限
- setupadmin ：安装程序管理员，拥有修改链接服务器权限
- securityadmin：安全管理员，拥有执行修改登录名的权限
- processadmin ：进程管理员，拥有管理服务器连接和状态的权限
- dbcreator ：数据库创建者，拥有创建修改数据库的权限
- diskadmin：磁盘管理员，拥有修改资源的权限
- bulkadmin ：块数据操作管理员，拥有执行块操作的权限
- public ：公共角色，没有预先设置的权限
将数据库登录 emp 添加到固定服务器角色 sysadmin 中
示例：execute sp_addsrvrolemember emp, sysadmin
从固定服务器角色 sysadmin 中删除数据库登录 emp
示例：execute sp_dropsrvrolemember emp, sysadmin
指明当前登录名是否为 sysadmin 固定服务器角色的成员
示例：if is_srvrolemember('sysadmin')=1 print 'current user''s login is a member of the sysadmin role' else if is_srvrolemember('sysadmin')=0 print 'current user''s login is not a member of the sysadmin role' else if is_srvrolemember('sysadmin') is null print 'error: invalid server role specified'
查看固定服务器角色 sysadmin 的成员
示例：execute sp_helpsrvrolemember sysadmin
6.3.3 固定数据库角色
固定数据库角色是数据库级别的主体，其作用范围是整个数据库。固定数据库角色具有固定的权限，不能被修改，但可以在这些角色中添加数据库用户以获得相关的权限
SQL Server 中的每一个数据库中都有一组固定数据库角色，从而很容易实现数据库用户的权限管理
固定数据库角色的功能描述
- db_accessadmin ：访问权限管理员
- db_backupoperator ：数据库备份管理员
- db_datareader：数据检索操作员
- db_datawriter ：数据库维护操作员
- db_ddladmin ：数据库对象管理员
- db_denydatareader ：拒绝执行检索操作员
- db_denydatawriter ：拒绝执行数据维护操作员
- db_owner：数据库所有者
- db_securityadmin ：安全管理员
- public ：公共角色
将数据库用户 user_emp 添加到固定数据库角色 db_ddladmin 中
示例：use lingju execute sp_addrolemember db_ddladmin, user_emp
从固定数据库角色db_ddladmin中删除数据库用户user_emp
示例：use lingju execute sp_droprolemember db_ddladmin, user_emp
查看固定数据库角色db_ddladmin的成员
示例：execute sp_helprolemember db_ddladmin
6.3.4 用户自定义数据库角色
使用 SSMS 创建用户自定义数据库角色
- 启动 SSMS，在“对象资源管理器”窗口中，点击数据库（例如，lingju），依次展开“安全性”-“角色”-“数据库角色”，右击弹出快捷菜单，执行【新建数据库角色】命令，打开“数据库角色－新建”对话框
- 在“角色名称”文本框中输入新建数据库角色的名称；通过“所有者”文本框后面的“…”按钮来设置该数据库角色的所有者；在“此角色拥有的架构”列表框中，可选择该角色拥有的架构；通过单击“此角色的成员”列表框下面的“添加”按钮，添加该角色中的各个成员
- 点击“确定”按钮即完成数据库角色的创建工作
创建名为 role_emp 的数据库角色
示例：use lingju go if exists(select * from sys.database_principals where name='role_emp' and type = 'r') drop role role_emp go create role role_emp go
创建名为 role_emp 的数据库角色，其所有者是 user_emp
示例：use lingju go if exists(select * from sys.database_principals where name='role_emp' and type = 'r') drop role role_emp go create role role_emp authorization user_emp go
将 user_emp 数据库用户添加到角色 role_emp
示例：
execute sp_addrolemember role_emp, user_emp将 user_emp 数据库用户添加到角色 role_emp
execute sp_droprolemember role_emp, user_emp 将 user_emp 数据库用户从角色 role_emp 中删除
修改和删除用户自定义数据库角色
示例：
alter role buyers with name=purchasing 将用户自定义数据库角色 buyers 改名为 purchasing
alter role sales add member jack alter role sales drop member jack 创建用户自定义数据库角色 sales，并添加成员jack, 然后从数据库角色sales中删除成员jack
6.3.5 应用程序角色
与数据库角色相比来说，应用程序角色有以下 3 个特点
- 在默认情况下不包含任何成员
- 在默认情况下该角色是非活动的，必须激活之后才能发挥作用
- 该角色有密码，只有拥有应用程序角色正确密码的用户才可以激活该角色。当激活某个应用程序角色之后，用户会失去自己原有的权限，转而拥有应用程序角色的权限
使用应用程序角色的过程
- 创建应用程序角色
- 对该应用程序角色分配权限
- 执行客户端应用程序
- 客户端应用程序作为用户连接到SQL Server
- 然后应用程序用一个只有它才知道的密码执行 sp_setapprole 存储过程
- 如果应用程序角色名称和密码都有效，则激活应用程序角色。此时，连接将失去用户权限，而获得应用程序角色权限，这些权限在连接期间始终有效
在 SQL Server 系统中，可以使用create application role语句创建应用程序角色
语法：create application role application_role_name with password='password' , default_schema=schema_name]
在 lingju 数据库中创建一个名为 approle 的应用程序角色，密码为 app123，默认架构为 schema_emp
示例：use lingju go create application role approle with password='app123', default_schema=schema_emp go
激活数据库 lingju 中的应用程序角色 approle
示例：use lingju go sp_setapprole approle, 'app123' go
6.4数据库权限管理
6.4.1 SQL Server 权限概要
权限是针对用户而言的，若用户需要对 SQL Server 进行某种操作，就必须具备使用该操作的权限。权限是用来指定授权用户可以使用的数据库对象和这些授权用户可以对这些数据库对象执行的操作
权限是执行操作、访问数据的通行证。只有拥有了针对某种安全对象的指定权限，才能对该对象执行相应的操作。可以对不同的主体对象设置不同的权限，从而赋予或限制它们访问数据的能力
6.4.2 权限类型
常用的针对所有对象的权限及其描述
- control ：为被授权者授予类似所有权的功能，被授权者拥有对安全对象所定义的所有权限。
- alter ：为被授权者授予更改特定安全对象的属性（所有权除外）的权限。
- alter any < 安全对象 > ：为被授权者授予创建、更改或删除服务器安全对象或数据库安全对象的各个实例的权限
- take ownership ：允许被授权者获得所授予的安全对象的所有权
- impersonate< 登录名 >|< 用户 > ：可以使被授权者模拟指定的登录名或指定的用户执行各种操作。
- create < 服务器安全对象 >|< 数据库安全对象 >|< 架构内的安全对象 > ：可以使被授权者获取创建服务器安全对象、数据库安全对象、架构内的安全对象的权限。
- view definition ：可以使被授权者访问元数据
常用的针对特殊对象的权限及其描述
- select ：对指定安全对象的检索操作。这些安全对象包括同义词、表和列、表值函数、视图和列等
- update ：对指定安全对象中数据的更新操作，这些安全对象包括同义词、表和列、视图和列等。
- references ：对指定安全对象的引用操作，这些安全对象包括标量函数、聚合函数、表和列、视图和列等
- insert ：对指定安全对象的插入操作，这些安全对象包括同义词、表和列、视图和列等
- delete ：对指定安全对象的删除数据操作，这些安全对象包括同义词、表和列、视图和列等
- execute ：对指定安全对象的执行操作，这些安全对象包括过程、标量函数、聚合函数、同义词等
常见数据库安全对象的常用权限
- 数据库 ：backup database、backup log、create database、create default、create function、create procedure、create rule、create table、create view
- 表 ：select、delete、insert、update、references
- 表值函数 ：select、delete、insert、update、references
- 视图 ：select、delete、insert、update、references
- 存储过程 ：execute、synonym
- 标量函数 ：execute、references
6.4.3 权限管理
使用 SSMS 实现用户授权
- 启动 SSMS，在“对象资源管理器”窗口中，点击数据库（例如，lingju），依次展开“安全性”-“用户”，点击用户（例如，user_emp）右击弹出快捷菜单，执行【属性】命令，打开“数据库用户”对话框
- 在数据库用户对话框中，选择“安全对象”选择页，进入其“安全对象”设置页面
- 在“数据库用户”对话框的“安全对象”设置页面中，单击“安全对象”选项后面的“搜索”按钮，进入“添加对象”对话框，在此对话框中，选择要添加对象的类型，这里选择“特定类型的所有对象（T）...”单选钮
- 单击“确定”按钮，进入“选择对象类型”对话框，在该对话框中选择允许该用户能够访问和操作的具体对象类型，此处选择“表”
- 选择完毕后，单击“确定”按钮，则返回“数据库用户”对话框的“安全对象”设置页面
使用 grant 语句实现用户授权
示例：
use lingju grant select, update on schema_emp.employee to user_emp 授予数据库用户 user_emp 查询、新增表 employee 的权限
use lingju grant delete on schema_emp.employee to user_emp with grant option 授予数据库用户 user_emp 关于表 employee 的 delete 权限，并允许该用户将这个权限转授给其他用户
use lingju grant update on schema_mgr.product(title,areaID) to user_mgr 授予数据库用户 user_emp 操作表中指定列的权限
use lingju grant all on database::lingju to user_mgr 授予数据库用户 user_mgr 具有操作数据库 lingju 的所有权限
使用 revoke 语句实现权限收回
示例：
revoke delete on schema_emp.employee from user_emp
将数据库用户 user_emp 对表 employee 的 delete 权限进行收回
revoke update on schema_mgr.product(title,areaID) from user_mgr cascade
收回数据库用户 user_mgr 更新 product 表中 title 列和 areaID 列内容的权限，并允许该用户将这个权限转 授给其他用户
revoke create table from user_cus 将数据库用户 user_cus 创建表的权限收回
安全主体可以通过两种方式获得权限
- 第一种方式是直接使用 grant 语句为其授予权限
- 第二种方式是通过作为角色成员继承角色的权限
使用 revoke 语句只能删除安全主体通过 grant 方式得到的权限，如果需要彻底删除安全主体的特定权限，则必须使用 deny 语句
示例：
revoke delete on schema_emp.employee from user_emp 删除数据库用户 user_mgr 更新 product 表中 title 列和 areaID 列内容的权限
revoke update on schema_mgr.product(title,areaID) from user_mgr cascade
将数据库用户 user_mgr 对 product 表中 title 列和 areaid 列的处于否认状态的 update 权限恢复到自然状态
总结 ：
- SQL Server 把登录名与用户名的关系称为映射，一个登录名可以被授权访问多个数据库，但一个登录名在每个数据库中只能映射一次
- dbo 是数据库中的默认用户，是系统自动生成的。dbo 用户拥有在数据库中操作的所有权限
- 在 SQL Server 中，角色是为了方便权限管理而设置的管理单位，它将数据库中的不同用户集中到不同单元（角色）中，并以单元为单位进行权限管理
- 权限是针对用户而言的，若用户需要对 SQL Server 进行某种操作，就必须具备使用该操作的权限。权限是用来指定授权用户可以使用的数据库对象和这些授权用户可以对这些数据库对象执行的操作
