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

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):

  1. Create a LOGIN and a USER for it.

  2. Create a SCHEMA called ‘classified’.

  3. Create the procedure under the schema.

  4. GRANT execute permission on the procedure to the user ‘classified’

  5. 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]