User Access Control - I Need Some Expert Help

13 posts by 4 authors in: Forums > CMS Builder
Last Post: May 25, 2012   (RSS)

By Perchpole - May 21, 2012 - edited: May 21, 2012

Hello, All -

A few months ago I was working on a project which required another layer of admin control over the users. I had to find a simple way to control the categories into which the users could post their records. I figured the easiest solution would be to create a drop-down menu in the User Accounts editor which listed all of the categories on the site. I could then select which categories each user was allowed to access.

When the user came to post a record, the category menu on the record screen would list only those categories I had hi-lighted.

Simple.

Except it didn't quite work!

I created the (multi-select) drop-down menu in the User Access editor - called categoryAccess.

I then created a category menu in the Record editor and added the following (advanced query) code:

<?php
$categoryAccess = join( ",", explode( "\t", trim( $CURRENT_USER['categoryAccess'], "\t" ) ) );
?>
SELECT num, name
FROM `<?php echo $TABLE_PREFIX;?>category`
WHERE num
IN (<?php echo $categoryAccess;?>)
GROUP BY num

It does work - but not all the time - and when it fails it throws an SQL error. Crucially, this error is seen in the backend and on the frontend pages of the site.

This should be realy simple way to control user access on the site - but I can't work out why it doesn't work properly.

I'd be very grateful for any support.

:o/

Perch

Re: [Perchpole] User Access Control - I Need Some Expert Help

By robin - May 21, 2012

Hey Perch,

Could the sql errors be from when the user has no categories set for access? In that case, the sql would look like:
WHERE num IN ()
...which sql doesnt like.

Oh also, if you're still having trouble, it would help if you posted the sql error.

Thanks,
Robin
Robin
Programmer
interactivetools.com

Re: [robin] User Access Control - I Need Some Expert Help

By Perchpole - May 23, 2012

Hi, Robin -

Thanks for your help. I think you've identified the problem. Part of the error message on the front of the site is:

There was an error creating the list field ''.

The error message in the back-end is more complex but basically says the same thing.

The question now is how do I solve it?

There are two occasions when this error will occur.

1) When a non-registered user visits the site.

2) When a registered user visits the site before they have been assigned categoryAccess.

Can you give me a solution that will handle both issues equally?

:0/

Perch

Re: [Perchpole] User Access Control - I Need Some Expert Help

By ross - May 23, 2012

Hi Perch

Thanks for getting back to us :). Could you post me up a copy of the schema files you are working with? I'd like to get a local version of your setup so I can play around with it a bit.

If you'd feel more comfortable doing this via email, send them over to support@interactivetools.com and reference this forum post.

Thanks!
-----------------------------------------------------------
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com

Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/

Re: [ross] User Access Control - I Need Some Expert Help

By Perchpole - May 23, 2012

Thanks, Ross -

Email is already sent.

:0)

Perch

Re: [Perchpole] User Access Control - I Need Some Expert Help

By gkornbluth - May 23, 2012 - edited: May 23, 2012

Hi Perch,

Could you set all new members to a categoryAccess of "no access" using the default setting field? That might solve some of the issues.

If there are not too many members, could you manually assign all blanks to that as well.

Jerry Kornbluth
The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

Re: [gkornbluth] User Access Control - I Need Some Expert Help

By Perchpole - May 23, 2012

Hi Jerry -

Thanks for that - but it doesn't work.

When a user tries to enter the record editor, the code pulls together a list of all the categories into which the user can post. If it can't find any (because non have been assigned) it throws an MySQL error.

As Robin mentions, above, the code is hitting a dead end:

WHERE num IN ()

My guess is I will have to create a default value - so that the list is never empty.

My only concern is what to do about unregistered users. It seems odd to me that the system falls over when a non $CURRENT_USER shows up!

:0/

Perch

Re: [Perchpole] User Access Control - I Need Some Expert Help

By gkornbluth - May 23, 2012 - edited: May 23, 2012

Hi Perch,

I was suggesting just that, creating a default. "no assignment" value.

I have no idea why the system does what it does when it doesn't find a Current User.

have you tries an @ before the variable or creating an if for the condition where there is a Current User and an else for the condition where there's no Current User (like You must be a current user to access this feature).

Jerry
The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

Re: [gkornbluth] User Access Control - I Need Some Expert Help

By Perchpole - May 24, 2012 - edited: May 24, 2012

OK,

This is what I tried:

<?php if($CURRENT_USER): ?>
<?php $categoryAccess = '1'; ?>
<?php $categoryAccess .= ',' . join( ",", explode( "\t", trim( $CURRENT_USER['categoryAccess'], "\t" ) ) ); ?>
SELECT num, name
FROM `<?php echo $TABLE_PREFIX;?>category`
WHERE num
IN (<?php echo $categoryAccess;?>)
GROUP BY num
<?php else: ?>
SELECT num, name
FROM `<?php echo $TABLE_PREFIX;?>category`
<?php endif ?>

This appears to take care of the non $CURRENT_USER problem - but there is still an issue with registered users who have not been assigned any categoryAccess.

When such a user attempts to create a record they receive this error:

Warning: Cannot modify header information - headers already sent by (output started at /server/path/cms/lib/menus/header.php:13) in /server/path/lib/database_functions.php on line 548 There was an error creating the list field 'category'. MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') GROUP BY num' at line 4

If I've got my code (above) right then I can't understand what's going on. The code gives categoryAccess a default value of 1 so we should have this:
WHERE num IN (1)

...but for some reason it still can't build the category menu.

:0/

Perch

NB: The error above also appears on the front of the site.