DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How to implement security on users queries?

  1. #1
    Join Date
    Mar 2002
    Posts
    303

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  3. #3
    Join Date
    Mar 2002
    Posts
    303
    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

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  5. #5
    Join Date
    Mar 2002
    Posts
    303
    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

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  7. #7
    Join Date
    Mar 2002
    Posts
    303
    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

  8. #8
    Join Date
    Mar 2002
    Posts
    303
    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
  •  


Click Here to Expand Forum to Full Width