Data Security

In TimeXtender, you can control access directly on the data warehouse or staging database. You can restrict access to specific views, schemas, tables and columns on tables - object level permissions - or specific data in a table - data level permissions.

The access control features can be found under Security under any data warehouse or staging database.

Adding a Database Role

Object level security is based on SQL Server database roles. A user has access to an object if he is a member of a database role that has access to that object. To add a database role, follow the steps below.

Under Security, right click Database Roles and click Add Database Role. The Database Role Setup window opens.
In Name, type a name for the role.
If you are using the Multiple Environments feature: In the Member setup list, click Environment Specific Role Members if you want to have a different setup for different environments. The different environments will then each have a tab in the list below.
Next, you should add users to the role. Click Add login to role to add Active Directory or SQL Server logins that are known to the SQL Server that the data warehouse resides on. The Select Login(s) window opens. Select the logins you want to add in the list and click OK to add the user(s).
Click Add manually to add Active Directory or SQL Server users or groups that the SQL Server does not know, e.g. users on a production server. The Enter User or Group ID window opens. In ID, type the user or group id. Under Type, click AD user/group or SQL user/group depending on the type of ID you entered. Click OK to add the user.
Click OK to close the window and add the database role, which is listed under Database Roles.

Note: Roles are limited to the data warehouse or business unit it was created on, i.e. you cannot use a role created on one data warehouse on another data warehouse or business unit.

If you need to add a new login on the SQL Server, you can right click Security under a data warehouse or staging database and click SQL Server Logins. Here, you can add logins if you have the necessary permissions on the SQL Server. However, for safety reasons, you cannot delete users here.

On each deployment, TimeXtender drops existing roles on the database before recreating them. By default, TimeXtender only drops database roles related to the data warehouse or staging database being deployed. However, you can also set TimeXtender to drop more database roles with a setting on the data warehouse or staging database. To access the setting, right click the data warehouse or staging database, click Edit Data Warehouse or Edit Staging Database as applicable and click Advanced… In the Drop Role Option list, click Roles Created by Application to drop all roles created by TimeXtender or All Roles on Database to drop all roles altogether.

Assigning Object Level Permissions to Database Roles

You can assign permissions to database roles on the object level. TimeXtender uses the same allow/deny concept as SQL Server with three possible states:

Not set (gray dot): The database role is not allowed to access the object, but are not explicitly denied access.
Grant (green with white checkmark): The database role is granted access to the object. However, if a user is a member of another database role that is denied access, he will not be able to access the object.
Deny (red with white bar): The database role is denied access to the object. Even if a user is a member of another database role that is allowed access, he will still be denied access.

In addition to the three states described above, a table can have different mixed states depending on the column level permissions set on the table. The mixed states are:

Partially Granted (green and gray icon). The database role is granted access to some columns on the table. Note that you will also see this icon if the database role is granted access to all columns on a table since this will not automatically set Allow on the table level.
Partially Denied (red and gray icon): The database role is denied access to some columns on the table. Note that you will also see this icon if the database role is denied access to all columns on a table since this will not automatically set Deny on the table level.
Mixed Grant/Deny (red and green icon): The database role is granted access to some columns and denied access to other columns on the table.

To assign object level permissions, or column level permissions on tables, to database roles, follow the steps below.

Under the data warehouse, right click Security and click Object Security Setup. The Object Security Setup window opens.
Click Tables, Views or Schemas in the left-hand column to choose the type of object you want to set up access for. Expand Tables and click an individual table to assign column level permissions for that table.
In the right-hand column, the table shows object names in the left-most column and database roles in the following columns. Click icon in the intersection between the object name and the database role to change the permission for the database role on that object. If you set column level permissions on a table, this will overwrite any current object level permissions set and the other way around.
(Optional) Click Add Role, Edit Role or Delete Role to add, edit or delete database roles as needed.
Click OK to save changes and close the window.

Assigning Data Level Permissions

In addition to configuring access on the object level, you can filter the data available to individual Active Directory users or SQL Server database roles. You might, for instance, want a sales person to be able to see all sales data, but only in his or her own region.

Data level security in TimeXtender is based on the concepts of securable columns, securable column setups, secured columns and secured views. This design allows you to create one security model and reuse it on any number of tables.

A securable column contains the values that we want to use in a filter. Continuing the example above, it could be “sales region id” in a “sales regions” table.
A securable column setup is a mapping between securable column values and users or database roles, e.g. what “sales region id” does the sales person have access to. Each securable column can have multiple securable column setups.
A secured column is a column on the table containing the data we want to filter. This could be a “sales region id” column on a “sales transactions” table.
A secured view is a view where all the data the user does not have access to is filtered out. For instance, all the “sales transactions” rows where the “sales region id” does not match the “sales region id” the sales person has access to. When using data level security, the secured view should be used for reporting instead of the table it secures.

To assign data level security to a table, follow the steps below.

Expand the table that contains the column you want base the permissions on, right click the field and click Add Securable Column. The Add Securable Column window opens.
(Optional) In the Display Column list, click the column value you want to display instead of the column you are adding as a securable column. If the securable column contains e.g. an ID, it might be helpful to choose something that is easier to understand, e.g. a name, as the display column.
(Optional) In the <All> Value box, type a value that will be used to indicate all values. This value should be a value that is guaranteed not to be among the values in the securable column.
Click OK. The Add Securable Column Setup window opens to let you add your first Securable Column Setup.
In Name, type a name for the securable column setup.
In the left-hand side of the window, you combine the values in the securable column with the users or database roles that should have permission to access the data. Select a number of values in the Column Values list and one or more users or database roles in the Security ID list and click Add->. The resulting pairs are displayed in the Security Configuration list.
(Optional) If you need to assign permissions to a value or a security ID that is not in either list, type the value or name in the box under the appropriate list and click Add. Select Database Role if you want to add a database role as opposed to an Active Directory user. For more information on database roles, see Adding a Database Role.
Click OK when you have finished configuring the securable column setup. The securable column setup can be found under Security, Securable Columns, [table name], [securable column name].

Applying Data Level Permissions

When you have created a securable column setup, you are ready to use it to apply data level permissions to a table. To do so, follow the steps below.

Drag and drop a securable column setup on a field in the table in the data warehouse that you want to secure. A secured view is created and can be found under Security, Secured Views, [table name]_SV.
If you want to add further permissions to the view, you can drag and drop a securable column setup on the view. The Add Field window opens.
In the Field Name list, select the field that contains the values you want to use in the filter with the securable column setup.
Click OK. The field is added to the secured view.