首页 > 代码库 > Chapter 2 User Authentication, Authorization, and Security（10）：创建包含数据库

Chapter 2 User Authentication, Authorization, and Security（10）：创建包含数据库

2024-07-23 18:16:02   227人阅读

原文出处：http://blog.csdn.net/dba_huangzj/article/details/39473895，专题目录：http://blog.csdn.net/dba_huangzj/article/details/37906349

未经作者同意，任何人不得以“原创”形式发布，也不得已用于商业用途，本人不负责任何法律责任。

        前一篇：http://blog.csdn.net/dba_huangzj/article/details/39003679

 

前言：

 

在SQL Server中，安全性分为两级：服务器级别和数据库级别。服务器的登录名被映射到数据库中的一个用户。身份验证在登录名连接到服务器时发生。如果数据库中有这个登录名所对应的用户，那么这个登录名就能访问数据库。登录名和用户之间的映射是基于一个内部SID，当从一个服务器复制数据库到另外一个服务器，即使用户名相同，如果SID不同，也会打算这个连接关系。从2012开始引入了包含数据库（Contained database）概念，用于解决这种问题。

一个包含数据库并不依赖于任何外部定义，可以在服务器之间自由移动而不需要任何额外配置。SQL Server的包含概念有下面3类：

  • 不包含（Non-contained）：数据库基于服务器。
  • 部分包含（Partially-contained）：用户是定义在数据库内部，数据库是独立的，但是依旧可以访问数据库外部的资源。
  • 完全包含（Fully-contained）：数据库是独立的，用户不能访问外部资源。

SQL Server 2012 仅支持部分包含，这个功能主要解决两个问题：登录名和用户的映射问题，还有就是临时表的排序规则。当在包含数据库范围内创建一个局部临时表时，CHAR/VARCHAR列的排序规则是按照调用的数据库的排序规则而定，而不是tempdb。

 

准备工作：

 

在创建包含数据库前，需要在实例级别启用包含数据库身份验证。右键实例→【属性】→【高级】

image

也可以使用T-SQL实现：

EXEC sp_configure ‘show advanced options‘, 1; 
RECONFIGURE; 
GO 
EXEC sp_configure ‘contained database authentication‘, 1; 
RECONFIGURE; 
GO 
sp_configure ‘show advanced options‘, 0; 
RECONFIGURE; 
GO


这个配置也要在需要被还原的服务器上启用（假设你的库需要移动到别的服务器）。

 

 

实现：

 

可以按照下面步骤创建部分包含数据库：

1. 在SSMS中右键【数据库】节点，选择【新建数据库】

2. 在【选项】页中选择【包含类型】选项中的【部分】

image

换成T-SQL就是：

CREATE DATABASE containedDb 
CONTAINMENT = PARTIAL;


 

3. 然后，你可以创建一个数据库里面的用户：

USE containedDb; 
CREATE USER Fred WITH PASSWORD = N‘Strong Password‘, –SQL 身份验证及密码
DEFAULT_SCHEMA = dbo; 
CREATE USER [DOMAIN\Fred];—Windows 身份验证


 

4. 查看包含数据库中的包含用户，可以使用下面语句：

SELECT name, type_desc, authentication_type_desc 
FROM sys.database_principals 
WHERE authentication_type = 2;


 

5. 在DMV sys.dm_exec_sessions中会显示使用的身份验证：

SELECT  session_id , 
        login_time , 
        login_name , 
        DB_NAME(database_id) AS db , 
    IIF( 
        authenticating_database_id = 1, 
        ‘server login‘, 
        QUOTENAME(DB_NAME(authenticating_database_id)) + ‘ user ‘ 
        + QUOTENAME(original_login_name) ) 
    AS authentication_type 
FROM    sys.dm_exec_sessions 
WHERE   is_user_process = 1;


 

 

原理：

 

包含数据库对传统SQL Server安全模式带来了一些改变。在过去，仅有已授权的登录才能连接到SQL Server，现在，用户可以独立于任何登录，而被直接授权。其密码是存储在数据库层面并可以在服务器层面修改。如果包含数据库用户是一个Windows 帐号，整个帐号仅在数据库内部有效，因为它没有在服务器层面有登录名映射。

一个包含用户没有默认数据库，所以如果数据库没有在连接时被显式定义将会导致连接不能创建，可以通过SSMS的连接属性或者在应用程序中的连接字符串中定义，如使用SQL Server Native Client ODBC驱动：

Driver={SQL Server Native Client 11.0};Server=SERVER\SQL2012;Database=ContainedDB;Uid=Fred;Pwd=iamaweakpassword;


在创建包含数据库时，由于目标服务器可能已经存在相同的登录名，可能导致一些安全隐患或者权限冲突，这时候可以使用DDL触发器应对现有数据库的包含类型修改。详细内容可以看下面博客：

http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/06/contained-database-authentication-how-to-control-which-databases-are-allowed-to-authenticate-users-using-logon-triggers.aspx

 

更多：

 

由于一些视图、存储过程可能依赖于其他数据库的表，或者同义词、服务器级别的系统对象，所以真正的包含数据库是很难实现的。所以SQL Server 2012中，包含数据库是部分包含。可以从sys.dm_db_uncontained_entities 这个DMV中查询：

 
SELECT  e.feature_name , 
        [object] = COALESCE(QUOTENAME(SCHEMA_NAME(o.[schema_id])) + ‘.‘ 
                            + QUOTENAME(o.[name]), 
                            QUOTENAME(SCHEMA_NAME(s.[schema_id])) + ‘.‘ 
                            + QUOTENAME(s.[name])) , 
        [line] = COALESCE(e.statement_line_number, 0) , 
        [statement / synonym target / route / user/login] = COALESCE(s.[base_object_name], 
                                                              SUBSTRING(m.[definition], 
                                                              e.statement_offset_begin 
                                                              / 2, 
                                                              e.statement_offset_end 
                                                              / 2 
                                                              - e.statement_offset_begin 
                                                              / 2) COLLATE CATALOG_DEFAULT, 
                                                              r.[name], 
                                                              ‘User : ‘ 
                                                              + p.[name] 
                                                              + ‘ / Login : ‘ 
                                                              + sp.[name]) 
FROM    sys.dm_db_uncontained_entities AS e 
        LEFT JOIN sys.objects AS o ON e.major_id = o.object_id 
                                      AND e.class = 1 
        LEFT JOIN sys.sql_modules AS m ON e.major_id = m.object_id 
                                          AND e.class = 1 
        LEFT JOIN sys.synonyms AS s ON e.major_id = s.object_id 
                                       AND e.class = 1 
        LEFT JOIN sys.routes AS r ON e.major_id = r.route_id 
                                     AND e.class = 19 
        LEFT JOIN sys.database_principals AS p ON e.major_id = p.principal_id 
                                                  AND e.class = 4 
        LEFT JOIN sys.server_principals AS sp ON p.[sid] = sp.[sid];


 

如何转换数据库成为包含数据库：

 

可以使用下面语句把一个数据库转换成包含数据库：

USE [master] 
GO 
ALTER DATABASE [marketing] SET CONTAINMENT = PARTIAL;


 

如果有用户映射到SQL登录，可以使用sp_migrate_user_to_contained系统存储过程转换包含数据库用户，如果需要自动化，可以查看微软文档：http://msdn.microsoft.com/en-us/library/ff929275.aspx ，也可以使用下面脚本：

SELECT ‘EXEC sp_migrate_user_to_contained @username = N‘‘‘ + dp.name + 
‘‘‘, 
  @rename = N‘‘keep_name‘‘, 
  @disablelogin = N‘‘do_not_disable_login‘‘ ;‘ 
FROM sys.database_principals AS dp 
JOIN sys.server_principals AS sp 
ON dp.sid = sp.sid 
WHERE dp.authentication_type = 1 AND sp.is_disabled = 0;

下一篇：

Chapter 2 User Authentication, Authorization, and Security（10）：创建包含数据库


联系
我们