SQL Server 数据库设计和实现（四）

4、更改表以添加未验证的约束：
      下例向表中的现有列上添加约束。该列中存在一个违反约束的值；因此，利用 WITH NOCHECK 来防止对现有行验证约束，从而允许该约束的添加。
      CREATE TABLE doc_exd
      (
       column_a INT
      )
      GO
      INSERT INTO doc_exd VALUES (-1)
      GO
      ALTER TABLE doc_exd WITH NOCHECK
       ADD CONSTRAINT exd_check CHECK (column_a > 1)
      GO
      EXEC sp_help doc_exd
      GO
      DROP TABLE doc_exd
      GO

   5、更改表以添加多个带有约束的列：
      下例向表中添加多个带有约束的新列。第一个新列具有 IDENTITY 属性；表中每一行的标识列都将具有递增的新值。
      CREATE TABLE doc_exe
      (
       column_a INT CONSTRAINT column_a_un UNIQUE
      )
      GO
      ALTER TABLE doc_exe
       ADD column_b INT IDENTITY CONSTRAINT column_b_pk PRIMARY KEY,
         column_c INT NULL CONSTRAINT column_c_fk REFERENCES doc_exe(column_a),
         column_d VARCHAR(16) NULL CONSTRAINT column_d_chk CHECK (column_d IS NULL OR column_d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" OR column_d LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),
         column_e DECIMAL(3,3) CONSTRAINT column_e_default DEFAULT .081
      GO
      EXEC sp_help doc_exe
      GO
      DROP TABLE doc_exe
      GO

   6、添加具有默认值的可为空的列：
      下例添加可为空的、具有 DEFAULT 定义的列，并使用 WITH VALUES 为表中的各现有行提供值。如果没有使用 WITH VALUES，那么每一行的新列中都将具有 NULL 值。
      ALTER TABLE MyTable
       ADD AddDate smalldatetime NULL CONSTRAINT AddDateDflt DEFAULT getdate() WITH VALUES

   7、禁用并重新启用一个约束：
      下例禁用用于限制可接受的薪水数据的约束。WITH NOCHECK CONSTRAINT 与 ALTER TABLE 一起使用，以禁用该约束并使正常情况下会引起约束违规的插入操作得以执行。WITH CHECK CONSTRAINT 重新启用该约束。
       CREATE TABLE cnst_example
      (
       id INT NOT NULL,
       name VARCHAR(10) NOT NULL,
       salary MONEY NOT NULL CONSTRAINT salary_cap CHECK (salary < 100000)
      )
      INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
      INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)
      INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
      ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
      INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
      ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
      INSERT INTO cnst_example VALUES (4,"Eric James",110000)

2.5 使用SQL语句创建登录
一、创建登录帐户：要在MASTER数据库下执行操作
（一）创建以WINDOWS身份验证的登录
   1、格式：
       EXEC sp_grantlogin 'windows域名（或本机名）/域（本机）帐户'
      EXEC sp_grantlogin [windows域名（或本机名）/域（本机）帐户]    2、相关示例：授与本机（ac8-1）中名为abc的帐户登录权限 EXEC sp_grantlogin 'ac8-1/abc'

（二）创建以SQL身份验证的登录
   1、格式：EXEC sp_addlogin '帐户名'[, '密码']
   2、相关示例：
     1) 创建一个名为abc的登录帐户，并将其密码设为123
      EXEX sp_addlogin 'abc','123'

     2) 创建没有密码和主默认数据库的登录 ID EXEC sp_addlogin 'Victoria'

     3) 创建登录 ID 和默认数据库
      EXEC sp_addlogin 'Albert', 'food', 'corporate'

（三）删除以WINDOWS身份验证的登录
   1、格式：
      EXEC sp_revokelogin 'windows域名（或本机名）/域（本机）帐户'
      EXEC sp_revokelogin [windows域名（或本机名）/域（本机）帐户]
   2、相关示例：删除本地账户abc的登录权限
      EXEC sp_revokelogin 'ac8-1/abc'

（四）删除以SQL身份验证的登录
   1、格式：EXEC sp_droplogin '帐户名'
   2、相关示例：删除名为'abc'的帐户
      EXEC sp_droplogin 'abc'

二、创建数据库用户：在指定数据库下操作
（一）格式：EXEC sp_grantdbaccess '登录帐户','数据库用户'
（二）相关法例：
     将abc帐户在pubs数据库下的的用户名设定为abcMaster,并授权可以访问pubs数据库
      use pubs
      go
      EXEC sp_grantdbaccess 'abc','abcMaster'
（三）删除数据库用户的方法：EXEC sp_revokedbaccess '数据库用户名'

三、向数据库用户授权：在指定数据库下操作
（一）格式：详见“联机从书”，查找关键字“grant”
（二）相关示例：
   1、授予语句权限：
      下面的示例给用户 Mary 和 John 以及 Windows NT 组 Corporate/BobJ 授予多个语句权限。
      GRANT CREATE DATABASE, CREATE TABLE TO Mary, John, [Corporate/BobJ]

      2、在权限层次中授予对象权限下例显示权限的优先顺序。首先，给 public 角色授予 SELECT 权限。然后，将特定的权限授予用户 Mary、John 和 Tom。于是这些用户就有了对 authors 表的所有权限。
      USE pubs
      GO
      GRANT SELECT ON authors TO public
      GO
      GRANT INSERT, UPDATE, DELETE ON authors TO Mary, John, Tom
      GO

   3、给 SQL Server 角色授予权限：下面的示例将 CREATE TABLE 权限授予 Accounting 角色的所有成员。 GRANT CREATE TABLE TO Accounting 4、用 AS 选项授予权限 用户 Jean 拥有表 Plan_Data。Jean 将表 Plan_Data 的 SELECT 权限授予 Accounting 角色（指定 WITH GRANT OPTION 子句）。用户 Jill 是 Accounting 的成员，他要将表 Plan_Data 上的 SELECT 权限授予用户 Jack，Jack 不是 Accounting 的成员。 因为对表 Plan_Data 用 GRANT 语句授予其他用户 SELECT 权限的权限是授予 Accounting 角色而不是显式地授予 Jill，不能因为已授予 Accounting 角色中成员该权限，而使 Jill 能够授予表的权限。Jill 必须用 AS 子句来获得 Accounting 角色的授予权限。 WITH GRANT OPTION语句用于进行传递式的授权，即被授权人有权将其享受的权利授权给其它人
      GRANT SELECT ON Plan_Data TO Accounting WITH GRANT OPTION GRANT
      SELECT ON Plan_Data TO Jack AS Accounting

（三）收回对数据库用户的授权：在指定数据库下进行操作
   1、格式：详见“联机丛书”，查找关键字“revoke”
   2、相关示例：
     1）废除授予用户帐户的语句权限 下例废除已授予用户 Joe 和 Corporate/BobJ 的 CREATE TABLE 权限。它删除了允许 Joe 与 Corporate/BobJ 创建表的权限。不过，如果已将 CREATE TABLE 权限授予给了包含 Joe 和 Corporate/BobJ 成员的任何角色，那么 Joe 和 Corporate/BobJ 仍可创建表。
      REVOKE CREATE TABLE FROM Joe, [Corporate/BobJ]

     2）废除授予多个用户帐户的多个权限：下例废除授予多个用户的多个语句权限。
      REVOKE CREATE TABLE, CREATE DEFAULT FROM Mary, John

     3）废除拒绝的权限用户 Mary 是 Budget 角色的成员，已给该角色授予了对 Budget_Data 表的 SELECT 权限。已对 Mary 使用 DENY 语句以防止 Mary 通过授予 Budget 角色的权限访问 Budget_Data 表。 下例删除对 Mary 拒绝的权限，并通过适用于 Budget 角色的 SELECT 权限，允许 Mary 对该表使用 SELECT 语句。
      REVOKE SELECT ON Budget_Data TO Mary

四、SQL SERVER数据库中的dbo用户：
    dbo用户是具有在数据库中执行所有活动的暗示性权限的用户，表示数据库的所有者（OWNER），一般说，如果创建了某个数据库，就是该数据库的所有者，即dbo用户，dbo用户是一个比较特殊的数据库用户，无法删除，且此用户始终出现在每个数据库中

2.6 对于数据库安全及用户权限的扩展
一、设置登录帐户的默认登录数据库：在MASTER数据库下操作
（一）格式：EXEC sp_defaultdb '登录帐户','默认登录数据库'
（二）相关示例：EXEC sp_defaultdb 'abc','pubs'

二、数据库中的角色：在MASTER数据库下进行操作
（一）功能：角色用于对一组数据户用户进行管理，统一管理其权限
（二）创建角色：
   1、格式：sp_addrole '角色名'
   2、相关示例：EXEC sp_addrole 'Managers'

（三）删除角色：
   1、格式：sp_droprole '角色名'
   2、相关示例：EXEC sp_droprole 'Managers'

（四）添加角色成员：
1、格式：sp_addrolemember '数据库现有角色','数据库用户'
2、相关示例：EXEC sp_addrolemember 'Engineering', 'Michael'

（五）删除角色成员：
   1、格式：sp_droprolemember '数据库现有角色','数据库用户'
   2、相关示例：EXEC sp_droprolemember 'Engineering', 'Michael'

三、拒绝受权：
（一）功能：在安全系统中创建一项，以拒绝给当前数据库内的安全帐户授予权限并防止安全帐户通过其组或角色成员资格继承权限。
（二）格式：详见“联机丛书”，查找关键字“deny”
（三）相关示例：
   1、拒绝语句权限下例对多个用户拒绝多个语句权限。用户不能使用 CREATE DATABASE 和 CREATE TABLE 语句，除非给他们显式授予权限。
      DENY CREATE DATABASE, CREATE TABLE TO Mary, John, [Corporate/BobJ]

   2、在权限层次结构内拒绝对象权限
      下例显示权限的优先顺序。首先，给 public 角色授予 SELECT 权限。然后，拒绝用户 Mary、John 和 Tom 的特定权限。这样，这些用户就没有对 authors 表的权限。

      USE pubs
      GO
      GRANT SELECT ON authors TO public
      GO
      DENY SELECT, INSERT, UPDATE, DELETE ON authors TO Mary, John, Tom

   3、对 SQL Server 角色拒绝权限
      下例对所有 Accouting 角色成员拒绝 CREATE TABLE 权限。即使已给现有的 Accouting 用户显式授予了 CREATE TABLE 权限，DENY 仍替代该权限。
      DENY CREATE TABLE TO Accounting