Re: How to create a View that hides the tables behind

[Christian Esborg]

Hi Peter

Unfortunately this model is not working for me, as I would have to have a table for each role, duplicating data many times.

Only showing the Organization was just to keep things simple, and to illustrate how a role should work. In real life its a bit more complex.
To illustrate this I have just added one more field 'Priority' to the call table.

Call table
----------
From, to, Org, Priority
222, 211, 1, 1
777, 711, 2, 1


Now I define 3 roles
Role0 and grants this role permission to 'Org 1' calls
Role1 and grants this role permission to 'Org 2' calls
Role2 and grants this role permission to 'Priority 1 calls'

This would give the following tables, with duplicate data:

CallRole0 table (equivalent to callOrg1)
---------------
From, to, Org, Priority
222, 211, 1, 1

CallRole1 table (equivalent to callOrg2)
---------------
From, to, Org, Priority
777, 711, 2, 1

CallRole2 table
---------------
From, to, Org, Priority
222, 211, 1, 1
777, 711, 2, 1


I don't have that much disk space, and as 'Roles' are dynamically defined, data could explode in size.

I do not see a way through this with MySQL?!?
So I am thinking about writing a 'data provider' service, that my SQL clients can connect to, and then let this service handle the authentication + authorization, and the filtering of data.

BR Christian