Submit a request

Manage User and Data Security with SQL

Avatar
Kepion Support
  • Updated

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

membership.PNG

 

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

 

1.PNG

 

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

permission.PNG

 

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

 

2.PNG

 

Model

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

 

3.PNG

 

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.

 

memberaccesssecurity.png

 

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.

Related articles