EXECUTE AS questions

[Olaf Doschke]

I am not very familiar with the EXECUTE AS clause and have read a lot about it, which made me unsure about when and how it works.

First of all I created a SQL Server Login named 'triggerlogin' with password.

I defined this AFTER DELETE trigger:

USE [mydb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[sometable_delete]
   ON  [dbo].[sometable]
   AFTER DELETE
AS 
EXECUTE AS LOGIN = 'triggerlogin'
BEGIN
	SET NOCOUNT ON;

	Insert Into [archive].[sometable] SELECT * FROM deleted
END

It's a little simplified, because the archive table has some more fields, but the question is not about the trigger, but the permissions involved.

I would like user to have no permissions on the archive schema, so only the trigger might add to (and also read from) the archive schema table.
From what I read the caller triggering that trigger by a DELETE operation must have rights to impersonate the triggerlogin and also this code doesn't need the password of the triggerlogin.

I am unsure how that really helps to achieve my goal of denying direct access to the archive schema to any db user besides the triggerlogin, if the users are granted the right to impersonate the login, they could do so anyway, if getting hands on the MSSQL Management Studio, couldn't they?

I read here, that "Ownership chaining rules continue to apply. This means if the owners of the calling and called objects are the same, no permissions are checked on the underlying objects." Does that mean if the archive.sometable has the same owner as the dbo.sometable, the permission of triggerlogin on the archive table are even not checked?

So in short: how would I really get a permission separation of the archive schema from the default (dbo) schema users work in?

Bye, Olaf.

 

[djj55]

I have this bookmarked but do not remember it well.
Execute AS

Hope it helps

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Olaf Doschke]

Thanks for trying, but that doesn't answer my questions and doesn't resolve my concerns about a user still being able to do whatever he likes on the archive just by knowing that login/user.

So maybe I should think of three groups: a) the users b) a trigger login/user only allowed to insert, which the users (a) are allowed to impersonate, c) administrative users allowed all operations

I just don't see how that would differ from granting archive insert privileges to users (a) and not using EXECUTE AS.

Bye, Olaf.

 

[fredericofonseca]

Olaf, I think you need to read with more attention the documentation.

just to execute the following command (which is the one that allows you to change id)
EXECUTE AS LOGIN = 'EncryptionUser'

or when you create a proc/function with a "with execute as owner" you need certain permissions such as IMPERSONATE and more - so as long as you do not grant those then knowing the login will not allow any user to trick the system.

so you have a archive schema - great - make sure only users you wish to have direct access to it are granted such access.

Now assume that the owner of schema archive is dbo (which normally would be).
To avoid ownership chaining you create another schema archiveqry and set its owner to anything else other than the owner of your schema archive.

You then grant the owner of schema archiveqry all required select/insert/etc permissions on your archive schema.

Now you create a proc belonging to archiveqry and owned by its owner, and on that one you add the "with execute as owner"

And you grant execute permissions to this proc to any user you wish to have access to schema archive THROUGH the use of the said proc.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Olaf Doschke]

>you need certain permissions such as IMPERSONATE and more - so as long as you do not grant those then knowing the login will not allow any user to trick the system.
Well, but the description of EXECUTE AS says you have to grant users permission to impersonate the login/user to let EXECUTE AS work.

I'll try what you said with a separate owner, that'll give things a new twist. But even if that works I could also simply only grant INSERT permissions to the archive to normal users and then don't use EXECUTE AS at all.

Bye, Olaf.

 

[yelworcm]

Actually, there are two separate EXECUTE AS clauses. The freeform or interactive one is the one that requires the impersonation permission. If you embed the impersonation in the trigger (or stored procedure), then it is the person who runs the CREATE TRIGGER statement who needs the impersonation permission (usually not a problem, as most folks create procs/triggers as a dbo user, anyway). Does that help?