Creating or Editing Row Filter Policies
You can use the TDV user interface to lead you through the creation of policies for row-based security. By selecting the default, Tabular option, TDV takes care of creating all the SQL scripting necessary to define the policy.
Column names referenced in the filter expression are not validated. You must test each row filter policy that you define to make sure that it works as expected. For example, if a filter is on REGION_NAME in the LOCATION table and the column is changed to REGION_CODE, the filter must be updated to reflect that change.
The SQL script for free-form row filter policies can be edited using Studio. All other definitions, editing, or deleting of row-based security objects must be done using Manager.
Note: There is no ranking or priority of the rules within a row filter policy. If a user or group is assigned to more than one rule within a row filter policy, the two predicates are combined using OR statements.
To add or edit a row-based security policy
2. To add a new row filter policy, click Add Policy. Or, to edit an existing row filter policy, select it and click Edit Policy.
3. Select or specify values for the following fields:
Field | Specify |
Name | Specifies the name you want to give to the row filter policy. Spaces are not allowed. |
Folder | Specify the location within /shared for the SQL Script procedure in the Folder field when adding a new row filter policy. If you are editing an existing row filter policy, the Folder field is read only. The default value for this field is /shared. |
Enabling | • Enable—Allow the use of the specific row filter policy. • Disable—Disallow the use of the specific row filter policy. Regardless of this setting, you can still add and edit the row filter policy. If the policy is disabled, it is not used against the data, even if the policy is part of a group. For example, if an RBS_group contains three policies (pig, hive, and gnu) and the gnu policy is disabled, when the RBS_group is applied only the pig and hive policies are used. |
Specification | • Tabular—Select to have TDV create the SQL script for you. • Free-Form—Select to create the SQL script on your own. • Group—Select to create a container that can include several row filter policies. |
Description | (Optional) Type an explanation of the row filter policy. |
If you selected Free-Form, skip to Step 7. If you selected tabular, the screen displays additional fields and buttons.
4. If you selected Tabular, you can add or edit a rule for a particular user or group using the Manager screens. Click Add or Edit, and then edit or specify values for the fields listed in the table below.
Field | Specify |
User/Group | Specifies whether this identity is for a Group or a User. |
Domain | Specifies the Domain to which you want the row filter policy to apply. |
Name | Specifies the user or group name to which you want the row filter policy to apply. |
Rule | Specify All, None, Predicate, or Procedure. |
Data/Path | If you selected Predicate or Procedure, then specify the string used to filter the data. For example, Region=’EUR’. |
5. Click OK.
6. If you selected Tabular, you can specify values for the following fields:
Field | Specify |
Default Rule | Specify rules to be carried out if an identity is matched, or a default rule to be used when no identity is matched. All Rows—Allow all rows to be returned. No Rows—Do not allow any rows to be returned. Predicate—Return only rows satisfying an explicit SQL predicate. A predicate is a part of a WHERE clause. Procedure—Return only rows satisfying a predicate returned by a different SQL script procedure. |
Data/Path | If you selected Predicate or Procedure, then specify the string used to filter the data, or a SQL script procedure that returns a valid filter expression (typically this is a WHERE-clause fragment). The SQL Script procedure must be a valid row filter procedure. For example, Region=’EUR’. Or you can input the path to a procedure. |
7. Click OK.
8. To edit the SQL script associated with the custom (free-form) row filter policy:
a. Open Studio.
b. Navigate to the /shared folder and then to the SQL script resource object that has the name of the custom row filter policy you created.
c. Edit the SQL script procedure. For row filter policies defined using the tabular method, you can view the SQL script, but you cannot edit it. For information on how the edit the script, see “Creating a SQL Script” in the TDV User Guide.
The following is a sample SQL script that defines a row filter policy:
PROCEDURE "policy1" (IN alias VARCHAR, OUT result VARCHAR)
BEGIN
DECLARE temp VARCHAR;
DECLARE test BOOLEAN;
SET result = '';
CALL /lib/users/TestUserIdentity('USER','admin','composite',test);
IF(test) THEN
SET result = '(' || alias || '.OrderID = 20)';
END IF;
CALL /lib/users/TestUserIdentity('USER','test','composite',test);
IF(test) THEN
SET temp = '(' || alias || '.OrderID < 20)';
IF (result = '') THEN
SET result = temp;
ELSE
SET result = result || ' OR ' || temp;
END IF;
END IF;
IF ( result = '') THEN
SET result = 'FALSE';
END IF;
END