You store the bitmask in an int or bigint field, as the sum of their permissions.The value is returned in a query and typically examined in the front end with bitwise operators, but can be done in SQL too.Say a value of:1 means "Can read page 1"2 means "Can read page 2"4 means "Can read page 3"So someone who can read page 1 and 3 (but not 2) gets a value of 5.You check who can do what with a bitwise and (&). Any non-zero value is true. Here's a simple sample:-- create user tableCreate Table #users (id int identity (1, 1), name varchar(32));insert #users (name) values ('Russell');insert #users (name) values ('jimoomba');insert #users (name) values ('Webfred');insert #users (name) values ('Visakh');GO-- permission tableCreate Table #permissions (id int, description varchar(16));insert #permissions values(1, 'read 1');insert #permissions values(2, 'read 2');insert #permissions values(4, 'read 3');insert #permissions values(8, 'read 4');GO-- map users to permissionsCreate Table #userPermissions (userid int, permissionID int)insert #userpermissions values(1, 1);insert #userpermissions values(2, 2);insert #userpermissions values(3, 5);insert #userpermissions values(4, 15);GO-- query to see who can read page 3SELECT u.name, 4 & up.permissionID as [can read page 3]FROM #users uJOIN #userPermissions upOn u.id = up.userid;GO-- any non-zero value is TRUEdrop table #userPermissionsdrop table #permissionsdrop table #users 
query to use maskvalues and provide access permiss
Posted - 2011-09-30 : 22:27:42
