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,'email@example.com','12346789');
At some point an ETL process is written that performs some activities in the
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?