Thursday, July 30, 2015

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.

Friday, July 17, 2015

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

Introduction

Securing a SQL Server instance is critical to every business.  Whether to comply with regulatory requirements, pass external audits with flying colors or to ensure proper restrictions on access to sensitive or financial data, it is imperative that database resources are only accessed by those with the need to do so and in a manner matching requirements.  This is the first in a series of articles on managing Security in SQL Server.

Challenges

Your database is accessed by a wide variety of people with different needs.  You need to be able to secure database assets in a  manageable way.  All too often we see implementations where every user has serveradmin rights and/or database owner (dbo) rights on one or more databases in some SQL Server instance.  This is a recipe for disaster!  Even the most conscientious user will make mistakes and the careless, disgruntled and even criminally-oriented users can wreak havoc, destroy valuable data or even siphon it off to sell to the highest bidder.

We know that SQL Server has the tools to GRANT or DENY access to its objects but all too often get frustrated managing user-by-user.  This is where the double-abstraction approach shines.

Double Abstraction

The first part of Double Abstraction (DA for short) can be found by reversing the letters in the acronym.  AD, or Active Directory allows us to create groups so that birds of a feather can flock together -- that is, so that users with similar requirements can be managed as a unit by placing them into a suitable group.  So, the first step, is to identify the sorts of actions different groups might need.  A reporting team needs to be able to read some tables from one or more databases; an ETL job needs to be able to load tables; a web application needs to be able to query and possibly update certain data; auditors need to be able to monitor the security of the database; administrators at various levels of responsibility need to manage the server, create databases, backup/restore and so on.  Each of these functions (and probably others, depending on your requirements) deserves its own group.

The second part of DA exploits database roles and server roles.  SQL Server comes with many out of the box, but for our purposes it makes sense to define custom database roles and (in SQL Server 2012) custom server roles.  Each role would be given just the privileges corresponding to the actual needs of the various groups.  For example, access to certain schemas, execute access on some stored procedures, the ability to create databases, tables and other objects, the ability to view all of these privileges and so on.  We set up these roles using the Principle of Least Privilege: each role has just the privileges actually required and no more.

To bring these together -- AD groups and DB/Server roles -- we use the ALTER ROLE ADD MEMBER command (or sp_addrolemember system stored procedure).  Say that we have created an AD group Corp\SalesReports and a database role in the Sales database called SalesReaders.  Then we can:
  1. Add domain users (and other groups) to the Corp\SalesReports group
  2. Add the AD group to the SalesReaders role like this:

ALTER ROLE SalesReaders Add Member [Corp\SalesReports]

Next time, we'll dig deeper and add more examples.

Summary

Managing security in SQL Server is made easier with the principle of Double Abstraction: Add users to groups; create roles in the database to cover the functions; add the groups to roles.

Friday, July 10, 2015

Alternative to cursor-based code for maintenance operations

Introduction

Many maintenance operations in SQL Server require us to iterate over some list and perform one or more operations against each item in the list. For example, perhaps you want to backup a number of databases or rebuild a number of indexes. Since the basic BACKUP and ALTER INDEX commands cannot take variables for database or index names, we often see a pattern like this:

DECLARE mycursor as CURSOR FOR SELECT ... OPEN mycursor DECLARE @var1 ... FETCH NEXT FROM mycursor into @var1 ... WHILE @@FETCH_STATUS = 0 BEGIN -- do something with @var1 FETCH NEXT mycursor into @var1 END CLOSE mycursor DEALLOCATE mycursor

In Real Life

For a real-life example, consider ETL (Extract, Transform and Load).  A typical sequence runs like this:

  1. Extract rows from some source
  2. Change/Add (transform) the data
  3. Load the rows into some target table
Often, prior to step 3, it is helpful to disable the non-clustered indexes (NCIs), then re-build them when the load is done.  (As a side note, if your table has a clustered index, and you ensure proper ordering of your data to be loaded, you want the clustered index.  You can't disable it, since that effectively disables access to the table (since the clustered index is the table)).

Using a Cursor

Here is a script that will disable the NCIs for a table in the AdventureWorks database:


DECLARE cur CURSOR READ_ONLY FAST_FORWARD FOR SELECT i.NAME ,o.NAME FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id = o.object_id INNER JOIN sys.schemas s ON s.schema_id = o.schema_id WHERE i.type_desc = 'NONCLUSTERED' AND o.type = 'U' AND o.NAME = 'EmployeeDepartmentHistory' AND s.NAME = 'HumanResources'; OPEN cur; DECLARE @ix_name SYSNAME, @db_name SYSNAME; FETCH NEXT FROM cur INTO @ix_name, @db_name; WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'ALTER INDEX ' + @ix_name + ' ON ' + @db_name + ' DISABLE;' + CHAR(13) + CHAR(10); FETCH NEXT FROM cur INTO @ix_name, @db_name; END; CLOSE cur; DEALLOCATE cur;


This does the job quite handily.  We can even use similar logic to rebuild the indexes when the load is done (or simply REBUILD ALL).  What I'd like to show is a method to do the same without using a cursor.

Using a set-based operation

The basic idea is to build up the ALTER INDEX commands as a single NVARCHAR string, then use sp_executesql to run it.  The code looks like this:


DECLARE @sql AS NVARCHAR(MAX) = ( SELECT 'ALTER INDEX ' + i.NAME + ' ON ' + o.NAME + ' DISABLE;' + CHAR(13) + CHAR(10) FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id = o.object_id INNER JOIN sys.schemas s ON s.schema_id = o.schema_id WHERE i.type_desc = 'NONCLUSTERED' AND o.type = 'U' AND o.NAME = 'EmployeeDepartmentHistory' AND s.NAME = 'HumanResources' FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'); PRINT @sql;


As you can see, there is less code to write and debug,  I also find it less visually distracting.  The net results are exactly the same.  The performance is also the same, except for the front-end job of building the query string.  As is usually the case, the set-based code from the second example out-performs the first.  However, since we are doing this for maintenance-type jobs, there are not millions of rows (perhaps only hundreds or thousands) so the run time of the front-end code is overwhelmed by the run time of the executed code.

Summary

For many small jobs where a cursor seems a natural way to go, there is an alternative using a set-based operation to construct a list of commands and running the list in one execution.  Run times are essentially the same but the code can be more compact and done in just two statements (build the list, run the list) instead of the several statements required by a cursor-based approach.


Friday, July 3, 2015

Set operations in SQL Server - Part 2: ANY() and ALL()

In the previous post Set operations in SQL Server - Part 1, we looked at the basic set options Union, Intersection, Complement (or subtraction) and how they can be used and combined.  We also looked at the problem of determining table equality in a relational database -- especially when duplicate rows are permitted.  In this installment, we will look at various ways of testing set membership.

Set membership is a property of an item that may or may not be a member of a given set.  In database terms, membership implies that there exists some row in a table that satisfies the membership test.  This usually means that one or more columns contain data that satisfy some constraint such as equality or inequality.  Testing for set membership is an essential operation in processing data in a relational database, such as SQL Server.

Starting easy

Let's start off with a simple example.  Suppose you have a table that contains information on bicycles.  Perhaps it looks something like this:

CREATE TABLE Bicycle ( BicycleId INT , ModelName VARCHAR(50) , Manufacturer VARCHAR(50) , Cost DECIMAL(6, 2) ) -- Add some data INSERT INTO Bicycle (BicycleId, ModelName, Manufacturer, Cost) VALUES (1, '700', 'Trek', 1000.01), (2, '500', 'Giant', 600.00)

Now, suppose this table was populated with various bike data and you want to see if there are any bicycles that cost over $1,000.00.  A typical query might look like:

IF EXISTS(SELECT * FROM Bicycle WHERE Cost > 1000.00) PRINT 'There is at least one bicycle costing over $1,000.00'

This is simple test for membership that prints a message if the constraint is satisfied. Note that it uses the function EXISTS(). There are actually four functions available to help us with membership testing like this: EXISTS(), IN(), ANY() (a.k.a SOME()) and ALL(). You're probably familiar with EXISTS() and IN() and may have used them before. Perhaps you are curious about the ANY() and ALL() functions and what they can do for you.

Using ANY()

Let's rewrite the sample query using the ANY() function instead:

IF ANY (SELECT Cost FROM Bicycle) > 1000.00 PRINT 'There is at least one bicycle costing over $1,000.00'

This query returns the same result as the first. It is actually a more direct translation of the requirement and is shorter to boot! Before we go further though, I'm going to rewrite this query this way:

IF 1000 < ANY (SELECT Cost FROM Bicycle) PRINT 'There is at least one bicycle costing over $1,000.00'

Why did I do that? Well, sometimes the query inside the call to the ALL() function can be long, making it harder to spot the condition being tested.

Using ALL()

Now that we've seen how ANY() operates, what about ALL()?  It turns out that this is fairly intuitive. The function performs the desired comparison against all rows in the query and only returns True if the comparison is True for every member examined. Sticking with out bicycle example:

IF 1000 < ALL (SELECT Cost FROM Bicycle) PRINT 'All bicycles cost more than $1,000.00'

If you try this against the sample data inserted above, you should receive no message, since there is at least one bicycle (the Giant 500) that costs less than $1,000.00.

Negating ANY() and ALL()

SQL allows for these operators when using ANY() and ALL():
    { = | <> | != | > | >= | !> | < | <= | !< } 
 
However the first time you use the negative forms, you may be surprised at the results.  For example, if you run this query:

IF 1000 !< ANY (SELECT Cost FROM Bicycle) PRINT 'No bicycle costs less than $1,000.00'

you might be surprised to see that the message is indeed printed! If so, that is probably because you are reading the query in the ordinary English meaning of the command, "If there is no bicycle that costs less than $1,000.00, print the message." However, SQL sees it differently. You see,

    <condition> ANY()

is really short hand for:

    value <condition> first item OR value <condition> second item, etc.

With our sample data, negating that:

    1000.00 !< ANY(...)

is executed as:

    1000.00 !< 1000.01 OR 1000.00 !< 600.00

Clearly. 1000.00 > 600.00 so the expression evaluates to True and the message is printed.  You might have expected behavior similar to:

IF NOT EXISTS(SELECT * FROM Bicycle WHERE Cost < 1000.00) PRINT 'No bicycle costs less than $1,000.00'

which would result in no message, but NOT EXISTS() is not the same as !< ANY() as shown. In fact, this is a case where using the alias SOME() can make the intent clearer.  That is:

IF 1000 !< SOME (SELECT Cost FROM Bicycle) PRINT 'No bicycle costs less than $1,000.00'

more clearly indicates what we're asking for. What about negating the ALL() function? Well, similar to ANY(), ALL() is evaluated like this:

    value <condition> first item AND value <condition> second item etc. So, this query:

IF 1000.00 !< ALL (SELECT Cost FROM Bicycle) PRINT 'No bicycle costs less than $1,000.00'

is executed as:

    1000.00 !< 1000.01 AND 1000.00 !< 600.00

which evaluates to False, so the message is not printed.

Note:  We can rewrite the !< ANY() query (that didn't work) to achieve the desired result in this way:

IF NOT 1000 < ALL (SELECT Cost FROM Bicycle) PRINT 'No bicycle costs less than $1,000.00'

which, when you think about it, makes perfect sense!

Summary

ANY() and ALL() are valuable functions in SQL Server that can be used to write succint queries that reflect the business logic.  However, we need to be careful when using them with negative comparisons, since their execution differs from the normal, English reading.