The SQL Server security model, part 4: execution context

This is the fourth installment in this series on SQL Server security. Today, we’re going to take a closer look at some key security concepts concerning object ownership and ownership chaining as well as execution context and impersonation in SQL Server. This could get technical.

If you haven’t kept up, in previous articles in this series we’ve looked at principals (part 1), securables (part 2) and permissions (part 3). You might want to read up on those subjects first.

Owner

Most securables, from the database on down, have a designated owner. The owner of a securable has unrestricted control of that item (equivalent of the CONTROL permission). Remember that permissions are implicitly inherited as well; the owner of a schema implicitly has CONTROL permission on all user objects in that schema, even if they have a different owner.

Historical note: Once upon a time, like a really long time ago, SQL Server ownership used to be the same thing as schema. This isn’t the case anymore. They are different entities, and you should treat them as such. But you may occasionally still stumble into the odd developer or DBA who doesn’t know this.

Setting ownership

Ownership on a securable is changed using the ALTER AUTHORIZATION statement:

--- Changing database owner:
ALTER AUTHORIZATION ON DATABASE::myDatabase TO myLogin;

--- Changing schema owner:
ALTER AUTHORIZATION ON mySchema TO myUser;

--- Changing an object owner:
ALTER AUTHORIZATION ON mySchema.myObject TO myUser;

--- .. or, resetting the object owner to the schema owner:
ALTER AUTHORIZATION ON mySchema.myObject TO SCHEMA OWNER;

Remember that the owner of a database object (schema, table, view, etc) is always a database principal (i.e. a user), whereas the owner of server-level objects, including databases, are server-level principals (logins).

Impersonation

Impersonation happens when you change a session’s execution context. The currently logged in user or login then assumes the rights and permissions of another user or login. In order to impersonate, the original user or login needs IMPERSONATE permission on the principal to be impersonated.

An example of impersonation, using EXECUTE AS:

--- Change security context by switching to (impersonating)
--- another login:
EXECUTE AS LOGIN='myDomain\myAccount';

--- Do stuff with the permissions of myDomain\myAccount.

--- Change back to the original context:
REVERT;

As you’ve no doubt noticed, granting impersonation permissions does not only allow other principals to gain more permissions by context switching – it may also allow them to perform DDL or DML operations in the database under a different user/login name, which is important to keep track of if you have an auditing mechanism in place in your database or server. Double-check what user or login you’re storing in your audit tables. There are a few different T-SQL functions that tell you not only what the user’s current security context is, but also who the original login (before impersonation) is. The two important ones here are:

SELECT --- Current security context:
       SUSER_SNAME() AS Whoami,
       --- Original security context:
       ORIGINAL_LOGIN() AS WoamiReally;

In an auditing scenario, you’d be wise to log at least the ORIGINAL_LOGIN(), but probably SUSER_SNAME() as well if your system allows people to impersonate.

Using EXECUTE AS in T-SQL modules

In stored procedures, you can specify the execution context of the stored procedure using WITH EXECUTE AS in the header of the module.

CREATE PROCEDURE dbo.sp_myProcedure
WITH EXECUTE AS 'myDomain\myAccount'
AS

SELECT SUSER_SNAME();

GO

Just like the stand-alone EXECUTE AS impersonation that we looked at above, this is also an impersonation. In order to CREATE or ALTER this procedure, you will need the appropriate IMPERSONATE permission. However, there are a few different ways to impersonate within a module:

WITH EXECUTE AS CALLER: This is the default (the one used if you don’t actually specify an EXECUTE AS clause). It executes the contents of the module as the principal calling it.

WITH EXECUTE AS SELF: The module will execute as the principal that created/altered it. In other words, using your permissions.

WITH EXECUTE AS OWNER: The module will execute as the owner of the module (or, lacking one, the owner of the module’s schema).

WITH EXECUTE AS ‘{user}‘: You can, of course, specify any user you wish. Just make sure that user isn’t deleted some time in the future.

Note: EXECUTE AS can be used for a range of different securables, including queues, DML triggers and DDL triggers, functions and stored procedures. For the sake of readability, this article only deals with functions and stored procedures.

Now, before you run off and put EXECUTE AS impersonations in every stored procedure, take a look at another very attractive security mechanism in SQL Server:

Ownership chaining

Ownership chaining is a clever construct in SQL Server that is often misunderstood. It allows you to use views, procedures, functions, etc (commonly referred to as “modules”) as “security proxies” for other database objects, as long as they all share the same owner. “Ownership chaining”, in other words, means that a module has DML and EXECUTE permissions on any other referenced object as long as they have the same owner.

Here’s an example:

--- Set up a test database for the ownership chaining demo:
USE master;
GO
CREATE DATABASE octest;
GO
USE octest;
GO

--- Here are two example schemas:
CREATE SCHEMA aSchema;
CREATE SCHEMA anotherSchema;

--- .. with each one table:
CREATE TABLE aSchema.aTable (
    a int NOT NULL
);

CREATE TABLE anotherSchema.anotherTable (
    a int NOT NULL
);
GO

--- ... and a view that combines those two tables:
CREATE VIEW dbo.aView
AS
SELECT a.a+b.a AS x
FROM aSchema.aTable AS a
CROSS JOIN anotherSchema.anotherTable AS b;
GO

--- Create a user to be the owner of our test objects. Make this user
--- * owner of one of the tables
--- * owner of the schema of the other table, and
--- * owner of the view:
CREATE USER o WITHOUT LOGIN;
GO
ALTER AUTHORIZATION ON aSchema.aTable TO o;
ALTER AUTHORIZATION ON SCHEMA::anotherSchema TO o;
ALTER AUTHORIZATION ON dbo.aView TO o;
GO

--- Here's the "reader" user, with just SELECT permissions on the view:
CREATE USER reader WITHOUT LOGIN;
GO
GRANT SELECT ON dbo.aView TO reader;
GO

--- Now we're ready to try it out. Change context to "reader"..
EXECUTE AS USER='reader';
--- .. and SELECT from the view:
SELECT * FROM dbo.aView;

--- The following, however, will fail because "reader"
--- doesn't have SELECT permission on the base table:
SELECT * FROM aSchema.aTable;

--- Reset the security context:
REVERT;

Note: the only permissions that are chained are SELECT, UPDATE, INSERT, DELETE (DML permissions) as well as EXECUTE.

Practical uses for ownership chaining

The primary application for ownership chaining is similar to that of impersonation – to act as a “security proxy” of sorts. You can use a view, stored procedure or function to read or modify specific information in a controlled manner without granting users access to everything. Here are some examples where ownership chaining can make a lot of sense:

• Performing calculations without having permissions on underlying data.
• Performing changes to data while logging to an audit table – without the user being able to read or write in the audit table.
• Displaying only certain aggregates from a table.
• Returning only a specific rows from a table depending on a user’s application-level permissions.

Viewing ownership chaining

The following DMV query shows you which modules can access other securables using ownership chaining.

SELECT DISTINCT
       --- The module:
       m.[object_id] AS module_id, sch.[name]+'.'+m.[name] AS module,
       --- The referenced object:
       ref.[object_id] AS referenced_id, ref_sch.[name]+'.'+ref.[name] AS referenced,
       --- Common owner principal:
       p.[name] AS [owner]
FROM sys.schemas AS sch
INNER JOIN sys.objects AS m ON sch.[schema_id]=m.[schema_id]
INNER JOIN sys.sql_expression_dependencies AS dep ON m.[object_id]=dep.referencing_id
INNER JOIN sys.objects AS ref ON dep.referenced_id=ref.[object_id]
INNER JOIN sys.schemas AS ref_sch ON ref.[schema_id]=ref_sch.[schema_id]
INNER JOIN sys.database_principals AS p ON ISNULL(m.principal_id, sch.principal_id)=p.principal_id
WHERE ISNULL(m.principal_id, sch.principal_id)=ISNULL(ref.principal_id, ref_sch.principal_id)
ORDER BY 2, 4;

Did I miss anything? Anything you’re left wondering over? Let me know in the comments below!

Check back next week for more, and make sure to like the Facebook page to receive regular updates on new posts.