This discussion is archived
4 Replies Latest reply: Jan 29, 2013 12:57 AM by fac586 RSS

Advanced authentication

NiharNarla Newbie
Currently Being Moderated
hi , I am using APex 4.2

I have around 20 IR reports in my application . Every report can be filtered based on FACILITY.

I want to know whether I can authenticate users within login screen itself to see filtered report based on their "Facility" privileges.

Thank you
Nihar Narla
  • 1. Re: Advanced authentication
    fac586 Guru
    Currently Being Moderated
    Nihar Narla wrote:
    hi , I am using APex 4.2

    I have around 20 IR reports in my application . Every report can be filtered based on FACILITY.

    I want to know whether I can authenticate users within login screen itself to see filtered report based on their "Facility" privileges.
    That is not authentication. Authentication determines the identity of the user. Authorization determines the operations that a user with that identity can perform. Access control determines the data that those operations can be performed on.

    What you are describing is therefore known as Fine Grained Access Control (FGAC), and is normally implemented using views or Oracle's Virtual Private Database (VPD) feature.

    If you're licensed to use an Enterprise Edition database and want to apply these restrictions across the entire application, or across multiple applications, then use Virtual Private Database (VPD). In APEX 4.2 you can set up/tear down the security context using the Initialization PL/SQL Code/Cleanup PL/SQL Code attributes. In earlier versions the set up attribute is termed Virtual Private Database (VPD), and there is no tear down option. (An out of date article on using VPD with APEX is also available.)

    Should you not be using EE, then you can roll your own VPD using parameterized views, again using the VPD security attribute to set the required application context with the user name. A simpler approach using a standard view where the user name is determined using <tt>v('APP_USER')</tt> is another possibility, but note that Oracle do not recommend using the <tt>v</tt> function in queries on large result sets for performance reasons.

    One of the benefits of using VPD over views is that with views you'd probably have to modify the SQL used in your 20 reports. Using VPD, the policies can be applied to the existing tables without any modification of existing queries.
  • 2. Re: Advanced authentication
    NiharNarla Newbie
    Currently Being Moderated
    Hi ,

    the approach which you suggested is an complicated and expensive solution.

    I want to know whether no alternate solution to achieve this based on login level filter?


    I mean when I will add users and assign them privileges to particular FACILITY. And when they login the desired filter should be applied to all the repports....



    Thank You
    Nihar Narla
  • 3. Re: Advanced authentication
    VC Guru
    Currently Being Moderated
    Nihar Narla wrote:
    Hi ,

    the approach which you suggested is an complicated and expensive solution.

    I want to know whether no alternate solution to achieve this based on login level filter?


    I mean when I will add users and assign them privileges to particular FACILITY. And when they login the desired filter should be applied to all the repports....
    Other option is: +(this is not easy to maintain because the where clause will go into all 20 individual sql's )+

    Create application item called USER_FACILITY and populate an appropriate value after login process. i.e. based on user privilege

    Amend your 20 IR sql queries to include a where clause similar to
    //make sure you use bind variable not v function
    where mytable.facility = :USER_FACILITY
  • 4. Re: Advanced authentication
    fac586 Guru
    Currently Being Moderated
    Nihar Narla wrote:
    Hi ,

    the approach which you suggested is an complicated and expensive solution.
    Two solutions were suggested. One was VPD, which I presume you consider expensive because it requires an Enterprise Edition license. You didn't indicate what database edition you were using (despite how to decrease font size in the List with Icon and Subcontext), nor specify any restrictions or limitations regarding acceptable solutions.

    The other proposal was to use views, which are freely available on any edition of Oracle.

    The cost and complexity of the solution is not wholly dependent on licensing. One of the benefits of the VPD approach is that it can be developed, deployed, and maintained independently of the application (and any other applications that use this data). How much time would you have to spend modifying the reports to use VC's application-based solution or views? You could have the VPD solution up and running in the time it takes to modify 5 of these reports. You put all of the access controls into VPD, then this aspect of the application is invisible thereafter. It just works. The security code is isolated in one layer, and never has to be repeated anywhere in application code.

    I used to work on a large APEX system that had been "designed", partially built, and partly deployed to production before my involvement. Despite running on EE it didn't use VPD, nor did it utilize APEX authorization schemes properly. There was FGAC and authorization code everywhere: report queries, LOVs, validations, processes, conditions...and it was badly designed, inefficient FGAC and authorization code. It made everything slow, buggy, and difficult to maintain. If you identified a security bug it took hours and hours to find and fix all of the manifestations. I realised in my first 3 days on the project that it should be using VPD and views, but the ubiquity of the existing security code, pressure to deliver additional modules, and continual fire-fighting of production bugs and performance problems meant that ripping it all out and implementing VPD was never going to be feasible. Two and a half years later (after the departure of all of the original developers) I had managed to implement a consistent view-based FGAC approach across the critical parts of the application, but some of the original, crazy security code will be there yet. Not using the appropriate features&mdash;VPD and APEX authorization schemes&mdash;when they were available meant that project overran it's costs and timescales multiple times over. Even if they hadn't been using EE originally, they could have bought the licenses and used VPD and still saved hundreds of thousands over the lifetime of the system.

    It's easy and cheap (in development costs) to include VPD or view-based FGAC at the start of the project. It's relatively easy and cheap to add VPD to a system that doesn't incorporate any FGAC in existing code. It's very difficult and very, very expensive to add proper VPD or view-based FGAC to an existing system that has inconsistent, complex, half-assed FGAC built into every query.
    I want to know whether no alternate solution to achieve this based on login level filter?
    What is a "login level filter"? This is not a standard APEX or Oracle term. You'll have to define that terminology.
    I mean when I will add users and assign them privileges to particular FACILITY. And when they login the desired filter should be applied to all the repports....
    And how do you add users and assign them privileges? What authentication scheme are you using?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points