Filtering data in back-end based on authenticated user's role set in MySQL DB

[NorthFred]

My latest project uses Firebase authentication for token issuing. The token doesn't contain any information regarding e.g. user's role or permissions. The MySQL database however contains a table 'Users' which stores the user's role.

I am looking for a way to filter the returned data from certain tables and columns based on the user's role which is set in the 'Users' table. I tried looking for an example from the documentation without success. Is it possible to do this?

Example data:

Table 'Users':

id            email                              role
-------------------------------------------
rTx40      [email protected]        admin
9Kxk       [email protected]               customer

Table 'Reservations':

id            date                                    origin               phoneNr
----------------------------------------------------------------------------------
1            08/08/2022 12:00:00          customer         012-456789
2            09/08/2022 09:30:00          system            098-786543
3            09/08/2022 11:00:00          system            022-599959   

Requirements:

• When admin user "[email protected]" is signed in, the API can return ALL information from the 'Reservations' table.
• When a customer user "[email protected]" is signed in, she may not have access to the phoneNr data, but needs to know the time of the Reservation slot.

[mevdschee]

I tried looking for an example from the documentation without success. Is it possible to do this?

Yes sure, the user is loaded in the $_SESSION variable and the authorization handlers can read that and apply some custom logic, based on the user properties.

[NorthFred]

I'm afraid I am stuck with this, I could really use an example on how the filtering could be done for given example in the authorization handlers. I'm looking for the logic to determine what role a user has in a specific table (based on the userId from $_SESSION['claims']['user_id'] and then allow the API to return that data based on the set role... I couldn't figure it out with the examples in the doc...

[NorthFred]

I tried fixing up the layout of the example in the OP. Hopefully someone can guide me in the right direction on how to use the authorization handlers to avoid access to some (complete) table, based on the user's role in another table.

[apps-caraga]

To get only the records owned by a user, the relevant table e.g. "Articles" should have a foreign key column containing the user id.

Sample schema

1. Articles table (Sample table with users per row)

id	subject	content	date_created	fk_owner_id
1	Welcome	Welcome to Php	9/5/2022	1
2	Sample	consectetur adipiscing elit, sed do eiusmod tempor incididunt	9/7/2022	2
3	Another Sample	Lorem ipsum dolor sit amet,	9/9/2022	2

Here, the fk_owner_id is the id from the users table.

2. Users table

id	username	password	fk_role_id
1	neo	**********	1
2	smith	**********	2

Note: For this schema, the user is not yet activated if its fk_role_id is null or zero.

3. Roles table

role_id	rolename	can_insert	can_update	can_delete	is_admin
1	admin	1	1	1	1
2	encoder	1	0	0	0

You can create a view joining the 2 tables as

CREATE VIEW valid_users AS
SELECT id,username,password,
fk_role_id, can_insert,can_update,can_delete,is_admin
FROM users
LEFT JOIN roles ON users.fk_role_id = roles.role_id
WHERE fk_role_id > 0;

then set the 'dbAuth.loginTable'=>valid_users.

Successful login will return these selected columns, excluding the password field. These data is accessible via $_SESSION ['user']['columnName']

You can then add the recordHandler to filter only the articles owned by the currently logged-in user.

'authorization.recordHandler'=>function($operaton,$tableName){
		 	 if($tableName === 'articles')
				return ($tableName == 'articles') ? 'filter=fk_owner_id,eq,'.$_SESSION['user']['user_id']: '';
			
		},
 

You may also adapt the 'authorization.columnHandler' to restrict the returned column,

[mevdschee]

Thank you @apps-caraga for helping @NorthFred. Great example.

[NorthFred]

@apps-caraga Thank you for the elaborate example! It gives a good idea on how it can be handled. I should have probably mentioned that my solution is using Firebase login to provide jwt auth tokens. So I don't have a 'login' table with username and password as such.

The users table only stores the user's userId and role (and some other information - but it is irrelevant for the solution I seek).

The Firebase auth token only contains the userId via $_SESSION['claims']['user_id']. Now I would need to find a way to look up the user's role from the 'users' table in order to limit the data access via the authorization.recordHandler.

[NorthFred]

@mevdschee @apps-caraga I am currently looking into getting rid of authentication via Firebase auth tokens and use the dbAuth middleware instead. That seems to support my needs better. I am running into a bunch of issues with dbAuth, but will create a new issue for that.

[apps-caraga]

So I just got a firebase auth working and yup, the $_SESSION['claims'] variable contains several pieces of user data. For our purpose, the user_id , and the email seems to be the most useful. In this case, it may be possible to adapt the sample Articles table so that the fk_owner_id is equal to the user_id or the email retrieved from the firebase access token.

'authorization.recordHandler'=>function($operaton,$tableName){
if($tableName === 'articles')
	return ($tableName == 'articles') ? 'filter=fk_owner_id,eq,'.$_SESSION['claims']['user_id']: ''; // or $_SESSION['claims']['email'] ;if using the email as the owner identifier
			
		},

[NorthFred]

@mevdschee I believe this issue can be closed. I moved the project away from Firebase auth and switched to dbAuth completely - and it seems to be working well so far. I'm only missing a password reset functionality, but that is a different story.

[mevdschee]

@NorthFred Thank you for reporting your success and @apps-caraga thank you for your great example!

[SMN947]

Just in case someone else looking for this where have firebase is a must, in my case I created a middleware using kreait/firebase-php to verify the jwt token.

As mentioned before, there must be a "users" table that will have the firebase user id in order to do the lookup, to keep it updated i made a custom user creation flow where after creating the user in firebase a call to custom endpoint is made in order to register the user in my local db.

    class SMN947Middleware extends Middleware
    {
        private $reflection;
        private $db;
        private $ordering;
        private $debug;
        private $firebase;
        private $auth;
        private $pathHandler;

        public function __construct(Router $router, Responder $responder, Config $config, string $middleware,ReflectionService $reflection, GenericDB $db)
        {
            parent::__construct($router, $responder, $config, $middleware);

            $this->reflection = $reflection;
            $this->db = $db;
            $this->ordering = new OrderingInfo();
            $this->debug = $config->getDebug();
            $firebase = (new Factory)->withServiceAccount('./path/to/your/firebase/file.json');
            $this->firebase = $firebase->createAuth();
            $auth = $firebase->createAuth();
            $this->auth = $auth;

            $this->pathHandler = new pathHandler($this->reflection, $this->db, $this->ordering, $this->auth, $this->responder);
        }

        private function getAuthorizationToken(ServerRequestInterface $request): string | null
        {
            $token = $request->getHeader('X-Authorization')[0] ?? null;
            if($token == null) {
                return null;
            }else{
                $parts = explode(' ', trim($token), 2);
                if (count($parts) != 2) {
                    return '';
                }
                if ($parts[0] != 'Bearer') {
                    return '';
                }
                return $parts[1];
            }
        }

        public function process(ServerRequestInterface $request, RequestHandlerInterface $next): ResponseInterface
        {
            $token = $this->getAuthorizationToken($request);

            if($token == null) {
                $response = $this->responder->error(ErrorCode::AUTHENTICATION_REQUIRED, '');
                return $response;
            }else{
                try {
                    $verifiedIdToken = $this->auth->verifyIdToken($token);
                    $uid = $verifiedIdToken->claims()->get('sub');
                    $user = $this->auth->getUser($uid);

                    $requestedPath = $request->getUri()->getPath();
                    
                    $authenticatedUser = $this->getAuthenticatedUser($uid);

                    if (count($authenticatedUser) < 1) {
                        return $this->responder->error(ErrorCode::AUTHENTICATION_FAILED, $user->email);
                    }

                    $result = array(
                        "status" => "OK",
                        "user" => array(
                            "uid" => $user->uid,
                            "email" => $user->email,
                            "displayName" => $authenticatedUser[0]["name"],
                            "phone" => $authenticatedUser[0]["phone"],
                            "rol" => $authenticatedUser[0]["rol"],
                            "storeId" => $authenticatedUser[0]["storeId"],
                            "vehicleId" => $authenticatedUser[0]["vehicleId"],
                            "token"=> $token
                        )
                    );

                    $response = ResponseFactory::fromStatus(ResponseFactory::OK);
                    $response = $response->withHeader('Content-Type', 'application/json; charset=utf-8');
                    $response = $response->withHeader('Content-Length', strlen(json_encode($result)));
                    $response->getBody()->write(json_encode($result));
                    $response = $this->responder->success($result);

                    return $response;

                } catch (FailedToVerifyToken $e) {
                    $response = $this->responder->error(ErrorCode::AUTHENTICATION_FAILED, $e->getMessage());   
                }
            }
            return $response;
        }
    }