custom authentication for defined users in a MS Sql Server database

Jun 30, 2009 at 6:32 AM

Hi Andrea,

I am new in the authorization , I am just read about the Microsoft Azman  and your NetSQlAzman but we prefered to use your manager in our project

I need just to understand how we can check access for users in Custom table in my database i have tabel users in my project and i neeed to check acess for this usres

you write that i shoud modify dbo.GetDBUsers function how this function will get users from my custom table

and may be the authorization store in Another database

 

Thanx Andrea

Coordinator
Jun 30, 2009 at 7:37 AM

Hi,

yes, you can users from a custom table on a different database.

Just configure a Linked Server (only if on different server) and add [YourExternalDB]. Prefix to point to another Database Users Table.

Just customize the dbo.GetDBUsers() Table Function as following.

USE [NetSqlAzManStorage]

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 [YourExternalServer].[YourExternalDB].dbo.GetDBUsers(<storename>, <applicationname>, NULL, NULL)            -- to retrieve all DB Users

            2)     SELECT * FROM [YourExternalServer].[YourExternalDB].dbo.GetDBUsers(<storename>, <applicationname>, <customsid>, NULL)  -- to retrieve DB User with specified <customsid>

            3)     SELECT * FROM [YourExternalServer].[YourExternalDB].dbo.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

*/

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

      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)

      ORDER BY UserName

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

-- THIS CODE IS JUST FOR AN EXAMPLE: comment this section and customize "INSERT HERE YOUR CUSTOM T-SQL" section below

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

__________________________________
Andrea Ferendeles
NetSqlAzMan - Project Coordinator

http://netsqlazman.codeplex.com

Jun 30, 2009 at 8:14 AM

I am still confused, here in the function you select from DemoUser table and this table in the same db of authorization

but my table in another database,  so i will do like this in the function

please just confirm my understanding

SELECT TOP 100 PERCENT CONVERT(VARBINARY(85), UserID) AS DBUserSid, UserName AS DBUserName FROM [YourExternalServer].[YourExternalDB].dbo.UsersTable

      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)

      ORDER BY UserName

Regards,

Ahmed

Coordinator
Jun 30, 2009 at 8:55 AM

Exactly. J

__________________________________
Andrea Ferendeles
NetSqlAzMan - Project Coordinator

http://netsqlazman.codeplex.com

Jun 30, 2009 at 11:36 AM

Thanx Andrea for your help

it is working,

but i have strang  deployment  secnario please if you can help

i have WCF Service for Authontication and authorization and this WCF work as warapper for Membeship provider and Netsqlazman

this service accessed by many customer every customer has a database to keeeb application data and may be diffrennt database to keeb authontication and authorization data

we mak install to the wcf service on the same server we install the netsqlazman

we create diffrent stores for diffrent customers so every customer my have one  store to manage its applications authorization

so  when i make check access i call it like this

CheckAccess(CustomerIdentity,Storename,ApplicationName,ItemName,DbUserName)

{

           storage = GetStorage(CustomerIdentity)
           'Retrieve DB User identity from dbo.GetDBUsers Table-Function
           Dim dbUser As IAzManDBUser = storage.GetDBUser(dbUserName)

}

the problem we face now is getdbuser /getdbusers get from one table , we neeed it dynamic according custoner

please if you can suggest solution

regards,

Ahmed

 

Coordinator
Jun 30, 2009 at 9:06 PM

Hi Ahmed,

no problem …

As you can see here:

ALTER FUNCTION [dbo].[netsqlazman_GetDBUsers] (

@StoreName nvarchar(255),

@ApplicationName nvarchar(255),

@DBUserSid VARBINARY(85) = NULL,

@DBUserName nvarchar(255) = NULL)

The GetDBUsers() function accepts @STORENAME and @APPLICATIONAME , so you can just add an additional WHERE clause to your custom DB Users SELECT …. or better create an IF branch like this inside the dbo.GetDBUsers() table function:

IF @StoreName = ‘My Store 1’

BEGIN
SELECT …. From [MyDB].dbo.[My Store 1 DB Users]
WHERE …
END

ELSE IF @StoreName = ‘My Store 2’

BEGIN
SELECT …. From [MyDB].dbo.[My Store 2 DB Users]
WHERE …
END

Let me know.

Regards,

Andrea.

__________________________________
Andrea Ferendeles
NetSqlAzMan - Project Coordinator

http://netsqlazman.codeplex.com