Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Security: The db_executor Role

By Brian Kelley,

This article represents the first in what I hope will become a regular series of articles here at SQLServerCentral.com.  Security is a big topic nowadays, and security with respect to SQL Server is no exception. SQL Server has a lot of constructs to help make it a secure database platform. One such construct is the concept of the role. A role is used to control access to data within a given database (database roles) or to delegate certain administrative functions on the server (server roles). Speaking of data access, within the security world there are typically three ways to handle access:

  • Mandatory Access Control (MAC)
  • Discretionary Access Control (DAC)
  • Role-Based Access Control (RBAC)

Mandatory Access Control is typically seen in military environments where information is classified at a certain level. If you don't have the level, you don't have access. For instance, if you bear a Secret clearance but the information is rated Top Secret, you can't look at the data. However, if you have Top Secret clearance, you can (provided everything else fits into place, such as a "Need to Know"). Discretionary Access Control is typically where permissions are assigned per user. SQL Server has such ability: I can assign SELECT rights against the Orders table for user JDoe. But SQL Server also has Role-Based Access Control. And RBAC can really simplify our security plan.

The db_datareader and db_datawiter Roles

Consider the database roles db_datareader and db_datawriter. If I want to give a particular user the ability to issue a SELECT statement against every table and view within a given database, I just add the user to the db_datareader role. Likewise, if I want a given user to have the ability to modify data in any table or view within the database, I add the user to the db_datawriter role.

Note: Before I go any further, I need to state that I'm not a big fan of these blanket permission type roles. The main reason is I don't like giving direct access to tables, preferring to control access through the use of views and stored procedures. After all, if a user forgets a WHERE clause in a query or includes it but doesn't get it quite right... you get the idea.. Also, the db_datareader role has access to all of the system tables within a given database. This is an information disclosure problem that violates the principle of least privilege: give a user only the permissions the user needs to complete his or her work and nothing more.  However, there are instances where these blanket permission type of roles are implemented because time is short and the risk is relatively small. The db_executor role will be just such a blanket permission type of role.

What's Missing: The db_executor Role

While there is a role to SELECT data from all the tables and views in the db_datareader role and there is a role to INSERT, UPDATE, and DELETE in db_datawriter, notice that there isn't a role for executing all stored procedures within a given database. I can fix this by creating a user-defined role, one which I've named the db_executor since I want it to have EXECUTE permissions on all stored procedures. To create the db_exector role, I simply need to use the sp_addrole system stored procedure. I'll go ahead and create this role in the pubs database:

USE pubs
GO

EXEC sp_addrole 'db_executor'
GO

However, the main problem with the db_executor role is that it currently has no permissions. This differs from the db_datareader and db_datawriter roles which implicitly have their rights. In other words, if I add a table or view, the db_datareader and db_datawriter roles automatically gain access to the table or view. In contrast, if I add a stored procedure, the db_executor role won't automatically have EXECUTE rights. Such is the life of a user-defined role!

The Fix: Manually Granting Permission

Since SQL Server isn't going to handle granting permissions for me, I'll have to do it manually. In order to do this, I'll need to make use of dynamic SQL since I won't know the owners and stored procedure names at run-time. The trick is to build the GRANT EXECUTE statement to give db_executor the ability to execute all stored procedures. The following code does just that in the pubs database. Notice that I just grant permissions to all stored procedures; I don't try and compare what db_executor already has versus what it doesn't. If you have a large number of stored procedures, this might be necessary. I've included a second script that only retrieves those stored procedures that db_executor doesn't have access to at the end of the article.

USE pubs
GO

DECLARE @SQL nvarchar(4000),
  @Owner sysname,
  @StoredProcedure sysname,
  @Return int

-- Cursor of all the stored procedures in the current database
DECLARE cursStoredProcedures CURSOR FAST_FORWARD
FOR
SELECT USER_NAME(uid) Owner, [name] StoredProcedure
FROM sysobjects
WHERE xtype = 'P'

OPEN cursStoredProcedures

-- "Prime the pump" and get the first row
FETCH NEXT FROM cursStoredProcedures
INTO @Owner, @StoredProcedure

-- Set the return code to 0
SET @Return = 0

-- Encapsulate the permissions assignment within a transaction
BEGIN TRAN

-- Cycle through the rows of the cursor
-- And grant permissions
WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))
  BEGIN
    -- Create the SQL Statement. Since we're giving
    -- access to all stored procedures, we have to 
    -- use a two-part naming convention to get the owner.
    SET @SQL = 'GRANT EXECUTE ON [' + @Owner
          + '].[' + @StoredProcedure 
          + '] TO db_executor'

    -- Execute the SQL statement
    EXEC @Return = sp_executesql @SQL

    -- Get the next row
    FETCH NEXT FROM cursStoredProcedures
    INTO @Owner, @StoredProcedure
  END

-- Clean-up after the cursor
CLOSE cursStoredProcedures
DEALLOCATE cursStoredProcedures

-- Check to see if the WHILE loop exited with an error.
IF (@Return = 0)
  BEGIN
    -- Exited fine, commit the permissions
    COMMIT TRAN
  END
ELSE
  BEGIN
    -- Exited with an error, rollback any changes
    ROLLBACK TRAN
    
    -- Report the error
    SET @SQL = 'Error granting permission to ['
    + @Owner + '].[' + @StoredProcedure + ']'
    RAISERROR(@SQL, 16, 1)
  END
GO

After the script runs, the db_executor role will have permission to execute every stored procedure in the database. That means any user I add to the role will have the ability to use all of the stored procedures in the database.

Scheduling Permission Updates

This script works great but there is a problem: it only runs once. In order to keep the db_executor role permissions up-to-date, the script will have to be run as frequently as is necessary for a given database. If there is an extreme amount of change, the script may be running once a hour, maybe more. SQL Server Agent can be used to schedule and run the script. But even with SQL Server Agent, there will be a time lapse between when a stored procedure is created and when the db_executor role has permission to execute it. The best solution is to simply grant permission to the db_executor role when the stored procedure is created, but this isn't always possible.

For instance, in a development environment you may have granted the db_ddladmin role to a few users. This role allows a user to create database objects but the role actually goes a step further: it allows a user to create a database object with any owner (so long as the owner is a valid user in the database). But the db_ddladmin role doesn't have the ability to assign permissions. The db_securityadmin role has such rights but it's rare indeed when I see a developer put in both roles (db_owner also can, but it's rarer still for a developer to have such high permissions). If you're using db_ddladmin you may have to look at scheduling the script in order to keep the db_executor role up- to- date. How often will depend on the environment.

In a production environment with proper change control procedures, this should never be a problem. When the stored procedure is put into the environment, permissions to execute the stored procedure should also be included. Even if they aren't, when you make an update to the production environment, you can also run the script manually to update all the permissions and ensure no stored procedure is missed. So scheduling automatic updates in the production environment isn't of as great a concern as in development.

In Closing

It is a shame that SQL Server has the db_datareader and db_datawriter roles but currently has nothing equivalent to a db_executor role. However, we can make a user-defined role that sort of fits the bill. The problem is, of course, keeping the permissions up-to-date. In a production environment change should be carefully controlled and this usually isn't that big an issue, but development is a different story. Unless developers possess the db_securityadmin role (or db_owner role), you'll need to look at some automated mechanism (such as through SQL Server Agent) scheduled regularly enough to be sufficient for the development effort.  If you have need of the db_executor role, hopefully this is enough to get you started. 

 © 2003 by K. Brian Kelley. http://www.truthsolutions.com/
 Author of Start to Finish Guide to SQL Server Performance Monitoring.

Resources:

db_executor_grant_new_only.sql | db_executor_grant.sql
Total article views: 25042 | Views in the last 30 days: 33
 
Related Articles
FORUM

Windows User of SQL Stored Procedure executor

Windows User of SQL Stored Procedure executor

FORUM

Server Permissions in a Stored Procedure with a Certificate

Granting a stored procedure the permissions to create database logins

FORUM

Procedures

Stored procedures permissions

FORUM

SQL Server 2005 Stored Procedure Permissions

Need to grant users the permission to view stored procedure properties, without granting sysadmin or...

BLOG

Execute Permissions

Written by Ian Treasure Gethyn posted on the use of execute permissions a while ago (db_executor at...

Tags
security    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones