May 12, 2011 at 10:40 pm
Hi Team,
I want to DENY the EXECUTE AS for a login. How to do that. Can anybody share their views??
Here is what i have done.
use master
go
create database testdb
go
-- creating a login
USE [master]
GO
CREATE LOGIN [login1]
WITH PASSWORD=N'test',
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
USE testdb
GO
CREATE USER [login1] FOR LOGIN [login1]
GO
USE testdb
GO
EXEC sp_addrolemember N'db_owner', N'login1'
GO
-- create one more login with only "public" role
USE [master]
GO
CREATE LOGIN [login2]
WITH PASSWORD=N'test',
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
USE testdb
GO
CREATE USER [login2] FOR LOGIN [login2]
GO
-- as "login1" created a stored procedure and granted executed the EXECUTE permission to "login2"
created PROCEDURE dbo.test_proc
(
@p1 varchar(max)
)
WITH EXECUTE AS 'login1'
AS
BEGIN
select SYSTEM_USER
SELECT @@SERVERNAME
END
go
-- grant execute permission to 'login2'
GRANT EXECUTE on dbo.usp_test to login2;
-- as login1
exec dbo.test_proc 'dsgf'
/* working fine */
-- as login2
exec dbo.test_proc 'dsgf'
/* working fine */
-- now to restrict the login2 to be impersonated as 'login1' done the following
-- server level
USE master;
DENY IMPERSONATE ON LOGIN::login1 TO login2;
GO
-- database level
USE testdb;
go
DENY IMPERSONATE ON USER::login1 TO login2;
GO
-- now tested as login2
exec dbo.test_proc 'dsgf'
/* working fine */
But i dont want this behaviour to happen.... whenever 'login2' is trying to impersonate as 'login1', we should allow to do that. that's why am denying at server level and database level.
IF i remove the EXECUTE permission, am able acheive what i need.
But basically, if 'login2' is being denied at server level and database level, why it is not restricting the esxectin of the stored procedre.
Now first I want understand what implicit permission's will be granted to a user who is having the EXECUTE permissions on a stored procedure.????????????
I tried modifying the stored procedure, even it is able to execute bt i dont want that to happen.
created PROCEDURE dbo.test_proc
(
@p1 varchar(max)
)
WITH EXECUTE AS CALLER;
AS
BEGIN
select SYSTEM_USER
SELECT @@SERVERNAME
END
go
But if i say one of the below then it is working as i expected.
use testdb
go
EXECUTE AS LOGIN = 'login1'
SELECT SYSTEM_USER
REVERT
SELECT SYSTEM_USER
go
/*
Msg 15406, Level 16, State 1, Line 1
Cannot execute as the server principal because the principal "login1" does not exist, this type of principal cannot be impersonated, or you do not have permission.
*/
Similarly,
use testdb
go
EXECUTE AS USER = 'login1'
SELECT USER_NAME()
REVERT
SELECT USER_NAME()
go
/*
Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal "login1" does not exist, this type of principal cannot be impersonated, or you do not have permission.
*/
This is what am expecting to behave whenever executing the stored procedure as well.
-- as 'login2'
use testdb
go
exec dbo.test_proc 'dsgf'
go
I want to know what implicit permissions will a login/dbuser gets if he has EXECUTE permission on a stored procedre????
Can anyone answer this???
Thanks in advance.
May 13, 2011 at 7:47 am
SQL Server is default DENY. So if the user doesn't have explicit permissions, is not a member of a role that gives implicit permission (note: I hate these), or ownership chaining doesn't happen, the user can't perform the operation.
Implicit Permission Due to Roles:
The fixed database roles like db_datareader and db_datawriter give implicit permissions. You won't see them in sys.database_permissions. You just have to remember that they are there and what they do. There are others, like db_ddladmin, too.
Ownership Chaining:
If an object, like a stored procedure, refers to another object, like a table, and both have the same owner, then an ownership chain forms. In the case of an ownership chain, the object being referred to (the table) can be accessed implicitly via the referring object (the stored procedure - this assumes the user can execute the stored procedure). SQL Server doesn't check the permissions. It makes the assumption that since the owner is the same, the owner intended the access.
In SQL Server 2005 and above, by default objects don't have any owners. Schemas do. And objects, if an owner is not explicitly assigned (you can do this), will assume the owner of the schema which they belong to. Ownership chains form based on this assignment if the object has no specified owner.
K. Brian Kelley
@kbriankelley
May 13, 2011 at 8:51 pm
Hi Brian,
Thanks for the wonderful explaination.
Regards,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply