Skip to main content

Data Warehouse Security (JDM 2016 and Newer)

Overview

In the Jet Data Manager, users can control access to database objects and data directly in the data warehouse.  It is easy to configure access on the object level, data level, or both.

Assigning data warehouse security at the object (ie: table) level is accomplished in two steps:

Assigning data warehouse security at the data (i.e., row) level is accomplished in three steps:

How-To Video

Data Warehouse Security

This video demonstrates how to configure security for your Data Warehouse

Adding a Database Role

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

  1. On the Data tab, expand the Security node under the data warehouse, right click Database Roles and click Add Database Role.

    The Database Role Setup window opens.

  2. In Name, type a name for the role.

  3. If you are using the Multiple Environments feature: In the Member Setup list, click Environment Specific Role Members if you wish to have a different setup for different environments.  The different environments will then each have a tab in the list below.

  4. 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 wish 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 that was entered. Click OK to add the user.

      2.jpg

  5. Click OK to close the window and add the database role, which is listed under Database Roles in the project tree.

    If the administrator needs to add a new login on the SQL Server, right-click Security in the project tree and click SQL Server Logins. Here the user can add logins if they have the necessary permissions on the SQL Server. For security reasons SQL users cannot be deleted here.

    Note: On each deployment, the JDM drops existing roles on the database before recreating them. By default, the JDM only drops database roles that are created by the JDM in the current project. However, you can also set the JDM to drop more database roles with a setting on the data warehouse. To access the setting, right click the data warehouse, click Edit Data Warehouse and click Advanced… In the Drop Role Option list there are the following options:

    • Roles Related to Data Warehouse (Default): This will drop all data warehouse roles created in the JDM for the current project.

    • Roles Created by Application : This will drop any roles in the data warehouse that were created with the JDM regardless of which project. This is primarily relevant if multiple projects use the same data warehouse.

    • All Roles on Database : This will drop all roles in the data warehouse regardless of how these were created. If a role was created in SQL Management Studio manually this would be dropped as well. This option is desirable when the user wants all security maintained directly in the JDM

Assigning Object Level Permissions to Database Roles

To assign permissions to database roles, follow the steps below:

  1. On the Data tab, expand the Security node, right-click Database Roles , and click Object Security Setup .  The Data Warehouse Access Control window opens.  This is where the user will assign permissions to specific objects to a user role.

  2. Click Tables , Views or Schemas in the left-hand column to choose the type of object to set up access for.

  3. In the right-hand column, the pane will show table, view or schema names in the left-most column and database roles in the following columns.  click the intersection between the object name and the database role to change the permission for the database role on that object.  The JDM uses the same Allow/Deny concept as SQL Server with three possible states:

    • Not set (grey dot): The database role is not allowed to access the object, but are not explicitly denied access.
    • Allow (green checkmark): The database role is allowed to access the object. However, if a user is a member of another database role that is denied access, they will not be able to access the object.
    • Deny (red 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, they will still be denied access.

  4. (Optional) click Add Role , Edit Role or Delete Role to add, edit or delete database roles as needed.

  5. click OK to save changes and close the window.

Assigning Data Level Permissions

In addition to configuring access on the object level, users can filter the data available to individual Active Directory users or SQL Server database roles.  For example, the user may want a salesperson to be able to see sales data only for their own region.

Data level security in the JDM is based on the concepts of securable columns, securable column setups, secured columns and secured views.  This design allows the organization to create one security model and reuse it on any number of tables.  As an overview:

  • A Securable Column contains the values that the user wishes to use in a filter. Continuing the example above, this could be “Salesperson Code” in a “Salesperson” table.
  • A Securable Column Setup is a mapping between Securable Column values and users or database roles, such as what “Salesperson Code” does the salesperson have access to. Each securable column can have multiple Securable Column Setups.
  • A Secured Column is a column on a particular table containing the data we wish to filter. This could be a “Salesperson Code” column on a “Sales Transactions” table.
  • A Secured View is a view where all of the data that the user does not have access to is filtered out. For instance, all of the “Sales Transactions” rows where the “Salesperson” does not match the filters set to the specific salesperson will be hidden from them when they query the “Sales Transactions” table. When using data level security, the secured view should be used for reporting instead of the table it secures. This will be handled automatically by Jet Essentials.

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

  1. On the Data tab, navigate to and expand the table that contains the column to base the permissions on, right-click the field to secure and click Add Securable Column.

    The Add Securable column window opens. For the example above this would be the “Salesperson” table.

  2. (Optional) In the Display Column list, click the column value to display instead of the column being add as a securable column.  If the securable column is a code, for example, it might be helpful to choose something that is easier to understand such as a name as the display column.

  3. (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 securable column values and be compatible with the data type of the securable column.  The default will be -9 which should be left unless there can be a Salesperson Code, in this example of -9.

  4. Click OK .  The Add Securable Column Setup window opens to let the user add the first Securable Column Setup.

  5. In the Name column type a name for the securable column setup.

  6. In the Column Values and Security IDs pane, the user can combine the values in the Securable Column with the users or database roles that should have permission to access the data.  Select the 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.

  7. (Optional) If it is necessary 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 wish to add a database role as opposed to an Active Directory user.  The Database Role can be an already existing Database Role managed outside of the JDM or a JDM managed Database Role.

  8. Click OK when finished configuring the securable column setup.  The Securable Column setup can be found in the project tree under Security -> Securable Columns

Applying Data Level Permissions

Once a Securable Column Setup has been created this can then be used to apply data level permissions to a table. To do so, follow the steps below.

  1. Drag and drop the Securable Column Setup onto a field on the table in the data warehouse to be secured. Using the example above this would be done by dragging the “NorthwestSales” Securable Column Setup and dropping it on the “Salesperson Code” field in the “Sales Transactions” table.  A Secured View will be created that restricts that data that will be shown to the users in the “NorthwestSales” group.  The Secured View can then be seen under Security -> Secured Views

  2. To add further permissions to the view, you can drag and drop another Securable Column Setup directly onto the view.  The Add Field window opens.

  3. In the Field Name list, select the field that contains the values you wish to use in the filter with the securable column setup.

  4. Click OK. The field is added to the Secured View.

Assigning Object or Column Level Permissions to Database Roles

Column Permissions

You can set permissions on specific columns/fields on a table, not just the entire table. In order to enforce security at a column level the table needs to be physically valid.

The JDM uses the same allow/deny concept as SQL Server with three possible states:

  • Not set (grey 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 grey 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 grey 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 allowed 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.

  1. On the Data tab, in the project tree, under Data Warehouses and the relevant data warehouse, right click Security and click Object Security Setup

    The Object Security Setup window opens.

  2. Click Tables , Views or Schemas in the left-hand column to choose the type of object you wish to set up access for.  Expand Tables and click an individual table to assign column level permissions for that table.

  3. In the right-hand column, the table shows object names in the left-most column and database roles in the following columns. click the icon in the intersection between the object name and the database role to change the permission for the database role on that object.  Note that if you set column level permissions on a table, this will overwrite any current object level permissions set and vice versa.

  4. Optionally, click Add Role, Edit Role or Delete Role to add, edit or delete database roles as needed.

  5. Click OK to save changes and close the window.

Was this article helpful?

We're sorry to hear that.