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.

In my database i store data of different users (e.g. addresses or invoices) in the corresponding tables. I want to make sure that a logged in user only has access to it's own data in the tables (so that the user cannot read e.g. an address of an other user).

Currently this is done in the application accessing the mysql server.

Because the application will be split into multiple independent parts, written in different languages, I'm looking for a solution that is closer to the database, otherwise i need to make sure that the access rules are equal in every application.

While I also have the alternative of a middleware on my list, I'm looking for a in database solution in the first place.

Currently I already have a structure running in a test environment (It is a shorted version for illustrating). But I would like to know if there is a better solution to achieve this.

I already know that that a VIEW with the algorithm MERGE is limited in what can be in the query and which JOINs can be done in the VIEW to keep the VIEW in a state that INSERT and UPDATE queries are still available.

Tables

CREATE TABLE IF NOT EXISTS `User` (
  `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mysqluser` varchar(120) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `_Address` (
  `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `owner` int(10) unsigned NOT NULL,
  PRIMARY KEY (`_id`),
  KEY `owner` (`owner`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

CREATE TABLE IF NOT EXISTS `_Invoice` (
  `_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `owner` int(10) unsigned NOT NULL,
  `address` int(10) unsigned NOT NULL,
  PRIMARY KEY (`_id`),
  KEY `owner` (`owner`),
  KEY `address` (`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Views

CREATE ALGORITHM=MERGE DEFINER=`cm_root`@`localhost` SQL SECURITY DEFINER VIEW `Address` AS 
SELECT `a`.`_id` AS `_id` 
     FROM `_Address` `a` JOIN `User` `u` ON (`a`.`owner` = `u`.`_id`)
     WHERE (`u`.`mysqluser` = substring_index(user(),'@',1));

CREATE ALGORITHM=MERGE DEFINER=`cm_root`@`localhost` SQL SECURITY DEFINER VIEW `Invoice` AS 
SELECT `a`.`_id` AS `_id`,`a`.`address` AS `address`
    FROM `_Invoice` `a` JOIN `User` `u` ON (`a`.`owner` = `u`.`_id`)
    WHERE (`u`.`mysqluser` = substring_index(user(),'@',1));

Constraints

ALTER TABLE `_Address`
  ADD CONSTRAINT `_owner_address_fk` FOREIGN KEY (`owner`) REFERENCES `User` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE;


ALTER TABLE `_Invoice`
  ADD CONSTRAINT `_owner_invoice_fk` FOREIGN KEY (`owner`) REFERENCES `User` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `_address_fk` FOREIGN KEY (`address`) REFERENCES `_Address` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE;
share|improve this question
1  
Out of curiosity, what is the advantage of calling some tables _something? Unfortunately I don't know too much about MySQL stored procedures, but in PostgreSQL they could be the appropriate solution for you. –  dezso Aug 23 '13 at 16:27
    
@dezson Just because the current application code uses something to access the data. So for testing I named the view something and the renamed the real table to _something (could also be something_data or whatever). I did this to check if the code would continue working without changing the existing queries. –  t.niese Aug 23 '13 at 16:30
    
1. Is SQL Server or Postgres an option? –  Neil McGuigan Dec 24 '13 at 7:04
    
2. Will users ever share data? –  Neil McGuigan Dec 24 '13 at 7:04
    
@NeilMcGuigan Ideally the users should also be able to share data. We are also willing to switch to another DBMS system. A closed source is not really an options. Postgres could be an option, currently we investigate NoSQL databases, what advantages/disadvantages the will have for our infrastructure. As a migration to a totally different storage system is always a little bit painful pain we are still not sure if we should invest in that. –  t.niese Dec 24 '13 at 12:49
add comment

1 Answer

MySQL views are a terrible solution to anything because they are VEERRYYY slow and also cause lots of issues during database backup and restores (due to @DEFINER keyword).

You store the id of the current user in the application session session and always use it as a filter for any queries that are executed since its a foreign key in each table.

share|improve this answer
    
I currently use the id in the queries in my application. As I said I will use the database with different applications (using different programming languages). To make sure that the access restrictions are equal, I want to move it away from the application code itself. I'm award that TEMPLATE algorithm is a bad choice, but from what i have read (not currently test) MEGRE looked like i would not have a noticeable slowdown compared to the unrolled query. Anyway thx for the feedback. –  t.niese Aug 25 '13 at 19:01
add comment

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.