Imagine the following scenario
CREATE DATABASE test
GO
USE test;
CREATE TABLE dbo.Customer
(
CustomerId INT,
Email VARCHAR(100),
SensitiveData VARCHAR(20)
);
INSERT INTO dbo.Customer
VALUES (1,'abc@foo.com','12346789');
At some point an ETL process is written that performs some activities in the test
database.
CREATE USER etlUser WITHOUT LOGIN; /*For demo purposes*/
CREATE TABLE dbo.StagingTable
(
StagingTableId INT,
SomeData VARCHAR(100),
)
GRANT UPDATE,INSERT,DELETE,SELECT,ALTER ON dbo.StagingTable TO etlUser;
DENY SELECT ON dbo.Customer TO etlUser;
DENY SELECT ON dbo.Customer (SensitiveData) TO etlUser; /*For good measure*/
The etlUser should not have permissions to the Customer
table (and certainly not to the SensitiveData
column) so these are explicitly denied above.
The ETL process truncates dbo.StagingTable
so is given ALTER
table permissions on that.
This is flagged during a security audit. How dangerous is this scenario?
Best Answer
Pretty dangerous...
In addition to the obvious permission to change the structure of
StagingTable
itself theALTER TABLE
permission allows them to create triggers on the table. So in this case through ownership chaining they are able to both see sensitive customer data (despite the explicitDENY
permissions) and perform vandalism on this second table.