Per user permissions on fact table
2 posters
Page 1 of 1
Per user permissions on fact table
Hi,
I am trying to implement a web based data mart that will be used by multiple users. I would like to assign permissions for each row on my fact table. Each row will have to be readable by specific users of the application. The fact table will have billions of rows and somehow the idea of creating a bridge table with multiple rows of permissions for each row on the fact table does not see feasible to me.
Do you guys think there is a solution to this problem that can produce queries in reasonable speeds ? If not what other solution would you propose.
I am trying to implement a web based data mart that will be used by multiple users. I would like to assign permissions for each row on my fact table. Each row will have to be readable by specific users of the application. The fact table will have billions of rows and somehow the idea of creating a bridge table with multiple rows of permissions for each row on the fact table does not see feasible to me.
Do you guys think there is a solution to this problem that can produce queries in reasonable speeds ? If not what other solution would you propose.
traboukos- Posts : 2
Join date : 2010-06-27
Re: Per user permissions on fact table
Security filtering is usually based on dimensional attributes, such as department or some other value. Include appropriate attributes in a new or existing dimension that identifies what particular users can see and force filters on those attributes.
Re: Per user permissions on fact table
Thanks for the reply,
If I understand correctly what you are suggesting would work well for group permissions but I think it cannot work for my use case. What I am looking for is permissions per user for every row on the fact table so e.g row 1 belongs to user_1 and user_5, row 2 belongs to user_11 and user_125 and user_300 e.t.c
I will try to give a simple example of what I want to do with a simple table.
Say I have a simple fact table with the following columns
profit , category_id , transaction_id
suppose that transaction_id is an id relevant to the bussiness process that data was gathered from.
User 1 imports the following 5 transactions
profit | category_id | transaction_id
10 | 2 | AB_1000
12 | 3 | AB_1001
13 | 5 | AB_1002
15 | 6 | AB_1003
11 | 1 | AB_1004
User 2 imports the following 5 transactions
profit | category_id | transaction_id
10 | nil | AB_1000
12 | nil | AB_1001
13 | nil | AB_1002
15 | nil | AB_1003
11 | nil | AB_1004
As you can see user 2 imports the same actual transactions but he does not have access to the category_id from his point of view. What I would like to do would be to create just 5 rows in the fact table with all the information. User 2 should be able to sum(profit) but should not be able to sum(profit) where category_id is 5 or whatever. A solution to this problem could be to create 10 rows in the fact table and have an importer id so every user has access to his own imported data and only that. This apart from the fact of storing duplicate information denies the ability for a third "super user" to run reports on the whole data.
Usually there are going to be just a hand full of users that will have access to the category_id column for every row in the fact table but these users are going to be different for every row and very rarely the same.
I really think that my only solution is duplicate data or even separate database for every user. Given the fact that every user will import about 3-4 million transactions per year I don't think that a belongs to many type association for a every fact table row will be feasible.
If I understand correctly what you are suggesting would work well for group permissions but I think it cannot work for my use case. What I am looking for is permissions per user for every row on the fact table so e.g row 1 belongs to user_1 and user_5, row 2 belongs to user_11 and user_125 and user_300 e.t.c
I will try to give a simple example of what I want to do with a simple table.
Say I have a simple fact table with the following columns
profit , category_id , transaction_id
suppose that transaction_id is an id relevant to the bussiness process that data was gathered from.
User 1 imports the following 5 transactions
profit | category_id | transaction_id
10 | 2 | AB_1000
12 | 3 | AB_1001
13 | 5 | AB_1002
15 | 6 | AB_1003
11 | 1 | AB_1004
User 2 imports the following 5 transactions
profit | category_id | transaction_id
10 | nil | AB_1000
12 | nil | AB_1001
13 | nil | AB_1002
15 | nil | AB_1003
11 | nil | AB_1004
As you can see user 2 imports the same actual transactions but he does not have access to the category_id from his point of view. What I would like to do would be to create just 5 rows in the fact table with all the information. User 2 should be able to sum(profit) but should not be able to sum(profit) where category_id is 5 or whatever. A solution to this problem could be to create 10 rows in the fact table and have an importer id so every user has access to his own imported data and only that. This apart from the fact of storing duplicate information denies the ability for a third "super user" to run reports on the whole data.
Usually there are going to be just a hand full of users that will have access to the category_id column for every row in the fact table but these users are going to be different for every row and very rarely the same.
I really think that my only solution is duplicate data or even separate database for every user. Given the fact that every user will import about 3-4 million transactions per year I don't think that a belongs to many type association for a every fact table row will be feasible.
traboukos- Posts : 2
Join date : 2010-06-27
Re: Per user permissions on fact table
There are a variety of strategies, but for anything to work there needs to be a level of reasonableness in the requirements.
One approach is to use roles and assign users to roles. LDAP supports this and allows you to store values relating to a user that you can use to filter and/or hide values. Many BI tools support LDAP.
Bottom line is there needs to be a systemaic means of identifying ownership if ownership is a criteria for enforcing security. None of this is easy, but it is certainly better than giving each user their own database.
One approach is to use roles and assign users to roles. LDAP supports this and allows you to store values relating to a user that you can use to filter and/or hide values. Many BI tools support LDAP.
Bottom line is there needs to be a systemaic means of identifying ownership if ownership is a criteria for enforcing security. None of this is easy, but it is certainly better than giving each user their own database.
Similar topics
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» How to create fact table with measures derived from comparing two fact table rows
» Healthcare Hospital Stay fact table - transaction fact table - a row for In and a row for Out?
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» How to create fact table with measures derived from comparing two fact table rows
» Healthcare Hospital Stay fact table - transaction fact table - a row for In and a row for Out?
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum