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:
EXEC sp_addrole 'db_executor'
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.
DECLARE @SQL nvarchar(4000),
-- Cursor of all the stored procedures in the current database
DECLARE cursStoredProcedures CURSOR FAST_FORWARD
SELECT USER_NAME(uid) Owner, [name] StoredProcedure
WHERE xtype = 'P'
-- "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
-- Cycle through the rows of the cursor
-- And grant permissions
WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))
-- 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
-- Clean-up after the cursor
-- Check to see if the WHILE loop exited with an error.
IF (@Return = 0)
-- Exited fine, commit the permissions
-- Exited with an error, rollback any changes
-- Report the error
SET @SQL = 'Error granting permission to ['
+ @Owner + '].[' + @StoredProcedure + ']'
RAISERROR(@SQL, 16, 1)
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.
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.