I have following table:
Create table `webservice`( `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, `hostname` text NOT NULL, `portalid` int NOT NULL, `serviceid` int NOT NULL, `customerid` int, primary key (`id`) )
I have multiple customers accessing multiple portals and services using different FQDN but same DOCROOT and MySQL login. It is multi-tenant application.
Now I want every visitor, to be able view only the data to which he is authorized to. All these users are using single MySQL connection login.
On first connect, browser is requesting the FQDN, so it should grant permission to view the associated "portalid" only rows. So this way without knowing FQDN, it is impossible to get access to rows with different portalid.
When user logs in, he's views should be restricted to only records with his own customerid.
I dont know how to do it, but I think I could use session data in SQL, which would allow to fetch the required data. However I dont know how to setup this at the moment.
I have session like this: How to secure MySQL based web session data table?