Stored Procedures , Explicit Permissions and Security concerns

Its always a good practice to have your code wrapped in stored procedures.
One of the reasons is Security. To explain a bit more, when one grants execution rights on a stored procedure to a user, the user gets the rights to perform all the operations ( Select/Insert/update/delete ) within the stored procedure.However, the same user cannot perform the operations outside the context of stored procedure.

Ex:
GRANT EXEC ON dbo.usp_stored_proc TO user1 

Assume that the stored proc dbo.usp_stored_proc performs Select and update on table1, then user1 can perform these operations only while executing dbo.usp_stored_proc and not directly. In other words, user1 cannot bypass the stored procedure and directly perform a select/update on table1.

But there are a few operations, where one needs to explictly grant permission to a object inside a stored procedure.In other words, its not enough if we just grant EXEC rights on the stored procedure. Let me list down such scenarios.

1) Using Dynamic sql queries using sp_executesql / EXEC :

If your stored procedure is using Dynamic SQL using sp_executesql then the
objects accessed in the dynamic sql require explicit permissions.

For Example

CREATE PROCEDURE dbo.Usp_stored_proc
AS
  BEGIN
      DECLARE @dsql NVARCHAR(100)

      SET @dsql = ' Select * from table1 '

      EXEC Sp_executesql @dsql
  END

GO 


For the above stored procedure, its not enough if we grant execution rights to
dbo.usp_stored_proc. In addition,one needs to grant select rights on 'table1' for the user executing stored procedure. Explicit grant is required because dynamic sql are always treated as separate batch outside the scope of the stored procedure.

2) Cross database reference

If you are accessing a table on another database, then one needs to explicitly grant rights.

CREATE PROCEDURE dbo.Usp_stored_proc
AS
  BEGIN
      SELECT empid,
             salary,
             increment,
             name
      FROM   hr_database.dbo.salary
      WHERE  username = 'clerk';
   .......

    ...........

   Some code

   ..........

   ...........

  
  END

GO 


In the above stored procedure, the salary from database 'HR_database' is accessed. The user who calls the stored procedure should have rights on the HR_database.dbo.salary for the stored procedure to execute successfully.

Please note that the above scenario is true, when cross database ownership chaining is not enabled.If cross database ownership chaining is enabled, and if both the objects(dbo.usp_stored_proc and HR_database.dbo.salary;) belong to the same owner, then explicit permissions need not be granted.

3) While using linked servers

CREATE PROCEDURE dbo.Usp_stored_proc
AS
  BEGIN
      SELECT *
      FROM   linkedserver.DATABASE.dbo.table2;
  END

GO 

On the above stored procedure, linked server is used to refer to a table in a remote server.In such a case, the login that maps the user ( executing the stored procedure ) to the remote server should have select rights on table2. For understanding login mappings on linked server refer here.

4) Using DDL statements

CREATE PROCEDURE dbo.Usp_stored_proc
AS
  BEGIN
      TRUNCATE TABLE dbo.t1;
  END

GO 


If the stored procedure contains DDL statement like truncate,alter table, Create index then appropriate rights should be granted to caller of the stored procedure.Meagre execution rights on the stored procedure wouldnt suffice.

On all the four scenarios listed above, ideally one would want the caller of the stored procedure to use these extra permissions only while executing the stored procedure. At the rest of the time, we wouldnt want the caller to gain direct access on the table.But by granting the rights explicitly, the caller of the stored procedure gets additional rights to perform the above mentioned operations without executing the stored procedure. ie, anyone can use the calling account and connect to the database and perform a Select on a entire salary table on HR_database or truncate a table t1, without executing stored procedure. Obviously, this can be a serious security concern.

To prevent the same, there are a few excellent options in SQL Server 2005, SQL Server 2008 which will be discussed in the next post.