Article: Separated work with table data via web depending on connection (MySQL 5.x)
Jun 20, 2008
Prev | Next |

Abstract
Let's consider a step-by-step creation of a web-page that allows a user to view and edit his own record only. |
Task
Suppose that you have a list of books owned by database users. There are users 'john' and 'mary' in the database. You need to organize their work (adding, editing, deleting) via web-page with their own books only.
Procedure
The books list is stored in a table with SQL definition as shown here:
CREATE TABLE `books` ( `id` int AUTO_INCREMENT NOT NULL, `title` varchar(80), `author` varchar(20), `cover` blob, `description` text, `owner` varchar(30), /* Keys */ PRIMARY KEY (`id`) ) ENGINE = InnoDB;
Picture 1. Table data in SQL Maestro for MySQL.
The following statement creates a view to separate the data representation by users:
CREATE VIEW `books_by_owner` ( `title`, `author`, `cover`, `descrition` ) AS select `testdb`.`books`.`title` AS `title`, `testdb`.`books`.`author` AS `author`, `testdb`.`books`.`cover` AS `cover`, `testdb`.`books`.`description` AS `descrition` from `testdb`.`books` where (`testdb`.`books`.`owner` = substring_index(current_user(),_utf8'@',1));
We've used substring_index() function to allow connections from different hosts.
To allow working with the view, grant to 'john' and 'mary' the following permissions:
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE `books_by_owner` TO 'john'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE `books_by_owner` TO 'mary'@'%';
Thus, the users can work with thier own data.
Picture 2. Viewing data as 'john'.
Picture 3. Viewing data as 'mary'.
On the next step you have to fix a newly added row to its owner. Create a trigger with the following definition.
DELIMITER | CREATE TRIGGER `trigger_on_insert` BEFORE INSERT ON `books` FOR EACH ROW BEGIN SET NEW.owner = SUBSTRING_INDEX(user(), '@', 1); END| DELIMITER ;
Create also a trigger to prevent a change of owner during of rows editing.
DELIMITER | CREATE TRIGGER `trigger_on_update` BEFORE UPDATE ON `books` FOR EACH ROW BEGIN SET NEW.owner = SUBSTRING_INDEX(user(), '@', 1); END| DELIMITER ;
This technique will guarantee that each user will be able to see only own records irrespective of software used.
Now start the creation of PHP script. We've used PHP Generator for MySQL for this purpose.
Picture 4. PHP script logged as 'john'.

Prev | Next |