In addition to the earlier post, we further discuss row level security here. We wanted to do the following:
1) Specify the tables that define the label categories and markings.
2) Create roles for these marking values.
3) Add an integer column to the base table.
4) Define the view.
Defining the labeling policies, we start by creating a few tables that define the category, marking, markinghierarchy, uniquelabel etc.
After we define the structure of labels in our policy, we define the roles. We create a role for each possible value for nonheirarchical categories that use any or all comparision rule. For hierarchical categories, we nest the roles to model the hierarchy.
We also create a helper view for our controls.
Next we define the changes to the base tables. These are the tables to which we add row level security. We assign the labelID to the row. Creating a non-clustered index on the labelID column will be very helpful. As an aside, it should be noted that this column need not be added to the base table and a junction table can be specified with IDs of the base table and the IDs of the labels.
Now we are ready for the last step which is to create the base table view for the end user.
Next let's look at the Insert, update and delete operations. All that we have discussed so far enables us to select the rows pertaining to labels and are generally allowed for all users but CRUD operations may or may not be allowed for all users. Besides these DML statements may not only be adhoc but come from stored procedures. Moreover, security labels may not be allowed with these operations. Usually they are set to a default value and then modified separately. When the users can specify the security labels, they can be specified . In general, updateable views permit only those rows to be edited that a user can be allowed to see.
When talking about labels its important to observe that labels themselves get misused. More than one labels could mean the same thing, labels may differ merely in spelling, labels may be concatenated or translated, labels may not be consistently used etc. In most of these cases, XML typing the labels prevents these issues.
Other common tasks with labels include the following :
getting an ID to represent a label - this is helpful for CRUD operations where the label is set,
checking whether an ID already exists - this is helpful to see if an ID already exists,
another variant of the above to check if an ID exists but not to generate a new one, and
translating labels to ID and back or finding the label corresponding to a user.
Finally cell level security and encryption could be used in addition to row level security.
This article courtesy of the literature on msdn.
1) Specify the tables that define the label categories and markings.
2) Create roles for these marking values.
3) Add an integer column to the base table.
4) Define the view.
Defining the labeling policies, we start by creating a few tables that define the category, marking, markinghierarchy, uniquelabel etc.
After we define the structure of labels in our policy, we define the roles. We create a role for each possible value for nonheirarchical categories that use any or all comparision rule. For hierarchical categories, we nest the roles to model the hierarchy.
We also create a helper view for our controls.
Next we define the changes to the base tables. These are the tables to which we add row level security. We assign the labelID to the row. Creating a non-clustered index on the labelID column will be very helpful. As an aside, it should be noted that this column need not be added to the base table and a junction table can be specified with IDs of the base table and the IDs of the labels.
Now we are ready for the last step which is to create the base table view for the end user.
Next let's look at the Insert, update and delete operations. All that we have discussed so far enables us to select the rows pertaining to labels and are generally allowed for all users but CRUD operations may or may not be allowed for all users. Besides these DML statements may not only be adhoc but come from stored procedures. Moreover, security labels may not be allowed with these operations. Usually they are set to a default value and then modified separately. When the users can specify the security labels, they can be specified . In general, updateable views permit only those rows to be edited that a user can be allowed to see.
When talking about labels its important to observe that labels themselves get misused. More than one labels could mean the same thing, labels may differ merely in spelling, labels may be concatenated or translated, labels may not be consistently used etc. In most of these cases, XML typing the labels prevents these issues.
Other common tasks with labels include the following :
getting an ID to represent a label - this is helpful for CRUD operations where the label is set,
checking whether an ID already exists - this is helpful to see if an ID already exists,
another variant of the above to check if an ID exists but not to generate a new one, and
translating labels to ID and back or finding the label corresponding to a user.
Finally cell level security and encryption could be used in addition to row level security.
This article courtesy of the literature on msdn.
No comments:
Post a Comment