Post reply

Implementing conditional permissions

  • June 7, 2004 at 10:30 am

    #160962

    I'm embarking on creating a system that is going to require "conditional permissions", i.e. permission is sometimes defined by data in the table.

    For example, if an Item has it's "Status" field set to "Current" only select few people would be allowed to modify select columns. That is a basic example.

    So, I've been thinking to just extend the Roles that sql server already supports with a few tables. Then I want to create some triggers that will check these permissions as updates and inserts occur, making this additional complexity transparent to the application.

    I've been able to prove that it can be done at least (I know, it is not _that_ advanced) with a simple test implementation, but it seems like it requires (or at least really really wants) dynamic sql in the triggers and cursors too. That's probably a bad approach to be using both of those in triggers that would be firing a lot, from what I've heard anyway.

    Has anybody done anything like this, or have any advice to offer?

    I'm thinking maybe I should dynamically create the triggers (based off of table schema) and that could help keep dynamic sql out of the tables' triggers.

    I can post some details and schema as my code develops.

    For example on why dynamic sql is important, one of my tables I created to handle these extended permissions is a "PermissionConditions" table, and it is a list of conditions that must evaluate to true for the permission to be applicable. Schema (and sample rows) look like this:

    ID | PermissionID | [Table] | Field | [Value]

    -----------------------------------------------------

    1 1 Items Status Current

    2 1 Items Season Fall2004

    Then in the trigger this condition would have to be tested. Dynamic SQL is obviously advantageous here, you could do it like this...

    SET @sql = 'SELECT * FROM ' + @table + ' WHERE ' + @field + ' = ''' + @value + ''' AND ' + @field2 + ' = ''' + @value2 + ''''

    Execute(@sql)

    Anyway, I don't think that is the best approach. Any discussion on this is appreciated and would hopefully be enlightening to all.

  • June 7, 2004 at 11:45 am

    #509292

    This might be simpler (I have not tested it)...

    Create a trigger with:

    IF (SELECT CONVERT(CHAR(30), CURRENT_USER)) IN ('list of approved usernames)

    THEN <code here to allow change>

    ELSE <code here to not allow change>

    -SQLBill

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply