Home » Blog » Database Level Object Permissions – Should you use them?

Database Level Object Permissions – Should you use them?

2014-02-01 - General, Security

Introduction

Yesterday I wrote about how to use schema permissions to simplify permission management. Today I would like to introduce you to a method that drives that concept to the extreme: Database level object permissions.

Before we get started however I need to put out a big disclaimer: Using database level object permissions is an edge case and in most cases not a security best practice. Before you implement this, re-read my post about the Principle of Least Privilege.

Database Level Object Permission Example

As with schema permissions, if you grant the e.g. SELECT privilege on the database, it automatically applies to all objects in that database (that can be SELECTed from). And like in the case of the schema permissions, this includes objects created at a later time, even if they reside in a schema that also was created after the privilege was granted.

I use the term database level object permission to describe a privilege that is granted on the database but, like the SELECT privilege, actually applies to objects within the database. As there is no difference between how these database permissions and other database permissions are granted and managed, you will often find them being referred to as just "database permissions".

To see database level object permissions in action we first need a database. The following script creates a database and then also creates a user and a table in a new schema:

[sql] CREATE DATABASE TestDatabase1;
GO
USE TestDatabase1;
GO
CREATE LOGIN TestLogin1 WITH PASSWORD='********', CHECK_POLICY = OFF;
CREATE USER TestUser1 FOR LOGIN TestLogin1;
GO
CREATE SCHEMA TestSchema1;
GO
CREATE TABLE TestSchema1.tst1(id INT, col1 INT);
INSERT INTO TestSchema1.tst1 VALUES(42, 1);
[/sql]

With those objects in place, we can grant the SELECT privilege on our new database TestDatabase1 to the TestUser1:

[sql] GRANT SELECT ON DATABASE::TestDatabase1 TO TestUser1;
[/sql]

Now we expect TestUser1 to be able to SELECT from that table:

It worked! I also claimed that this would work for newly created objects too. To prove that we need to create a new object first:

[sql] CREATE SCHEMA TestSchema2;
GO
CREATE VIEW TestSchema2.tst2 AS SELECT NEWID() a_new_id;
[/sql]

To show that this concept not only works for tables, I decided to create a view this time instead of a table. Because the database permission is in place already, TestUser1 should have access to this new view without us doing anything else.

Even though neither the schema nor the view itself existed at the time the SELECT privilege was granted, the permission is automatically extended to include the new object.

Should you or should you not?

As I said in the beginning, while database permissions make permission management very easy, they also make it easy to not follow the Principle of Least Privilege anymore. But that principle should always guide us when we designing our database security.

That means in general, you don't want to use this permission type. But in some situations is might be appropriate to give a principal some type of access on the entire database. In that case, database permissions provide an easy way to make it happen.

Summary

An object access privilege granted on the database allows the grantee to access all objects in that database, even objects created at a later time. While this is a powerful way to give a principal access to a large number of objects, the principle of least privilege reminds us, that in most cases it is not a best practice to grant all inclusive permissions.