Delete Soft Deleted Users After Deleting Portals
Mar
10
Written by:
3/10/2010
I ran into an interesting situation last night. I don’t know why I hadn’t noticed this in the past. My blog site was originally one of many in a single DotNetNuke® installation or instance. Since it has grown in popularity and traffic, I had to move it to it’s own standalone DNN instance. The quickest way to make that happen was to move a copy of the original instance, and delete all of the other portals that came with it. In doing so, this left all of the users from the other portals still in the Users table in the database.
WARNING!!! Don’t just run the queries here. Read the post in detail first. Also, BACKUP your database before manually changing any data. You may severely mess up your site. You’ve been warned!
At first, I kind of panicked, because I was looking at the users in my Superusers Account module. It was 100+ pages of users! We all know that there is probably not a single site out there that has 10 super users, much less 100+ pages worth of super users. In the first page, there were several test accounts, so my first thought was that I had been hacked at some point. However, I didn’t take the history of the portal into account.
As it turns out, I had learned in a very scary way how DNN handles user accounts when portals are deleted. I am not sure of the thought process behind it, but the users are soft deleted from the portals that get deleted. In this situation, this has a couple of distinctive attributes to take note of.
- The IsDeleted column in the Users table is 1
- The PortalId column in the UserPortals table is NULL
You can see these attributes by running the following query:
1: SELECT up.[PortalId], u.[UserID], u.[Username], u.[FirstName], u.[LastName], u.[IsSuperUser], u.[Email], u.[DisplayName], u.[IsDeleted]
2: FROM [databaseOwner}[{objectQualifier}Users] u
3: LEFT OUTER JOIN [dbo].[UserPortals] up ON u.[UserId] = up.[UserId]
4: ORDER BY up.[PortalId], up.[UserId];
5: -- OR --
6: SELECT up.[PortalId], u.[UserID], u.[Username], u.[FirstName], u.[LastName], u.[IsSuperUser], u.[Email], u.[DisplayName], u.[IsDeleted]
7: FROM [dbo].[Users] u
8: LEFT OUTER JOIN [dbo].[UserPortals] up ON u.[UserId] = up.[UserId]
9: ORDER BY up.[PortalId], up.[UserId];
At first glance, this appears to be an easy fix. That is, if you’re not paying attention. Just delete all of the users that are no longer associated with a portal. Let’s try it!
1: DELETE FROM {databaseOwner}[{objectQualifier}Users]
2: WHERE [UserId] IN (
3: SELECT u.[UserId]
4: FROM {databaseOwner}[{objectQualifier}Users] u
5: LEFT OUTER JOIN {databaseOwner}[{objectQualifier}UserPortals] up ON u.[UserId] = up.[UserId]
6: WHERE up.[PortalId] IS NULL
7: )
8: -- OR --
9: DELETE FROM [dbo].[Users]
10: WHERE [UserId] IN (
11: SELECT u.[UserId]
12: FROM [dbo].[Users] u
13: LEFT OUTER JOIN [dbo].[UserPortals] up ON u.[UserId] = up.[UserId]
14: WHERE up.[PortalId] IS NULL
15: )
Well, that didn’t work so well. What I failed to notice is what you might not have as well. Super User accounts are not associated to a portal. So the previous query would have deleted all of those users as well. D’oh! Luckily, I backup my database before I do anything like this, so I just restored it real quick.
To delete all of the soft deleted users from the other portals, we need to adjust the previous query to filter out the super user accounts.
1: DELETE FROM {databaseOwner}[{objectQualifier}Users]
2: WHERE [UserId] IN (
3: SELECT u.[UserId]
4: FROM {databaseOwner}[{objectQualifier}Users] u
5: LEFT OUTER JOIN {databaseOwner}[{objectQualifier}UserPortals] up ON u.[UserId] = up.[UserId]
6: WHERE up.[PortalId] IS NULL AND NOT u.[IsSuperUser] = 1
7: )
8: -- OR --
9: DELETE FROM [dbo].[Users]
10: WHERE [UserId] IN (
11: SELECT u.[UserId]
12: FROM [dbo].[Users] u
13: LEFT OUTER JOIN [dbo].[UserPortals] up ON u.[UserId] = up.[UserId]
14: WHERE up.[PortalId] IS NULL AND NOT u.[IsSuperUser] = 1
15: )
Viola! That did the trick. All of the goodness, none of the oversights. I hope this helps you too!
2 comment(s) so far...
Re: Delete Soft Deleted Users After Deleting Portals
Hi Will,
The reason for this behavior is due to the non-repudiation assurances manifest in DotNetNuke 5.0+. Consider an attacker who is able to obtain elevated access to an installation via an arbitrary account, perform a malicious attack (e.g. steal credit card information), and then delete the portal. If the malicious user's account were deleted with the portal, then there would be no resultant binding between that account and the malicious behavior.
Persisting these user data beyond the lifetime of the portal ensures that, even if an attacker were to be able to somehow remove a portal, we can obtain audit information, and therefore ensure non-repudiation. (Extra credit: what is the weakness in this argument, with respect to host users?)
An alternate way to look at this is that DotNetNuke cannot delete ANY user unless that user has no audit data associated therewith. But, since the act of creating an account also creates audit information, any users cannot be deleted without compromising the principle of non-repudiation.
So here we're trading a tiny bit of usability for some added security. When thinking about it this way, I think the trade-off is warranted.
Brandon
By Brandon Haynes on
3/10/2010
|
Re: Delete Soft Deleted Users After Deleting Portals
Whoops... I was just made aware that there was a similar blog post last month by Chris Hammond.
How to Hard Delete Users in DotNetNuke
At least I managed to get a different use case... Whew! :D
By Will on
3/10/2010
|