flavours-without-borders/Database implementation/users/users.sql

DROP USER IF EXISTS 'flavoursSimpleUser'@'localhost';
DROP USER IF EXISTS 'flavoursSimpleUser'@'%';
DROP USER IF EXISTS 'flavoursModerator'@'localhost';
DROP USER IF EXISTS 'flavoursModerator'@'%';
DROP USER IF EXISTS 'flavoursOwner'@'localhost';
DROP USER IF EXISTS 'flavoursOwner'@'%';
FLUSH PRIVILEGES;

# Simple users of the application (aka customers)
CREATE USER 'flavoursSimpleUser'@'localhost' IDENTIFIED BY 'flavoursSimpleUserPasswd';
CREATE USER 'flavoursSimpleUser'@'%' IDENTIFIED BY 'flavoursSimpleUserPasswd';

# Customers should not be able to access the columns that indicate whether a restaurant/food/drink/diet is approved or not
# we need to grant column-based privileges on these tables
GRANT SELECT, INSERT, UPDATE (`restaurant_id`, `restaurant_name`, `restaurant_category`, `restaurant_longitude`,
	`restaurant_latitude`, `restaurant_opening`, `restaurant_closing`)
    ON flavours_without_borders.restaurant TO 'flavoursSimpleUser'@'localhost';
GRANT SELECT, INSERT, UPDATE (`restaurant_id`, `restaurant_name`, `restaurant_category`, `restaurant_longitude`,
	`restaurant_latitude`, `restaurant_opening`, `restaurant_closing`)
    ON flavours_without_borders.restaurant TO 'flavoursSimpleUser'@'%';

GRANT SELECT, INSERT, UPDATE (`food_id`, `food_name`, `food_description`, `food_calories`, `restaurant_id`)
	ON flavours_without_borders.food TO 'flavoursSimpleUser'@'localhost';
GRANT SELECT, INSERT, UPDATE (`food_id`, `food_name`, `food_description`, `food_calories`, `restaurant_id`)
	ON flavours_without_borders.food TO 'flavoursSimpleUser'@'%';

GRANT SELECT, INSERT, UPDATE (`drink_id`, `drink_name`, `drink_description`, `drink_has_alcohol`, `restaurant_id`)
	ON flavours_without_borders.drink TO 'flavoursSimpleUser'@'localhost';
GRANT SELECT, INSERT, UPDATE (`drink_id`, `drink_name`, `drink_description`, `drink_has_alcohol`, `restaurant_id`)
	ON flavours_without_borders.drink TO 'flavoursSimpleUser'@'%';

GRANT SELECT, INSERT, UPDATE (`diet_id`, `diet_name`, `diet_description`)
	ON flavours_without_borders.diet TO 'flavoursSimpleUser'@'localhost';
GRANT SELECT, INSERT, UPDATE (`diet_id`, `diet_name`, `diet_description`)
	ON flavours_without_borders.diet TO 'flavoursSimpleUser'@'%';

# Users can alter the rows of all other tables, except those implementing the role based access system
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`diet_prohibits_ingredient` TO 'flavoursSimpleUser'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`diet_prohibits_ingredient` TO 'flavoursSimpleUser'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`drink_has_ingredient` TO 'flavoursSimpleUser'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`drink_has_ingredient` TO 'flavoursSimpleUser'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`food_has_ingredient` TO 'flavoursSimpleUser'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`food_has_ingredient` TO 'flavoursSimpleUser'@'%';

GRANT SELECT, INSERT ON `flavours_without_borders`.`ingredient` TO 'flavoursSimpleUser'@'localhost';
GRANT SELECT, INSERT ON `flavours_without_borders`.`ingredient` TO 'flavoursSimpleUser'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user` TO 'flavoursSimpleUser'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user` TO 'flavoursSimpleUser'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_follows_diet` TO 'flavoursSimpleUser'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_follows_diet` TO 'flavoursSimpleUser'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_prohibits_ingredient` TO 'flavoursSimpleUser'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_prohibits_ingredient` TO 'flavoursSimpleUser'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_rates_drink` TO 'flavoursSimpleUser'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_rates_drink` TO 'flavoursSimpleUser'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_rates_food` TO 'flavoursSimpleUser'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_rates_food` TO 'flavoursSimpleUser'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_rates_restaurant` TO 'flavoursSimpleUser'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_rates_restaurant` TO 'flavoursSimpleUser'@'%';

# Moderators
CREATE USER 'flavoursModerator'@'localhost' IDENTIFIED BY 'flavoursModeratorPasswd';
CREATE USER 'flavoursModerator'@'%' IDENTIFIED BY 'flavoursModeratorPasswd';

# Mods have elevated privileges for all other tables, except those implementing the role based access system
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`diet` TO 'flavoursModerator'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`diet` TO 'flavoursModerator'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`diet_prohibits_ingredient` TO 'flavoursModerator'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`diet_prohibits_ingredient` TO 'flavoursModerator'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`drink` TO 'flavoursModerator'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`drink` TO 'flavoursModerator'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`drink_has_ingredient` TO 'flavoursModerator'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`drink_has_ingredient` TO 'flavoursModerator'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`food` TO 'flavoursModerator'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`food` TO 'flavoursModerator'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`food_has_ingredient` TO 'flavoursModerator'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`food_has_ingredient` TO 'flavoursModerator'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`ingredient` TO 'flavoursModerator'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`ingredient` TO 'flavoursModerator'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`restaurant` TO 'flavoursModerator'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`restaurant` TO 'flavoursModerator'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user` TO 'flavoursModerator'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user` TO 'flavoursModerator'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_follows_diet` TO 'flavoursModerator'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_follows_diet` TO 'flavoursModerator'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_prohibits_ingredient` TO 'flavoursModerator'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_prohibits_ingredient` TO 'flavoursModerator'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_rates_drink` TO 'flavoursModerator'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_rates_drink` TO 'flavoursModerator'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_rates_food` TO 'flavoursModerator'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_rates_food` TO 'flavoursModerator'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_rates_restaurant` TO 'flavoursModerator'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_rates_restaurant` TO 'flavoursModerator'@'%';

# Owners of restaurants or other stuff (cooks etc.)
CREATE USER 'flavoursOwner'@'localhost' IDENTIFIED BY 'flavoursOwnerPasswd';
CREATE USER 'flavoursOwner'@'%' IDENTIFIED BY 'flavoursOwnerPasswd';

# Owners should not be able to access the columns that indicate whether a restaurant/diet is approved or not
# we need to grant column-based privileges on these tables
GRANT SELECT, INSERT, UPDATE (`restaurant_id`, `restaurant_name`, `restaurant_category`, `restaurant_longitude`,
	`restaurant_latitude`, `restaurant_opening`, `restaurant_closing`)
    ON flavours_without_borders.restaurant TO 'flavoursOwner'@'localhost';
GRANT SELECT, INSERT, UPDATE (`restaurant_id`, `restaurant_name`, `restaurant_category`, `restaurant_longitude`,
	`restaurant_latitude`, `restaurant_opening`, `restaurant_closing`)
    ON flavours_without_borders.restaurant TO 'flavoursOwner'@'%';

GRANT SELECT, INSERT, UPDATE (`diet_id`, `diet_name`, `diet_description`)
	ON flavours_without_borders.diet TO 'flavoursOwner'@'localhost';
GRANT SELECT, INSERT, UPDATE (`diet_id`, `diet_name`, `diet_description`)
	ON flavours_without_borders.diet TO 'flavoursOwner'@'%';

# Owners can alter the rows of all other tables, except those implementing the role based access system
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`diet_prohibits_ingredient` TO 'flavoursOwner'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`diet_prohibits_ingredient` TO 'flavoursOwner'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`drink_has_ingredient` TO 'flavoursOwner'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`drink_has_ingredient` TO 'flavoursOwner'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`food_has_ingredient` TO 'flavoursOwner'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`food_has_ingredient` TO 'flavoursOwner'@'%';

GRANT SELECT, INSERT ON `flavours_without_borders`.`ingredient` TO 'flavoursOwner'@'localhost';
GRANT SELECT, INSERT ON `flavours_without_borders`.`ingredient` TO 'flavoursOwner'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user` TO 'flavoursOwner'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user` TO 'flavoursOwner'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_follows_diet` TO 'flavoursOwner'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_follows_diet` TO 'flavoursOwner'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_prohibits_ingredient` TO 'flavoursOwner'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_prohibits_ingredient` TO 'flavoursOwner'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_rates_drink` TO 'flavoursOwner'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_rates_drink` TO 'flavoursOwner'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_rates_food` TO 'flavoursOwner'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_rates_food` TO 'flavoursOwner'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_rates_restaurant` TO 'flavoursOwner'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `flavours_without_borders`.`user_rates_restaurant` TO 'flavoursOwner'@'%';

# They can however view and alter the columns that indicate whether a food/drink is approved or not!