Transact SQL – Securing Personally Identifiable Information ( PII) Data at the Database Column Level

Background

We are letting more and more people directly query our database.

But, as we have some Personal Identifier Information ( PII) columns we have to be a bit careful.

Those columns are encrypted, but unfortunately we are using the same symmetric keys and those users need access to some of the encrypted columns, but are to be denied access to others.

 

Denying access to some columns

Granting and Revoking Object & Column permissions

Tasks

  1. Grant view definition on symmetric key
  2. Grant control on certificate
  3. Grant select on specific tables
  4. Revoke select on specific columns ( the targeted table list is a subset of tables listed earlier )

Code


/* 
	Encryption Objects
*/
grant View Definition on  SYMMETRIC KEY::[symkeyPII] to [roleUser]
go

grant control on certificate::[certPII] to [roleUser]
go

grant execute on dbo.Salt  to [roleUser]
go

/* 
	Tables
*/
grant select on [dbo].[employee] to [roleUser]
go

grant select on [dbo].[salary] to [roleUser]
go


/* 
	Table -  Columns
*/
revoke select on 
		 OBJECT::[dbo].[employee]
			(
				  [last4ssn]
				, [drvlicense]
			)
		 from [roleUser]
GO  

go

 

 

Review Granted & Denied Permission Sets

Here is a Stored Procedure that lists granted and denied permissions.

Code



use master
go

if object_id('dbo.sp_ListObjectLevelPermissions') is null
begin

	exec('create procedure dbo.sp_ListObjectLevelPermissions as select 1/0 ')

end
go

alter procedure dbo.sp_ListObjectLevelPermissions
(
	@username sysname
)
as

begin

	/*
		Get Object Level Permissions
	*/
	SELECT
			  [UserName]
			= USER_NAME(dppriper.grantee_principal_id)
 
		, [principalType]
			= dppri.type_desc
 
		, [classDesc]
			= dppriper.class_desc
 
		, objectName
			=
			case dppriper.class_desc    
				when 'DATABASE' then db_Name(dppriper.major_id) 
				when 'OBJECT_OR_COLUMN'
					   then Object_Schema_Name(dppriper.major_id) 
									  + '.'
									  + object_Name(dppriper.major_id) 
				when 'SCHEMA' then schema_Name(dppriper.major_id) 
				when 'SYMMETRIC_KEYS' then
				   ( 
						select [name] 
						from sys.symmetric_keys 
						where dppriper.major_id
							   = symmetric_key_id
					)
					when 'CERTIFICATE' then
					(
						select [name] 
						from sys.certificates 
						where dppriper.major_id = certificate_id
					)
			end


		, columnName
			=
			case dppriper.class_desc    
				when 'OBJECT_OR_COLUMN'
					   then tblSC.name
				else NULL
			end
 
		, [permissionName]
			= dppriper.permission_name
 
		, [permissionState]
			= dppriper.state_desc

		, [grantStatement]
			=
			case dppriper.class_desc    

				when 'DATABASE' 
					then
							  dppriper.state_desc + ' '
							+ dppriper.permission_name
							+ case 

								when dppriper.[state] in ('G', 'W')
									 then ' to '

								when dppriper.[state] in ('D', 'R')
										 then ' from '

							  end
							+ quotename(USER_NAME(dppriper.grantee_principal_id))
							+ ';'
								collate database_default


				when 'OBJECT_OR_COLUMN'
					   then dppriper.state_desc + ' '
								+ dppriper.permission_name
								+ ' on '
								+ quotename(Object_Schema_Name(dppriper.major_id)) 
								+ '.'
								+ quotename(object_Name(dppriper.major_id) )
								+ ' '
								+
									case
										when ( dppriper.minor_id is null ) then ''
										when ( dppriper.minor_id = 0 ) then ''
										else '('
												+ 
													case dppriper.class_desc    
															when 'OBJECT_OR_COLUMN'
																then quotename(tblSC.name)
														 else ''
													end
											 + ')'
									end

								+ case 

									when dppriper.[state] in ('G', 'W')
										 then ' to '

									when dppriper.[state] in ('D', 'R')
											 then ' from '

									else 'N/A'

								  end

								+ quotename(USER_NAME(dppriper.grantee_principal_id))
								+ ';'
								 collate database_default

				when 'SCHEMA' 
						then
								  dppriper.state_desc + ' '
								+ dppriper.permission_name
								+ ' on '
								+ ' SCHEMA :: '
								+ quotename(Schema_Name(dppriper.major_id)) 
								+ ' to '
								+ quotename(USER_NAME(dppriper.grantee_principal_id))
								+ ';'
								 collate database_default

				when 'SYMMETRIC_KEYS' 
					   then dppriper.state_desc + ' '
								+ dppriper.permission_name
								+ ' on '
								+ ' symmetric key::'
								+
								   ( 
										select quotename( [name] )
										from sys.symmetric_keys 
										where dppriper.major_id
											   = symmetric_key_id
									)
								+ ' to '
								+ quotename(USER_NAME(dppriper.grantee_principal_id))
								+ ';'
								 collate database_default

				when 'CERTIFICATE' 
					   then dppriper.state_desc + ' '
								+ dppriper.permission_name
								+ ' on '
								+ ' certificate::'
								+
								   ( 
										select quotename( [name] )
										from sys.[certificates] tblSC
										where dppriper.major_id
											   = certificate_id
									)
								+ ' to '
								+ quotename(USER_NAME(dppriper.grantee_principal_id))
								+ ';'
								 collate database_default



			end

			--, dppriper.major_id			
 
	FROM    sys.database_permissions dppriper
 
	INNER JOIN sys.database_principals dppri
 
		   ON dppriper.grantee_principal_id = dppri.principal_id

	LEFT OUTER JOIN sys.columns tblSC
 
		    ON dppriper.major_id = tblSC.object_id
		   AND dppriper.minor_id = tblSC.column_id

 
	where dppriper.grantee_principal_id 
		= case
				 when @username is null then dppriper.grantee_principal_id
			 
				 else user_id(@username)
 
			  end

	UNION ALL

	/*
		Get Role Permissions
	*/
	SELECT
			  [UserName]
			= USER_NAME(dppriper.member_principal_id)
 
		, [principalType]
			--= dppri.type_desc
 			= 'Database Role'

		, [classDesc]
			--= dppriper.class_desc
			= 'Database Role'
 
		, objectName
			= 'Database' collate database_default

		, columnName
			= null

		, [permissionName]
			= cast(dppriR.name as sysname) collate database_default

			 
		, [permissionState]
			= 'Grant' collate database_default

		, [grantStatement]
			= 'exec sp_addrolemember '
					+ '  @rolename = '
					+ quotename(dppriR.name)
					+ ' '
					+ ' ,'
					+ '  @membername = '
					+ quotename(USER_NAME(dppriper.member_principal_id))
					+ ' '
					+ ';'
				 collate database_default

 
    FROM	sys.database_role_members dppriper
 
	INNER JOIN sys.database_principals dppriM
 
		   ON dppriper.member_principal_id = dppriM.principal_id

	INNER JOIN sys.database_principals dppriR
 
		   ON dppriper.role_principal_id = dppriR.principal_id

	where dppriper.member_principal_id 
		= case
				 when @username is null then dppriper.member_principal_id
			 
				 else user_id(@username)
 
		  end


	order by
		  USERNAME
		, [principalType]
		, objectName
		, permissionName


end

go

grant execute on [dbo].[sp_ListObjectLevelPermissions] to [public]
go

EXEC sys.sp_MS_marksystemobject 'dbo.sp_ListObjectLevelPermissions'
go



Review Permissions

Code


use [HRDB]
go

declare @username sysname

set @username = 'roleUser'

exec dbo.sp_ListObjectLevelPermissions
 @username = @username

Output

permissionSet

Source Control

GitHub

Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s