Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from security.stackexchange.com Jul 20 '12 at 17:27

This question came from our site for Information security professionals.

1 Answer 1

up vote 4 down vote accepted

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:

share|improve this answer
    
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 –  Andrew Smith Jul 19 '12 at 6:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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