Restricted Columns
When you configure column level security, set a column option that determines what happens when a user selects the restricted column in a query. You can substitute the restricted data with a default value. Or, you can fail the query if a user selects the restricted column.
For example, an Administrator denies a user access to the salary column in the Employee table. The Administrator configures a substitute value of 100,000 for the salary column. When the user selects the salary column in an SQL query, the Data Integration Service returns 100,000 for the salary in each row.
Run the infacmd sql UpdateColumnOptions command to configure the column options. You cannot set column options in the Administrator tool.
When you run infacmd sql UpdateColumnOptions, enter the following options:
- ColumnOptions.DenyWith=option
- Determines whether to substitute the restricted column value or to fail the query. If you substitute the column value, you can choose to substitute the value with NULL or with a constant value. Enter one of the following options:
- ERROR. Fails the query and returns an error when an SQL query selects a restricted column.
- NULL. Returns null values for a restricted column in each row.
- VALUE. Returns a constant value in place of the restricted column in each row. Configure the constant value in the ColumnOptions.InsufficientPermissionValue option.
- ColumnOptions.InsufficientPermissionValue=value
- Substitutes the restricted column value with a constant. The default is an empty string. If the Data Integration Service substitutes the column with an empty string, but the column is a number or a date, the query returns errors. If you do not configure a value for the DenyWith option, the Data Integration Service ignores the InsufficientPermissionValue option.
To configure a substitute value for a column, enter the command with the following syntax:
infacmd sql UpdateColumnOptions -dn empDomain -sn DISService -un Administrator -pd Adminpass -sqlds employee_APP.employees_SQL -t Employee -c Salary -o ColumnOptions.DenyWith=VALUE ColumnOptions.InsufficientPermissionValue=100000
If you do not configure either option for a restricted column, default is not to fail the query. The query runs and the Data Integration Service substitutes the column value with NULL.
