针对包含数据库的安全性最佳方法Security Best Practices with Contained Databases

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

包含的数据库面临着一些独有的威胁, SQL Server 数据库引擎SQL Server Database Engine 管理员应该了解并缓解这些威胁。Contained databases have some unique threats that should be understood and mitigated by SQL Server 数据库引擎SQL Server Database Engine administrators. 大部分威胁与 USER WITH PASSWORD 身份验证过程相关,该过程会将身份验证的范围从 数据库引擎Database Engine 级别转到数据库级别。Most of the threats are related to the USER WITH PASSWORD authentication process, which moves the authentication boundary from the 数据库引擎Database Engine level to the database level.

包含的数据库中具有 ALTER ANY USER 权限的用户(例如,db_owner 和 db_accessadmin 固定数据库角色的成员)可以在不告知 SQL ServerSQL Server 管理员或者得到其允许的情况下授予该数据库的访问权限 。Users in a contained database that have the ALTER ANY USER permission, such as members of the db_owner and db_securityadmin fixed database roles, can grant access to the database without the knowledge or permission or the SQL ServerSQL Server administrator. 授予用户对包含数据库的访问权限会增加整个 SQL ServerSQL Server 实例受到攻击的可能性。Granting users access to a contained database increases the potential attack surface area against the whole SQL ServerSQL Server instance. 管理员应了解访问控制的这种委托,而且在为包含数据库中的用户授予 ALTER ANY USER 权限时要非常谨慎。Administrators should understand this delegation of access control, and be very careful about granting users in the contained database the ALTER ANY USER permission. 所有数据库所有者都拥有 ALTER ANY USER 权限。All database owners have the ALTER ANY USER permission. SQL ServerSQL Server 管理员应该定期审核包含数据库中的用户。administrators should periodically audit the users in a contained database.

使用 guest 帐户访问其他数据库Accessing Other Databases Using the guest Account

数据库所有者和拥有 ALTER ANY USER 权限的数据库用户可以创建包含数据库用户。Database owners and database users with the ALTER ANY USER permission can create contained database users. 在连接到 SQL ServerSQL Server实例上包含的数据库后,如果其他数据库启用了 数据库引擎Database Engineguest 帐户,包含数据库的用户就可以访问 上的其他数据库。After connecting to a contained database on an instance of SQL ServerSQL Server, a contained database user can access other databases on the 数据库引擎Database Engine, if the other databases have enabled the guest account.

在另一个数据库中创建重复用户Creating a Duplicate User in Another Database

某些应用程序可能要求用户可以访问多个数据库。Some applications might require that a user to have access to more than one database. 可以通过在每个数据库中创建相同的包含的数据库来做到这点。This can be done by creating identical contained database users in each database. 在创建带密码的第二个用户时,使用 SID 选项。Use the SID option when creating the second user with password. 下面的示例在两个数据库中创建两个完全相同的用户。The following example creates two identical users in two databases.

USE DB1;  
GO  
CREATE USER Carlo WITH PASSWORD = '<strong password>';   
-- Return the SID of the user  
SELECT SID FROM sys.database_principals WHERE name = 'Carlo';  
  
-- Change to the second database  
USE DB2;  
GO  
CREATE USER Carlo WITH PASSWORD = '<same password>', SID = <SID from DB1>;  
GO  

若要执行跨数据库查询,必须在调用数据库上设置 TRUSTWORTHY 选项。To execute a cross-database query, you must set the TRUSTWORTHY option on the calling database. 例如,如果以上定义的用户 (Carlo) 在 DB1 中,要执行 SELECT * FROM db2.dbo.Table1; ,则数据库 DB1 的 TRUSTWORTHY 设置必须为 on。For example if the user (Carlo) defined above is in DB1, to execute SELECT * FROM db2.dbo.Table1; then the TRUSTWORTHY setting must be on for database DB1. 执行以下代码以将 TRUSTWORTHY 设置为 on。Execute the following code to set the TRUSTWORTHY setting on.

ALTER DATABASE DB1 SET TRUSTWORTHY ON;  

创建复制登录名的用户Creating a User that Duplicates a Login

如果创建了一个有密码的包含数据库用户,所使用的名称与 SQL ServerSQL Server 登录名相同,而且在 SQL ServerSQL Server 登录名进行连接时将包含的数据库指定为初始目录,则 SQL ServerSQL Server 登录名将无法连接。If a contained database user with password is created, using the same name as a SQL ServerSQL Server login, and if the SQL ServerSQL Server login connects specifying the contained database as the initial catalog, then the SQL ServerSQL Server login will be unable to connect. 该连接将被判定为包含数据库上的具有密码主体的包含数据库用户发起,而不是基于 SQL ServerSQL Server 登录名的用户发起。The connection will be evaluated as the contained database user with password principal on the contained database instead of as a user based on the SQL ServerSQL Server login. 这可能导致 SQL ServerSQL Server 登录名遭遇到拒绝服务。This could cause an intentional or accidental denial of service for the SQL ServerSQL Server login.

  • 最佳做法是, sysadmin 固定服务器角色的成员应该始终考虑在连接时不使用初始目录选项。As a best practice, members of the sysadmin fixed server role should consider always connecting without using the initial catalog option. 这样会使登录名连接到 master 数据库,并可避免数据库所有者滥用登录名的可能性。This connects the login to the master database and avoids any attempts by a database owner to misuse the login attempt. 然后,管理员可以通过使用 USE <数据库> 语句更改为包含的数据库。Then the administrator can change to the contained database by using the *USE***database> statement. 您也可以将登录操作的默认数据库设置为包含的数据库,这样会先登录到 master数据库,然后再转而登录到包含的数据库。You can also set the default database of the login to the contained database, which completes the login to master, and then transfers the login to the contained database.

  • 最佳做法是,创建有密码的包含数据库用户时,其名称不得与 SQL ServerSQL Server 登录名相同。As a best practice, do not create contained database users with passwords who have the same name as SQL ServerSQL Server logins.

  • 如果存在重复的登录名,请连接到 master 数据库,但不要指定初始目录,然后执行 USE 命令转到包含的数据库。If the duplicate login exists, connect to the master database without specifying an initial catalog, and then execute the USE command to change to the contained database.

  • 存在包含的数据库时,非包含数据库的用户应连接到 数据库引擎Database Engine ,但不要使用初始目录,或者将非包含数据库的数据库名称指定为初始目录。When contained databases are present, users of databases that are not contained databases should connect to the 数据库引擎Database Engine without using an initial catalog or by specifying the database name of a non-contained database as the initial catalog. 这样就避免了连接到包含的数据库而导致 数据库引擎Database Engine 管理员的直接控制减弱。This avoids connecting to the contained database which is under less direct control by the 数据库引擎Database Engine administrators.

通过更改数据库的包含状态来增加访问Increasing Access by Changing the Containment Status of a Database

具有 ALTER ANY DATABASE 权限的登录名(如 dbcreator 固定服务器角色的成员)以及非包含数据库中具有 CONTROL DATABASE 权限的用户(如 db_owner 固定数据库角色的成员)都可以更改数据库的包含设置。Logins that have the ALTER ANY DATABASE permission, such as members of the dbcreator fixed server role, and users in a non-contained database that have the CONTROL DATABASE permission, such as members of the db_owner fixed database role, can change the containment setting of a database. 如果数据库的包含设置从 NONE 更改为 PARTIALFULL,则可以通过创建有密码的包含数据库用户来授予用户访问权限。If the containment setting of a database is changed from NONE to either PARTIAL or FULL, then user access can be granted by creating contained database users with passwords. 这样就可以在 SQL ServerSQL Server 管理员不知情或不允许的情况下提供访问。This could provide access without the knowledge or consent of the SQL ServerSQL Server administrators. 若要防止将任何数据库更改为包含的数据库,请将 数据库引擎Database Enginecontained database authentication 选项设置为 0。To prevent any databases from being contained, set the 数据库引擎Database Enginecontained database authentication option to 0. 若要阻止有密码的包含数据库用户连接到选定的包含的数据库,请使用登录触发器取消有密码的包含数据库用户进行的登录尝试。To prevent connections by contained database users with passwords on selected contained databases, use login triggers to cancel login attempts by contained database users with passwords.

附加包含的数据库Attaching a Contained Database

通过附加包含的数据库,管理员会向用户授予针对 数据库引擎Database Engine实例的不必要的访问权限。By attaching a contained database, an administrator could give unwanted users access to the instance of the 数据库引擎Database Engine. 担心这种风险的管理员可以使数据库以 RESTRICTED_USER 模式联机,这样会阻止对使用密码的包含数据库用户进行身份验证。An administrator concerned about this risk can bring the database online in RESTRICTED_USER mode, which prevents authentication for contained database users with passwords. 只有通过登录授权的主体才能够访问 数据库引擎Database EngineOnly principals authorized through logins will be able to access the 数据库引擎Database Engine.

用户是使用在其创建时有效的密码要求创建的,并且在附加数据库时不重新检查密码。Users are created using the password requirements in effect at the time that they are created and passwords are not rechecked when a database is attached. 通过将一个允许使用弱密码的包含数据库附加到一个具有更严格密码策略的系统上,管理员可能允许在附加 数据库引擎Database Engine上使用不满足当前密码策略的密码。By attaching a contained database which allowed weak passwords to a system with a stricter password policy, an administrator could permit passwords that do not meet the current password policy on the attaching 数据库引擎Database Engine. 通过要求对附加数据库重置所有密码,管理员可以避免保留弱密码。Administrators can avoid retaining the weak passwords by requiring that all passwords be reset for the attached database.

密码策略Password Policies

可以要求数据库中的密码是强密码,但不能通过可靠的密码策略来保护这些密码。Passwords in a database can be required to be strong passwords, but cannot be protected by robust password policies. 尽可能地使用 Windows 身份验证,以便利用 Windows 提供的更加广泛的密码策略。Use Windows Authentication whenever possible to take advantage of the more extensive password policies available from Windows.

Kerberos 身份验证Kerberos Authentication

有密码的包含数据库用户不能使用 Kerberos 身份验证。Contained database users with passwords cannot use Kerberos Authentication. 尽可能使用 Windows 身份验证,以便利用 Kerberos 等 Windows 功能。When possible, use Windows Authentication to take advantage of Windows features such as Kerberos.

脱机字典攻击Offline Dictionary Attack

有密码的包含数据库用户的密码哈希值存储在包含的数据库中。The password hashes for contained database users with passwords are stored in the contained database. 任何具有数据库文件访问权限的人都可以对未审核系统上有密码的包含数据库用户进行字典攻击。Anyone with access to the database files could perform a dictionary attack against the contained database users with passwords on an unaudited system. 若要减轻这种威胁,请限制对数据库文件的访问,或者只允许使用 Windows 身份验证连接到包含的数据库。To mitigate this threat, restrict access to the database files, or only permit connections to contained databases by using Windows Authentication.

避免使用包含的数据库Escaping a Contained Database

如果某个数据库为部分包含,则 SQL ServerSQL Server 管理员应该定期审核包含的数据库中用户和模块的能力。If a database is partially contained, SQL ServerSQL Server administrators should periodically audit the capabilities of the users and modules in contained databases.

通过 AUTO_CLOSE 拒绝服务Denial of Service Through AUTO_CLOSE

不要将包含的数据库配置为自动关闭。Do not configure contained databases to auto close. 如果关闭,打开数据库对用户进行身份验证会消耗额外的资源,并可能导致拒绝服务攻击。If closed, opening the database to authenticate a user consumes additional resources and could contribute to a denial of service attack.

另请参阅See Also

包含的数据库 Contained databases
迁移到部分包含的数据库Migrate to a partially contained database