How to insert multiple rows to a table from a bitmap

Question

I need to extract data from a third party system (I have no influence over its design). It's a SQL Server 2005 database uses bitmaps to store user privileges. It has five INT fields giving a maximum of 5 * 32 = 160 privileges. It stores a number of types of privilege and re-uses the bitmaps for each type. So in total there are 6 fields that drive privileges. Each privilege can be assigned to a specific item of a given type.

An example of a type is “table” so items in that context would be table names.

The privilege table looks like this:

ID | PRIVTYPE | USERNAME | ITEMNAME | BITMAP1 | BITMAP2 | BITMAP3 | BITMAP4 | BITMAP5

For example

123 | Table | Joe | Customers | 0x408 | 0x1 | 0x5c | 0x1000 | 0x0

Another table contains the privileges represented by each bit. It looks like this:

PRIVTYPE | BITMAP_ID | BITVALUE | PRIVILEGE_NAME

For example, entries relating to the above bitmaps would be:

Table | 1 |0x8   | View
Table | 1 |0x400 | Edit
Table | 2 |0x1   | Report
Table | 3 |0x4   | View Address Data
Table | 3 |0x8   | View Order Data
Table | 3 |0x10  | View Payment Data
Table | 3 |0x40  | View System Data
Table | 4 |0x1000| View Hidden Fields

I want to somehow parse the privilege table into a new table or view that will have one record per user per item privilege. Like this:

USERNAME | ITEMNAME |PRIVILEGE_NAME
Joe | Table | Customers | View
Joe | Table | Customers | Edit
Joe | Table | Customers | Report
Joe | Table | Customers | view Address Data
Joe | Table | Customers | view Order Data
Joe | Table | Customers | view Payment Data
Joe | Table | Customers | view System Data
Joe | Table | Customers | view Hidden Fields

I think I need to create a view by running a select statement that will return multiple rows for each row in the privilege table: one row for every set bit in a bitmask field. So, for example, a single row in the privilege table that has 3 bits set in the bitmasks will cause three rows to be returned.

I have searched for answers about breaking tables into multiple rows. I’ve looked at various joins and pivots but I can’t find something that will do what I need. Is the above possible? Any guidance appreciated…

score 1 · Accepted Answer · answered Nov 03 '11 at 07:14

You could unpivot the first table (called @UserPrivileges below) and join it to the second one (@Privileges) on privilege type, bitmap ID and the result of bitwise AND between the bitmap in the first table and BITVALUE in the second table.

Below is my implementation.

Setup:

DECLARE @UserPrivileges TABLE (
  ID int,
  PRIVTYPE varchar(50),
  USERNAME varchar(50),
  ITEMNAME varchar(50),
  BITMAP1 int,
  BITMAP2 int,
  BITMAP3 int,
  BITMAP4 int,
  BITMAP5 int
);
INSERT INTO @UserPrivileges
  (ID, PRIVTYPE, USERNAME, ITEMNAME, BITMAP1, BITMAP2, BITMAP3, BITMAP4, BITMAP5)
SELECT 123, 'Table', 'Joe', 'Customers', 0x408, 0x1, 0x5c, 0x1000, 0x0
;

DECLARE @Privileges TABLE (
  PRIVTYPE varchar(50),
  BITMAP_ID int,
  BITVALUE int,
  PRIVILEGE_NAME varchar(50)
);
INSERT INTO @Privileges (PRIVTYPE, BITMAP_ID, BITVALUE, PRIVILEGE_NAME)
SELECT 'Table', 1, 0x8   , 'View              ' UNION ALL
SELECT 'Table', 1, 0x400 , 'Edit              ' UNION ALL
SELECT 'Table', 2, 0x1   , 'Report            ' UNION ALL
SELECT 'Table', 3, 0x4   , 'View Address Data ' UNION ALL
SELECT 'Table', 3, 0x8   , 'View Order Data   ' UNION ALL
SELECT 'Table', 3, 0x10  , 'View Payment Data ' UNION ALL
SELECT 'Table', 3, 0x40  , 'View System Data  ' UNION ALL
SELECT 'Table', 4, 0x1000, 'View Hidden Fields'
;

Query:

WITH unpivoted AS (
  SELECT
    ID,
    PRIVTYPE,
    USERNAME,
    ITEMNAME,
    RIGHT(BITMAP_ID, 1) AS BITMAP_ID,  -- OR: STUFF(BITMAP_ID, 1, 6, '')
                                       -- OR: SUBSTRING(BITMAP_ID, 7, 999)
                                       -- OR: REPLACE(BITMAP_ID, 'BITMAP', '')
    BITMAP_VAL
  FROM UserPrivileges
  UNPIVOT (
    BITMAP_VAL FOR BITMAP_ID IN (
      BITMAP1, BITMAP2, BITMAP3, BITMAP4, BITMAP5
    )
  ) u
),
joined AS (
  SELECT
    u.USERNAME,
    u.PRIVTYPE,
    u.ITEMNAME,
    p.PRIVILEGE_NAME
  FROM unpivoted u
    INNER JOIN Privileges p
       ON u.PRIVTYPE = p.PRIVTYPE
      AND u.BITMAP_ID = p.BITMAP_ID
      AND u.BITMAP_VAL & p.BITVALUE <> 0
)
SELECT * FROM joined

Results:

USERNAME  PRIVTYPE  ITEMNAME   PRIVILEGE_NAME
--------  --------  ---------  ------------------
Joe       Table     Customers  View              
Joe       Table     Customers  Edit              
Joe       Table     Customers  Report            
Joe       Table     Customers  View Address Data 
Joe       Table     Customers  View Order Data   
Joe       Table     Customers  View Payment Data 
Joe       Table     Customers  View System Data  
Joe       Table     Customers  View Hidden Fields

score 0 · Answer 2 · answered Nov 02 '11 at 15:01

0

If this is a ONE OFF task or something that will only run RARELY, then CURSORS!

Otherwise, just do 6 distinct select statements that are unionized in your insert:

INSERT INTO FOO (myName, myValue)
SELECT myName, myCol1
  From BAR
UNION
 SELECT myName, myCol2
   FROM BAR
UNION
 SELECT myName, myCol3
   FROM BAR

answered Nov 02 '11 at 15:01

Stephen Wrighton

36,783
6
67
86

I've been trying stuff around unions but the data "myCol1/2/3" are in a bitmap in the same field. I am unsure what the best way is to extract these so I can then do such a union. I've tried this sort of think to make a new column representing one bit but it doesn't scale when there are many bits: /* Table Privileges */ ,CASE p.[PRIVTYPE] WHEN 0x00000014 -- Table THEN CAST (p.[BITMAP1] & 0x8 as INT) / 0x8 ELSE 0 END as PRIVILEGE_VIEW – starfry Nov 02 '11 at 15:13
Ah, I'm sorry, I misunderstood. – Stephen Wrighton Nov 02 '11 at 15:24

How to insert multiple rows to a table from a bitmap

2 Answers2