How to restrict multi-tenant MySQL database?

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: https://security.stackexchange.com/questions/17318/how-to-secure-mysql-based-web-session-data-table

The simplest answer is probably: give each portal a different MySQL database, with a different login and password.

The next-simplest answer is: put each portal's stuff in a separate table. Review your code carefully to ensure that each portal can only access data in its own table.

If you don't want to change your database schema, the pragmatic answer is: When the code for your portal application sets up a MySQL connection, the first thing it should do is create a view. Define the view so it provides access to only the subset of the data associated with that particular portal. You can define the view using a SQL statement with a WHERE clause of the form WHERE portalid = xxx (replace xxx with the id of the currently running portal). e.g.,

CREATE VIEW webserviceview
AS
SELECT *
FROM webservice
WHERE portalid = xxx

(fill in xxx with the currently running portal). Now make sure that all of your code only accesses webserviceview and never accesses the webservice table directly.

When a user logs in, you can use the same idea to further restrict access. Create a view that has only the data for the currently logged-in user. e.g.,

CREATE VIEW currentuser
AS
SELECT *
FROM webserviceview
WHERE customerid = yyy

Then make sure that your code only accesses the currentuser table, never the webservice or webserviceview table.

If you want to read some research papers that take this sort of idea a bit further, check out the following papers:

-
Diesel: Applying Privilege Separation to Database Access, ASIACCS 2011.

-
CLAMP: Practical Prevention of Large-Scale Data Leaks, IEEE Security & Privacy 2009.

-
Nemesis: Preventing Authentication & Access Control Vulnerabilities in Web Applications, Usenix Security 2009.

StackExchange Database Administrators Q#21256, answer score: 5