I was trying to find a way in the forum to implement row level security easily.

One company I worked at did it with bit level operators in stored procedures.

Another one did with a seperate security table for each regular table that stored the keys of the roles that had access and what rows they had access to. Again, this was all handled in stored procedures.

I was thinking of doing it the following way:

Lets say we have roles 1, 2, 4, and 8 (think of them as bits, 2^0, 2^1, etc.)

We have a table Customer that has a column called Security (tinyint).

I have a function called GetSecurityValues(int RoleValue) that returns all possible tinyint values that could contain the role I want.

So for role 1 it would return ALL odd values. For role 2 it would return 2, 3, 6, 7, etc as an array.

Basically I'm doing a bit AND(&) operator, the really really hard way.

Then I would just use the CompareRange Predicate since the values are stored in an array.

It would be a simple way to implement security as I would only have to add one column to a table for every 63 roles I would have (bigint = 63 bits). But things get very slow even at 12 roles because the array of values is huge.

Does anyone know of a simpler/easier way to implement row level security?

The ways I have come across all have exclusive row-level security. What I want is the ability for user A and user B to both see Customer 1, but only User A can see Customer 2, and only user B can see customer 3. To add to that, User C could only see customer 1 and 3.

I hope that confused everyone.

long shot: you could try a fieldcompareexpression predicate in your filter and use a bitwise operator in the expression (on sqlserver that is) ?

That is a great idea!

I just read what the fieldcompareexpression does and that would be perfect. I had scanned the docmentation, but apparently that comparison didn't stick in my mind.

Thanks for the help. I love the product and I'm glad I purchased it, just have to figure out how to effectively use it.

Valdar wrote:

Does anyone know of a simpler/easier way to implement row level security?

One suggestion...

We actually used a binary 1000 field in our users table to store permissions. The id (integer identity field) of the Permission would be the position in the bit number. This gave us room for 8000 permissions (8 bits per byte.) We also had a similar field in our Roles Table.

In the users object (IIdentity) we have a bit array that stores the permissions the user had, ANDing it with the permissions (Bit string) of the roles the users was in had to compose their full permissions. We don't have deny permissions but we could have Xor those in, or whatever binary thing removes bits.

So, it sounds like above you use Roles only, and not permissions. And you assign record access at the role level. So, similar to above you could create a bit array that stored the roles the user was in, in his IIdentity object.

So, then in each record you could have the binary1000 field. This would hold the roles that could access the record. Then your pridicate expression you would need to build a expression that did a binary Or/And whatever was needed to return only the records which are allowed for the roles that you passed in.

Second suggestion...

If you are using impersonation you could use Views. Since the SQL Server would know what user it is getting data for.

Of course, in a Web app this probably isn't feasable because you are using connection pooling and the SQL server has no idea who it is getting data for.

The binary math makes my head spin. I usually have to sit down and figure it out before coding anything, such as, is it an And / Or / XAnd / Xor... ug.

As you said one of the simplest methods, have a table for each table that held the roles/permissions/users allowed to access each record. Then include a Join and an IN () query to return the right records. You could probably code something to add the security part to the predicate expression automatically in the Entitys that have row security so your UI code doesn't have to create this each time. This would probably be easier to abstract using the Adapter pattern, I assume.

Finally, you could use the bit string method above but do the filtering on the client after the fact.

BOb

Thanks for both suggestions. I will just have to test out both methods and see which one is going to be easiest for the long term.

People are very helpful on this board, I didn't expect two reponses so quickly.

PilotBoba,

I finally got around to implementing the row level security with the binary field, but SQL Server doesn't allow bit operators unless one of the arguments is a tiny, int, or bigint type. So with a binary field of 30 bytes I can't do a bitwise comparison.

How did you handle it? Did you retrieve all the relevant records then loop through each one and delete the ones they don't have access to?

Is there a faster way to do client side filtering? Our main table has a few hundred thousand rows so transferring large chunks of data from SQL server to the web server across the network just kills our response times (and our network).

For an individual record client side checking wouldn't be a problem, but for a list or search it seems like there has to be a better way with using the binary field.

Using the bigint seems to work, but with almost 300 users and 200 different roles (possible increasing later on), it could get real ugly having multiple bigint columns on each table.

Just to start we'd need 4 bigint columns instead of one nice 30 byte length binary field.

Thanks for any help on this.

I did do something that helps. I did used a SUBSTRING() functions on the binary field to get the byte at a specific location then I can do a binary operator on that byte.

I did this all in Query analyzer, however, and I'm not sure how to get it into LLBLGen code.

Does LLBLGen support SUBSTRING? Or a similar way, not using LIKE, that I can accomplish the same thing?

Valdar wrote:

How did you handle it? Did you retrieve all the relevant records then loop through each one and delete the ones they don't have access to?

We didn't manipulate the binary string in SQL at all.

BOb

How did you do the client side filtering then?

Using a foreach and checking each records binary field then deleting if they don't have access?

thanks for the help on this, btw, it'll be so nice to have a clean row level security implementation.

Valdar wrote:

How did you do the client side filtering then?

Using a foreach and checking each records binary field then deleting if they don't have access?

We never really used the bit array the way you are doing it. We used the bit array to store the roles(permissions) that a user was in. Let me see if I can dig out the code in out user object here...

        public bool IsInRole(string RoleName)
        {
            return Access(int.Parse(RoleName));
        }

        private bool Access(int AccessID)
        {
            int ByteIndex;

            int Bit;
            byte Mask = 0;
            byte Default = 1;

            //subtract one because byte index starts at 0
            ByteIndex = (int)Math.Ceiling(((double)AccessID / 8)) - 1;

            // Determine what bit of the bit the right is stored in
            Bit = AccessID - ByteIndex * 8;
            
            // Create a binary mask for this right
            Mask = GetMaskForBit(Bit);

            // user rights
            byte[] UserRoles = EffectiveRights;

            if ((Default & UserRoles[0]) > 0)  // IF first bit of first byte is turned on, access to everything
                return true;
            else if ((Mask & UserRoles[ByteIndex]) > 0) //access to item
                return true;
            else
                return false; //no access
        }

I'm not sure why you can do what you need server side though. But, I have to admit we didn't do our row level rights as a bit mask. But, I think it would be similar to the above.

BOb

We've started implementing it and writing a few tests for the row level security. It's actually working quite well. I created my own Predicate class (just extended the FieldComparePredicate) in order to use SubString. Had a few hiccups with that, but it's working great. I'm hoping to have a good test running of our intranet site with security implemented next week.

Now when a user searches for a customer it only shows the ones they have access to AND we can have overlapping access, not exclusive access to our rows.

In the BL I just do a quick check before returning a single row to ensure the user has access to read customers and to make sure they have access to read and actual customer.

We are also going to store our configuration options this way for customers and roles for our users. Using bit masks is so much easier than having 100 different columns.

Thanks for the help!

I've been following this thread and I am really intrigued with implementing Row-Level security using bit-masks.

Can anyone please explain the concept? 1- When do we create the authorized users/groups bitmasks for a row? Is that done when the row is created? If so, then which user's bitmask is assumed to have the authority to fetch this row (the user that created the row or all the groups that the user "who created the row" belongs to?

2- Do you check for the authority bitmask at the server or you have to bring the data-rows to the BL and there filter the rows the user is authorized to see.

3- The authority bitmasks are only concerned with seeing (fetching) the row or they can also decide if a user CAN see a row but CANNOT edit or delete the row?

I am sorry for all the questions, but the idea is indeed very useful and I want to try to build a design-pattern for implementing such security with LLBL

1- When do we create the authorized users/groups bitmasks for a row? Is that done when the row is created? If so, then which user's bitmask is assumed to have the authority to fetch this row (the user that created the row or all the groups that the user "who created the row" belongs to?

When the object is first created we set the default access levels to this object. This is determined by our business logic.

For example, Accounting always has access to all customer data for reading. So when a new invoice is created by a salesperson access is granted to our Admins, Accounting, and the salesperson that created the order. I beleive it is largely determined by business logic, but every row should be given read/update access to an Admin role and the person who created it by default.

2- Do you check for the authority bitmask at the server or you have to bring the data-rows to the BL and there filter the rows the user is authorized to see.

This depends on whether we are retrieving a group or a single entity. If it is a group we use our hacked substring predicate and filter on the server by selecting the correct byte and doing a bitwise & operation to see what rows we have access to.

ex: SELECT * FROM Customer WHERE (SUBSTRING(SecurityMask, 5, 1) & BitMask) > 0

SecurityMask is a binary 20 and BitMask is the RoleKey. if RoleKey = 1, check 1st bit if RoleKey = 2, check 2nd bit etc.

If it's a single entity we retrieve first then do a check. I suppose for consistency we could do a check on the sql server even for a single entity, but then we didn't want to write extra code for the entity collection, PredicateBucket, and type casting if we already have the Primary Key for the Entity.

3- The authority bitmasks are only concerned with seeing (fetching) the row or they can also decide if a user CAN see a row but CANNOT edit or delete the row?

Our Role table has something called RoleAccessMask and each bit represents a different OPERATION the Role has access to. This is seperate from the authority bitmask that we have on each row in our tables.

RoleAccessMask ex: Bit 1: Customer Create Access Bit 2: Customer Read Access Bit 3: Customer Update Access Bit 4: Customer Delete Access //we never really delete, only set our IsActive bit to 0. Bit 5: Invoice Create Access ...

So when a CustomerEntity must be fetched we first do a check to see if this role even has access to read from the customer table with our RoleAccessMask, then we do a check to see if the Role even has access to entity they want to access with the row's autority mask.

We also have more complex queries where we must do several checks at a time, but that is a requirement of the business logic.

I hope that answered your questions, below is just some comments about how we went about doing it.

The downside of this, for us, is that it has added an extra db hit every time we need to do any operation because we need to retrieve the data for the role after each request for our web apps.

Also, we implemented Sessions because we have other companies accessing our data through webservices or using our BL objects. Basically, our security object has a static method that takes a username and a password and IP Address and returns a SessionId. That SessionId is valid for 2 hours from creation.

We did this so that the username and password were not passed back and forth over http and other developers using our BL objects just passed in the sessionkey to all of our accessor/modifier methods instead of having access to the entire Security object and being able to spoof a rolekey.

It really works great and occasionally you run into a snafu where someone didn't give someone the correct access, but those are solved quickly. You will take a performance hit.