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

Posted on 2009-02-16
Last Modified: 2012-05-06

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.


Question by:RQuadling
    LVL 39

    Expert Comment

    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.
    LVL 40

    Author Comment

    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).

    LVL 39

    Expert Comment

    "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

    LVL 40

    Author Comment

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

    LVL 39

    Accepted Solution

    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

    LVL 40

    Author Closing Comment

    Can definitely work with that!

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now