Topic : DATABASE MANAGEMENT SYSTEMS Question 2 (20 pts Implement the Virtual Pri
ID: 3838027 • Letter: T
Question
Topic : DATABASE MANAGEMENT SYSTEMS Question 2 (20 pts Implement the Virtual Private Database by doing the following step, so that each sale person can only see the sales transactions of their own (reference slides in W8-1 DB security.ppt). The assumption is that each sale person has his/her own DB login. After implementing, please do some test by execute select statement on the view that you created below and see whether different user see different data. 1. Create the table Sales VERI (you can define the primary key and the attributes for the table) to store all the sale transactions (you can use ctl upd user to control the ownership of each row) 2. Create a VIEW to display rows that belong only to the logged on user 3. Create a trigger so that when user insert record into the table, the attribute ctl upd user will be auto populated by logged on user nameExplanation / Answer
Create the table;
CREATE TABLE `test`.`Sales_VER1` ( `id` INT NOT NULL AUTO_INCREMENT , `item` VARCHAR(100) NOT NULL , `price` FLOAT(20) NOT NULL , `ctl_upd_user` VARCHAR(10) NOT NULL , `timestamp`TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`id`))
Create the view;
CREATE VIEW SaleForUser AS SELECT * FROM `sales_ver1` where ctl_upd_user=(SELECT CURRENT_USER());
Create the trigger:
DELIMITER $$
CREATE TRIGGER update_user_name
BEFORE INSERT ON Sales_VER1
FOR EACH ROW
BEGIN
UPDATE Sales_VER1
SET ctl_upd_user = (SELECT CURRENT_USER()) WHERE Sales_VER1.ID=OLD.ID;
END$$
DELIMITER ;