SQL Permissions insert

Do not post support requests, bug reports or feature requests. Discuss phpBB here. Non-phpBB related discussion goes in General Discussion!
Ideas Centre
Post Reply
krazifan
Registered User
Posts: 13
Joined: Fri Feb 25, 2005 5:37 pm

SQL Permissions insert

Post by krazifan »

So I am doing several things with the forums based on actions happening in my ASP site. One of them is adding permissions for a user to see a forum. I'm doing this by using a SQL INSERT into the phpbb_acl_users table. Something odd is happening though.

1. SQL INSERT is done
2. Check site with user and they can't see the forum.
3. Post anything to any forum
4. They can now see the forum the SQL gave them permissions for

My question, is what would cause this behavior? Is there a "commit" timestamp or flag somewhere I have to trip with the SQL? Why would adding a post to any forum trigger permissions to be used that have been in the table for minutes already?

I hope this has been clear enough. I know not many people manipulate PHPBB with SQL, but hoping that someone can help me out here as this is very frustrating.

Thanks!

User avatar
dellsystem
Former Team Member
Posts: 3879
Joined: Sat Apr 09, 2005 8:54 pm
Location: Montreal
Name: Wendy
Contact:

Re: SQL Permissions insert

Post by dellsystem »

It's probably due to caching. See this: http://wiki.phpbb.com/Tutorial.Permissions#Using_SQL
Former moderator and website team member | My MODs, and more (GitHub)

krazifan
Registered User
Posts: 13
Joined: Fri Feb 25, 2005 5:37 pm

Re: SQL Permissions insert

Post by krazifan »

Damn I read that article tonight and completely missed the part about the cache.

Will go try deleting the files after the inserts and see how it goes.

krazifan
Registered User
Posts: 13
Joined: Fri Feb 25, 2005 5:37 pm

Re: SQL Permissions insert

Post by krazifan »

It didn't work. Files got deleted and still can't see the forums show up for the user, even a few minutes after the fact.

ToonArmy
Former Team Member
Posts: 4608
Joined: Sat Mar 06, 2004 5:29 pm
Location: Worcestershire, UK
Name: Chris Smith
Contact:

Re: SQL Permissions insert

Post by ToonArmy »

You need to empty the user_permissions field for the user in the users table.
Chris SmithGitHub

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: SQL Permissions insert

Post by drathbun »

One of the things phpBB3 does is cache permissions for each user so they don't have to be checked on each and every page view. Once a user logs in, my understanding is that their security is checked and stored on their row in the user table. From that point on, that set of permissions is used for the remainder of the session. In fact that permissions data persists from one session to the next until an admin makes any changes to the permissions table. I assume that when an admin makes any changes, it does NOT go out and update every user in the users table, but instead resets permissions to null. The next time a user logs in, or requests a page if they're already logged in, then the permissions field for that user is rebuilt.

That's what I have pieced together from reading posts here, in any case. I have not reviewed the code.

That means if you update the security settings directly (using SQL, as opposed to going through the forms) then you have to take the extra step of clearing out the user permissions field for all users, allowing it to be rebuilt on a user-by-user basis as happens with the standard code.
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

Pony99CA
Registered User
Posts: 4783
Joined: Thu Sep 30, 2004 3:13 pm
Location: Hollister, CA
Name: Steve
Contact:

Re: SQL Permissions insert

Post by Pony99CA »

drathbun wrote:That means if you update the security settings directly (using SQL, as opposed to going through the forms) then you have to take the extra step of clearing out the user permissions field for all users, allowing it to be rebuilt on a user-by-user basis as happens with the standard code.
That sounds like overkill. If the admin changes a group permission, I would hope that you'd only have to reset permissions for that group. Similarly, if the admin changes a user permission, I would hope that you'd only have to reset permissions for that user. That would save a lot of server load rebuilding everybody's permissions if only one user's permissions were changed.

Steve
Silicon Valley Pocket PC (http://www.svpocketpc.com)
Creator of manage_bots and spoof_user (ask me)
Need hosting for a small forum with full cPanel & MySQL access? Contact me or PM me.

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51986
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: SQL Permissions insert

Post by Brf »

When the admin changes permissions in the ACP, the user cached permissions are cleared properly. The problem here is that the admin is trying to change permissions directly in the database.

But to answer your question, you would only have to clear the permissions for that one group's user, for instance, but it is much easier just to do everybody. Their permissions are recalculated when they login, so there is not really much "server load" involved.

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: SQL Permissions insert

Post by drathbun »

Brf wrote:But to answer your question, you would only have to clear the permissions for that one group's user, for instance, but it is much easier just to do everybody. Their permissions are recalculated when they login, so there is not really much "server load" involved.
That's the "brute force" approach that I was taking. Yes, you could run a query to figure out which users you need to update, but the simplest approach would be to simply null out everything.
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

Pony99CA
Registered User
Posts: 4783
Joined: Thu Sep 30, 2004 3:13 pm
Location: Hollister, CA
Name: Steve
Contact:

Re: SQL Permissions insert

Post by Pony99CA »

drathbun wrote:
Brf wrote:But to answer your question, you would only have to clear the permissions for that one group's user, for instance, but it is much easier just to do everybody. Their permissions are recalculated when they login, so there is not really much "server load" involved.
That's the "brute force" approach that I was taking. Yes, you could run a query to figure out which users you need to update, but the simplest approach would be to simply null out everything.
If the OP is just adding permissions for one user, it's not like it's rocket science -- just add a WHERE clause to the UPDATE with the user's user_id (which he probably already has for updating the permissions). Why change everybody when the "better" way is so easy?

If he's updating a group, he'll need a join or a nested query, which is a bit more complicated (but not much).

Steve
Silicon Valley Pocket PC (http://www.svpocketpc.com)
Creator of manage_bots and spoof_user (ask me)
Need hosting for a small forum with full cPanel & MySQL access? Contact me or PM me.

User avatar
A_Jelly_Doughnut
Former Team Member
Posts: 34457
Joined: Sat Jan 18, 2003 1:26 am
Location: Where the Rivers Run
Contact:

Re: SQL Permissions insert

Post by A_Jelly_Doughnut »

Pony99CA wrote: If the OP is just adding permissions for one user, it's not like it's rocket science -- just add a WHERE clause to the UPDATE with the user's user_id (which he probably already has for updating the permissions). Why change everybody when the "better" way is so easy?
When a single user is edited, only that user's permission cache is reset. When a group, or several users are changed, everyone's permission caches are cleared.
A Donut's Blog
"Bach's Prelude (Cello Suite No. 1) is driving Indiana country roads in Autumn" - Ann Kish

Post Reply

Return to “phpBB Discussion”