How dangerous is granting the ALTER TABLE permission?

Question

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?

Martin Smith · Answer 1 · 2018-04-15T10:01:58.470

Pretty dangerous...

In addition to the obvious permission to change the structure of StagingTable itself the ALTER 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 explicit DENY permissions) and perform vandalism on this second table.

EXECUTE AS user='etlUser'

GO

CREATE OR ALTER TRIGGER TR ON dbo.StagingTable AFTER UPDATE AS 
/*Exposure of sensitive data*/
SELECT * FROM dbo.Customer;

/*Vandalism*/
DELETE FROM dbo.Customer;

go

--Fire the trigger
UPDATE dbo.StagingTable SET SomeData = SomeData WHERE 1=0;

REVERT

Solomon Rutzky · Answer 2 · 2018-04-16T14:17:59.313

In addition to being able to add triggers, the ALTER TABLE permission also allows for:

Disabling triggers (avoid audit trail)
Disable constraints (allowing for bad data)
Altering contraints (allowing for bad data)
Altering column definitions (change datatype, max size, NULLability)
Add a computed column that calls a T-SQL UDF (making it very difficult to get a parallel plan, which could easily hurt performance)

It also allows for removing columns, but that is not likely to go unnoticed (since it seems that we are looking for potential actions here that are more deceptive than malicious).

Fortunately, it is never necessary to grant this permission to anyone, nor is it necessary to wrap this in a stored procedure that uses the EXECUTE AS clause (typically followed by 'dbo' or OWNER). Module Signing allows for easy abstraction of privileged actions behind signed code (stored procedures, triggers, scalar UDFs, and multi-statement TVFs). I have example code showing how to accomplish this in the following answers, here on DBA.SE:

The difference between those two answers is the permission granted to the signature-based User. The permission to be granted (or DB Role to be added to) depends on the scope of what is needed. If you only need permission for a single table, then only grant ALTER on that table. If permission is needed for all tables in a specific Schema, then don't grant permission to individual tables, but instead grant the permission to the Schema itself. And so on.

Module signing is a few extra steps as compared to creating a schema specifically for the ETL user, or using the EXECUTE AS clause, but:

it is really just a simple copy and paste given that the code is available in both answers linked above, and
it is certainly the most secure option available. It only allows that operation via that piece of code, and only to those given EXECUTE permission to that code. Being a Schema owner allows for certain implicit permissions that are unnecessary. And, using EXECUTE AS 'dbo' or EXECUTE AS OWNER (assuming the owner is dbo) will give the entire process, from that point forward, dbo permissions, not just the stored procedure / trigger / function that you used EXECUTE AS with. Module signing confines the permissions to only the code that you signed, and not any code called by the signed code.

score 2 · Answer 3 · answered Apr 15 '18 at 17:47

A better practice would be to create a staging schema, owned by the ETL user. Then the ETL process can truncate tables, disable constraints, perform partition switching, etc within the staging schema. The ETL user would only need limited permission on the other schemas.

You can also use a database role instead of a single user.

Of course you can also enable your limited user to perform table truncations with a dbo-owned stored procedure, like this:

create procedure truncate_t 
with execute as owner
as
begin
  truncate table t;
end

How dangerous is granting the ALTER TABLE permission?

3 Answers3