• Madeira Team

The Risk of Ownership Chaining

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