Double Abstraction + Principle of Least Priviledge = Easy-to-manage Security Part II

Intoduction

In Part I of this series, we introduced the idea of Double Abstraction as a way to manage permissions with ease and consistency.  The basic concepts are:

1. Use Server and Database custom roles to hold permission grants
2. Add Windows groups to the roles
3. Add users to the Windows Groups

We combine this with the principle of least privilege.  That is, we ensure that only the permissions actually required to perform the duties of the role (in a business sense) are granted to the role.

In this post, we will work through an example to demonstrate some of these ideas.  We'll create a new database, a database schema, a database role, and a SQL user to add to the role.  In Part III, we'll extend the ideas to use Windows groups for management.

So, let's begin!  

1. Create a database:

-- Create a database to demonstrate security principles USE master; GO -- Delete database if it already exists IF db_id('SecureDB') IS NOT NULL BEGIN ALTER DATABASE SecureDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE SecureDB; END CREATE DATABASE SecureDB;

2. Switch to the context of the new database and create a database role:

-- Change the database context to the new database USE SecureDB; GO -- Create a role in the new databse CREATE ROLE SecureRole; GO -- View the attributes of the new role SELECT * FROM sys.database_principals WHERE name = 'SecureRole'; GO

3. Create a database schema and a user with access to it:

-- Create a schema to hold secure objects owned by the new role CREATE SCHEMA SecureSchema AUTHORIZATION SecureRole; GO -- View attributes of new schema. Note that the principal_id matches the one in sys.database_principals SELECT * FROM sys.schemas WHERE name = 'SecureSchema' GO -- Create a database user without a matching SQL Server Login. CREATE USER SecureUser WITHOUT LOGIN WITH DEFAULT_SCHEMA = SecureSchame; GO -- View attributes of new user. Note default schema. SELECT * FROM sys.database_principals where name = 'SecureUser' -- Grant new user permission to create tables in the database GRANT CREATE TABLE TO SecureUser; GO

4. The new schema is owned by the new role and the user has database permissions to create tables. Let's try that:

EXECUTE AS user = 'SecureUser'; CREATE TABLE SecureSchema.SecureTable (SecureId INT); GO -- Switch the execution context back to the caller of the last EXECUTE AS statement. REVERT;

5. Whoops! That didn't work! If you are following along on your own SQL Server instance, you should have seen an error message like this:

Msg 262, Level 14, State 1, Line 42

CREATE TABLE permission denied in database 'SecureDB'.

The thing is, the user needs ALTER TABLE permission in addition to CREATE TABLE. The database schema is owned by the custom role, so let's add the user to that role and try again:

ALTER ROLE SecureRole ADD member SecureUser; -- ALTER ROLE SecureRole DROP member SecureUser GO -- Set the execution context to the newly-created user and try to create a table EXECUTE AS user = 'SecureUser'; SELECT SUSER_NAME() CREATE TABLE SecureSchema.SecureTable (SecureId INT); -- Switch the execution context back to the caller of the last EXECUTE AS statement. REVERT; SELECT SUSER_NAME();

\
6. It worked! The table creation worked. Let's see if we can access it:

EXECUTE AS user = 'SecureUser'; SELECT * from SecureSchema.SecureTable; REVERT;

Success! We can access the table. Actually we can do anything to it. Since the new user is a member of the custom role that owns the schema, we have carte blanche.

Summary

In this section, we have shown how to use a database role to control who can do what.  The role we created is the owner of the schema, so members of the role can do anything, provided other requirements are met.

Next time, we'll look at creating a restricted role with very limited access, applying the principle of least privilege.