x

What security precautions need to be taken in term of impersonation(EXECUTE AS)

Hi all, I have been tasked with ensuring that our SQL Server 2008 R2 meets certain security requirements. The problem I am having is in regards to Impersonation specifically referring to the EXECUTE AS SELF command. Our security auditor believes that if, lets say I have dbo rights on a database that the EXECUTE AS SELF command would effectively allow me to execute stored procedures as a dbo on other databases on the same server.

How does impersonation on SQL Server 2008 R2 affect security of the server? What are the default setting allowing impersonation? Is it a at all possible for the above situation to happen?

Would really appreciate the help, really not sure where to get the information to ensure we comply to this prerequisite.
more ▼

asked Jul 13, 2012 at 12:58 PM in Default

WcMyburgh gravatar image

WcMyburgh
80 4 4 6

Are you talking about the EXECUTE AS (Sets the execution context of a session) OR EXECUTE AS CLAUSE (defines the execution context of the user-defined modules)? The security concern seems to be about EXECUTE AS, whereas EXECUTE AS SELF is pertaining to EXECUTE AS CLAUSE for the modules like function, procedures etc.
Jul 13, 2012 at 01:12 PM Usman Butt

That will be revering to EXECUTE AS that sets the execution context of the session.

Would a easy way of mitigating security concerns in regards to the EXECUTE AS CLAUSE in modules be to keep users out of role that execute functions, procedures etc ?

Sorry for the vague questions, bit new to the security side of SQL Server, please just bear with me.
Jul 16, 2012 at 07:52 AM WcMyburgh
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

@WcMyburgh If you are talking about EXECUTE AS SELF, then it has consequences like you may give more permissions than desired. The rule of thumb is that either you execute the procedure with a least privilege user OR with default settings i.e. EXECUTE AS CALLER. I have faced limitations where a loginless user is made with least privileges and almost all the procedures are executed with that user explicitly. This way security is hardened.

Now talking about executing the stored procedures in other databases, I do not think that is possible until you have access to that database OR Cross Database Ownership Chaining is ON for the database.

Members of highly privileged database roles can use cross-database ownership chaining to access objects in databases external to their own. For example, if cross-database ownership chaining is enabled between database A and database B, a member of the db_owner fixed database role of either database can spoof his/her way into the other database. The process is simple: Diane (a member of db_owner in database A) creates user Stuart in database A. Stuart already exists as a user in database B. Diane then creates an object (owned by Stuart) in database A that calls any object owned by Stuart in database B. Because the calling and called objects have a common owner, permissions on the object in database B will not be checked when Diane accesses it through the object she has created
more ▼

answered Jul 13, 2012 at 02:26 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left

SQL Injection, if there is any dynamic sql used in the the stored procedure.


-- Assuming table & sp created by admin user
Create 
table testsql
(ID INT,
c1 VARCHAR(100))

GO

INSERT INTO testsql
SELECT 1,'A'

GO

CREATE PROCEDURE usp_dynamic_sql
@C1 VARCHAR(100)
WITH EXECUTE AS SELF
AS
BEGIN
DECLARE @SQL VARCHAR(100)

SET @SQL = 'SELECT * FROM testsql WHERE c1 = '+''''+@C1+''''
EXEC (@SQL)
PRINT @SQL
END

GO
-- assuming prev_user is user with limited rights, grant execute on sp to user
GRANT EXECUTE ON usp_dynamic_sql  TO prev_user 
GO

-- if the user "prev_user" takes advantage of dynamic sql he can even drop the table!

EXEC usp_dynamic_sql 'A'';  DROP TABLE testsql--'
Result, table is dropped!
more ▼

answered Jul 13, 2012 at 01:17 PM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

@Cyborg +1 for showing the elevation of permissions through an example. I got the point ;)
Jul 13, 2012 at 01:27 PM Usman Butt
BTW... Congrats for your 10K :)
Jul 13, 2012 at 01:42 PM Cyborg
Thanks a lot :)
Jul 13, 2012 at 01:45 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x162

asked: Jul 13, 2012 at 12:58 PM

Seen: 1214 times

Last Updated: Jul 16, 2012 at 07:54 AM