-
How to implement security on users queries?
Hi,
In one of our Oracle 9.2.0.8 DWH application, we have 5 fact tables.
I've to restrict some user queries when they are using (3 columns cust_id or cust_name or adress_name) in the query.
those columns exist in the 5 fact tables.
The user can manipulate datas from 1,2 or 5 fact tables in the same query.
The restriction is:
If select count(distint cust_id) or select count(distint cust_name) or select count(distint adress_name) <10 Then the query return no row
Else if select count(distint cust_id) or select count(distint cust_name) or select count(distint adress_name) >10 Then the query return rows for users.
Existing configuration:
- 400 users
- Users submit queries directly from Oracle and BusinessObject reporting tool.
How can I implement this staff on Oracle?
Best Regards
Bensmail Salah
-
I got the first part of your explanation but your "The restriction is" paragraph doesn't makes any sense to me so I'll address the first part.
For Business Objects queries just take out of the Universe the columns you don't want users to query.
For non-BO users you may want to hide your tables behind a view that does not includes the columns you don't want users to query.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Hi,
users can und must view results from the query (including cust_id,cust_name,adress_name) , if the following condition is valid:
select count(distint cust_id) or select count(distint cust_name) or select count(distint adress_name) >10 for the current query.
Otherwise not.
Taking out the objects (cust_id,cust_name,adress_name) from the universe is not a solution, because the above restriction must be verified.
Best Regards
Salah
-
Gotcha.
I see... I don't even want to know what kind of business requirement is driving such a condition.
You are in troubles.
I can see nothing in Oracle helping you to solve it; my guess is you will need to build some interface to interact with users, no way you can impose something like that to a guy having access to the table and access to sql*plus.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
The guy for me in this project implement a VPD (Virtual Private Database), because the configuration before allowed this kind of restriction; The data model contain only one fact table and 1 column concerned by this restriction.
It works fine till they changed the data model and spread this colum into many columns and in many fact tables.
Maybe someone of you worked in a similar problem.
Salah
-
VPD would solve row level privileges, not sure how it would help in your case.
Better for you to check what your predecesor had in place and just replicate the solution for each one of your tables.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
My predecesor created a VPD in one fact table for 1 column, it works fine.
But the trouble is; if I create a VPD for every fact table, when write a query containing a join between the fact tables , it return no rows if one of the subquery return no rows, but the hole query return rows (count >10).
Really I'm confused.
Any one has experiences in data restrictions using multiple VPD.
Salah
-
My predecesor created a VPD in one fact table for 1 column, it works fine.
But the trouble is; if I create a VPD for every fact table, when write a query containing a join between the fact tables , it return no rows if one of the subquery return no rows, but the hole query return rows (count >10).
Really I'm confused.
Any one has experiences in data restrictions using multiple VPD.
Salah
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|