Flexible Database-Level Roles

January 22, 2009

This is following up from my previous post on Database Roles. In my previous post, I talked about fixed database roles. If you’ve forgotten, go back and re-read the article. I’ll still be here when you get back. So, what exactly are flexible database roles? They are roles that you create in the database. It’s as simple as that. When you start with a new database, there are no flexible roles, just the fixed roles. The upside of this is that you are free to create all of the roles that you need and grant all of the permissions that you desire to these roles. So, where to begin? Let’s start with an empty database:``` – Create our sample database and switch to it CREATE DATABASE TestFixedRoles; GO USE TestFixedRoles; GO

– Create a new user and grant them db_securityadmin CREATE USER user_security WITHOUT LOGIN; CREATE USER user_test WITHOUT LOGIN; GO

EXEC sp_addrolemember N’db_securityadmin’, N’user_security’; GO

So, now we’ve created a new database, a new user, and granted db\_securityadmin to our new user. You might remember from the previous article that db\_securityadmin can’t grant access to fixed database roles. However, db\_securityadmin has no such limitation when dealing with flexible database roles. Now, let’s set up a test user for the purposes of this introduction to flexible database roles. CREATE USER test_user WITHOUT LOGIN; GO

This user is now a member of the PUBLIC role. What kind of access does public have? CREATE SCHEMA test; GO

CREATE TABLE test.t1 (number INT); GO

DECLARE @i INT; SET @i = 0;

WHILE @i < 1000 BEGIN INSERT INTO test.t1 VALUES (@i);

SET @i = @i + 1;

END

EXECUTE AS USER=‘test_user’; GO

– This will fail because we have no access to the test schema SELECT * FROM t1; GO

REVERT GO

None. PUBLIC has no access, thus test\_user has no access. Let’s get test\_user set up with some access. CREATE ROLE test_role; GO

– The scope qualifier ‘::’ is required. GRANT SELECT ON SCHEMA :: test TO test_role; GO

EXEC sp_addrolemember N’test_role’, N’test_user’; GO

EXECUTE AS USER=‘test_user’; GO

– Success! SELECT * FROM t1; GO

REVERT GO

There you have it! We can select from the test schema. How does this help, though, in the real world? For starters, by creating roles and adding users to roles you can streamline managing security through the use of roles rather than having to monitor the permissions assigned to every user, login, Windows user, and Windows group. Second, by combining roles for managing security with schemas and stored procedures, it’s possible to carefully control and define granular access to stored procedures and data. Let’s take a look at AdventureWorks and create an example of how we might want to accomplish this. This procedure just retrieves customers who placed an order between two dates. Pretty simple. CREATE PROCEDURE Sales.GetCustomersWithOrdersBetweenDates ( @StartDate DATETIME, @EndDate DATETIME ) WITH EXECUTE AS OWNER AS

SELECT pc.LastName, pc.FirstName, pc.EmailAddress FROM Sales.SalesOrderHeader AS ssoh INNER JOIN Sales.SalesOrderDetail AS ssod ON ssoh.SalesOrderID = ssod.SalesOrderID INNER JOIN Sales.Customer AS sc ON ssoh.CustomerID = sc.CustomerID INNER JOIN Sales.Individual AS si ON sc.CustomerID = si.CustomerID INNER JOIN Person.Contact AS pc ON si.ContactID = pc.ContactID WHERE ssoh.OrderDate BETWEEN @StartDate AND @EndDate GROUP BY pc.LastName, pc.FirstName, pc.EmailAddress; GO

Next we’ll want to set up some roles: – this is our internal sales personnel CREATE ROLE internal_sales_team; GO

– these are sales people in the field CREATE ROLE field_sales_team; GO

Now we’ll create users for those roles: CREATE USER Iris WITHOUT LOGIN; GO

CREATE USER Frank WITHOUT LOGIN; GO

EXEC sp_addrolemember N’internal_sales_team’, N’Iris’; EXEC sp_addrolemember N’field_sales_team’, N’Frank’; GO

GRANT EXECUTE ON SCHEMA :: Sales TO internal_sales_team; GO

Now let’s test this to see how it works: EXECUTE AS USER = N’Iris’; GO EXEC Sales.GetCustomersWithOrdersBetweenDates ‘20040101’, ‘20040601’; GO REVERT GO

EXECUTE AS USER = N’Frank’; GO EXEC Sales.GetCustomersWithOrdersBetweenDates ‘20040101’, ‘20040601’; GO REVERT GO