Security Questions: Differences between “Grant” and “With Grant?”

As I mentioned in the introductory post, in the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer.  Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic.  Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.

THE GRANT question

The third question in the list is:

Can you please expound on the difference between “Grant” and “With Grant?”

When you GRANT something within SQL Server, that provides the permissions required to access the object, or securable, that is included in the statement.  For instance, a GRANT of read permissions on a tables allows the person to read from the table.

The WITH GRANT option, requires that Grant Fritchey (Blog | @GFritchey) show up for any of your permissions to work.  No, that’s not right.

In all seriousness, the WITH GRANT option allows the person who has been granted access to an object to GRANT that same access to other principals.  It is basically a method for delegating permissions when you have someone you can trust to manage those permissions.

Generally,  I’m not a big fan of this option.  The main reason is that, as a DBA, I’ve tended to be more concerned with who has permissions within a database compared to others.  The frequency in which I’ve seen others over grant permissions has been disheatening, especially when I was an FTE DBA and working on trying to constrain all of the run-away permissions.  That said, as a team member, we can’t always hold all of the cards and granting a junior DBA or team lead in another department the option to grant permissions through delegation can be especially handy.

Demo

To demonstrate how WITH GRANT works, let’s run a couple GRANT statements in AdventureWorks2012.  We’ll start by creating two new logins, WithGrantLogin1 and WithGrantLogin2, using the script in Listing 1.  The last part of the script includes a GRANT statement for WithGrantLogin1 provide access to Sales.SalesOrderHeader.  At the end of the GRANT, notice that “WITH GRANT OPTION” is included.  That is the clause that allows WithGrantLogin1 to have the permissions required to assign that GRANT to other users.

--Listing 1. Create new login
USE [master]
GO
CREATE LOGIN [WithGrantLogin1] WITH PASSWORD=N'pass@word1'
, DEFAULT_DATABASE=[master]
, CHECK_EXPIRATION=ON
, CHECK_POLICY=ON
GO

CREATE LOGIN [WithGrantLogin2] WITH PASSWORD=N'pass@word1'
, DEFAULT_DATABASE=[master]
, CHECK_EXPIRATION=ON
, CHECK_POLICY=ON
GO

USE [AdventureWorks2012]
GO

CREATE USER [WithGrantLogin1] FOR LOGIN [WithGrantLogin1]

CREATE USER [WithGrantLogin2] FOR LOGIN [WithGrantLogin2]

GRANT SELECT ON Sales.SalesOrderHeader TO [WithGrantLogin1] WITH GRANT OPTION

To test this out, open two query windows and login to each of them with one of the two new users.  After doing so, run the script in Listing 2 in each of the two query windows, once for each of the users.  As you might expect, the query will succeed for WithGrantLogin1, but fails for WithGrantLogin2.

--Listing 2. Query Sales.SalesOrderHeader
USE AdventureWorks2012
GO

SELECT * FROM Sales.SalesOrderHeader

In the query window for WithGrantLogin1, run the script in Listing 3.  This script; which does not include the WITH GRANT OPTION, will GRANT read permissions to WithGrantLogin2 on Sales.SalesOrderHeader.  You should now be able to successfully run the query in Listing 2 for WithGrantLogin2.

--Listing 3. Create new login
GRANT SELECT ON Sales.SalesOrderHeader TO [WithGrantLogin2]

For the DBAs reading this, one of your first concerns with using the WITH GRANT clause for the GRANT statement is auditing how permissions have been assigned.  Fortunately, through SQL Server’s catalog views, this information is easy to discern.  With the catalog view sys.database_permissions, there are columns that identify both the grantee and the grantor for permissions.  Joining the view to sys.database_principals provides the names associated with the principal_ids and the names associated with accepting and granting permissions.  Using the query in Listing 4 and output in Figure 1, you can see that there is an audit trail of both who has and who granted permissions.

--Listing 4. Review permissions and the grantor
SELECT dp.class
,dp.class_desc
,dp.permission_name
,dp.state_desc
,OBJECT_NAME(dp.major_id) AS object_name
,dp2.name AS grantee_principal
,dp1.name AS grantor_principal
FROM sys.database_permissions dp
left JOIN sys.database_principals dp1 ON dp.grantor_principal_id = dp1.principal_id
left JOIN sys.database_principals dp2 ON dp.grantee_principal_id = dp2.principal_id

Figure 1. Sys.permissions output

Summary

When it comes to granting permissions, using the WITH GRANT OPTION might not be my favorite security command.  But it is a very useful option for delegating permissions, and potentially freeing DBAs from mindless tasks to more fun tasks, like performance tuning.  Properly leveraging this option is something that definitely should be a consideration.  What do you think?  Do you use the WITH GRANT OPTION in your environment?