Nothing to do with Dolphin, (sorry)
I have a view I created for a database application I am working on. In this view, i want to restrict access to a specific table to specific users outside of the role base security I have already setup.
I have figured out how to create a view to limit access to one specific user but i want to be able to allow more than one user access. Here is the view script I have already.
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `access_property` AS
select
`property`.`Name` AS `name`,
`property`.`Address` AS `address`,
`property`.`City` AS `city`,
`property`.`Comments` AS `comments`
from
`property`
where
(`property`.`Assigned_User` = substring_index(user(), '@', 1))
With this setup I can keep everyone out except one specific user. It works ok but what if I have a Manager, a Board member and an assistant that I want to let have access to this Property (row)? Better yet, How would I develop a role based approach? Maybe using 3 tables like this?
User.id
User_Property.id
Property.id
I really suck at this. How would I write a view that uses the `User_Property.id` table instead allowing me to create access permissions for more than one user? I already know I can't 'select' more than one table in a view so I'm kinda lost. I've been trying to figure this out all afternoon. It's all way over my head (obviously, lol)
Thanks ahead of time....
