Different database users.

Question

Different database users.

dimhoLt May '14

Created May '14	Last Reply May '14	Replies 3	Views 712	Votes 0

dimhoLt 22.6k

May '14

Hello!

For security reasons, I am only granting access to some tables for specific database users, and when staff members log in, I want a different database user to be querying the DB from the system.

I've set up different connection services in accordance with this instruction, but I'm facing the problem that the initialize()-method is only run once per model, meaning that if you query a disallowed model once, log in (which should make it available) and then query the model again, the cached connection service still uses the database user who'd not allowed access.

How can I reset that cache when you log in, or is there a different functionality I should look into to solve this problem?

Thank you.

// dimhoLt

Dylan Anderson · Answer 1 · 2014-05-12T14:29:35-07:00

Caching a database connection between requests doesn't make sense. The connection isn't persistent, so caching the object doesn't save you a lot since most of the overhead will be with establishing a connection to the database.

I guess I'm a little unclear on exactly what your situation is.

firemyst13 · Answer 2 · 2014-05-14T15:34:14-07:00

Did you try using onConstruct() instead of inititalize()? Then your connection class is set each time the object is created, instead of just once per session.

dimhoLt · Answer 3 · 2014-05-14T15:49:52-07:00

Thank you for your replies.

@quasipickle: Well, the connection service is set once, statically, and then kept, which means that if I use a specific database user (guest) which has limited access and initializes a model, then logs in at a later time in the same request changing database user through the connection service and attempts to access the same model again, the previous connection service is used.

To try to explain the situation better; I have a database with very, very sensitive information. The public users only have access to some tables, but the staff should have access to some more tables once they've logged in, so I want to change connection service after the login has been successful. However, if a model has been intialized, the connection service cannot be changed in the same request.

@firemyst13: Yes, I did. Problem is that it doesn't work with the static methods; Model::find(), Model::findFirst() etc. I'd have to overload those to actively re-run the self::initialize()-methods again, which wouldn't be good practice... At least, I'd like to find the "proper" way to resolve this problem.

Dylan Anderson · Answer 4 · 2014-05-14T16:02:07-07:00

Ah see, I misunderstood. I thought the query was run once. Then on a different page load, the user logs in, then on a different page load, you want to query again. My mistake.

While you say it won't be good practice, I think your best option is to overwrite find() (I believe findFirst() calls find() itself so I don't think you'd need to overwrite both methods. The overwrite could be as simple as (assuming all your models extend a base model):

class BaseModel extends \Phalcon\Mvc\Model{
    public function find($parameters){
        if(userIsLoggedIn(){
            $this->setConnectionService('dbConnectionAuthed');
        }
    }

    parent::find($parameters);
}