The SQL Tab Clause and the User Admin screens

3 minute read time.

Normally when you enter a SQL Tab condition associated with an option in a Tabgroup you can either work with the current entity in context or the current user in context. But what if you want to control the tabs for the Admin Screens in the user area then we will end up in a dilemma because which 'user' are we trying to refer to?

There are 4 tab options in the AdminSingleUser system menu.

User Details
User Preferences
User Security Profile
User Activity History

I tested access to the tab using the standard Admin logon. Below is the SQL generated:

SELECT * from UserSettings where USet_UserID=1 AND Uset_Key = N'AdminLastView'
select * from vSearchListUser where User_UserId=4
select ChLi_Channel_ID from Channel_Link where ChLi_User_ID = 4
SELECT * FROM vUsers WHERE User_TemplateName = N'Default User Template'

I gave an Info Manager (Susan Maye) rights to administer users and tested access to the tab.

This generated the following SQL:

SELECT * from UserSettings where USet_UserID=4 AND Uset_Key = N'AdminLastView'
select * from vSearchListUser where User_UserId=4
select ChLi_Channel_ID from Channel_Link where ChLi_User_ID = 4
SELECT * FROM vUsers WHERE User_TemplateName = N'Default User Template'

The only tabs displayed were:

User Details
User Preferences
User Security Profile

The Info Manager was not given access to the User Activity History tab. But the SQL generated does not indicate this. This shows that the Admin area is covered by other dll cached rules rather than simple Meta Data checks. But this is a side issue. I want to control one of my own custom tabs.

I added an Extra Tab to the AdminSingleUser that would call an ASP page. The tab appeared for both the System Administrator and the Info Manager with User Admin Rights. There was no change in the SQL that underlay this screen.

I put a condition on the preferences tab

user_primarychannelid = 5

This should mean that only people in the Operations Team should see this tab.

The new tab disappeared for both my Info Manager and the Full System Administrator. The System Admin in the default system demo data is in the Operations team! So why didn't it appear for the user??

The SQL under the screen now includes an extra check. This is the SQL for the Info Manager!

select * from vusers where (user_userid=4) AND (user_primarychannelid=5 AND (user_disabled = N'' OR user_disabled IS NULL) AND (user_istemplate = N'' OR User_IsTemplate=N'N' OR user_istemplate IS NULL))

The Info Manager logon I used was Susan Maye, she is in team Sale Team (id = 1) and so it is not surprising that the tab didn't display.

The SQL for the System Admin looked like this:

select * from vusers where (user_userid=4) AND (user_primarychannelid=5 AND (user_disabled = N'' OR user_disabled IS NULL) AND (user_istemplate = N'' OR User_IsTemplate=N'N' OR user_istemplate IS NULL))

So what is wrong with this? The user_primarychannelid =5 is not a problem as the Administrator is in the Operations team. But look at the user_userid check! It says user_userid=4 NOT user_userid=1 which is the id of the System Admin. D'oh! The system is checking the details of the user that is being Administered and not the logged on User!!!!

So what is the work around? I don't think there is anything that I can do in the SQL tab. So the page is going to have to be displayed. If this is an ASP page or a .NET application extension then this is not too much of a problem as controlling access to the data is most important.

Let's assume that we we have an ASP page added here.

We will just need to build a check for the user details with the eWare.GetContextInfo("user","user_userid") method. We can then guard the inclusion or display of information.

if (CRM.GetContextInfo("user","user_primarychannelid")==5)
{
//Display Block
myContainer.AddBlock(userextraadminblock);
}
else
{
//Use Error Message
myContainer.AddBlock(accessdeniedcontentblock);
}

Sage CRM

The SQL Tab Clause and the User Admin screens

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING