Introduction

Secured views can be created in Data Warehouse based on the roles to limit the users from accessing all data and tables. It is essential that user groups for SQL Server Logins are created and roles are assigned for each user group.

Create Logins for security

1. Created logins to define security for views. Right Click on Security in DWH and select SQL Server Login and add new login.
    

2. Add Database role:
   

3. New Window Database role setup Click on Add login to role and select login and click ‘OK’.
   

4. The above login is added to Development. Apply the same login to ProdTest/Prod.

Add additional Database Roles:

Right click Database Roles and Select Database Roles. Follow the above steps and roles are created as shown below:

Note: The users and Logins associated with User Defined Database Roles must not have any server roles assigned. The default role should always be ‘public’.
For DBA/Developers to access all the secured views and tables, their User Login and Groups should be added under ‘db_owner’ database role in SQL Server Management Studio.

In the SQL Server Management Studio, select and expand Security under the database where secured views are being configured. Right click the DBA/Developer Group and select Properties. In our case, we are using BIDevelopers (this group consists of DBA Admin and Key Developers)

Select ‘db_owner’ under Membership and click OK.

Defining Securable Column for Dim_BusinessUnit table:

Dim_BusinessUnit table is identified as this table is the key table that consists of unique key_BusinessUnit.

1. Select Key_BusinessUnit and right click to point Add Securable Column.       
   

2. Click on Add Securable Column and new window appears. Select the field BusinessUnit as Key_BusinessUnit has integer values corresponding to BusinessUnit values and Click Ok.
   

3. Next screen appears to select Column Values and Security IDs.
   

4. Select Column Values and Security IDs and click Add button. Similarly select Column Values and different Security IDs. Name the Column Set up as ‘BusinessUnit_Secured’.
   

5. For the DBA/BI Developer Groups to access all records, database role db_owner needs to be added under Security IDs. Once this role is added, Under Column Values <All > is selected and assigned to db_owner under Security IDs. Follow the steps as shown below.
   
   
   

6. Click Ok.
7. Key_BusinessUnit role is created under Securable Columns.
   

Mapping Security column ‘BusinessUnit_Secured’ to Fact_SalesOrderTransaction

The newly created security column must be mapped to different tables to enable security and hence limit access to users.

1. Open BusinessUnit_Secured in new window.
   

2. Select column BusinessUnit under Fact_SalesOrderTransaction. Drag the BusinessUnit_Secured and drop on BusinessUnit.
   View Fact_SalesOrderTransaction_SV is created under Secured Views.
   Follow similar steps to map BusinessUnit_Secured to Business Unit for other tables having BusinessUnit column.
   The view appears as below:
   

Defining Database Schema to secure Tables

To secure the tables from providing access to users, we need to establish Schemas. 2 types of schemas are assigned. The role based tables and corresponding views will have ‘etl schema and remaining tables and views that can be accessible to all users will have default schema ‘dbo.

If schemas ‘dbo’ and ‘etl are already created, Schema behavior needs to be changed.

Below are the steps to create Database Schema if they are not created.

1. Right Click the Database Schema and Select Add Database Schema.
   

2. Name the Database Schema as etl and click OK.
    

3. The raw tables, Transformation process (including store procedures, tables and views), valid tables and error tables should have etl The users will be having limited access to tables as defined on their role. Right click etl under Database Schemas and click Schema Behavior and select the highlighted rows one at a time.
   

5. The dbo schema appears as below after selection of all rows identified above.
   
6. Right click dbo schema and click on Schema Behavior. Select as shown in the screen shot.
   
   The schema etl under Database Schemas appears as below:
   

Applying Schemas to table Fact_SalesOrderTransaction.

1. The schemas defined need to applied to tables that are selected as secured tables with limiting access to users. Right click Fact_SalesOrderTransaction and select Table

3. In the Table Settings window, Click on Table Schemas. Change Valid schema to dbo schema will be dynamically applied to Raw schema, transformation schema and Error/Warning schema.
   
   
4. Rename Secured View.Rename view Fact_SalesOrderTransaction_SV view to dbo. Fact_SalesOrderTransaction_V.
5. Rename TablesRename Dim_BusinessUnit table to Dim_ BusinessUnit _Secured. Rename Fact_ SalesOrderTransaction table to Fact_ SalesOrderTransaction _Secured.

Key Points to consider before Deployment

1. Verify the Pre-Scripts and Post-Scripts related to secured Tables have table name parameterized otherwise execution of the secured tables will fail. If the Pre-Scripts and Post-Scripts do not have parameterized table names, they should be updated before executing the secured tables.
2. The external report services such as Microstrategy develops code using table name defined in the SQL Server Management Studio. Hence it is important that the secured views have name matching to table name defined in MMS.