I have a security model where user1 can see key1 and user2 can see both key.
Table1
| User | Key |
| User1 | 1 |
| User2 | 2 |
| User2 | 1 |
Table2
| User | ProjectType |
| User1 | A |
| User2 | A |
| User2 | B |
Now I need to apply another security on top of it, where user1 can see only project type A within Key1 where are user2 can see protype A & B with key1 & key2.
Please see the data below.
Table3
| Employee | Project Type | Key | Measure |
| 100 | A | 1 | 1 |
| 100 | B | 1 | 2 |
| 101 | A | 2 | 3 |
| 101 | B | 2 | 4 |
| 102 | A | 2 | 5 |
| 102 | B | 2 | 6 |
| 103 | A | 2 | 7 |
| 103 | B | 2 | 8 |
| 104 | A | 3 | 9 |
One simple way to achive this is
Solution:
Combining values in Table 1 and Table 2
Table1 & 2 -> Derived Table
| User | Key | NewKey |
| User1 | 1 | 1A |
| User2 | 2 | 2A |
| User2 | 2 | 2B |
| User2 | 1 | 1A |
| User2 | 1 | 1B |
Concatenation Project Type and Key as New Key.
Table3
| Employee | Project Type | Key | Measure | NewKey |
| 100 | A | 1 | 1 | 1A |
| 100 | B | 1 | 2 | 1B |
| 101 | A | 2 | 3 | 2A |
| 101 | B | 2 | 4 | 2B |
| 102 | A | 2 | 5 | 2A |
| 102 | B | 2 | 6 | 2B |
| 103 | A | 2 | 7 | 2A |
| 103 | B | 2 | 8 | 2B |
| 104 | A | 3 | 9 | 3A |
Using NewKey to link Derived table & Table 3.
Can some one please let me know if there is any other want to do it.
Thanks in advance.
