CODEWITHSUNDEEP
sql-serverstored-proceduresaccess-controlsql-grant
Karl Z
Karl Z

Reputation: 43

SQL Server stored procedure that grants itself EXECUTE permission

Tried searching for this but was not able to find anything. I have a handful of stored procedures in a production database where the developer has, at the very end, added a

GRANT EXECUTE ON [ProcName] to [USER1] AS [dbo]

My spidy sense tells me this should not be done as all user access should be (and is) managed in SSMS at the appropriate level. This also can introduce deadlocks should the database start to really get busy and continually granting this access each time should not be necessary.

I can't for the life of me think of a valid reason why this would need to be done other than to ensure the user gets the permission maybe during development. I plan on removing this, but I was just wondering if I'm missing something?

Upvotes: 2

Views: 2978

Answers (2)

Jeff Moden
Jeff Moden

Reputation: 3494

No... you're NOT missing a thing. This is actually a pretty common thing for a lot of "developers" to do and the developer needs to be pulled up on the carpet for it. The CTO, the Dev Manager, and the DBAs should join them if one of these makes it through and is one of the primary reasons why...

  1. Developers should never be allowed to deploy code to staging, UAT, or Prod
  2. why there should be a 100% code review process
  3. why there should be written rules about such things with absolutely no tolerance for such things. IMHO, "one and done, you're out'a here".

Companies have enough of a problem with security without the people they're paying doing this type of stupid thing. There should be zero tolerance for this type of thing.

If this all makes me sound brutal on this subject, then I've succeeded in saying what needs to be said on this subject but then you (the reader) might be a part of the problem.

{EDIT} Let me qualify this a bit more...

This MAY be legitimate as a part of a deployment script but it should NEVER be a part of an actual stored procedure because there is no reason to constantly grant the same privs to a given stored procedure. This is still a "violation" to me because Developers should not be assigning privs by code or any other way. Only the DBAs should be assigning such privs and there needs to be a ticket or some other traceability as to why the privs were necessary in order to be able to pass a security audit.

There is also no reason to grant execution privs at the DBO level to a given user. You should only grant EXECUTE privs to the user and the stored procedure should have the proper WITH EXECUTE AS xxxxx statement in it to be able to accomplish what the stored procedure needs to do.

If "user1" in the grant code is a Developer, then it's time to put the Developer on the spot as to why they're doing such a thing. But, like I said, this code shouldn't actually exist. It should be up to the DBAs to grant such privs.

If it's an existing stored procedure where a legitimate user needs it to execute with "dbo" level privs, the stored procedure likely needs to be repaired using WITH EXECUTE AS xxxxx instead of granting individuals such privs.

As with all else in SQL Server, "IT DEPENDS" and there may be a very rare exception to what I've stated but I'd find a different way to make it work, especially if the cited "user1" was a public face application.

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

99.999% chance this is a mistake. A stored procedure is ended only by the end of a batch. The developer probably created the procedure with a DDL batch like this:

create procedure foo
as
begin
  print 'foo'
end

grant execute on foo to User1 as dbo  --this is still part of the procedure!
go

And stored procedures execute as the caller, by default, and the caller wouldn't be able to run that GRANT without being an admin.

It's a good practice to include GRANTs in your DDL scripts, but having seperate GRANTs for each object is a practice that dates from the time before good tools (like SSDT), and before user-schema seperation.

Today it's better practice to GRANT at the schema level, so there's no need to set permissions for each object. And while GRANTs should be part of the schema design, and be the same across environments, those grants should normally be a GRANT to a ROLE on a SCHEMA. Then in the different environments the ROLEs may have different members but the GRANTs never change.

So a production DBA might run 

ALTER ROLE APP_FRONT_END ADD MEMBER [MyDomain\AppPoolIdentity]

but not

GRANT EXECUTE ON FOO TO [MyDomain\AppPoolIdentity]

Upvotes: 3

Related Questions