如何获取mysql数据库更新以在PHP会话中触发重新身份验证

Question

When starting a session, an authentication is made to the database through a query. If this authentication is granted, one or more session variables are filled with data. This allows the user to transfer through multiple pages without having to re-authenticate - which is great. However, if a session variable that is being used is changed within the database, i.e. username change, access privileges change, the changes are not rippled through to the session (obviously).

How is it possible to get the database changes to trigger, or ripple to, the PHP session variables.

An example is being logged into a website where you have access privileges x that allows you to access pages 1,2,3. Your privileges are now taken away for some reason and you now have access privileges y which allows you to only access page 1. If the user is already authenticated within the site, these changes will not affect the users current session, and will still be able to access pages 2 and 3. This could be an issue in many situations.

Currently my solution to the problem is to re-authenticate the user every page, and update the session variables accordingly. This definitively seems the wrong way to accomplish this task from my limited understanding of how sessions (should) work.

Essentially, I would like a way for database updates to trigger a re-authenticate of the current logged in user. i.e. if user john12 has his database row altered, then his session should require re-authentication.

At the moment I can't think of any way to accomplish this without querying the database every time a page is loaded.

Any tips or solutions would be greatly appreciated.

Answer 1

How I usually do it is by having 4 fields for authentication is my database.

• Username
• Password (usually hashed)
• Token
• Logged IP

I remember the user's auth data in cookies. When user has entered the right username and password the website generates a new token and sets two cookies - username and token. On every page load you check if the username, token and logged IP match (to prevent token steal).If one of them isn't right remove all of them and redirect to login page.

In your case if you want to relogin on password change just delete the token when it has been changed.

The only con here is that only one machine can be logged at a given time.

---

I don't see why you don't want to query the database for user permissions, SQL databases are incredibly fast even with lots of records especially if searching by primary key.