Geek Sync | SQL Server Security Basics

What do you need to know to work with SQL Server security properly?

Register for our Geek Sync, SQL Server Security Basics, on Wednesday, July 8, at 11 AM CT, with host K. Brian Kelley.

In this talk, we'll look at the must knows of SQL Server security. We will start with how a person or application connects to SQL Server and the types of authentication SQL Server provides. We will then look at the hierarchical security model SQL Server implements and how this flows down from server all the way down to tables, views, and stored procedures. Afterwards, we will discuss particular security roles which allows access without explicit permissions. Finally, we will look at ownership chaining and how that can also allow a user access to an object because of a reference from a different object.

Speaker: K. Brian Kelley is an author, columnist, Certified Information Systems Auditor (CISA), and former Microsoft Data Platform (SQL Server) MVP (2009-2016) focusing primarily on SQL Server and Windows security. In addition to being a database administrator, he has served as an infrastructure and security architect encompassing solutions with Citrix, virtualization, and Active Directory. Brian is also a Certified Information Systems Auditor (CISA) and has been the head of a financial organization’s computer incident response team. Brian is active in the IT community having spoken at DevConnections, SQL Saturdays, code camps, and user groups.

You can view the session slides here and the replay will be posted in the IDERA Resource Center after the event. Scripts from the session are attached below.

01_Authentication_and_Authorization.sql
-- SQL Server-based login. Use only when you have to.
CREATE LOGIN [SQLServerBasedLogin] WITH PASSWORD = 'S0methingN0tEasilyGuessable!';
GO

/* Windows group. This one is local to the server. If we were on a domain (not possible for the demo)
   we would want to use a domain group instead. */
CREATE LOGIN [BUILTIN\Users] FROM WINDOWS;
GO

-- Create the database we'll be doing backups for.
CREATE DATABASE SQLSecurityBasicsDemo;
GO

-- Switch to the database.
USE SQLSecurityBasicsDemo;
GO

-- Create the user from the SQL Server-based login
CREATE USER [SQLServerBasedLogin] FROM LOGIN [SQLServerBasedLogin];
GO

-- Create the "user" from the Windows group login
CREATE USER [BUILTIN\Users] FROM LOGIN [BUILTIN\Users];
GO

-- Create a user that is not associated with a login
-- We'll use her later in another example
CREATE USER JaneSmith WITHOUT LOGIN;
GO

USE MASTER;
GO

02_Database_Permissions.sql
USE SQLSecurityBasicsDemo;
GO

CREATE SCHEMA Test;
GO 

CREATE TABLE Test.Foo (FooID INT);
GO 

CREATE ROLE ObjectAccessRole;
GO 

GRANT SELECT ON Test.Foo TO ObjectAccessRole;
GO 

ALTER ROLE ObjectAccessRole
ADD MEMBER JaneSmith;
GO 

CREATE USER JohnDoe WITHOUT LOGIN;
GO 

EXECUTE AS USER = 'JaneSmith';
GO 

SELECT * FROM Test.Foo;
GO 

REVERT;
GO 

EXECUTE AS USER = 'JohnDoe';
GO 

SELECT * FROM Test.Foo;
GO 

REVERT;
GO 


03_Setup_Schema_OwnershipChaining_Example.sql
USE [SQLSecurityBasicsDemo];
GO

-- Build two different schema to show ownership chaining
CREATE SCHEMA Business AUTHORIZATION dbo;
GO
CREATE SCHEMA Accomodations AUTHORIZATION dbo;
GO

-- Create database roles to manage security
CREATE ROLE ReservationAgent;
GRANT EXECUTE ON SCHEMA::Business TO ReservationAgent;
ALTER ROLE ReservationAgent
ADD MEMBER JaneSmith;
GO

-- Build objects for accomodations
CREATE TABLE [Accomodations].[Park]
(
	ParkID INT NOT NULL PRIMARY KEY CLUSTERED,
	ParkName VARCHAR(50) NOT NULL
);
GO

INSERT INTO [Accomodations].[Park] (ParkID, ParkName) VALUES (1, 'Barnwell State Park');
INSERT INTO [Accomodations].[Park] (ParkID, ParkName) VALUES (2, 'Edisto Beach State Park');
GO

SELECT ParkID, ParkName FROM [Accomodations].[Park];
GO

CREATE TABLE [Accomodations].[Cabin] 
(
	CabinID INT NOT NULL PRIMARY KEY CLUSTERED,
	ParkID INT NOT NULL,
	CabinNumber INT NOT NULL,
	CONSTRAINT FK_Cabin_Park FOREIGN KEY (ParkID) REFERENCES [Accomodations].[Park] (ParkID)
);
GO

INSERT INTO [Accomodations].[Cabin] (CabinID, ParkID, CabinNumber) VALUES (1, 1, 1);
INSERT INTO [Accomodations].[Cabin] (CabinID, ParkID, CabinNumber) VALUES (2, 1, 2);
INSERT INTO [Accomodations].[Cabin] (CabinID, ParkID, CabinNumber) VALUES (3, 2, 1);
INSERT INTO [Accomodations].[Cabin] (CabinID, ParkID, CabinNumber) VALUES (4, 2, 2);
GO

SELECT CabinID, ParkID, CabinNumber FROM [Accomodations].[Cabin];
GO

CREATE TABLE [Accomodations].[CabinAvailability]
(
	CabinID INT NOT NULL,
	AvailabilityDate SMALLDATETIME NOT NULL,
	AvailabilityFlag CHAR(1) NOT NULL DEFAULT 'Y',
	CONSTRAINT PK_CabinAvailability PRIMARY KEY CLUSTERED (AvailabilityDate, CabinID)
);
GO

INSERT INTO [Accomodations].[CabinAvailability] (CabinID, AvailabilityDate) VALUES (1, '2020-12-18');
INSERT INTO [Accomodations].[CabinAvailability] (CabinID, AvailabilityDate) VALUES (1, '2020-12-19');
INSERT INTO [Accomodations].[CabinAvailability] (CabinID, AvailabilityDate) VALUES (2, '2020-12-18');
INSERT INTO [Accomodations].[CabinAvailability] (CabinID, AvailabilityDate) VALUES (2, '2020-12-19');
INSERT INTO [Accomodations].[CabinAvailability] (CabinID, AvailabilityDate) VALUES (3, '2020-12-18');
INSERT INTO [Accomodations].[CabinAvailability] (CabinID, AvailabilityDate) VALUES (3, '2020-12-19');
INSERT INTO [Accomodations].[CabinAvailability] (CabinID, AvailabilityDate) VALUES (4, '2020-12-18');
INSERT INTO [Accomodations].[CabinAvailability] (CabinID, AvailabilityDate) VALUES (4, '2020-12-19');
GO

SELECT CabinID, AvailabilityDate, AvailabilityFlag FROM [Accomodations].[CabinAvailability];
GO


CREATE TABLE [Business].[Reservation]
(
	ReservationID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	CabinID INT NOT NULL,
	StartDate SMALLDATETIME NOT NULL,
	EndDate SMALLDATETIME NOT NULL,
	CONSTRAINT FK_Reservation_Cabin FOREIGN KEY (CabinID) REFERENCES [Accomodations].[Cabin] (CabinID)
);
GO

CREATE PROC [Business].[ScheduleReservation]
  @CabinID INT,
  @StartDate SMALLDATETIME,
  @EndDate SMALLDATETIME
AS
	BEGIN
	  SET NOCOUNT ON;
	  
	  IF NOT EXISTS (SELECT AvailabilityFlag FROM [Accomodations].[CabinAvailability] WHERE AvailabilityFlag = 'N' AND CabinID = @CabinID AND AvailabilityDate BETWEEN @StartDate AND @EndDate)
		  BEGIN
			  BEGIN TRAN;
			  
			  INSERT INTO [Business].[Reservation] (CabinID, StartDate, EndDate) VALUES (@CabinID, @StartDate, @EndDate);
			  
			  UPDATE [Accomodations].[CabinAvailability] 
			  SET AvailabilityFlag = 'N'
			  WHERE AvailabilityDate BETWEEN @StartDate AND @EndDate
			  AND CabinID = @CabinID;
			  
			  COMMIT TRAN;
			  
			  RETURN(0);
		  END;
	  ELSE
	    RETURN(1);
	END;
GO

USE MASTER;
GO
04_Demonstrate_Schema_OwnershipChaining.sql
USE [SQLSecurityBasicsDemo];
GO

CREATE USER [NoPermissions] WITHOUT LOGIN;
GO

-- This will fail. No permissions.
EXECUTE AS USER = 'NoPermissions';
GO 

SELECT * FROM [Accomodations].[Cabin];
GO

REVERT;
GO

EXECUTE AS USER = 'NoPermissions';
GO 

-- This will fail, too. Again, no permissions
DECLARE @Result INT;

EXECUTE @Result = [Business].[ScheduleReservation]
  @CabinID = 2,
  @StartDate = '2020-12-18',
  @EndDate = '2020-12-19';

SELECT @Result;
GO

REVERT;
GO

-- This will also fail. No permissions.
EXECUTE AS USER = 'JaneSmith';
GO 

SELECT * FROM [Accomodations].[CabinAvailability];
GO

REVERT;
GO

-- This will work! The user has EXECUTE rights on the Business schema.
EXECUTE AS USER = 'JaneSmith';
GO 

DECLARE @Result INT;

EXECUTE @Result = [Business].[ScheduleReservation]
  @CabinID = 2,
  @StartDate = '2020-12-18',
  @EndDate = '2020-12-19';

SELECT @Result;
GO

REVERT;
GO

SELECT CabinID, AvailabilityDate, AvailabilityFlag FROM [Accomodations].[CabinAvailability] ORDER BY AvailabilityFlag, AvailabilityDate;
SELECT CabinID, StartDate, EndDate FROM [Business].[Reservation];

USE MASTER;
GO
99 - Clean Up Script.sql
USE master;
GO

DROP DATABASE SQLSecurityBasicsDemo;
GO

DROP LOGIN [SQLServerBasedLogin];
GO

DROP LOGIN [BUILTIN\Users];
GO