SQL SERVER – Securing TRUNCATE Permissions in SQL Server
- by pinaldave
Download the Script of this article from here.
On December 11, 2010, Vinod Kumar, a Databases & BI technology evangelist from Microsoft Corporation, graced Ahmedabad by spending some time with the Community during the Community Tech Days (CTD) event. As he was running through a few demos, Vinod asked the audience one of the most fundamental and common interview questions – “What is the difference between a DELETE and TRUNCATE?“
Ahmedabad SQL Server User Group Expert Nakul Vachhrajani has come up with excellent solutions of the same. I must congratulate Nakul for this excellent solution and as a encouragement to User Group member, I am publishing the same article over here.
Nakul Vachhrajani is a Software Specialist and systems development professional with Patni Computer Systems Limited. He has functional experience spanning legacy code deprecation, system design, documentation, development, implementation, testing, maintenance and support of complex systems, providing business intelligence solutions, database administration, performance tuning, optimization, product management, release engineering, process definition and implementation. He has comprehensive grasp on Database Administration, Development and Implementation with MS SQL Server and C, C++, Visual C++/C#. He has about 6 years of total experience in information technology. Nakul is an member of the Ahmedabad and Gandhinagar SQL Server User Groups, and actively contributes to the community by actively participating in multiple forums and websites like SQLAuthority.com, BeyondRelational.com, SQLServerCentral.com and many others.
Please note: The opinions expressed herein are Nakul own personal opinions and do not represent his employer’s view in anyway.
All data from everywhere here on Earth go through a series of four distinct operations, identified by the words: CREATE, READ, UPDATE and DELETE, or simply, CRUD. Putting in Microsoft SQL Server terms, is the process goes like this: INSERT, SELECT, UPDATE and DELETE/TRUNCATE.
Quite a few interesting responses were received and evaluated live during the session. To summarize them, the most important similarity that came out was that both DELETE and TRUNCATE participate in transactions. The major differences (not all) that came out of the exercise were:
DELETE:
DELETE supports a WHERE clause
DELETE removes rows from a table, row-by-row
Because DELETE moves row-by-row, it acquires a row-level lock
Depending upon the recovery model of the database, DELETE is a fully-logged operation.
Because DELETE moves row-by-row, it can fire off triggers
TRUNCATE:
TRUNCATE does not support a WHERE clause
TRUNCATE works by directly removing the individual data pages of a table
TRUNCATE directly occupies a table-level lock.
(Because a lock is acquired, and because TRUNCATE can also participate in a transaction, it has to be a logged operation)
TRUNCATE is, therefore, a minimally-logged operation; again, this depends upon the recovery model of the database
Triggers are not fired when TRUNCATE is used (because individual row deletions are not logged)
Finally, Vinod popped the big homework question that must be critically analyzed:
“We know that we can restrict a DELETE operation to a particular user, but how can we restrict the TRUNCATE operation to a particular user?”
After returning home and having a nice cup of coffee, I noticed that my gray cells immediately started to work. Below was the result of my research.
As what is always said, the devil is in the details. Upon looking at the Permissions section for the TRUNCATE statement in Books On Line, the following jumps right out:
“The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.“
Now, what does this mean? Unlike DELETE, one cannot directly assign permissions to a user/set of users allowing or revoking TRUNCATE rights. However, there is a way to circumvent this. It is important to recall that in Microsoft SQL Server, database engine security surrounds the concept of a “securable”, which is any object like a table, stored procedure, trigger, etc. Rights are assigned to a principal on a securable. Refer to the image below (taken from the SQL Server Books On Line).
urable”, which is any object like a table, stored procedure, trigger, etc. Rights are assigned to a principal on a securable. Refer to the image below (taken from the SQL Server Books On Line).
SETTING UP THE ENVIRONMENT – (01A_Truncate Table Permissions.sql) Script Provided at the end of the article.
By the end of this demo, one will be able to do all the CRUD operations, except the TRUNCATE, and the other will only be able to execute the TRUNCATE.
All you will need for this test is any edition of SQL Server 2008. (With minor changes, these scripts can be made to work with SQL 2005.)
We begin by creating the following:
1. A test database
2. Two database roles: associated logins and users
3. Switch over to the test database and create a test table. Then, add some data into it. I am using row constructors, which is new to SQL 2008.
Creating the modules that will be used to enforce permissions
1. We have already created one of the modules that we will be assigning permissions to. That module is the table: TruncatePermissionsTest
2. We will now create two stored procedures; one is for the DELETE operation and the other for the TRUNCATE operation. Please note that for all practical purposes, the end result is the same – all data from the table TruncatePermissionsTest is removed
Assigning the permissions
Now comes the most important part of the demonstration – assigning permissions. A permissions matrix can be worked out as under:
To apply the security rights, we use the GRANT and DENY clauses, as under:
That’s it! We are now ready for our big test!
THE TEST (01B_Truncate Table Test Queries.sql) Script Provided at the end of the article.
I will now need two separate SSMS connections, one with the login AllowedTruncate and the other with the login RestrictedTruncate.
Running the test is simple; all that’s required is to run through the script – 01B_Truncate Table Test Queries.sql. What I will demonstrate here via screen-shots is the behavior of SQL Server when logged in as the AllowedTruncate user. There are a few other combinations than what are highlighted here. I will leave the reader the right to explore the behavior of the RestrictedTruncate user and these additional scenarios, as a form of self-study.
1. Testing SELECT permissions
2. Testing TRUNCATE permissions (Remember, “deny by default”?)
3. Trying to circumvent security by trying to TRUNCATE the table using the stored procedure
Hence, we have now proved that a user can indeed be assigned permissions to specifically assign TRUNCATE permissions. I also hope that the above has sparked curiosity towards putting some security around the probably “destructive” operations of DELETE and TRUNCATE.
I would like to wish each and every one of the readers a very happy and secure time with Microsoft SQL Server.
(Please find the scripts – 01A_Truncate Table Permissions.sql and 01B_Truncate Table Test Queries.sql that have been used in this demonstration. Please note that these scripts contain purely test-level code only. These scripts must not, at any cost, be used in the reader’s production environments).
01A_Truncate Table Permissions.sql
/*
*****************************************************************************************************************
Developed By : Nakul Vachhrajani
Functionality : This demo is focused on how to allow only TRUNCATE permissions to a particular user
How to Use : 1. Run through, step-by-step through the sequence till Step 08 to create a test database
2. Switch over to the "Truncate Table Test Queries.sql" and execute it step-by-step
in two different SSMS windows, one where you have logged in as 'RestrictedTruncate',
and the other as 'AllowedTruncate'
3. Come back to "Truncate Table Permissions.sql"
4. Execute Step 10 to cleanup!
Modifications :
December 13, 2010 - NAV - Updated to add a security matrix and improve code readability when applying security
December 12, 2010 - NAV - Created
*****************************************************************************************************************
*/
-- Step 01: Create a new test database
CREATE DATABASE TruncateTestDB
GO
USE TruncateTestDB
GO
-- Step 02: Add roles and users to demonstrate the security of the Truncate operation
-- 2a. Create the new roles
CREATE ROLE AllowedTruncateRole;
GO
CREATE ROLE RestrictedTruncateRole;
GO
-- 2b. Create new logins
CREATE LOGIN AllowedTruncate WITH PASSWORD = 'truncate@2010',
CHECK_POLICY = ON
GO
CREATE LOGIN RestrictedTruncate WITH PASSWORD = 'truncate@2010',
CHECK_POLICY = ON
GO
-- 2c. Create new Users using the roles and logins created aboave
CREATE USER TruncateUser FOR LOGIN AllowedTruncate WITH DEFAULT_SCHEMA = dbo
GO
CREATE USER NoTruncateUser FOR LOGIN RestrictedTruncate WITH DEFAULT_SCHEMA = dbo
GO
-- 2d. Add the newly created login to the newly created role
sp_addrolemember 'AllowedTruncateRole','TruncateUser'
GO
sp_addrolemember 'RestrictedTruncateRole','NoTruncateUser'
GO
-- Step 03: Change over to the test database
USE TruncateTestDB
GO
-- Step 04: Create a test table within the test databse
CREATE TABLE TruncatePermissionsTest (Id INT IDENTITY(1,1),
Name NVARCHAR(50))
GO
-- Step 05: Populate the required data
INSERT INTO TruncatePermissionsTest VALUES (N'Delhi'),
(N'Mumbai'),
(N'Ahmedabad')
GO
-- Step 06: Encapsulate the DELETE within another module
CREATE PROCEDURE proc_DeleteMyTable
WITH EXECUTE AS SELF
AS DELETE FROM TruncateTestDB..TruncatePermissionsTest
GO
-- Step 07: Encapsulate the TRUNCATE within another module
CREATE PROCEDURE proc_TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE TruncateTestDB..TruncatePermissionsTest
GO
-- Step 08: Apply Security
/*
*****************************SECURITY MATRIX***************************************
===================================================================================
Object | Permissions | Login
| | AllowedTruncate | RestrictedTruncate
| |User:NoTruncateUser| User:TruncateUser
===================================================================================
TruncatePermissionsTest | SELECT, | GRANT | (Default)
| INSERT, | |
| UPDATE, | |
| DELETE | |
-------------------------+-------------+-------------------+-----------------------
TruncatePermissionsTest | ALTER | DENY | (Default)
-------------------------+-------------+----*/----------------+-----------------------
proc_DeleteMyTable | EXECUTE | GRANT | DENY
-------------------------+-------------+-------------------+-----------------------
proc_TruncateMyTable | EXECUTE | DENY | GRANT
-------------------------+-------------+-------------------+-----------------------
*****************************SECURITY MATRIX***************************************
*/
/* Table: TruncatePermissionsTest*/
GRANT SELECT, INSERT, UPDATE, DELETE ON TruncateTestDB..TruncatePermissionsTest
TO NoTruncateUser
GO
DENY ALTER ON TruncateTestDB..TruncatePermissionsTest
TO NoTruncateUser
GO
/* Procedure: proc_DeleteMyTable*/
GRANT EXECUTE ON TruncateTestDB..proc_DeleteMyTable
TO NoTruncateUser
GO
DENY EXECUTE ON TruncateTestDB..proc_DeleteMyTable
TO TruncateUser
GO
/* Procedure: proc_TruncateMyTable*/
DENY EXECUTE ON TruncateTestDB..proc_TruncateMyTable
TO NoTruncateUser
GO
GRANT EXECUTE ON TruncateTestDB..proc_TruncateMyTable
TO TruncateUser
GO
-- Step 09: Test
--Switch over to the "Truncate Table Test Queries.sql" and execute it step-by-step in two different SSMS windows:
-- 1. one where you have logged in as 'RestrictedTruncate', and
-- 2. the other as 'AllowedTruncate'
-- Step 10: Cleanup
sp_droprolemember 'AllowedTruncateRole','TruncateUser'
GO
sp_droprolemember 'RestrictedTruncateRole','NoTruncateUser'
GO
DROP USER TruncateUser
GO
DROP USER NoTruncateUser
GO
DROP LOGIN AllowedTruncate
GO
DROP LOGIN RestrictedTruncate
GO
DROP ROLE AllowedTruncateRole
GO
DROP ROLE RestrictedTruncateRole
GO
USE MASTER
GO
DROP DATABASE TruncateTestDB
GO
01B_Truncate Table Test Queries.sql
/*
*****************************************************************************************************************
Developed By : Nakul Vachhrajani
Functionality : This demo is focused on how to allow only TRUNCATE permissions to a particular user
How to Use : 1. Switch over to this from "Truncate Table Permissions.sql", Step #09
2. Execute this step-by-step in two different SSMS windows
a. One where you have logged in as 'RestrictedTruncate', and
b. The other as 'AllowedTruncate'
3. Return back to "Truncate Table Permissions.sql"
4. Execute Step 10 to cleanup!
Modifications :
December 12, 2010 - NAV - Created
*****************************************************************************************************************
*/
-- Step 09A: Switch to the test database
USE TruncateTestDB
GO
-- Step 09B: Ensure that we have valid data
SELECT * FROM TruncatePermissionsTest
GO
-- (Expected: Following error will occur if logged in as "AllowedTruncate")
-- Msg 229, Level 14, State 5, Line 1
-- The SELECT permission was denied on the object 'TruncatePermissionsTest', database 'TruncateTestDB', schema 'dbo'.
--Step 09C: Attempt to Truncate Data from the table without using the stored procedure
TRUNCATE TABLE TruncatePermissionsTest
GO
-- (Expected: Following error will occur)
-- Msg 1088, Level 16, State 7, Line 2
-- Cannot find the object "TruncatePermissionsTest" because it does not exist or you do not have permissions.
-- Step 09D:Regenerate Test Data
INSERT INTO TruncatePermissionsTest VALUES (N'London'),
(N'Paris'),
(N'Berlin')
GO
-- (Expected: Following error will occur if logged in as "AllowedTruncate")
-- Msg 229, Level 14, State 5, Line 1
-- The INSERT permission was denied on the object 'TruncatePermissionsTest', database 'TruncateTestDB', schema 'dbo'.
--Step 09E: Attempt to Truncate Data from the table using the stored procedure
EXEC proc_TruncateMyTable
GO
-- (Expected: Will execute successfully with 'AllowedTruncate' user, will error out as under with 'RestrictedTruncate')
-- Msg 229, Level 14, State 5, Procedure proc_TruncateMyTable, Line 1
-- The EXECUTE permission was denied on the object 'proc_TruncateMyTable', database 'TruncateTestDB', schema 'dbo'.
-- Step 09F:Regenerate Test Data
INSERT INTO TruncatePermissionsTest VALUES (N'Madrid'),
(N'Rome'),
(N'Athens')
GO
--Step 09G: Attempt to Delete Data from the table without using the stored procedure
DELETE FROM TruncatePermissionsTest
GO
-- (Expected: Following error will occur if logged in as "AllowedTruncate")
-- Msg 229, Level 14, State 5, Line 2
-- The DELETE permission was denied on the object 'TruncatePermissionsTest', database 'TruncateTestDB', schema 'dbo'.
-- Step 09H:Regenerate Test Data
INSERT INTO TruncatePermissionsTest VALUES (N'Spain'),
(N'Italy'),
(N'Greece')
GO
--Step 09I: Attempt to Delete Data from the table using the stored procedure
EXEC proc_DeleteMyTable
GO
-- (Expected: Following error will occur if logged in as "AllowedTruncate")
-- Msg 229, Level 14, State 5, Procedure proc_DeleteMyTable, Line 1
-- The EXECUTE permission was denied on the object 'proc_DeleteMyTable', database 'TruncateTestDB', schema 'dbo'.
--Step 09J: Close this SSMS window and return back to "Truncate Table Permissions.sql"
Thank you Nakul to take up the challenge and prove that Ahmedabad and Gandhinagar SQL Server User Group has talent to solve difficult problems.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Filed under: Best Practices, Pinal Dave, Readers Contribution, Readers Question, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Tips and Tricks, T SQL, Technology
