Home » Blog » Using SQL Server Schema Permissions to Simplify Permission Management

Using SQL Server Schema Permissions to Simplify Permission Management

2014-01-31 - General, Security

Introduction

Schema comes from the Greek word σχήμα which means as much as "shape" or "configuration". The plural form is schemata (σχήματα).

The schema concept in SQL Server is something unique. While all RDBMSs are using the term "schema", only in SQL Server a schema is not the same as a database.

In SQL Server schemata can be used to group related objects together. That not only helps with organizing objects for easier administration and maintenance. More importantly it simplifies permission management.

Instead of granting a given privilege on every object in a schema, you can just grant that same privilege on the schema itself. This automatically extends that permission to all objects that are created in that same schema, current objects and future ones.

Schema Permission Example

Let's look at an example. First we need to create a schema and a table inside of it:

[sql] CREATE SCHEMA TestSchema1;
GO
CREATE TABLE TestSchema1.tst1(id INT, col1 INT);
INSERT INTO TestSchema1.tst1 VALUES(42, 1);
[/sql]

With schema and table in place, let's grant SELECT on TestSchema1 to the user TestUser1:

[sql] GRANT SELECT ON SCHEMA::TestSchema1 TO TestUser1;
[/sql]

This is the only privilege granted. In particular, we did not grant anything on the table itself. If the permission on the schema extends to the table, TestUser1 should now be able to select from that table. Let's see:

As promised, the permission was transferred automatically and TestUser1 was able to select from our table. Now let's see what happens if we create a new object in that schema:

[sql] CREATE VIEW TestSchema1.tst2 AS SELECT NEWID() a_new_id;
[/sql]

Even though the new object is a view and not a table, the existing SELECT permission automatically applies and TestUser1 is able to select from it:

As you can see, schema permissions apply to all objects to which the given permission is grantable. (It does for example not really make sense to grant SELECT on a procedure.)

Now, just to show that this is indeed due to the privilege granted on the schema, the next example creates a table in the dbo schema:

[sql] CREATE TABLE dbo.tst3(id INT, col1 INT);
INSERT INTO dbo.tst3 VALUES(42, 3);
[/sql]

However, not really to our surprise, TestUser1 can't access that table:

Summary

To simplify permission management, SQL Server allows us to group database objects together into schemata. Instead of granting privileges on each object, we can then just grant the privilege on the schema itself. Such a permission automatically extends to all objects (that that permission can be applied to) in that schema. This includes objects created at a later time.