Simplifying Security Using Schemas

One of my favorite aspects of schemas (which were introduced in 2005) is the ability easily manage permissions. For those getting caught in terminology, schemas are the new abstraction layer that replace object owners. In SQL 2000, every object had an owner. And that owner was a user. In most cases the user was dbo. So your stored procedures would have a naming convention of dbo.spExecuteMe. With schemas, the model has changed.

While object owners were actual users, schemas are not. Schemas are abstract objects used to separate owners from the objects. The beauty of this is that you can assign a user access to an entire schema, which may be comprised of hundreds of objects. Instead of assigning permissions at the object level, you can say, “Any user assigned to the role of ‘ExecApp’ will be able to execute any object that is created within the schema ‘App’. This way, by simply adding a user to the ExecApp role, he will inherently be able to execute any procedure in the application schema. Likewise, any procedure created within the schema ‘App’, will automatically be able to execute that procedure.

So how do you do it? Start by Creating a schema.

[cc lang=”sql”]

CREATE SCHEMA App

[/cc]
Next, transfer stored procedures to this schema, or simply create stored procedures with this schema.

To transfer:

[cc lang=”sql”]

ALTER SCHEMA App TRANSFER dbo.spGetCustomers

[/cc]
Lastly, create a role that will be allowed to execute procedures in the App schema; Add a user to the role; and then allow the role to execute anything on the schema App.

[cc lang=”sql”]

CREATE ROLE [ExecApp] AUTHORIZATION [dbo]

EXEC sp_addrolemember N’ExecApp’, N’JohnJacobs’

GRANT EXECUTE ON SCHEMA::[App] TO [ExecApp]

[/cc]
Viola!