User not being checked for validity.

Apr 9, 2010 at 7:57 AM

When you have DB Users linked into a storagegroup, role, task, operation if the DBUser is then removed from the membership database (in my case aspnet membership tables) NetSqlAzMan seems to retain a link to the user but instead of displaying their name displays their SID instead.  If this user if then re-added to the membership database and then relinked into NetSqlAzMan as in this instance the DBUser would have a new SID, NetSqlAzMan does something weird and in some way the DBUsers old linked SID seems to be relinked to the DBUsers new SID and therefore NetSqlAzMan seems to think the user has been linked in again.  I have had the same user linked into a role about a dozen times on several occasions.  Saying NetSqlAzMan is meant to be an authorization management system I would of thought all user would be checked for validity before any operations against them are performed and if the user can't be found then all reference for that user be removed.  But sadly this doesn't seem to be the case and has both myself and my colleagues concerned as this could be a potential security issue.  The same could be happening for Windows users too but the concern is mroe for DB Users as these users are generally external from the company.  Saying there is a GetDBUser method and this is a security orientated application it just seems odd that users aren't being checked out first.

Coordinator
Apr 9, 2010 at 12:05 PM

Hi,

DB Users are "external" to the NetSqlAzMan solution; this is the reason why I have called the user database table "DBUsersDemo".

Being outside cannot catch the moment when they are modified or removed.

Same goes for Windows users; NetSqlAzMan deals only Authorizations and it is not responsible of the Users registry management.

When Database/Windows SID's are not resolved the Anonymous user icon appears (the one with the question) just to demonstrate this fact.

This is a desired behavior (or saying as Microsoft ... it is by-design :-)).

Regards,

Andrea.

__________________________________
Andrea Ferendeles
NetSqlAzMan Project Coordinator
E-mail aferende@hotmail.com Web http://netsqlazman.codeplex.com

Apr 12, 2010 at 7:12 PM

Agree with Andrea this is just authorization, not authentication.

Once you delete the user from your membership database, they would no longer be allowed to login (by your code) and therefore you would never even query the authorization.

However if adding a new user into your membership database automatically re-links that new user with some old deleted user, that would seem to be an issue.

You could modify the aspnet_Users_DeleteUser stored proc in aspnetdb to delete the appropriate data from the NetSqlAzMan tables or create a stored proc you run via SQLAgent that clears out any dbusers from NetSqlAzMan that no longer exist and run it daily (just to remove those invalid users from the GUI).

Maybe Andrea can give some guidance on exactly how to remove all references to a given dbuser from NetSqlAzMan tables?

 

Coordinator
Apr 14, 2010 at 1:52 PM

Hi,

I think this TSQL could be useful to get “unresolved” windows user SIDs (deleted Windows NT Account):

SELECT     *

FROM         netsqlazman_AuthorizationsTable

WHERE     (SUSER_SNAME(objectSid) IS NULL)

Use instead a custom TSQL for check deleted DB Users …

Regards,

Andrea.

__________________________________
Andrea Ferendeles
NetSqlAzMan Project Coordinator
E-mail aferende@hotmail.com Web http://netsqlazman.codeplex.com

Apr 14, 2010 at 2:00 PM

If a user is unresolved or a dbuser is deleted, how do we remove all authorizations (and group memberships) for that user?

Coordinator
Apr 14, 2010 at 2:02 PM

I suggest to add a Delete Trigger on the DBUsersDemo table that clean all.

Regards,

Andrea.

__________________________________
Andrea Ferendeles
NetSqlAzMan Project Coordinator
E-mail aferende@hotmail.com Web http://netsqlazman.codeplex.com

Coordinator
Apr 14, 2010 at 2:03 PM

For Windows NT Users/Groups … Delete Triggers are everywhere into the NetSqlAzManStorage DB.

Apr 14, 2010 at 2:05 PM

1)  So you are saying, for Windows NT Users/groups - when you delete those - all the authorizations are removed?

2)  What TSQL is necessary to remove all authorizations for a given DBUser?  Maybe a stored proc would be nice addition - 'remove_authorizations_for_dbuser'?

 

 

Coordinator
Apr 14, 2010 at 2:09 PM

1) Yes (they are also deleted from Store/Application Groups … if found)

2) Could be for you a nice exercise … writing this stored procedure for DB User removal J

Apr 14, 2010 at 2:13 PM

I will do it, if you will just give me a quick nudge on what tables I need to look in to remove all their data.

 

Coordinator
Apr 14, 2010 at 2:16 PM
Edited Apr 15, 2010 at 6:00 AM

 

Use this trick.


Create a Window User and assign him to a:

- Store Group

- Application Group

- Authorize on a Role, Task and Operation.


Open SQL Server Profiler, catch all (include trigger audit) and delete this user using MMC Console.

You will have the right delete sequence and the list of all SQL objects involved.

;-)

 

__________________________________
Andrea Ferendeles
NetSqlAzMan Project Coordinator
E-mail aferende@hotmail.com Web http://netsqlazman.codeplex.com


 

Apr 15, 2010 at 4:36 AM

Not funny.

 

Apr 15, 2010 at 6:18 AM

Thanks....that is very useful.

 

Coordinator
Apr 15, 2010 at 6:27 AM

This is the ready jelly:

/* ASSIGN @DBUserName */

DECLARE @DBUserName nvarchar(255)

SET @DBUserName = 'Andrea'

DECLARE @DBUserSID varbinary(85)

SELECT @DBUserSID = DBUSerSid FROM [NetSqlAzManStorage].[dbo].[netsqlazman_GetDBUsers] (

   NULL

  ,NULL

  ,NULL

  ,@DBUserName)

BEGIN TRANSACTION

delete from dbo.netsqlazman_AuthorizationsTable

where objectSid = @DBUserSID

delete from dbo.netsqlazman_ApplicationGroupMembersTable

where objectSid = @DBUserSID

delete from dbo.netsqlazman_StoreGroupMembersTable

where objectSid = @DBUserSID

DELETE FROM [NetSqlAzManStorage].[dbo].[UsersDemo]

where UserID = CONVERT(int, @DBUserSID)

COMMIT TRANSACTION

__________________________________
Andrea Ferendeles
NetSqlAzMan Project Coordinator
E-mail aferende@hotmail.com Web http://netsqlazman.codeplex.com