February 11, 2012 at 4:52 am
i have following code
CREATE PROC Test_Caller with execute as caller
AS
select '1'
GO
exec Test_Caller
create login user1 with password = 'Password'
create user1 from login user1
grant execute on Test_Caller to user1
--logged to database with user1 in new querry window, or logged from another machine to sql 2008 sever on same database
exec Test_Caller -- result: procedure is executing
WHY??!!???!!!!!
i run the following querry just to be sure
sp_configure 'cross db ownership chaining', 0
reconfigure
then i run
exec Test_Caller
the procedure is executed and i don't understand why?
Any help would be appreciated
thankyou in advanced.
February 11, 2012 at 5:10 am
The procedure is running in the context of logged in user & he has rights to execute it. That is why it’s executing.
You need to understand Execution Context & Context Switching. I am giving few pointers for the same. Please go through it.
Understanding Execution Context
http://msdn.microsoft.com/en-us/library/ms187096.aspx
Understanding Context Switching
http://msdn.microsoft.com/en-us/library/ms191296.aspx
As a side note, please don’t just alter any server configuration if you are not very sure what it will do? I assume you are doing this exercise in test environment (strictly prohibited in PROD environment).
February 11, 2012 at 2:08 pm
This was a very bad example indeed!!
I create a table
create table audit(id int identity(1, 1),
col1 varchar(20),
col2 varchar(20)
constraint pk_id primary key(id))
then i insert data into table audit
insert into audit select 'Name', 'Surname'
I modify procedure Test_Caller
ALTER PROC Test_Caller with execute as caller
AS
select * from audit
GO
I login as user1, and i execute the following query
select * from audit
result:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'audit', database 'Test', schema 'dbo'.
SO GOOD, SO FINE
then i execute the procedure Test_Caller in the context o user1
exec Test_Caller
result:
id col1 col2
----------- -------------------- --------------------
1 Name Surname
WTF I'm doing wrong!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
February 12, 2012 at 3:16 am
Did you read the articles I suggested to you?
Please execute the following on Test server and see it yourself how it works.
set nocount on
Print 'table created'
create table some_table
(
some_value varchar(100)
);
go
Print 'sample data inserted'
insert into some_table values('aaa')
go
Print 'created a procedure to fetch data from the table'
go
create procedure get_some_table
as
select * from some_table
go
Print 'created a new user'
create user new_user WITHOUT LOGIN
go
Print 'granted EXCEUTE permissions ONLY'
grant execute on get_some_table to new_user
go
print 'user: ' + user_name() -- old
Print 'context switch...'
EXECUTE AS USER = 'new_user';
print 'user: ' + user_name() -- new
print 'executing procedure in new context'
EXEC get_some_table -- SUCCESS
print 'SUCCESS'
print 'user: ' + user_name() -- new
print 'selecting data in new context'
select * from some_table -- ERROR
print 'ERROR'
print 'user: ' + user_name() -- new
print 'context switch back'
revert
print 'user: ' + user_name() -- old
print 'selecting data in old context'
select * from some_table -- SUCCESS
print 'SUCCESS'
go
--=============== Clean up ========
drop user new_user
go
drop table some_table
go
drop procedure get_some_table
go
set nocount off
June 9, 2012 at 4:08 pm
Manole,
your question is very good.
Procedure is created "with execute as caller".
When we call the procedure with a user that has no rights on underlying objects (objects that procedure uses),
it would be natural to expect that the call would fail.
But it succeeds!
Why?
Because of ownership chaining. It's not cross-database ownership chaining. It is in-the-database ownership chaining:
http://msdn.microsoft.com/en-us/library/ms188676%28SQL.105%29.aspx
Basically, it means that if underlying objects (ones that procedure uses) belong to the same owner as procedure,
the rights on that objects won't be checked.
So, you don't have to grant a user rights to all objects that procedure uses, you just have to grant "execute" permission.
That simplifies managing permissions.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply