Creaing a wrapper around NetSqlAzman

Jan 14, 2013 at 4:30 PM

I am creating a service wrapper around netsqlazman to be consumded by a WPF application. I am using NetSqlAzman  to implmement role based authorization on client side.

I would like to understand the best approach to implement the following;

1. Cache all permissions for the users on the service side.

2. When the client facade makes a service call, it will make a dip into the client side cache and retrieve all authorized operations the user is allowed to perform.

Thanks.

Developer
Jan 14, 2013 at 5:26 PM

To my knowledge, there is not a "Get All Info" script.  Everything is on a "per call" basis.  Take what I say with a grain of salt, it's been a year since I looked at this library.

 

I wrote a custom query to "Get All".  Maybe it can help your "cause".

 

Sql Server TSQL script below.

Note, the "Create Schema 'NetSqlAzManAdapterSchema'" tsql is not included.

 

PS

Be super sure you want to do Role based security.  It's very non-maintainable, IMHO.

http://granadacoder.wordpress.com/2010/12/01/rant-hard-coded-security-roles/

 


/*

--SqlCmd Notes:

--Remove comments and space between ":" and "setvar" to run in sqlcmd mode.

--!!! Checked in code MUST *recomment out* the setvars below !!! (Putting a space between the ":" and the "setvar" is sufficient)
-- (This is not preferred behavior, the issue has been reported at http://connect.microsoft.com/SQLServer/feedback/details/382007/in-sqlcmd-scripts-setvar-should-have-a-lower-precedence-than-command-line-variable-assignments )


: setvar ErrorOutputFileFromCommandLine "c:\wuwutemp\sqlcommmanderrorfile.txt"
: setvar NETSQLAZMANDBNAME "NetSqlAzManDeploymentViaSqlCmdDB"
: setvar NETSQLAZMANDBNAME "NetSqlAzManStorage"
: setvar DBUSERNAME "public"

*/



Use [$(NETSQLAZMANDBNAME)]
GO


:Error $(ErrorOutputFileFromCommandLine)


IF EXISTS
(
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'NetSqlAzManAdapterSchema' and ROUTINE_NAME = N'uspIPrincipalAdapterDataGetByStoreNameAndUserName'
)
BEGIN
DROP PROCEDURE [NetSqlAzManAdapterSchema].[uspIPrincipalAdapterDataGetByStoreNameAndUserName]
END

GO

/*

Exec [NetSqlAzManAdapterSchema].[uspIPrincipalAdapterDataGetByStoreNameAndUserName]
'DoesNotExist' , 'UserName23'

Exec [NetSqlAzManAdapterSchema].[uspIPrincipalAdapterDataGetByStoreNameAndUserName]
'LocalQualityAssuranceNetSqlAzManStoreName1' , 'DoesNotExist'

Exec [NetSqlAzManAdapterSchema].[uspIPrincipalAdapterDataGetByStoreNameAndUserName]
'LocalDeveloperNetSqlAzManStoreName1' , 'UserName23'



Exec [NetSqlAzManAdapterSchema].[uspIPrincipalAdapterDataGetByStoreNameAndUserName]
'LocalDeveloperNetSqlAzManStoreName1' , 'diotrephes'




*/


CREATE PROCEDURE [NetSqlAzManAdapterSchema].[uspIPrincipalAdapterDataGetByStoreNameAndUserName]

(@StoreName nvarchar(255), @DBUserName nvarchar(255), @ApplicationName nvarchar(255) = NULL, @DBUserSidStubbed VARBINARY(85) = NULL)

AS

BEGIN

SET NOCOUNT ON

/*START CONSTANTS*/

declare @IAzManItem_ROLE int
declare @IAzManItem_TASK int
declare @IAzManItem_OPERATION int

select @IAzManItem_ROLE = 0
select @IAzManItem_TASK = 1
select @IAzManItem_OPERATION = 2

/*END CONSTANTS*/


declare @ErrorMsg varchar(256)

declare @FoundStoreId int
declare @FoundDBUserSid VARBINARY(85)
select @FoundStoreId = NULL
select @FoundDBUserSid = NULL



/* Find the StoreKey based on the Name */
select @FoundStoreId = (select StoreId from dbo.netsqlazman_StoresTable where Name = @StoreName )

print '/@FoundStoreId/'
print @FoundStoreId
print ''


/* The Store(Id) was not found in the table. Raise an Error. */
if (@FoundStoreId IS NULL)
BEGIN
select @ErrorMsg = 'Could not find row in dbo.netsqlazman_StoresTable using @StoreName = "'+COALESCE(@StoreName,'')+'"'
RAISERROR (@ErrorMsg, 16, 10)
RETURN
END


declare @SecurityFrameworkSuperApplicationName varchar(128)
select @SecurityFrameworkSuperApplicationName = (select NetSqlAzManAdapterSchema.udfSecurityFrameworkSuperApplicationNameGetter() )
/* print @SecurityFrameworkSuperApplicationName */


/* Locate the User from the Table-UDF */
select @FoundDBUserSid = (select DBUserSid from dbo.[netsqlazman_GetDBUsers] (@StoreName, @SecurityFrameworkSuperApplicationName, @DBUserSidStubbed, @DBUserName) where DBUserName = @DBUserName )

print '/@FoundDBUserSid/'
print @FoundDBUserSid
print ''

/* The User was not found in the UDF. Raise an Error. */
if (@FoundDBUserSid IS NULL)
BEGIN
select @ErrorMsg = 'Could not find row in dbo.[netsqlazman_GetDBUsers] using @StoreName = "'+COALESCE(@StoreName,'')+ '" and @DBUserName = "'+COALESCE(@DBUserName,'')+'"'
RAISERROR (@ErrorMsg, 16, 10)
RETURN
END


/* This table will hold the applications to which the user has access. This will eleviate redundant calls for this data. */
Declare @ApplicationsHolder table (
[ApplicationId] [int]NOT NULL,
[StoreId] [int] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Description] [nvarchar](1024) NOT NULL
)



/* Populate the Application(s) to a variable table (@ApplicationsHolder). Look at the Authorizations table to find matches for this specific user. */
Insert into @ApplicationsHolder ( ApplicationId , StoreId , Name , [Description] )

SELECT DISTINCT
apps.ApplicationId
, apps.StoreId
, apps.Name
, apps.[Description]
FROM
dbo.netsqlazman_ApplicationsTable apps
join
( select innerItems.ItemId,innerItems.ApplicationId,innerItems.Name,innerItems.[Description],innerItems.ItemType,innerItems.BizRuleId
, authors.[objectSid]
from dbo.[netsqlazman_ItemsTable] innerItems join
-- select * from
[dbo].[netsqlazman_AuthorizationsTable] authors on innerItems.ItemId = authors.ItemId )
derived1

on apps.ApplicationId = derived1.ApplicationId

Where
derived1.[objectSid] = @FoundDBUserSid


/* RESULT SET #0, UserInfo */
/* Return a ResultSet with information about the User. */
print '/UserInfo/'

select
DBUserSid
, DBUserName
, FullName
, OtherFields
from
dbo.[netsqlazman_GetDBUsers] (@StoreName, @SecurityFrameworkSuperApplicationName, @DBUserSidStubbed, @DBUserName)
where
DBUserName = @DBUserName



/* RESULT SET #1, Application(s) */
/* Return the list of Applications. This data is already in the @ApplicationsHolder table. So a re-lookup is unnecessary. */
print '/Applications/'


SELECT
appHolder.ApplicationId
, appHolder.StoreId
, appHolder.Name
, appHolder.[Description]
FROM
@ApplicationsHolder appHolder


/* RESULT SET #2, Roles */
/* Find any roles associated with this user and return as resultset. */
print '/Roles/'

SELECT
azManItems.ItemId
, azManItems.ApplicationId
, azManItems.Name
, azManItems.[Description]
, azManItems.ItemType
, azManItems.BizRuleId
from
dbo.[netsqlazman_ItemsTable] azManItems
join
-- select * from
[dbo].[netsqlazman_AuthorizationsTable] authors on azManItems.ItemId = authors.ItemId

Where
authors.[objectSid] = @FoundDBUserSid
and
exists ( select null from @ApplicationsHolder innerAppHolder where innerAppHolder.ApplicationId = azManItems.ApplicationId )
and
azManItems.ItemType = @IAzManItem_ROLE


/* RESULT SET #3, Tasks */
/* Find any tasks associated with this user and return as resultset. At the time of writing, Tasks were not used. But stubbed for the future. */
print '/Tasks/'


SELECT
azManItems.ItemId
, azManItems.ApplicationId
, azManItems.Name
, azManItems.[Description]
, azManItems.ItemType
, azManItems.BizRuleId
from
dbo.[netsqlazman_ItemsTable] azManItems
join
-- select * from
[dbo].[netsqlazman_AuthorizationsTable] authors on azManItems.ItemId = authors.ItemId

Where
authors.[objectSid] = @FoundDBUserSid
and
exists ( select null from @ApplicationsHolder innerAppHolder where innerAppHolder.ApplicationId = azManItems.ApplicationId )
and
azManItems.ItemType = @IAzManItem_TASK


/* RESULT SET #4, Operations */
/* Find any rights (operations) (by role) associated with this user and return as resultset. */
print '/Operations By Roles (Under this User)/'


SELECT
DISTINCT
operationItems.ItemId
, operationItems.ApplicationId
, operationItems.Name
, operationItems.[Description]
, operationItems.ItemType
, operationItems.BizRuleId

from
[dbo].[netsqlazman_ItemsHierarchyTable] hier

join dbo.[netsqlazman_ItemsTable] operationItems
on hier.ItemId = operationItems.ItemId

join
(
SELECT
azManItemsAsRoles.ItemId
, azManItemsAsRoles.ApplicationId
, azManItemsAsRoles.Name
, azManItemsAsRoles.[Description]
, azManItemsAsRoles.ItemType
, azManItemsAsRoles.BizRuleId
from
dbo.[netsqlazman_ItemsTable] azManItemsAsRoles
join
-- select * from
[dbo].[netsqlazman_AuthorizationsTable] authors on azManItemsAsRoles.ItemId = authors.ItemId

Where
authors.[objectSid] = @FoundDBUserSid
and
exists ( select null from @ApplicationsHolder innerAppHolder where innerAppHolder.ApplicationId = azManItemsAsRoles.ApplicationId )
and
azManItemsAsRoles.ItemType = @IAzManItem_ROLE
)
as derivedRoles on hier.MemberOfItemId = derivedRoles.ItemId
Where
exists ( select null from @ApplicationsHolder innerAppHolder where innerAppHolder.ApplicationId = operationItems.ApplicationId )
and
operationItems.ItemType = @IAzManItem_OPERATION






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

/* RESULT SET #5, Operations */
/* Find any rights (operations) at the user-level that are associated with this user and return as resultset. */
print '/Operations By Individual User/'


SELECT
azManItems.ItemId
, azManItems.ApplicationId
, azManItems.Name
, azManItems.[Description]
, azManItems.ItemType
, azManItems.BizRuleId
from
dbo.[netsqlazman_ItemsTable] azManItems
join
-- select * from
[dbo].[netsqlazman_AuthorizationsTable] authors on azManItems.ItemId = authors.ItemId
Where
authors.[objectSid] = @FoundDBUserSid
and
exists ( select null from @ApplicationsHolder innerAppHolder where innerAppHolder.ApplicationId = azManItems.ApplicationId )
and
azManItems.ItemType = @IAzManItem_OPERATION



SET NOCOUNT OFF


END

GO


GRANT EXECUTE ON [NetSqlAzManAdapterSchema].[uspIPrincipalAdapterDataGetByStoreNameAndUserName] TO [$(DBUSERNAME)]
GO

 

 

 

 

 

Developer
Jan 14, 2013 at 5:33 PM
zkay15 wrote:

 

I am creating a service wrapper around netsqlazman to be consumded by a WPF application. I am using NetSqlAzman  to implmement role based authorization on client side.

I would like to understand the best approach to implement the following;

1. Cache all permissions for the users on the service side.

2. When the client facade makes a service call, it will make a dip into the client side cache and retrieve all authorized operations the user is allowed to perform.

Thanks.

 

2. When the client facade makes a service call, it will make a dip into the ***client*** side cache and retrieve all authorized operations the user is allowed to perform.

 

Do you mean this instead?

2. When the client facade makes a service call, it will make a dip into the ***server*** side cache and retrieve all authorized operations the user is allowed to perform.

You're #1 statement ... doesn't make sense to me....with your original #2 statement.

Jan 14, 2013 at 8:34 PM

Thanks grandaCoder, you are correct in a way because they way we have designed our application , is to cache the permissions on the client side as well, so yes the first time the service call will get all permissions from the service side cache and then point forward use the client side cache.

Also, the object is to do operation based authorization and hence the reason I just want to Cache all operations a user is allowed to perform. Is there a way to retrieve just all user allowed operations?

 

 

 

 

Developer
Jan 14, 2013 at 8:55 PM

 

//Is there a way to retrieve just all user allowed operations?//

To my best knowledge, the answer is either

A:  Using the existing code....you have to loop over every Application, Every Operation, then run a check to see if your person (identity) has that permission.  Which is database intensive and not very smart.

So if you have 3 Applications, with 1000 operations each, but your user only has 3 permissions (3 operations).......you'll have to loop 3000 times to find those 3 items.

Again, I'm going from memory.

or

B: Use the TSQL (above) to get everything in "one shot".  The reason I wrote the above script is because getting everything in one shot was not available.

 

 

Developer
Jan 14, 2013 at 8:59 PM

 

See this post:

http://netsqlazman.codeplex.com/discussions/352107

 

 

Developer
Jan 14, 2013 at 9:02 PM

If you're going to cache on the client side, why are you also caching on the server side?

Why not cache (only on the client side) and let the server side remain "slim" and stateless ?

 

 

Developer
Jan 14, 2013 at 9:12 PM

There is a GetRolesForUser (By Application),
but there is not a GetAllRolesForUser...........

 

There is not a  
GetItemsForUser or GetAllItemsForUser (to the best of my knowledge).

Thus why I wrote the custom TSQL script above.

 

You can download the

NetSqlAzMan Reference.chm

help file to fish around if you'd like.

Coordinator
Jan 15, 2013 at 8:28 AM
zkay15 wrote:

I am creating a service wrapper around netsqlazman to be consumded by a WPF application. I am using NetSqlAzman  to implmement role based authorization on client side.

I would like to understand the best approach to implement the following;

1. Cache all permissions for the users on the service side.

2. When the client facade makes a service call, it will make a dip into the client side cache and retrieve all authorized operations the user is allowed to perform.

Thanks.

But why not use the NetSqlAzMan WCF Cache Service.
It was designed for the scope you are asking for.

Give it a chance !

Regards,
Andrea.

Jan 15, 2013 at 4:06 PM

Thanks Andrea, yes I did take a look at it but that does not fall into our architecture approach. We have our own service layer designed and would like all services to follow the same pattern, so instead of creating a wrapper around the NetSqlAzman WCF Cache Service, we have opted to create our own.

Also , as per a discussion I understand that the Cache service is constantly trying to send analytics data, which is violating our company policies. Hence, I am trying to write a service api that can cache all authorized operations for all the users.

Thanks.

Coordinator
Jan 15, 2013 at 4:29 PM

Ok but all Latest NetSqlAzMan versions does not send anything about analytics data (dismissed).

 

Jan 15, 2013 at 4:36 PM

That is good to know, thanks.

But would your recommendation be to do something similar to what what grandacoder has suggested?

 Is there anyway we can leverage the API's StorageCache to achieve retrieval of all permissions ?

What would be the best way to atleast retrieve  all the Users(not DB users) for an application from the Storage.

 

Thannks for your help!!

Coordinator
Jan 15, 2013 at 4:41 PM

Try inspect the WCF Cache Service source code ... and get the code fragments that achieve your goal (that is the same).

Jan 15, 2013 at 5:41 PM

Thanks...