Recently, I encountered at work the true meaning of ownership chaining and a security risk it can cause. I was asked to define a login for a web service for the sole purpose of executing one and only one stored procedure. The stored procedure performs a simple select on a single column from a workers table that also contains classified information on the workers.
In order to be able to follow the case, please run the scripts in figure 1 and figure 2. The first one creates a database by the name of [oChaining] and the second creates two schemas ([Lists] and [operation]) and two tables ([list].[Genders] for gender types and [operation].[workers] that contains details about the workers, including a name column that is allowed to be displayed and a salary column that we don’t want to disclose to the web service on any account).
Figure 1
Figure 1
Transact-SQL
Use [master] GO IF DB_ID (N'oChaining') IS NOT NULL BEGIN ALTER DATABASE oChaining SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE oChaining; END; GO CREATE DATABASE oChaining ON PRIMARY ( NAME = N'oChain', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL12.SQLMADEIRAMSSQLDATAoChain.mdf' , SIZE = 5120KB , FILEGROWTH = 10% ) LOG ON ( NAME = N'oChain_log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL12.SQLMADEIRAMSSQLDATAoChain_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10%) COLLATE Hebrew_CI_AS GO ALTER DATABASE oChaining SET RECOVERY SIMPLE GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Use [master]
GO
IF
DB_ID (N'oChaining') IS NOT NULL
BEGIN
ALTER DATABASE
oChaining
SET
SINGLE_USER
WITH
ROLLBACK IMMEDIATE;
DROP DATABASE
oChaining;
END;
GO
CREATE DATABASE
oChaining
ON PRIMARY
( NAME = N'oChain',
FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL12.SQLMADEIRAMSSQLDATAoChain.mdf' ,
SIZE = 5120KB ,
FILEGROWTH = 10% )
LOG ON
( NAME = N'oChain_log',
FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL12.SQLMADEIRAMSSQLDATAoChain_log.ldf' ,
SIZE = 2048KB , FILEGROWTH = 10%)
COLLATE Hebrew_CI_AS
GO
ALTER DATABASE
oChaining
SET RECOVERY
SIMPLE
GO
Figure 2
Figure 2
Transact-SQL
Use [oChaining] GO IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Operation') BEGIN EXEC('CREATE SCHEMA [Operation] AUTHORIZATION [dbo]') END IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Lists') BEGIN EXEC('CREATE SCHEMA [Lists] AUTHORIZATION [dbo]') END IF NOT EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID('[Lists].[Genders]') = Object_id AND TYPE = N'U') BEGIN CREATE TABLE Lists.Genders ( Id TINYINT NOT NULL CONSTRAINT pk_Genders_c_Id PRIMARY KEY CLUSTERED , Name NVARCHAR(50) NOT NULL ) ON [PRIMARY]; END; GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID('[operation].[workers]') = Object_id AND TYPE = N'U') BEGIN CREATE TABLE [operation].[workers] ( Id INT NOT NULL CONSTRAINT pk_workers_c_Id PRIMARY KEY CLUSTERED , Name NVARCHAR(50) NOT NULL , Gender TINYINT NOT NULL , Salary NUMERIC(7,2) NOT NULL ) ON [PRIMARY]; END; GO INSERT INTO Lists.Genders (Id , Name) VALUES (1, N'Male'),(2,N'Female' ) GO INSERT INTO Operation.workers (Id,Name,Gender,Salary) VALUES (1,N'Christine McVie',2,17000.00),(2,N'Stevie Nicks',2,24000.00), (3,N'Lindsey Buckingham',1,21000.00),(4,N'John McVie',1,13000.00), (5,N'Mick Fleetwood',1,18000.00) GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
Use [oChaining]
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Operation')
BEGIN
EXEC('CREATE SCHEMA [Operation] AUTHORIZATION [dbo]')
END
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Lists')
BEGIN
EXEC('CREATE SCHEMA [Lists] AUTHORIZATION [dbo]')
END
IF NOT EXISTS
(SELECT *
FROM sys.objects
WHERE OBJECT_ID('[Lists].[Genders]') = Object_id AND TYPE = N'U')
BEGIN
CREATE TABLE
Lists.Genders
(
Id TINYINT NOT NULL CONSTRAINT pk_Genders_c_Id PRIMARY KEY CLUSTERED ,
Name NVARCHAR(50) NOT NULL
)
ON
[PRIMARY];
END;
GO
IF NOT EXISTS
(SELECT *
FROM sys.objects
WHERE OBJECT_ID('[operation].[workers]') = Object_id AND TYPE = N'U')
BEGIN
CREATE TABLE
[operation].[workers]
(
Id INT NOT NULL CONSTRAINT pk_workers_c_Id PRIMARY KEY CLUSTERED ,
Name NVARCHAR(50) NOT NULL ,
Gender TINYINT NOT NULL ,
Salary NUMERIC(7,2) NOT NULL
)
ON
[PRIMARY];
END;
GO
INSERT INTO
Lists.Genders (Id , Name)
VALUES
(1, N'Male'),(2,N'Female' )
GO
INSERT INTO
Operation.workers (Id,Name,Gender,Salary)
VALUES
(1,N'Christine McVie',2,17000.00),(2,N'Stevie Nicks',2,24000.00),
(3,N'Lindsey Buckingham',1,21000.00),(4,N'John McVie',1,13000.00),
(5,N'Mick Fleetwood',1,18000.00)
GO
After a short discussion, the DBA team decided to implement the request by doing as follows (all are included in the script in figure 3):
Create a LOGIN and a USER for it.
Create a SCHEMA called ‘classified’.
Create the procedure under the schema.
GRANT execute permission on the procedure to the user ‘classified’
GRANT select only to the column selected and to the column used in the predicate (compared against the input parameter of the procedure).
Figure 3
Figure 3
Transact-SQL
USE [master] GO IF NOT EXISTS (SELECT name FROM master.sys.server_principals WHERE name = 'Classified') BEGIN CREATE LOGIN [Classified] WITH PASSWORD=N'Aa123456', DEFAULT_DATABASE=[oChaining], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON END; GO Use [oChaining] GO IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = 'Classified') BEGIN CREATE USER [Classified] FOR LOGIN [Classified] END; GO IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Classified') BEGIN EXEC('CREATE SCHEMA [Classified] AUTHORIZATION [dbo]') END /* Stored Procedure */ IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[Classified].[usp_GetMales]') AND TYPE IN (N'P', N'PC')) EXEC(N'CREATE PROCEDURE [Classified].[usp_GetMales] AS RETURN ') GO ALTER PROCEDURE [Classified].[usp_GetMales] @Gender INT AS BEGIN SELECT Name --* FROM [Operation].[workers] WHERE Gender = @Gender END; GO /* Permissions */ GRANT EXECUTE ON [Classified].[usp_GetMales] TO [classified] GO GRANT SELECT ON [Operation].[workers] ([Id]) TO [Classified] GO GRANT SELECT ON [Operation].[workers] ([name]) TO [Classified] GO GRANT SELECT ON [Operation].[workers] ([Gender]) TO [Classified] GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
USE [master]
GO
IF NOT EXISTS (SELECT name FROM master.sys.server_principals WHERE name = 'Classified')
BEGIN
CREATE LOGIN [Classified] WITH PASSWORD=N'Aa123456', DEFAULT_DATABASE=[oChaining], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
END;
GO
Use [oChaining]
GO
IF NOT EXISTS
(SELECT name
FROM sys.database_principals
WHERE name = 'Classified')
BEGIN
CREATE USER [Classified] FOR LOGIN [Classified]
END;
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Classified')
BEGIN
EXEC('CREATE SCHEMA [Classified] AUTHORIZATION [dbo]')
END
/* Stored Procedure */
IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[Classified].[usp_GetMales]')
AND TYPE IN (N'P', N'PC'))
EXEC(N'CREATE PROCEDURE [Classified].[usp_GetMales] AS RETURN ')
GO
ALTER PROCEDURE [Classified].[usp_GetMales]
@Gender INT
AS
BEGIN
SELECT Name --*
FROM [Operation].[workers]
WHERE Gender = @Gender
END;
GO
/* Permissions */
GRANT EXECUTE ON [Classified].[usp_GetMales] TO [classified]
GO
GRANT SELECT ON [Operation].[workers] ([Id]) TO [Classified]
GO
GRANT SELECT ON [Operation].[workers] ([name]) TO [Classified]
GO
GRANT SELECT ON [Operation].[workers] ([Gender]) TO [Classified]
GO
All was tested successfully (execute the procedure as user [Classified]), but a colleague wasn’t 100% satisfied. Something was bothering him and it had to do with ownership chaining and a security risk it can cause. “What if,” he asked, “the select in the procedure will not be only on a certain column but on some other classified columns? Will these columns be displayed as well?” We decided to put his suspicions to test by changing the query inside the procedure to SELECT *, and oops… All the columns were proudly displayed – not good, not good at all!
What is the cause of this “breach” of security? Well, after a short investigation, the culprits were found – Firstly, the ownership chaining and secondly, whoever decided to leave the schema owner of the ‘Classified’ schema as dbo (the careless DBA team). Before I continue to explain exactly what happened I would like to define very briefly the term ownership chaining (with the help of BOL and the illustration in figure 5):
When multiple database objects access each other sequentially, the sequence is known as a chain. When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.
In our case, the procedure belongs to the [Classified] schema and this schema owner is [dbo]. The SELECT inside the procedure is on table [Operation].[Workers] and the [operation] schema is also owned by [dbo].Therefore, although we explicitly authorized the [Classified] user to select the [Name] column only, this user will be able to select all the columns due to ownership chaining and the lack of permission evaluation. What is the solution? Change the owner of the [classified] schema to [classified] (in that case, the GRANT EXECUTE to the procedure is unnecessary – see the command in figure 4). Try it – and see for yourself.
Figure 4
Figure 4
Transact-SQL
/* Change owner of Classified schema */ ALTER AUTHORIZATION ON SCHEMA::[Classified] TO [classified] GO EXEC AS LOGIN = 'Classified' SELECT SUSER_SNAME() EXEC [Classified].[usp_GetMales] 1 REVERT
1
2
3
4
5
6
7
8
9
10
11
/* Change owner of Classified schema */
ALTER AUTHORIZATION ON SCHEMA::[Classified] TO [classified]
GO
EXEC AS LOGIN = 'Classified'
SELECT SUSER_SNAME()
EXEC [Classified].[usp_GetMales] 1
REVERT