SQL Server – Security: Schemas, Ownership Chaining and the dreaded TRUSTWORTHY flag – Part 1

soapbox_man44_t-147x265_zpsa987e01e

Warning: this post involves me standing on a soapbox: it’s going to be a bit of a rant/preach about why everyone should care about security!  Starting with….

SCHEMAS

When I talk to my developer colleagues, very few of them realise that database schemas are actually a security feature of SQL Server.  They also don’t appreciate that security in a database needs to be designed, it doesn’t just happen.  If it’s considered from an early point in the design of the database and application, then it will be much easier to implement and cause them less headaches in the long run when they ask me for some elevated permission or other and I say “no, because….” and start explaining about ownership chaining and a whole bunch of other security related stuff that makes them roll their eyes at me…. ahem, I digress.

Schemas are an example of a securable in SQL Server.  They should be designed as a collection of objects that the same sets of permissions should be applied on to database principals such as a database role or a database user.  Without them, we would either have to grant blanket permissions at a database level, which may result in wider permissions being granted than we need, or, grant permissions on each object individually, which becomes a laborious task both in terms of implementation and maintenance and for keeping track of who has what permission on what.

In order to implement the permissions granted to a schema, ownership chaining is used.  The schema has an owner, the authorisation under which those objects will run.  The essence of ownership chaining is that objects owned by the same owner have the requisite permissions to interact with each other.  As such, if we create a view, or a stored procedure, which references objects entirely within the same schema and therefore the same owner, we only need to grant permissions on the view/procedure: it’s assumed that by granting permission on the view/procedure, we are granting to do everything that the view/procedure is intended to do.  So: we can give a user permission on a view or procedure, but don’t have to give them permissions on the underlying table.  This is pretty neat!

If another schema is owned by the same user, then exactly the same applies. If we create a procedure that references objects in a different schema, but that schema is owned by the same user, then under the context of that procedure execution access will be granted to those tables. As such, the owner of the schema is an important consideration!  If we just set it to be dbo, as is often the case, then we’re saying we want any object under that schema to be treated as the database owner when it is run.  There are a few exceptions to this; I’ll come back to these later.  Even if the schemas are not owned by the database owner, we’re allowing any procedures or views in either schema full access to objects in both schemas.

Let’s have a look at how this works with an example, first we’ll do some cleanup of the objects we’re going to use:-

/* clean up */
IF OBJECT_ID('Schema2.TestTable2') IS NOT NULL
	DROP TABLE Schema2.TestTable2
GO
IF OBJECT_ID('Schema1.TestTable') IS NOT NULL
	DROP TABLE Schema1.TestTable
GO
IF OBJECT_ID('Schema1.spSekectTestTable') IS NOT NULL
	DROP PROC Schema1.spSelectTestTable
GO
IF SCHEMA_ID('Schema2') IS NOT NULL
	DROP SCHEMA Schema2
GO
IF SCHEMA_ID('Schema1') IS NOT NULL
	DROP SCHEMA Schema1
GO
IF USER_ID('Schema1Owner') IS NOT NULL
	DROP USER Schema1Owner
GO
IF USER_ID('ExecutePermission') IS NOT NULL
	DROP USER ExecutePermission
GO

After this, we’re going to create a new user to do our testing as, a user to own the schema we’re going to create, a test table with a row of data, and a procedure to select from the table, granting our test user the permissions to execute the procedure:-

/* create a user that we will grant execute permission to */
CREATE USER ExecutePermission WITHOUT LOGIN
GO

/* create a user that will own the schema of the procedure that we wish to use*/
CREATE USER Schema1Owner WITHOUT LOGIN
GO

/* create a schema to hold the procedure obejcts */
CREATE SCHEMA Schema1 AUTHORIZATION Schema1Owner
GO

/* create a table and insert a record*/
CREATE TABLE Schema1.TestTable
(ID INT IDENTITY (1,1) PRIMARY KEY)

INSERT Schema1.TestTable
DEFAULT VALUES
GO

/* Create a procedure to select data from the TestTable, and show us the user information */
CREATE PROCEDURE Schema1.spSelectTestTable
AS
BEGIN

	SELECT	USER_NAME()
	,		SUSER_NAME()
	,		UT.principal_id
	,		UT.sid
	,		UT.name
	,		UT.type
	,		UT.usage
	FROM sys.user_token AS UT

	SELECT ID
	FROM Schema1.TestTable AS TT

END
GO

/* Grant our user permission to run the stored procedure */
GRANT EXEC ON Schema1.spSelectTestTable TO ExecutePermission
GO

So, all our test objects are now created, so let’s do some testing. We’re going to impersonate the user, and then try to select from the table directly (we’re expecting this to fail as the user has no direct permissions on the table) then execute the procedure (we’re expecting this to work, because of the ownership chaining from having permissions on the procedure.):-

/* Grant our user permission to run the stored procedure */
GRANT EXEC ON Schema1.spSelectTestTable TO ExecutePermission
GO

/* impersonate the ExecutePermission user*/
EXEC AS USER = 'ExecutePermission'

/* try selecting directly from the SChema1.TestTable*/
SELECT * FROM SChema1.TestTable AS TT

/* try executing the procedure */
EXEC Schema1.spSelectTestTable

/*return to our original context */
REVERT
GO

Here’s the results:-

OwnershipChaining_2.jpgOwnershipChaining_1As you can see, we got the expected permission denied message for the direct select, followed by a successful return of our data from the procedure.  This is exactly what we might want, to allow a user access to some data, but only through a stored procedure with whatever logic and functionality we apply in that procedure.

To  highlight the importance of the schema’s AUTHORIZATION, we’re going to create another schema, and at first give it the dbo user’s authorization, create another table with a row, and amend our procedure to select from that table as well:-

/* create an additional schema, owned by a different user, that we will NOT grant any permissions on */
CREATE SCHEMA Schema2 AUTHORIZATION dbo
GO

/* create a table in this schema */
CREATE TABLE Schema2.TestTable2
(ID INT PRIMARY KEY IDENTITY(1,1))

INSERT INTO Schema2.TestTable2
DEFAULT VALUES
GO

/* alter the procedure to include a select from the new table */
ALTER PROCEDURE Schema1.spSelectTestTable
AS
BEGIN

	SELECT	USER_NAME()
	,		SUSER_NAME()
	,		UT.principal_id
	,		UT.sid
	,		UT.name
	,		UT.type
	,		UT.usage
	FROM sys.user_token AS UT

	SELECT ID
	FROM Schema1.TestTable AS TT

	SELECT ID
	FROM Schema2.TestTable2 AS TT

END
GO

Let’s go ahead and impersonate our test user, and then execute our altered procedure:-

/* impersonate the ExecutePermission user*/
EXEC AS USER = 'ExecutePermission'

/* try executing the procedure */
EXEC Schema1.spSelectTestTable

REVERT
GO

Below are our results; we can still successfully select from the table in Schema1, through ownership chaining, however, because Schema2 is owned by a different user, dbo, we cannot select from that table:-

OwnershipChaining3To allow this permission to be granted, the two schemas would need to be owned by the same user, so we’ll use the ALTER AUTHORIZATION statement to swith schema2 over to our Schema1Owner user:-

/* alter the authorization for Schema2 to be the SAME USER as Schema1 */
ALTER AUTHORIZATION ON SCHEMA::Schema2 TO Schema1Owner
GO

Running under the context of our test user, we’re able to select from a table in both schemas now:-

/* impersonate the ExecutePermission user*/
EXEC AS USER = 'ExecutePermission'

/* try executing the procedure */
EXEC Schema1.spSelectTestTable

REVERT
GO

OwnerhsipChaining4

So, this clearly demonstrates the importance of schema choice from a security point of view, and the owner of each schema. Schemas allow us to group up objects that should have the same security level, and allow a simple way of controlling the access to that group. However, we need to be careful of simply using the same user for schemas if the two sets of security need to be kept apart.

In the next part, I’ll look into some of the permissions that can’t be granted via ownership chaining, and also into how cross database chaining works, in combination with the trustworthy flag, and why I personally think that these are probably a bad idea!

Here’s the clean up script again, to drop all the demo object:-

/* tidy up */
IF OBJECT_ID('Schema2.TestTable2') IS NOT NULL
	DROP TABLE Schema2.TestTable2
GO
IF OBJECT_ID('Schema1.TestTable') IS NOT NULL
	DROP TABLE Schema1.TestTable
GO
IF OBJECT_ID('Schema1.spSekectTestTable') IS NOT NULL
	DROP PROC Schema1.spSelectTestTable
GO
IF SCHEMA_ID('Schema2') IS NOT NULL
	DROP SCHEMA Schema2
GO
IF SCHEMA_ID('Schema1') IS NOT NULL
	DROP SCHEMA Schema1
GO
IF USER_ID('Schema1Owner') IS NOT NULL
	DROP USER Schema1Owner
GO
IF USER_ID('ExecutePermission') IS NOT NULL
	DROP USER ExecutePermission
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s