User can delete rows from a table by calling a procedure

Question

I'm getting somewhat confused with SQL Server security

• we have a login and a user: test

• we have a table: dbo.tblSessionFilter

User test has no select and no delete permission on this table (I tested this!!)

• Then we have a procedure:

  create procedure dbo.procFilter_Clear with execute as caller 
  as
     delete from dbo.tblSessionfilter 
     where spid = @@SPID
  

User test has execute right on this procedure.

And now, user test can call this procedure and can delete entries from the table; although he has no direct delete access on the table, and the procedure is execute as caller !

How is that possible ?

Is it probably because procedure and table are in the same schema?

Answer 1

See Ownership Chains:

When multiple database objects access each other sequentially, the sequence is known as a chain. Although such chains do not independently exist, when SQL Server traverses the links in a chain, SQL Server evaluates permissions on the constituent objects differently than it would if it were accessing the objects separately.

and,

When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

(My emphasis)