Restrict access to rows by usename 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;
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.