New stored procedure uses wrong security context

Question

New stored procedure uses wrong security context

David Rueter

SSCrazy

Points: 2661
More actions
April 2, 2015 at 10:04 am

#316124
I would like to allow a minimally-privileged user to create and execute stored procedures in a particular schema, but I want the user's privileges to control access...not the schema owner's.
For example, consider:
- A schema Foo that contains a table Foo.Table1
- A user Joe has ALTER and EXEC rights on the schema, as well as CREATE PROCEDURE rights on the database.
- Joe does NOT have SELECT rights to Foo.Table1.
Problem: If Joe creates a procedure Foo.Proc1 that contains a SELECT * FROM Foo.Table1, Joe will be able to receive rows from Table1 even though he does not have rights to select from that table.
I understand WHY this is: when procedure Foo.Proc1 is created, the owner gets set to schema Foo instead of the user Joe. The procedure then runs within the security context of Foo's owner (dbo) instead of in the context of user Joe.
How can I prevent this from happening?
For example, a DDL trigger that fires when user Joe creates procedures, that explicitly changes the ownership of the procedure to user Joe works around the issue...but this seems ugly.
Is there not a way to indicate that when Joe creates a procedure, Joe should be the owner of the procedure?
I don't want Joe to be able to select from Foo.Table1...from either a direct query or from within a stored procedure he creates.
Thoughts?
Here is a script to show what I am working with:
CREATE DATABASE Example1
USE Example1
CREATE SCHEMA Foo
CREATE TABLE Foo.Table1 (Id int PRIMARY KEY)
INSERT INTO Foo.Table1(Id)
SELECT TOP 50
ROW_NUMBER() OVER (ORDER BY so.object_id)
FROM
sys.objects so
CREATE LOGIN Joe WITH PASSWORD='testpass'
CREATE USER Joe FOR LOGIN Joe
GRANT CREATE PROCEDURE TO Joe
GRANT ALTER ON SCHEMA::Foo TO Joe
GRANT EXEC ON SCHEMA::Foo TO Joe
After running this script, log in as Joe. Try SELECT * FROM Foo.Table1 This returns an error as expected, because Joe does not have rights to select from the table.
Then, while logged in as Joe execute this:
CREATE PROCEDURE Foo.Proc1
AS
BEGIN
SELECT * FROM Foo.Table1
END
EXEC Foo.Proc1
Joe now gets the rows from Foo.Table1, even though he does not have rights.
When logged in as sa, execute the following:
ALTER AUTHORIZATION ON OBJECT:: Foo.Proc1 TO Joe
Now, logged in as Joe try
EXEC Foo.Proc1
Now Joe receives an error, as desired, indicating that he does not have permission to select from the table.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Edit: nm, misread

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

I'm trying to understand the actual use case. That is, why do you have this permission setup in the first place? The user can create procedures in the schema and execute them - but he may not read the data. How did you end up here?

I can see three solutions.

1) The DDL trigger that automatically makes him owner of the procedure.

2) Change the owner of the sensitive table to someone else than the schema owner. so that there is no owner-chaining.

3) Create a separate schema for the developer to create is stored procedure and revoke his permissions to create procedure in the schema with sensitive tables.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

David Rueter SSCrazy Points: 2661 More actions · Answer 3

Thanks, Eric. So it sounds like there is not a way to have ownership of created objects automatically assigned to the user that creates them. That's too bad...and counter-intuitive.

The other curious part to this is that even if the procedure is explicitly created WITH EXECUTE AS CALLER the unprivileged user can still successfully call Proc1 to retrieve rows from the table he doesn't have rights to. (I thought that the owner's security context would be used WITH EXECUTE AS OWNER, but that the caller's context would be used WITH EXECUTE AS CALLER...but this seems not to be the case.)

I can explain the use-case if you are interested: The use-case involves allowing a user to create a stored procedure for reporting purposes (think self-service BI). The user should only be allowed to access objects/data that he has permissions to (i.e. should only be allowed to report on data he has rights to)...but should be allowed to create a stored procedure to encapsulate his reporting query.

IMO creating a stored procedure should not escalate his rights to that of the schema owner. IMO the stored procedure the user creates should be owned by the user...and should be executed under the user's security context.

I appreciate any other thoughts or feedback that you or others may have.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 4

David Rueter (4/2/2015)
Thanks, Eric.

Eric?

So it sounds like there is not a way to have ownership of created objects automatically assigned to the user that creates them. That's too bad...and counter-intuitive.

Yes, the behaviour that schema owner becomes the owner and not the creator is not exactly intuitive, but it is due to legacy. In SQL 2000, owner and schema was the same thing, and Microsoft separated the two in SQL 2005, they decided to keep the old behaviour.

The other curious part to this is that even if the procedure is explicitly created WITH EXECUTE AS CALLER the unprivileged user can still successfully call Proc1 to retrieve rows from the table he doesn't have rights to. (I thought that the owner's security context would be used WITH EXECUTE AS OWNER, but that the caller's context would be used WITH EXECUTE AS CALLER...but this seems not to be the case.)

This is because of ownership chaining. If a stored procedure accesses a table owned by the procedure, all performance checks are voided.

I can explain the use-case if you are interested: The use-case involves allowing a user to create a stored procedure for reporting purposes (think self-service BI). The user should only be allowed to access objects/data that he has permissions to (i.e. should only be allowed to report on data he has rights to)...but should be allowed to create a stored procedure to encapsulate his reporting query.

So you should go for the third solution I suggested. Create a separate schema for the users to create their procedures, and revoke their permission to create procedures in the current schema. Important! This new schema needs to have a different owner than the schema with the tables.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 5

I learn toward Erland's last suggestion. Another schema.

David Rueter SSCrazy Points: 2661 More actions · Answer 6

I agree with ~~Erik's~~ Erland's suggestion #3 too. Having the user create the stored procedure in a different schema sounds to be the way to go. I'll do some testing with that and let you know how it goes.

Thanks guys for helping me out.

This situation is one where testing saved the day: i was just sure that the user permissions were what I was expecting. This could have been a significant "whoops" had it not been for testing.