Per user permissions on fact table

View previous topic View next topic Go down

Per user permissions on fact table

Post  traboukos on Sun Jun 27, 2010 7:58 pm

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.

traboukos

Posts : 2
Join date : 2010-06-27

View user profile

Back to top Go down

Re: Per user permissions on fact table

Post  ngalemmo on Mon Jun 28, 2010 11:55 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Per user permissions on fact table

Post  traboukos on Mon Jun 28, 2010 2:56 pm

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.



traboukos

Posts : 2
Join date : 2010-06-27

View user profile

Back to top Go down

Re: Per user permissions on fact table

Post  ngalemmo on Mon Jun 28, 2010 3:53 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Per user permissions on fact table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum