Administration Guide > Managing Column-Based Security > Creating or Editing Column Filter Policies
 
Creating or Editing Column Filter Policies
You can use the TDV user interface to lead you through the creation of policies for Column-Based security.
Column names referenced in the filter expression are not validated. You must test each Column 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.
Note: There is no ranking or priority of the rules within a Column Filter policy. If a user or group is assigned to more than one rule within a Column Filter policy, the two predicates are combined using OR statements.
To add or edit a Column-Based security policy
1. Follow the instructions in Enabling Column-Based Security on TDV Resources.
2. To add a new Column Filter policy, click Add Policy. Or, to edit an existing Column Filter policy, select it and click Edit Policy.
3. Select or specify values for the following fields:
Field
Specify
Policy Name
Specifies the name you want to give to the Column Filter policy. Spaces and ‘/’ are not allowed.
Data Type
• String—Valid assignments are: CHAR, VARCHAR, LONGVARCHAR, BOOLEAN.
• Integer—Valid assignments are: TINYINT, SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, FLOAT, REAL, DOUBLE,CHAR, VARCHAR, BIT.
• Decimal—Valid assignments are: DECIMAL, NUMERIC, FLOAT, REAL, DOUBLE, CHAR, VARCHAR.
• Date—Valid assignments are: DATE,CHAR, VARCHAR.
• Datetime—Valid assignments are: TIMESTAMP, CHAR, VARCHAR.
• Unspecified —Valid assignments are any TDV data type.
• Decfloat - Valid assignments are: DECFLOAT, DECIMAL, DOUBLE, FLOAT, REAL, CHAR, VARCHAR.
• Double - Valid assignments are: DOUBLE, DECIMAL, DECFLOAT, FLOAT, REAl, CHAR, VARCHAR.
• Time - Valid assignments are: TIME, CHAR, VARCHAR.
Enabling
• Enable—Allow the use of the specific Column Filter policy.
• Disable—Disallow the use of the specific Column Filter policy.
Regardless of this setting, you can still add and edit the Column Filter policy.
If the policy is disabled, it is not used against the data.
Annotation
(Optional) Type an explanation of the Column Filter policy.
4. Select the row in the table on the page.
5. Select the pencil to edit a rule or select Add Rule to add a new rule.
Field
Specify
Apply To
Specifies whether this identity is for a Group or a User.
Not available for the default policy, because that policy governs all users and groups.
Domain
Specifies the Domain to which you want the Column Filter policy to apply.
Not available for the default policy, because that policy governs all users and groups.
User/Group
Specifies the user or group name to which you want the Column Filter policy to apply.
Not available for the default policy, because that policy governs all users and groups.
Rule Type
Specify:
— Original Value
— Null
— Static Value
— Partial String Mask
— Custom Function
— Expression
Partial String Mask is available only if you selected String as the Data Type.
A default rule is required and is added by default for column-based security. The order of the default rules cannot be changed. You can, however, change the rule type for the default rule.
6. Depending on the Rule Type that you select, you can specify values for the following:
Rule Type
Specify
Original Value
No further fields to edit.
Null
No further fields to edit. All values for the column will display as Null values.
Static Value
Type the value to display for the column data. For example, alwaysthesame.
Partial String Mask
Type values for:
• Prefix—Number of characters at the beginning of the string to leave alone.
• Padding—Type any valid string value.
• Suffix—Number of characters at the end of the string to leave alone.
Custom Function
Select a custom function from the list. This must be a custom function that you have defined in Studio and it must have at least one input and one output.
Expression
Type and expression in the text field. It can be any valid expression syntax.
7. Click Apply.
8. Click Save.
Example of Expression
Any expression can replace the column in select statement when it apply to column.
Example 1:
Select “columnname” from “tablename”
 
If you define expression to “columnname”, the select statement is rewritten:
select “expression” as “columnname” from “tablename”
Example 2:
If you want to use the same expression for more than one column, you can use a placeholder instead of the column name.
CAST(LPAD('X', LENGTH($PARAM$)-4, ‘X’) || SUBSTRING($PARAM$, LENGTH($PARAM$)-3) AS VARCHAR(30))
At the time of policy assignment, you will be prompted to assign a value for the parameter $PARAM$. Use this parameter to assign the expression to any column you need.