4

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

0

1 Answer 1

5

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:

1
  • Many thanks, I am just creating the views, so all multi-portal tables are represented as views instead tables. However I have a big app done the above way, so I cant use multiple tables, because it's way too many, so I thought to use session data in isolated transaction and no auto-commit Commented Jul 19, 2012 at 6:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.