NetSqlAzman and Silverlight

Topics: General Topic, NetSqlAzMan Core, WCF Cache Service
Apr 12, 2012 at 6:50 PM

I've been using NetSqlAzman for the past while in a Windows application and have been impressed with its ease of use and functionality...it's a great product.

A new project has us moving a large portion of our applications functionality into a web based Silverlight project. My question is does anyone here have any experience with NetSqlAzman and Silverlight. My main areas of concern are around the architecture needed to support these 2 technologies (you can't directly reference the NetSqlAzman dll from a silverlight module).

Any and all input is greatly appreciated.

Developer
Apr 12, 2012 at 7:35 PM

ChesterFool!

So the dev team on which I am on..........uses NetSqlAzMan to ........ populate a CUSTOM IPrincipal.

RolesAndRightsPrincipal.cs seen below.

Ok....

The "mappings"

SecurityRole = IAzManItem ( of type ItemType.Role)

SecurityRight = IAzManItem ( of type ItemType.Operation)

IAzManItem ( of type ItemType.Task ) is NOT used (in our setup)

........

Motivation and why? 

(1)  Our custom RolesAndRightsPrincipal is easier to pass "over the wcf 'wire'" than the actual NetSqlAzman objects.

(Notice the wcf'ish decorations of the RolesAndRightsPrincipal.cs object below.

(2)  What if we decide to not use NetSqlAzMan library anymore? (Highly unlikely, but what if.......some hater of well written DotNet 4.0 code becomes the new boss at your work)..............

But by abstracting things to your own object (RolesAndRightsPrincipal.cs in my case)..........you have PROTECTED yourself against future "back end library" swap outs.  (Imagine having 50 client apps that are hard coded to NetSqlAzMan...and then some boss type dude saying "I don't like NetSqlAzMan...change it now!"  (<<It happens).

There are a few other subtle reasons.  But the two above are the main ones.  WCF wire passing.  (Maybe you can do that with NetSqlAzman, i just never tried.)  And "future proofing" your code...in case someone forces a "no more NetSqlAzMan" decision upon you.

 

..............................

 

Basically, I use the NetSqlAzMan model .. to populate the below object.  (using the "mappings" from above.)

 

 

 

    [Serializable]
    [DataContract]
    public class RolesAndRightsPrincipal //: IRolesAndRightsPrincipal
    {

        public RolesAndRightsPrincipal(System.Security.Principal.IIdentity iid)
        {
            this.Identity = iid;
        }

        public RolesAndRightsPrincipal(System.Security.Principal.IIdentity iid, SecurityRoleCollection roles, SecurityRightCollection rights)
        {
            this.Identity = iid;
            this.AllRoles = roles;
            this.AllRights = rights;

        }

        #region IRolesAndRightsPrincipal Members

        public bool IsInRole(Guid role)
        {

            if (null == this.AllRoles)
            {
                return false;
            }


            if (null != this.AllRoles[role])
            {
                return true;
            }


            return false;

        }

        public bool IsInAnyRole(Guid[] roles)
        {
            if (null == this.AllRoles)
            {
                return false;
            }

            if (null == roles)
            {
                return false;
            }

            foreach (Guid providedRole in roles)
            {

                 if (null != this.AllRoles[providedRole])
                {
                    return true;
                }


            }

            return false;
        }

        public bool IsInAllRoles(Guid[] roles)
        {

            if (null == this.AllRoles)
            {
                return false;
            }

            if (null == roles)
            {
                return false;
            }

            foreach (Guid providedRole in roles)
            {
                if (null == this.AllRoles[providedRole])
                {
                    return false;//found a missing item
                }
            }

            return true;
        }

        public bool HasRight(Guid right)
        {
            if (null == this.AllRights)
            {
                return false;
            }


            if (null != this.AllRights[right])
            {
                return true;
            }


            return false;
        }

        public bool HasAnyRight(Guid[] rights)
        {
            if (null == this.AllRights)
            {
                return false;
            }

            if (null == rights)
            {
                return false;
            }

            foreach (Guid providedRight in rights)
            {

                if (null != this.AllRights[providedRight])
                {
                    return true;
                }



            }

            return false;
        }

        public bool HasAllRights(Guid[] rights)
        {
            if (null == this.AllRights)
            {
                return false;
            }

            if (null == rights)
            {
                return false;
            }

            foreach (Guid providedRight in rights)
            {
                bool foundMatch = false;

                if (null != this.AllRights[providedRight])
                {
                    foundMatch = true;
                }


                if (!foundMatch)
                {
                    return false;
                }

            }

            return true;




        }

        #endregion

        #region IPrincipal Members

        [DataMember]
        public System.Security.Principal.IIdentity Identity { get; private set; }

        public bool IsInRole(string role)
        {
            return IsInRole(new Guid(role));
        }

        #endregion

        #region IRolesAndRightsPrincipal Members


        [DataMember]
        public SecurityRoleCollection AllRoles { get; private set; }

        [DataMember]
        public SecurityRightCollection AllRights { get; private set; }


        #endregion
    }

Developer
Apr 12, 2012 at 7:37 PM

Let me nutshell my above post....................

"An ounce of prevention is worth a gallon of cure"..........

 

............................

Sloan, the "granadaCoder" and squirrel feeder fan!

www.john9crazysquirrelfeeders.com

Developer
Apr 12, 2012 at 8:29 PM

Another "nutshell"

This is kind of the "Proxy" Design Pattern.

http://www.dofactory.com/Patterns/PatternProxy.aspx

............................

Sloan, the "granadaCoder" and squirrel feeder fan!

www.john9crazysquirrelfeeders.com

Developer
Apr 12, 2012 at 8:32 PM
Edited Apr 12, 2012 at 8:52 PM

The below URL is a good "third party" explanation as to why it is better (IMHO) to code against RIGHTS (NetSqlAzMan Operations)...instead of ROLES.

Coding to Roles is (two thumbs down).

http://lostechies.com/derickbailey/2011/05/24/dont-do-role-based-authorization-checks-do-activity-based-checks/

Here is MY explanation.

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

............................

Sloan, the "granadaCoder" and squirrel feeder fan!

www.john9crazysquirrelfeeders.com

Coordinator
Apr 12, 2012 at 8:48 PM

Nice job granada !

:-)

Apr 12, 2012 at 9:01 PM

I agree...nice job...thanx very much.

I agree with the role vs rights discussion. The original application uses roles to logically group rights...basically for the system admin to be able to easily manage users and what they can do. All of the code checks to see if the current user has the right to perform a specific task. Keeps the code really clean and readable and separates it from role definitions.

I have one other question...in Silverlight how are you handling User Authentication...with the default membership provider or something else?

 

Thanx for the quick excellent response.

Apr 12, 2012 at 9:10 PM

I was thinking that a custom membership provider might be the easiest solution...I can link it to exiting code in a module from the original application that does authentication of both domain and application users.

Developer
Apr 12, 2012 at 9:10 PM

I'm not a Silverlight dude.  But without knowing that.................the DotNetFramework (normal) and the DotNetFramework (Silverlight) are two different beasts.  (different CRL's)  So somewhere there is an "adapter translation".  RIA services "auto voodoos" this for you.

Again....take that with a grain of salt.  Silverlight ain't my cup of tea.

 

Developer
Apr 12, 2012 at 9:16 PM

//custom membership provider might be the easiest solution//

:(

Booo!

EASY != Good

Rapid Development Model != Good

ShortCuts != Good

...............

The MembershipProvider is based on ROLES,....and that is for your kid's soccer club...NOT a professional DotNet application.

There is a "middle ground" workaround.

http://www.lhotka.net/weblog/CommentView,guid,9efcafc7-68a2-4f8f-bc64-66174453adfd.aspx

Basically.  Use the MembershipProvider......but treat the word/phrase "Role" as "Right".  (In your mind..you cannot change the names of objects in MS code of course)

 

Rocky's NUTSHELL ( from url above)

bool result = currentPrincipal.IsInRole(requiredPermission);

 

Apr 12, 2012 at 9:21 PM

I wasn't thinking of using the membership provider for anything but user authentication...is see the 2...authentication and authorization...as 2 separate things...just looking for short cuts to allow me to reuse as much code as possible. I guess I'm going to have to spend some time looking at RIA services.

thanx again.

Developer
Apr 12, 2012 at 9:41 PM

Ahh.  #1 You are 100% correct.  And we are actually doing this EXACT thing.  Using Asp.Net Membership (SqlMembershipProvider) to handle the users.

Nutshell:

Create ONE application in Asp.Net membership.

Add Users to Asp.Net Membership.

Tweak "dbo.netsqlazman_GetDBUsers" to point to the Asp.Net Membership.

 

I cannot post all my code. But I can post my TSQL for the above UDF.  (Next Post).

Developer
Apr 12, 2012 at 9:42 PM



/*

--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 )

--     See     http://msdn.microsoft.com/en-us/library/ms174187.aspx or internet-search for "sqlcmd mode management studio"


: setvar ErrorOutputFileFromCommandLine "c:\wuwutemp\sqlcommmanderrorfile.txt"

: setvar NETSQLAZMANDBNAME "MyDatabaseName"
: setvar DATABASEUSERNAMEVARIABLE "public"



: setvar ASPNETMEMBERHIPDBNAME ASP_NET_DB
: setvar NETSQLAZMANDBNAME NetSqlAzManDB
: setvar DATABASEUSERNAMEVARIABLE "public"
: setvar ErrorOutputFileFromCommandLine "c:\wuwutemp\sqlcommmanderrorfile.txt"


 
*/

       

 
Use [$(NETSQLAZMANDBNAME)]
GO

:Error $(ErrorOutputFileFromCommandLine)


/*

The contract of the function will have to be manually tweaked to match the real one.
You could choose to drop the function.  However, I've run into issues with this a few times, thus why I use the CREATE (if it does not exist), then an ALTER.

*/





IF NOT EXISTS
    (
        SELECT * FROM INFORMATION_SCHEMA.ROUTINES
        WHERE ROUTINE_TYPE = N'FUNCTION' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'netsqlazman_GetDBUsers' 

    )
    BEGIN
        print 'Creating the stubbed version of [dbo].[netsqlazman_GetDBUsers]'
        EXEC ( 'CREATE FUNCTION [dbo].[netsqlazman_GetDBUsers] ( @i nvarchar(255), @j nvarchar(255), @k VARBINARY(85) = NULL, @l nvarchar(255) = NULL ) RETURNS TABLE AS RETURN Select [Fake] = 333')
    END

GO




/*  Original DatabaseName = (u_s_e_) [NetSqlAzManStorage]    */

GO

/****** Object:  UserDefinedFunction [dbo].[netsqlazman_GetDBUsers]    Script Date: 03/27/2012 18:17:41 ******/


GO



GO

/*
    NetSqlAzMan GetDBUsers TABLE Function
    ************************************************************************
    Creation Date: August, 23  2006
    Purpose: Retrieve from a DB a list of custom Users (DBUserSid, DBUserName)
    Author: Andrea Ferendeles
    Revision: 1.0.0.0
    Updated by: <put here your name>
    Parameters:
    use:
        1)     SELECT * FROM dbo.netsqlazman_GetDBUsers(<storename>, <applicationname>, NULL, NULL)            -- to retrieve all DB Users
        2)     SELECT * FROM dbo.netsqlazman_GetDBUsers(<storename>, <applicationname>, <customsid>, NULL)  -- to retrieve DB User with specified <customsid>
        3)     SELECT * FROM dbo.netsqlazman_GetDBUsers(<storename>, <applicationname>, NULL, <username>)  -- to retrieve DB User with specified <username>

    Remarks:
    - Update this Function with your CUSTOM CODE
    - Returned DBUserSid must be unique
    - Returned DBUserName must be unique
*/

/*
                select * from [dbo].[netsqlazman_GetDBUsers] ( null , null , null , null )
               
                select * from [dbo].[netsqlazman_GetDBUsers] ( null , 'Application01' , null , null )
               
*/




ALTER FUNCTION [dbo].[netsqlazman_GetDBUsers] (@StoreName nvarchar(255), @ApplicationName nvarchar(255), @DBUserSid VARBINARY(85) = NULL, @DBUserName nvarchar(255) = NULL) 
RETURNS TABLE
AS 
RETURN

   
    /*
    SELECT TOP 100 PERCENT CONVERT(VARBINARY(85), UserID) AS DBUserSid, UserName AS DBUserName, FullName, OtherFields FROM dbo.UsersDemo
    */
   
   
    select TOP 100 PERCENT
   
          [DBUserSid]        = CONVERT(VARBINARY(85), userAlias.UserId)
        , [DBUserName]        = userAlias.UserName
        , [FullName]        = userAlias.UserName
        , [OtherFields]        = userAlias.MobileAlias

    from
        [$(ASPNETMEMBERHIPDBNAME)].dbo.aspnet_Users userAlias
        left join [$(ASPNETMEMBERHIPDBNAME)].dbo.aspnet_Applications apps on  userAlias.ApplicationId = apps.ApplicationId

    WHERE
        (@DBUserSid IS NOT NULL AND CONVERT(VARBINARY(85), UserID) = @DBUserSid OR @DBUserSid  IS NULL)
        AND
        (@DBUserName IS NOT NULL AND UserName = @DBUserName OR @DBUserName IS NULL)
       
        /* This clause removed to keep users at a more global level.  03/27/2012  */
        /*  AND  (      ((( @ApplicationName IS NULL OR DATALENGTH(@ApplicationName) <=0  )))   OR   ( lower(apps.ApplicationName) = lower(@ApplicationName)    )    ) */
       
       
       
    ORDER BY UserName

GO



GO

if exists     (
                SELECT
                SPECIFIC_NAME ,DATA_TYPE , SPECIFIC_SCHEMA

                FROM INFORMATION_SCHEMA.ROUTINES --Only Procs
                WHERE 1=1
                AND ROUTINE_TYPE='FUNCTION'
                AND SPECIFIC_SCHEMA = 'dbo'
                AND SPECIFIC_NAME = 'netsqlazman_GetDBUsers'
                AND UPPER(DATA_TYPE) = UPPER('TABLE')
            )
    BEGIN
        GRANT SELECT ON [dbo].[netsqlazman_GetDBUsers] TO [$(DATABASEUSERNAMEVARIABLE)]
    END
ELSE
    BEGIN
        GRANT EXECUTE ON [dbo].[netsqlazman_GetDBUsers] TO [$(DATABASEUSERNAMEVARIABLE)]
    END


/*


   
GRANT SELECT ON [dbo].[netsqlazman_GetDBUsers] TO [$(DATABASEUSERNAMEVARIABLE)]   

*/


GO