[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

Is there a shortcut to settings a set of bits in an MS SQL 2005 table.


I've got a table which contains a set of bits related to user permissions.

The actual list can be added to by the end user and the description for the bit is saved in the server as the column description.

So, a user adding a new security tag will not need to amend the code to be able to edit the column.

Currently I have to iterate the data coming from the client in PHP to be able to issue individual sql statements like these ...

UPDATE Users SET Permission{$s_Permission} = 0 WHERE UserID={$i_ID}
UPDATE Users SET Permission{$s_Permission} = 1 WHERE UserID={$i_ID}

for each permission.

$s_Permission is appropriately validated as it has to be in an array of known values (no security issue).

What I would like to do is something like ...

EXEC usp_SetPermissions @i_User = {$i_ID}, @s_Perms = '{$a_ValidPerms.join(',')}'

for example. Be able to supply all the valid perms in a single go.

If I have construct SQL statements in SQL just like I have to in PHP, then I'm saving very little.

I'm really looking to see if there is any new ways to handle this.

The perms don't have the "Permission" prefix on the PHP side, but that could be added as part of the SQL call if necessary.

Any ideas.


Richard Quadling
Richard Quadling
  • 3
  • 3
1 Solution
So you have a series of columns that can be added by the user?  It really would be best served to break them out into a name/value pair table so that the user just adds records to the database when a new security permission is added, and not a new column.
Richard QuadlingSenior Software DeverloperAuthor Commented:
Ah. A permissions table. Good point. A little more work, but a much better way.

If I'd started the project (rather than just picking it up), I think I may have eventually come round to that way of thinking.

But there is something to be said for having a 1 row = 1 account. Purity vs practicality. Though views and SPs are probably the better way to go anyway - hide the storage from the programming interface.

The permissions are the only thing the end users can add and they are only ever going to be bit columns. Using the column description to hold the meaning/label of the permission is really useful. And one of the rules of normalisation is that 1 column should never hold different data - ok, an interpretation here is that permission A is different data to permission B. Also as a user will not have 2 permission A's, a subtable is not best served - at least as I understand normalisation - but again purity vs practicality comes into play here.

Overall, I think I like the structure as it is (mainly the app is not really ready for any significant amount of reworking).

"Also as a user will not have 2 permission A's"

That can be managed with a unique constraint on the Permission_id and user_id.

What format is '{$a_ValidPerms.join(',')}' in?   If it's a comma delimited list, you can use a function I have written and used to split it out into records.  From there, you can generate dynamic SQL.

Using this function:

if object_id('[dbo].[fn_DelimitedToTable]') is not null
     drop function [dbo].[fn_DelimitedToTable]
create function [dbo].[fn_DelimitedToTable](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,theValue      nvarchar(max)
*    Author:        Brandon Galderisi
*    Last modified: 07-Oct-2008
*    Purpose:       splits an input string (@DelimitedString)
*                   on a delimiter (@delimiter) and outputs
*                   a table of values.

insert into @Values (thePosition,theValue)
            select n, substring(@delimiter + @DelimitedString + @delimiter, n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @DelimitedString + @delimiter, n + datalength(@delimiter)/2) - n - datalength(@delimiter)/2) as string_value
            from      dbo.vw_Nums
                  n <= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
                  and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter

-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
-- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
with   cte0 as (select 1 as c union all select 1),
       cte1 as (select 1 as c from cte0 a, cte0 b),
       cte2 as (select 1 as c from cte1 a, cte1 b),
       cte3 as (select 1 as c from cte2 a, cte2 b),
       cte4 as (select 1 as c from cte3 a, cte3 b),
       cte5 as (select 1 as c from cte4 a, cte4 b),
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums

Sample Usage:
select * from [dbo].[fn_DelimitedToTable]('a|%25basdf|%25c|%25d','|%25')
select theValue from [dbo].[fn_DelimitedToTable]('a','|')
select * from [dbo].[fn_DelimitedToTable]('a basdf c d',' ')

create usp_SetPermissions
 @i_User  int
,@s_Perms nvarchar(max)
declare @SQL nvarchar(max)
select @SQL = coalesce(@SQL + N'
', N'') + N'update Users SET Permission' + theValue + N' = 1 where userid = @i_user'
from [dbo].[fn_DelimitedToTable](@s_Perms,',')
exec sp_executesql @SQL, '@i_user int', @i_user = @i_user

Open in new window

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Richard QuadlingSenior Software DeverloperAuthor Commented:
Thanks for that. Fundamentally, it is a case of constructing the individual SETs. That's what I expected, but hoped for something different.

Actually, this way should be better.

You are still going to have to SET each column.
create usp_SetPermissions
 @i_User  int
,@s_Perms nvarchar(max)
declare @SQL nvarchar(max)
select @SQL = coalesce(@SQL + N',', N'update Users SET ') + N'Permission' + theValue + N' = 1'
from [dbo].[fn_DelimitedToTable](@s_Perms,',')
set @SQL = @SQL +  N' where userid = @i_user'
exec sp_executesql @SQL, '@i_user int', @i_user = @i_user

Open in new window

Richard QuadlingSenior Software DeverloperAuthor Commented:
Can definitely work with that!

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now