Manage User and Data Security with SQL

Author: Joyce Zhou

Introduction

Kepion allows you to configure security through the Kepion Administrator UI or SQL Server Management Studio. This article will walk you through the relationship tables that are related to security settings.

Example

In Security -> Membership, you can add a User/Group/Role to a Role.

In the database, the [dbo].[UserAffiliations] will be updated.

SELECT
    [UserID]
    ,[Affiliation] --UserID of User Group or Role from dbo.User table
    ,[ModifiedBy]  --UserID from dbo.User table
FROM [dbo].[UserAffiliations]

In Security -> Permission, you can grant Dimension or Model permissions to a selected user, user group, or role.

Granting Model permissions is easy-- just check the corresponding box in the READ or WRITE tabs.

In the database, the [dbo].[ModelAccesses] will be updated.

SELECT
    [ModelID]    --From [dbo].[Models] table
    ,[UserID]
    ,[Operation] --1-Self; 2-All Descendants; 3-Children; 4-Leafs;
    ,[ModifiedBy]
    ,[ModifiedDate]
FROM [dbo].[ModelAccesses]

To grant users permission by Dimension, select the Dimension from the drop-down in the top-left, and then select between the READ-ONLY and READ/WRITE tabs. Then you can double-click on an individual Dimension Member, or select from the Single / All (A) / Children (C) / Leaves (L) options at the top.

In the database, the [dbo].[DimensionDataPermissions] will be updated.

SELECT
    [DimensionID]  --From [dbo].[Dimensions] table
    ,[MemberID]    --From D_ table
    ,[UserID]
    ,[Operation]   --1-Self; 2-All Descendants; 3-Children; 4-Leafs;
    ,[Operator]    --From the Operation group of [dbo].[Constants] table
                   --1-read; 2-write
    ,[HierarchyID] --From [dbo].[Hierarchies] table
    ,[ModifiedBy]
    ,[ModifiedDate]
FROM [dbo].[DimensionDataPermissions]

To conclude, if you prefer to configure user permissions in SQL Server Management Studio, there are three tables you will need to update manually:

• [dbo].[UserAffiliations]
• [dbo].[ModelAccesses]
• [dbo].[DimensionDataPermissions]

Inherited permissions are stored in [dbo].[ExtendedDimensionPermissions] and [dbo].[ExtendedModelAccesses]. These two tables will be automatically updated on deployment.