Manage User and Data Security with SQL

Author: Joyce Zhou

Kepion allows you to configure security permissions through its Administrator or SQL Server Management Studio (SSMS). This article will walk you through how to update the relationship tables via the Administrator.

Membership

In the Membership node, you can manage user and group memberships to various roles.

Any changes made will update [dbo].[UserAffiliations].

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

Permission

In the Permission node, you can grant Dimension or Model permissions to a user, group, or role.

Model

Granting Model permissions is easy—just check the appropriate box under the Read or Write column.

Any changes made will update [dbo].[ModelAccesses].

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

Dimension

Select the Dimension from the drop-down and go between the Read and Write tabs. You can double-click an individual Dimension Member, or select from the Single / All (A) / Children (C) / Leaves (L) options at the top.

Any changes made will update [dbo].[DimensionDataPermissions].

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]

Configure via SSMS

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.