GRANT EXECUTE ... TO [blah] AS [dbo] - What does the "AS DBO" do exactly?
-
GRANT EXECUTE ON [dbo].[SomeSproc] TO [Some_User] AS [dbo]
What EXACTLY does the "AS DBO" part do?
http://msdn.microsoft.com/en-us/library/ms188371.aspx
That talks about it, but I don't understand.
What happens when I execute it and leave "AS [DBO]" off? It still runs, so what is different about the two statements?
Does it just use my login to derive permissions from?
-
AS principal
Specifies a principal from which the principal executing this query derives its right to grant the permission.
So in other words it grants the user to give other users the same permission.
-
So what is I leave it blank and don't use that option?
What does the default to?
Also I don't think I fully understand that.
If I grant execute rights on a sproc to UserBob, then I am the one granting rights for UserBob to execute the sproc.
What does the "AS principal" do exactly?
Grants UserBob the right to execute it AS SomePrincipal?
-
I think the first part of sanderstad's reply is correct. But "So in other words it grants the user to give other users the same permission" is not, or at least seems misleading to me.
The "AS principle" is a way of specifying what right you have to have to make this GRANT in the first place. I've never had to do this, wish I could find a good example of why this is even useful...maybe when using certificates?
I'm confident the many "AS dbo" GRANTs my DBA group is seeing lately represent no threat. This is just MS latest default way of scripting out GRANTs. See BOL "GRANT Object Permissions (Transact-SQL)", under "Permissions" about 2/3 down the page. You can't GRANT permission AS dbo unless you meet these conditions.
-
It's a way of changing permissions.
If you create sproc A, you might grant execute to use Alice and Bob for this sproc. However, if you're managing security correctly, you might be granting this in a role, not individually.
However, suppose you have sproc B that does something like read salary information, or edit the inventory directly. These aren't rights that Alice or Bob normally have in their group, and you don't want to grant them these rights. Perhaps they should change that read with this sproc. So you can choose a higher level account, be it DBO or Steve, and use the "AS DBO" clause which elevates the caller's rights to that of the DBO (or other specified account) ONLY for the execution of the sproc.
Make sense?
-
Thanks for the clarification, Steve. But I want to be sure.... When we don't specify this, and we usually don't, is the default "AS <owner>"?
I've almost exclusively worked with dbo-owned stored procedures, and until newer tools became widely used our scripts have never included the "AS principle" portion.
For a dbo-owned procedure, is there any difference between adding "AS dbo" and simply omitting that part of the GRANT?
PS - What did you think of that earlier statement "So in other words it grants the user to give other users the same permission?"
-
Default is "as the caller"
The permissions are set according to who executes it. The exception being that you might have ownership chaining enabled, so if the sproc owner owns a table called in the sproc, then the caller can get rights to that table for the sproc.
I didn't quite understand you PS. Doesn't quite read clearly in English. Or I'm short some coffee this am.
-
The PS referenced text in earlier 7/1/2009 reply to the original post. That reply is why I
first posted here today, I keep having it quoted to me.
Since I can't create dbo proc unless I am effectively dbo, I think for a dbo proc, there is no difference between adding "AS dbo" and simply omitting this part of the GRANT.
I'm rephrasing this way, because that is the question I keep getting asked: What is the difference between adding "AS dbo" and not adding it? I believe at least in my case (dbo procs)...nothing.
-
There is a difference.
If you use "as dbo" in the proc definition, then when the user executes it, it runs as if they were logged in as dbo, or an account with db_owner rights.
If you do not include this, then when the sproc is executed, it's executed with the rights of the user that calls it, typically not dbo.
That's a huge security difference. If you don't know if you need it, don't include it.
-
With all due respect, Steve, that just cannot be right. The vast majority of our procs were compiled with no "AS principal", and they are all run by users with absolutely no rights except to run the procs. I believe this works because of basic ownership chaining, just like it did in prior versions of SQL.
Also, we keep creating procs with no "AS principle" and a few my coworkers keep reverse-engineering them (VS 2008 maybe?) with "AS dbo".
Now I'm trying to figure out where this "AS principle" metadata is stored. First step is to make a simple proc that has a GRANT ... AS something other than dbo. But I cannot get one to work.
-
Maybe you meant this, which jibes with your earlier posts:
If you do not include this, then when the sproc is executed, it's executed with the rights of the user that created it (which must be dbo for a dbo proc).
-
They do likely work because of ownership chaining, but that is not the same as including "AS DBO" in the proc. It might be OK for you today, but if someone changes the proc logic to do something not included in ownership chaining, or removes security for some reason, you could open a security hole.
If you are looking to test the syntax, create a new table with another user/schema. Then create a sproc with a different schema, not DBO, and use the syntax.
Here's a good writeup: http://www.sommarskog.se/grantperm.html#EXECUTE_AS
-
Doh, my mistake here, and apologies Mike. I misread the first code you posted, thinking you were changin permissions for the sproc at compile time.
The AS DBO at the end is used to specify an account that has permissions to perform the grant. Kind of circular logic, but from your perspective using DBO, you don't need it. No change, and it's redundant in what you describe as using dbo for your procs.
The permissions section here (http://msdn.microsoft.com/en-US/library/ms188371%28v=SQL.90%29.aspx) kind of explains what permissions are needed. These are because you might be deriving permission on the object that you are granting. In your case, assuming you are db_owner/DBO on your account, you are fine.
-
Hi Steve,
Are we talking about two different things in this thread?
Example 1:
Grant Execute on <proc> to <user> as dbo
and
Example 2:
Create Proc <procname>
WITH Execute AS <dbo>
AS
<.........>
Are you saying that the first example here is going to execute as db_owner?
Thanks!
-
Ah...never mind. I see now the replies. Thanks!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply