• 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