Article: Separated work with table data via web depending on connection (MySQL 5.x)

Jun 20, 2008

Abstract

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'.

Back to news archive