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
Iris can successfully execute the stored procedure, despite her user not having access to the Sales schema because she is a member of the internal_sales_team role which does have execute permissions on the Sales schema. However, Frank cannot execute the stored procedure since the field_sales_team role does not have access to the Sales schema. If you try to run the SQL from this stored procedure as either Frank or Iris the SQL will fail since neither user has select permissions on the Sales or Person schemas.
As I mentioned earlier, through a careful combination of users, logins, roles, Windows users, and Windows groups you can assemble a very secure, robust security infrastructure in SQL Server that can handle a variety of tasks while make your administrative life a lot easier.
