Why does ALTER let me DROP?

I was recently chatting with a coworker, discussing why there is no discrete DROP permission for tables, procedures, etc. The conversation actually came from a #SQLHelp question on SQL Slack that was asked along the lines of “What if I want to let a user CREATE and ALTER objects, but not DROP them?”

The answer is that if you can ALTER an object, then you also have permission to DROP it (if you really want to prevent DROP commands, you can use a DDL trigger to force a rollback). I’d never thought too much about the question of why permissions are this way–in fact, I often forget that there is no DROP permission, and have to re-learn it when I try to grant or deny that permission.

DROP is destructive

Dropping stuff is destructive. Anyone who’s ever dropped a plate or a car knows that. But dropping a table, procedure, or database makes it go away completely. The only way to un-drop something is to recover it from backup, or to re-deploy from source control (you do have all your code & schema in source control, right?). Getting back to the original question’s premise (it’s OK to change the object but not to completely make it disappear), I ask…does it even matter? What are you preventing? Is dropping an object worse than altering it?

ALTER is destructive, too

Altering an object is equally destructive to dropping it. Let us think about the most innocent way in which altering a stored procedure can totally break your code. If you remove a parameter from a stored procedure, and it’s being used by the calling code, it will start to fail with an error:

CREATE OR ALTER PROCEDURE dbo.Widget_Get 
    @WidgetID INT
AS ...;
GO
EXEC dbo.Widget_Get @WidgetID = 1, @ObsoleteParam = 'Andy';

Msg 8144, Level 16, State 2, Procedure dbo.Widget_Get, Line 0 [Batch Start Line 0]

Procedure or function Widget_Get has too many arguments specified.

Similarly, adding a new required parameter that isn’t being used by the calling code will also start to fail with an error:

CREATE OR ALTER PROCEDURE dbo.Widget_Get 
    @WidgetID INT, 
    @Active bit 
AS ...;
GO
EXEC dbo.Widget_Get @WidgetID = 1;

Msg 201, Level 16, State 4, Procedure dbo.Widget_Get, Line 0 [Batch Start Line 0]

Procedure or function ‘Widget_Get’ expects parameter ‘@Active’, which was not supplied.

But that’s just how innocent mistakes can break stuff.

What if someone is being malicious?

Maybe Disgruntled Doug knows that you don’t have that procedure in source control, and wants to destroy the production code for dbo.Widget_Get and make it difficult to recover. If he couldn’t drop the procedure, he could still destroy production code…and it would be super easy. Disgruntled Doug can delete all the code without dropping the procedure:

CREATE OR ALTER PROCEDURE dbo.Widget_Get 
    @WidgetID INT
AS RETURN 0;
GO
EXEC dbo.Widget_Get @WidgetID = 1;

Commands completed successfully.

OK, but what about tables?

How badly can you break a table if you could alter, but not drop it? Well, you could truncate it. The permission for TRUNCATE TABLE is included in ALTER TABLE…so just trash the data if you want to be malicious. But maybe there are foreign key constraints that will make truncating the table difficult. How else could you be destructive?

Let’s look at an example. The dbo.Person table is probably pretty central to the database. I could imagine there being dozens, or even hundreds of FKs pointing at PersonID, making truncating this table difficult for Disgruntled Doug:

CREATE TABLE dbo.Person(
     PersonID       int          NOT NULL,
     PersonType     nchar(2)     NOT NULL,
     Title          nvarchar(8)      NULL,
     FirstName      nvarchar(60) NOT NULL,
     MiddleName     nvarchar(60)     NULL,
     LastName       nvarchar(60) NOT NULL,
     Suffix         nvarchar(10)     NULL,
     ModifiedDate   datetime     NOT NULL,
     CONSTRAINT PK_Person_BusinessEntityID 
          PRIMARY KEY CLUSTERED (PersonID)
);

Disgruntled Doug wants to break this fast. He’s gotta finish breaking things before HR shows up at his desk to walk him out. Updating columns isn’t going to be fast enough. He can just drop all the columns!

ALTER TABLE dbo.Person
     DROP COLUMN PersonType, 
                 Title, 
                 FirstName, 
                 MiddleName, 
                 LastName, 
                 Suffix, 
                 ModifiedDate;

Now the data is all gone. All that’s left is those pesky PersonIDs.

OK, but what about permissions

Dropping an object also drops the permissions. So one could argue that preventing the DROP will at least preserve the permissions—presumably so that you can restore the object definition and not have to restore permissions? To me, this seems like a straw man argument. If you’re restoring the object definition from somewhere (source control? backup?), you can restore permissions from the same place. Permissions are pretty darned important–they should really be kept in source control.

Summary

Yes, the DROP permissions are included when you GRANT ALTER. And that’s because someone can intentionally, or unintentionally completely destroy an object without needing to drop it. In a way, dropping is just the most extreme version of altering it.

The post Why does ALTER let me DROP? appeared first on Andy M Mallon - AM².