How to manage the IS_GRANTABLE security risk

14 November,2014 by Jack Vamvas

Question:An internal security audit   returned a finding of  databases tables  set with IS_GRANTABLE = yes , indicating that any user with access can grant access to other users.  That is considered a security violation by most organisations. What measures can be implemented to mitigate the security risk?

Answer:According to MSDN Online “IS_GRANTABLE  Indicates whether the GRANTEE is permitted to grant permissions to other users (often referred to as "grant with grant" permission). Can be YES, NO, or NULL. An unknown, or NULL, value refers to a data source for which "grant with grant" is not applicable.”

How to view the IS_GRANTABLE status

One way to view the  IS_GRANTABLE status is sp_column_priveleges ‘my_table_name’

The IS_GRANTABLE flag is a default. And does allow permissions to be granted at any object level.  Consideration should be given to creating a policy for the security risk .  It is not uncommon for SQL Injections to occur via identifying the whether IS_GRANTABLE is set to YES or NO

 Some methods to manage the security risk

1)      Turn the IS_GRANTABLE flag to NO.  Before you change any flags , consider the consequences of changing the flag. 

2)      Run a regular security violation report which monitors for database users who have object level permissions.

3)       Database security is done with the use of AD groups or SQL Roles & Remove the use of  unauathorised elevated privileges

4)      Run a regular Database Server Security Audit

Read More on managing SQL Server Roles

How to Script database role permissions and securables

SQL Server - Find sql database users and Find database roles

SQL Database roles and members

SQL Server - Orphaned users and how to Synchronise Users and Logins after SQL Server 2005 restore

SQL Server - List all SQL Server users

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to manage the IS_GRANTABLE security risk


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer