Retrieving tabs using SQL, security-aware

There are times when you like to go deeper with DNN and retrieve certain information by means of an SQL Query or a stored procedure. That's what happened to me when I needed to create a custom combo box which would redirect the user to specific pages.

In order to achieve that functionality, I had to use a module which would permit me to issue SQL queries to DotNetNuke and then format the results as I wanted. As I've stated many times before, I find the ListX module pretty useful in these situations.

One thing I discovered, though, is that you need to have a deeper understanding on what's going on with security in order to issue an SQL query that selects ONLY the tabs that a registered user has the right to see. You see, DNN tabs are not always visible to all user roles, so when using SQL you must be careful not to retrieve tab records that lead nowhere for the current user.

I'm not going to go into details on what I've done with the ListX module, unless someone asks specifically for it. Instead, on this post, I'm going to focus on how to issue a SQL query that retrieves records from the Tabs table that are visible for the current user.

For this post, it is assumed that you already know the ID of the current user in some way.

If you issue something like:

SELECT tabid, tabtitle FROM Tabs

Then you'll have all tabs, even those that are deleted. So we first need to have a WHERE clause to exclude deleted tabs, like this:

SELECT tabid,tabtitle FROM Tabs WHERE isDeleted=0

That leaves as with all active tabs, even those that are accessible by the administrator. In order to make our query security-aware, we have to see what tables the Tabs table is related to, in the context of security permissions.

The related tables are the following:

• TabPermission: A table which holds role permissions for every tab. Remember, DNN security works with roles. (We assume that we're not using the special "specific userid" feature that was introduced in later version of DNN).
• UserRoles: A table which holds the participations of DNN users to DNN security roles
• Users: A table which holds the actual DNN users.

The table that interests us more is the TabPermission table. Let's see the fields that interest us the most from this table:

TabId:

The tab id the record is referring to. There may be more than one records per tab id in the TabPermissions table, if the tab is available to multiple roles or even to one role with multiple permissions (DNN grants VIEW and EDIT permissions to tabs).

PermissionID:

The number you see here is the type of permission specified, and is related to the Permission.PermissionId field. If you do a SELECT * FROM Permissions you'll get, among other records, the following two records:

3    SYSTEM_TAB    -1    VIEW    View Tab
4    SYSTEM_TAB    -1    EDIT    Edit Tab

For our purposes, we will need permission id 3, i.e. VIEW permissions. Remember, we need the tabs that our user is allowed to see.

RoleID:

This is where things get a bit complicated. The RoleID field is related to the Roles.RoleId field (try a SELECT * FROM Roles to see what you'll get). But here, we don't need all the roles but just the roles our user is participating in. So we need to relate this field to the UserRoles table, which holds that information. If you do a SELECT * FROM UserRoles WHERE UserId=xxx (put your user id in the place of xxx), you'll get all role participations for the specified user.

(You'll notice that the UserRoles table also has the fields ExpiryDate, IsTrialUsed and EffectiveDate. For the sake of simplicity, we'll suppose that we don't have any roles with dates set, although it would be very easy to extend our final query to include only roles that are effective in the current time period.)

In the TabPermissions table, you'll notice two additional role ids that DO NOT exist on the Roles table: -1 and -2. -2 stands for "Host", and concerns pages accessible by the host user. -1 stands for "All Users" and concerns all non-administrative pages that are accessible from the "All Users" virtual role (this role actually does not exist anywhere).

Try doing the following:

--Fetch only admin pages accessed by the Administrators role 
select * from tabpermission 
inner join tabs 
on tabs.tabid = tabpermission.tabid 
where roleid=0

--Fetch only non-admin pages accessed by all users 
select * from tabpermission 
inner join tabs 
on tabs.tabid = tabpermission.tabid 
where roleid=-1

--Fetch only host pages 
select * from tabpermission 
inner join tabs 
on tabs.tabid = tabpermission.tabid 
where roleid=-2

And observe the different results.


So what do we need in order to get the tabs that are accessible from a single, specific user? We'll take our initial query:

SELECT 
    tabid,tabtitle 
FROM 
    Tabs 
WHERE 
    isDeleted=0

And we'll amend it as follows:

declare @userid int 
select @userid = 2 

    select 
        tabs.tabid, tabs.title 
    from 
        tabs 

inner join ( 
    select 
        distinct tabpermission.tabid 
    from 
        tabpermission 
        left outer join 
        userroles on tabpermission.roleid = userroles.roleid 
        left outer join 
        users on users.userid = userroles.userid 
    where 
        tabpermission.permissionid=3 
        and (users.userid = @userid 
        or tabpermission.roleid=-1 or @userid=1) 
    ) as perm 
on perm.tabid=tabs.tabid 

where 
    tabs.isdeleted=0 
order by tabs.tabid ASC

What we did was to initially declare a @userid variable and give it a value (4 for our example, whatever suits you for your DNN installation - if you're making an SP out of this you can just provide the user's ID value as a parameter to the SP).

We're using the @userid variable inside our JOIN statement, which actually joins the Tabs table with the TabPermission table, but restricts the TabPermission table results to where the permission type is VIEW (id 3) and our user id is equal to @userid (users.userid = @userid) OR there are view rights for all users (tabpermission.roleid=-1)  OR our user is the administrator. (@userid=1), which overrides everything else. The DISTINCT clause is used to ommit propable multiple results for the same tab.

This will essentially get us the tab records that are accessible with VIEW permissions for the given user.

As I've mentioned before, some things are not covered in this query:

• Permissions to a specific user id



• Roles with start and expiry dates

But in my opinion it's a good starting point for anyone who needs to create a security-aware tab retrieval query in DNN.

Please let me know what you think.