PLEASE, Please, please Stop Using Impersonation / EXECUTE AS

Despite features added in SQL Server 2005 (yes, 2005!) that allow for very flexible, granular, and robust security, it is still quite common for people to be using the older, riskier mechanisms of temporarily granting additional privileges.

What follows is an overview of a presentation that I have given a few times, and will continue presenting, on the topic of Module Signing, and why it is far superior to using any combination of Cross-Database Ownership Chaining, Impersonation, or TRUSTWORTHY :

Module Signing or: How I Learned to Stop Using EXECUTE AS and Love Certificates

Common “Problem” Scenarios

Proper security starts with giving users the lowest set of permissions necessary. Quite often we run into a situation where a user is not able to execute something with their minimal permissions. Those situations are most often the following:

  • Need Elevated Permission that is not Grantable

    Certain operations do not have a specific, defined permission that can be granted (e.g. SET IDENTITY_INSERT ON ). In these cases, it is usually required to be in at least one Server-level Role (e.g. sysadmin ) or Database-level Role (e.g. db_owner ), or have been granted a high-level permission (e.g. CONTROL SERVER ), etc.

  • Need Elevated Permission that is not Granular

    Certain operations do have a specific, defined permission, BUT that permission allows access to far more than is desired. For example, sys.dm_exec_query_stats requires the VIEW SERVER STATE Server-level permission. However, VIEW SERVER STATE allows access to most DMVs.

  • Cross-Database Operations

    By default, a process is confined to the Database in which it is initiated. However, it is not uncommon to need to access objects in other Databases. Sometimes, the user doesn’t even have access to the other Database(s).

  • Dynamic SQL

    Dynamic SQL breaks ownership chaining, including Cross-Database Ownership Chaining.

Ownership Chaining

Ownership chaining is the basic security mechanism that allows Stored Procedures, Views, etc to be an API to the data model (i.e. the Tables) so that permissions do not need to be granted to any Tables. Anyone who has permission to access a particular object will be able to access any objects referenced in that object as long as the referenced objects are owned by the User that owns the initial object. Object ownership is determined by:

  • IF principal_id in sys.objects is NOT NULL
    • THEN use principal_id in sys.objects
    • ELSE use principal_id in sys.schemas for schema_id that matches the value in sys.objects

Ownership chaining does not need to be enabled; it is simply how SQL Server works. Assumed permissions apply to DML , SELECT , and EXEC[UTE] statements, but not to DDL statements or statements like SET IDENTITY INSERT ON , TRUNCATE TABLE , etc.

Using Dynamic SQL breaks ownership chaining and permissions will be reevaluated as the Dynamic SQL is being compiled.

Cross-Database Ownership Chaining

By default, ownership chaining only works within a Database and does not extend out to other Database’s (or even up to the Server-level). The simplest way around this restriction is to enable Cross-Database Ownership Chaining. There are two ways to accomplish this:

  1. Enable for all Databases at the Server-level:
    EXEC sp_configure 'cross db ownership chaining', 1;
      RECONFIGURE;

    When this setting is enabled, all Databases are enabled and cannot be disabled individually. When this setting is disabled, Databases can be enabled individually, at the Database-level.

  2. Enable for individual Databases at the Database-level:

    ALTER DATABASE { [database_name] | CURRENT } SET DB_CHAINING ON;

    When the Server-level setting is enabled, this setting has no effect. When the Server-level setting is disabled, this setting is used to enable Databases individually.

Cross-Database ownership chaining requires that the same SID for the owner of the objects exist in both Databases, but the User accessing the object does not need to exist in the other Database(s). However, when using Dynamic SQL, then the User accessing the object must exist in both Databases.

To be clear, Cross-Database Ownership Chaining, just like ownership chaining, cannot be used to elevate permissions. It can only be used to assume basic permissions to allow for DML , SELECT , and EXEC[UTE] statements.

Impersonation

  • “Instead-of” Permissions
  • Account-based security
  • Accomplished using EXECUTE AS
  • EXECUTE AS Clause

    • Part of “CREATE OBJECT” statement
    • Impersonated Principals are always DB level
    • No IMPERSONATE permission needed
  • EXECUTE AS Statement
    • Can do Server-level Logins and DB-level Users
    • Requires IMPERSONATE permission
  • Cross-DB Ownership Chaining:
    • security risk (can spoof User / DB-level)
    • db_ddladmin & db_owner users can create objects for other owners
    • Users with CREATE DATABASE permission can create new databases and attach existing databases
  • Impersonation:
    • If IMPERSONATE is required:
      • can be used any time
      • No granular control over permissions
    • Cross-DB operations need TRUSTWORTHY ON
    • Need to use ORIGINAL_LOGIN() for Auditing
    • Elevated permissions last until process / sub-process ends or REVERT
      TRUSTWORTHY:
    • Bigger security risk
      • can also spoof Logins, such as “sa” !
      • If using SQLCLR Assemblies, no per-Assembly control of ability to be marked as either EXTERNAL_ACCESS or UNSAFE ; all Assemblies are eligible to be marked as either of those elevated permission sets.

Module Signing

  • “In Addition To” Permissions
  • Code-based security
  • Accomplished using ADD SIGNATURE

Can sign modules:

  • Multi-statement Table-Valued Functions (a.k.a. msTVFs)
  • Stored Procedures
  • Scalar Functions
  • Triggers
  • SQLCLR Assemblies

Neither Inline Table-Valued Functions (a.k.a. iTVFs) nor Views can be signed. And, unfortunately, server-scoped DDL Triggers also cannot be signed (please see, and hopefully vote for, my attempt to get this fixed: Allow signing Database DDL Triggers and Server DDL and Logon Triggers – ADD SIGNATURE ).

BENEFITS OF MODULE SIGNING

  • Privileged principal cannot login / connect or be impersonated
  • Very Granular permissions
  • No security holes (e.g. TRUSTWORTHY , Cross-Database Ownership Chaining)
  • Signature is dropped if code is changed !! This effectively forces a code review since the operation will no longer work as the additional permissions are gone until the module is re-signed. If re-signing requires intervention by a DBA, then that provides the DBA an opport
  • Elevated permissions confined to signed code. Unlike Impersonation where the elevated permissions persist until the process / sub-process ends, or the REVERT command is executed, the permissions granted via the signature do not, by default, extend beyond the signed module. This means that you have full control over what code gets the extra permissions. If additional modules in a chain need to be granted additional privileges, then they can either be signed or counter-signed.
  • Multiple Signatures can be used to combine permission “sets”

The Only Valid Use of Impersonation

Starting with SQL Server 2005, the only reason to use Impersonation is for testing, to temporarily become a lower-privileged User to make sure that the security setup actually works as intended.

Conclusion

Starting with the release of SQL Server 2005, it is no longer necessary to use Cross-Database Ownership Chaining, Impersonation, or TRUSTWORTHY to grant additional permissions and/or perform cross-Database functionality. Those mechanisms existed because there was no other way. But, SQL Server 2005 introduced Module Signing which uses Certificates (or Asymmetric Keys) to overcome all of the deficiencies of the older methods.

For more information, please visit: ModuleSigning.info

Tagged:
Impersonation ,
Module Signing ,
Security ,
T-SQL

SQLServerCentral责编内容来自:SQLServerCentral (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » PLEASE, Please, please Stop Using Impersonation / EXECUTE AS

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录