SQL Server: How to permission schemas?

Question

Inspired by various schema related questions I've seen...

Ownership chaining allows me to GRANT EXECUTE on a stored procedure without explicit permissions on tables I use, if both stored procedure and tables are in the same schema.

If we use separate schemas then I'd have to explicitly GRANT XXX on the the different-schema tables. The ownership chaining example demonstrates that. This means the stored proc executing user can read/write your tables directly.

This would be like having direct access to your instance variables in a class, bypassing getter/setters, breaking encapsulation.

We also use row level security to restrict what someone sees and we apply this in the stored procedures.

So, how can we maintain schema separation and prevent direct table access?

Of course, the question won't apply if you use an ORM or don't use stored procs. But I'm not asking if I should use an ORM or stored proc in case anyone feels the need to enlighten me...

Edit, example

CREATE USER OwnsMultiSchema WITHOUT LOGIN
GO
CREATE SCHEMA MultiSchema1 AUTHORIZATION OwnsMultiSchema
GO
CREATE SCHEMA MultiSchema2 AUTHORIZATION OwnsMultiSchema
GO

CREATE USER OwnsOtherSchema WITHOUT LOGIN
GO
CREATE SCHEMA OtherSchema AUTHORIZATION OwnsOtherSchema
GO

CREATE TABLE MultiSchema1.T1 (foo int)
GO
CREATE TABLE MultiSchema2.T2 (foo int)
GO
CREATE TABLE OtherSchema.TA (foo int)
GO

CREATE PROC MultiSchema1.P1
AS
SELECT * FROM MultiSchema1.T1
SELECT * FROM MultiSchema2.T2
SELECT * FROM OtherSchema.TA
Go
EXEC AS USER = 'OwnsMultiSchema'
GO
--gives error on OtherSchema
EXEC MultiSchema1.P1
GO
REVERT
GO

CREATE PROC OtherSchema.PA
AS
SELECT * FROM MultiSchema1.T1
SELECT * FROM MultiSchema2.T2
SELECT * FROM OtherSchema.TA
Go
GRANT EXEC ON OtherSchema.PA TO OwnsMultiSchema
GO
EXEC AS USER = 'OwnsMultiSchema'
GO
--works
EXEC OtherSchema.PA
GO
REVERT
GO

Edit 2:

• We don't use "cross database ownership chaining"
• Row level security is a red herring and irrelevant: we don't use it everywhere

Answer 1

I fear that either your description or your conception of Ownership Chaining is unclear, so let me start with that:

"Ownership Chaining" simply refers to that fact that when executing a Stored Procedure (or View) on SQL Server, the currently executing batch temporarily acquires the rights/permissions of the sProc's Owner (or the sProc's schema's Owner) while executing that SQL code. So in the case of a sProc, the User cannot use those privs to do anything that the sProc code does not implement for them. Note especially that it never acquires the Identity of the Owner, only it's rights, temporarily (however, EXECUTE AS... does do this).

So the typical approach to leverage this for security is to:

1. Put all of the Data Tables (and all non-security Views as well) into their own Schema, let's call it [data] (though typically [dbo] is used because it's already there and too privileged for the User's schema). Make sure that no existing Users, Schemas or Owners have access to this [data] schema.

2. Create a schema called [exec] for all of the sProcs (and/or possibly any security Views). Make sure that the owner of this schema has access to the [data] schema (this is easy if you make dbo the owner of this schema).

3. Create a new db-Role called "Users" and give it EXECUTE access to the [exec] schema. Now add all users to this role. Make sure that your users only have Connect rights and have no granted access to any other schema, including [dbo].

Now your users can access the data only by executing the sProcs in [exec]. They cannot access any other data or execute any other objects.

I am not sure if this answers your question (because I was uncertain what the question was exactly), so feel free to redirect me.

---

As for row-level security, here is how I always do it with the security scheme above:

1. I always implement row-level security as a series of Views that mirror-wrap every table and compare the User's identity (usually with Suser_Sname() or one of the others) to a security list keyed from a security code in the row itself. These are the Security-Views.

2. Create a new schema called [rows], give it's owner access to the [data] schema and nothing else. Put all of the Security-Views in this schema.

3. Revoke the [exec] owner's access to the [data] schema and instead grant it data access to the [rows] schema.

Done. Now row-level security has been implemented by transparently slipping it between the sProcs and the tables.

---

Finally, here is a stored procure that I use to help me remember how much of this obscure security stuff works and interacts with itself (oops, corrected version of code):

CREATE proc [TestCnxOnly].[spShowProc_Security_NoEX]  as
--no "With Execute as Owner" for this version
--create User [UserNoLogin] without login
--Grant connect on database :: TestSecurity to Guest
--alter database TestSecurity set trustworthy on

--Show current user context:
select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (sproc)]
, suser_sname() as sname
, system_user as system_


--Execute As Login = 'UserNoLogin'
select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (after exec as)]
, suser_sname() as sname
, system_user as system_

EXEC('select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (in Exec(sql))]
, suser_sname() as sname
, system_user as system_')

EXEC sp_ExecuteSQL N'select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (in sp_Executesql)]
, suser_sname() as sname
, system_user as system_'

--Revert
select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (aftr revert)]
, suser_sname() as sname
, system_user as system_

[EDIT: corrected version of code)