automatically associate DB users to store or application groups

Jul 15, 2009 at 5:57 PM

Hi,

we have a setup where we store Leads and Customers in separate tables.  Both of these need the capability to use our application, obviously with different features.

I've created the GetDBUsers to return the data from both tables, and it works fine, it also returns an extra field with the type of the user (Lead or Customer).

In the store I've also defined store groups for Lead and Customer.  However every time new entries are added in the Leads or Customers tables we have to go into the console and add the new users to the specific store group which is quite an inconvenience.

I guess the question is if there is a way to have "dynamic store groups" for db users?

Thank you

John

Coordinator
Jul 15, 2009 at 7:50 PM

Hi John,

one of the advantages  of NetSqlAzMan is that all is stored into a SQL Server database. J

You can solve just using a couple of Triggers (AFTER trigger) defined on Leads and Customers tables.

Use these triggers to invoke the dbo.netsqlazman_AuthorizationInsert Stored Procedure to insert your DB Users authorizations as following:

DECLARE @RC int

DECLARE @ItemId int

DECLARE @ownerSid varbinary(85)

DECLARE @ownerSidWhereDefined tinyint

DECLARE @objectSid varbinary(85)

DECLARE @objectSidWhereDefined tinyint

DECLARE @AuthorizationType tinyint

DECLARE @ValidFrom datetime

DECLARE @ValidTo datetime

DECLARE @ApplicationId int

-- TODO: Set parameter values here.

EXECUTE @RC = [NetSqlAzManStorage].[dbo].[netsqlazman_AuthorizationInsert]

   @ItemId

  ,@ownerSid

  ,@ownerSidWhereDefined

  ,@objectSid

  ,@objectSidWhereDefined

  ,@AuthorizationType

  ,@ValidFrom

  ,@ValidTo

  ,@ApplicationId

By now NetSqlAzMan does not support Dynamic DB Groups (like LDAP Groups)

Regards,

Andrea.

__________________________________
Andrea Ferendeles
NetSqlAzMan - Project Coordinator

http://netsqlazman.codeplex.com

Jul 15, 2009 at 8:53 PM
Edited Jul 15, 2009 at 8:54 PM

Andrea,

I was afraid we would end up having to do something like this.

oh well....

Thank you for the prompt response.

 

John

Jul 15, 2009 at 9:13 PM

After looking into how to call netsqlazman_AuthorizationInsert I realized that I don't need to insert an authorization, all I really want to do is associate the user (Lead) to the Leads store group.

If I've set up the authorizations to be based on the StoreGroup, just adding the user to the store group should be enough right?

 

John

Coordinator
Jul 16, 2009 at 7:05 AM

Obviously.  :-)
I read wrong.

You should use the [dbo].[netsqlazman_StoreGroupMemberInsert] and the [dbo].[netsqlazman_ApplicationGroupMemberInsert] SP:

DECLARE @RC int
DECLARE @StoreId int
DECLARE @StoreGroupId int
DECLARE @objectSid varbinary(85)
DECLARE @WhereDefined tinyint
DECLARE @IsMember bit

-- TODO: Set parameter values here.

EXECUTE @RC = [NetSqlAzManStorage].[dbo].[netsqlazman_StoreGroupMemberInsert]
   @StoreId
  ,@StoreGroupId
  ,@objectSid
  ,@WhereDefined
  ,@IsMember

------------

DECLARE @RC int
DECLARE @ApplicationGroupId int
DECLARE @objectSid varbinary(85)
DECLARE @WhereDefined tinyint
DECLARE @IsMember bit
DECLARE @ApplicationId int

-- TODO: Set parameter values here.

EXECUTE @RC = [NetSqlAzManStorage].[dbo].[netsqlazman_ApplicationGroupMemberInsert]
   @ApplicationGroupId
  ,@objectSid
  ,@WhereDefined
  ,@IsMember
  ,@ApplicationId

__________________________________
Andrea Ferendeles
NetSqlAzMan - Project Coordinator

http://netsqlazman.codeplex.com