Forum Stats

  • 3,814,069 Users
  • 2,258,814 Discussions
  • 7,892,523 Comments

Discussions

Pipelined Stored Procedure optimization

2

Answers

  • brilliant
    brilliant Member Posts: 14
    edited May 9, 2019 6:17AM

    Agreed. There is a warehouse where I am fetching the information from, however, the view and materialized view are currently extremely slow. View giving a 60 second per click response, while materialized view giving a 12 second response per click. I find it hard to understand why a stored procedure is against standard practice whereby a parameter is required to fetch any data thereby ensuring security. I see that VPD allows for the same but given the new age tools, limitations bring us down to having traditional methods of securing and analyzing data.

    Might you have a template of how this is accomplished using a procedure within a package using functions, table type and row type? I don't know what the performance will be but worth a try. Getting it close to 4-5 seconds or less per click is my target for the user experience.

    Here's another thought, how about I go with your VPD suggestion, does that mean, I can still call it through a procedure, which accepts the user id as a parameter since the connection is with a system account and the procedure calls the materialized view using the userid that was passed as parameter, sort of like impersonate a user session? Thereby improving my fetch time?

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited May 9, 2019 7:07AM
    brilliant wrote:Agreed. There is a warehouse where I am fetching the information from, however, the view and materialized view are currently extremely slow. View giving a 60 second per click response, while materialized view giving a 12 second response per click. I find it hard to understand why a stored procedure is against standard practice whereby a parameter is required to fetch any data thereby ensuring security. I see that VPD allows for the same but given the new age tools, limitations bring us down to having traditional methods of securing and analyzing data.Might you have a template of how this is accomplished using a procedure within a package using functions, table type and row type? I don't know what the performance will be but worth a try. Getting it close to 4-5 seconds or less per click is my target for the user experience.Here's another thought, how about I go with your VPD suggestion, does that mean, I can still call it through a procedure, which accepts the user id as a parameter since the connection is with a system account and the procedure calls the materialized view using the userid that was passed as parameter, sort of like impersonate a user session? Thereby improving my fetch time?

    Take a step back.

    Trace where the time is currently going for your 12 second response. Use extended sql trace and tkprof.

    SBJ
  • Dom Brooks
    Dom Brooks Member Posts: 5,558 Silver Crown
    edited May 9, 2019 7:28AM

    The super-sensitive nature of the data and the current approach to applying security do not sit well with each other.

    The chance of accidentally exposing the wrong data is currently very high.

    The data /underlying tables should be protected via VPD at least.

    Accessing the data via the system should cause an application context to be set which gives access to the appropriate data via the usual VPD methods.

    Accessing the data without this context being set should cause no data to be returned.

    Then whether the data is accessed directly via views or stored procs is neither here nor there.

    If you want to set the context by calling a proc which sets the context , that's up to you/ your architecture.

    L. FernigriniMustafa_KALAYCISBJ
  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,789 Silver Crown
    edited May 9, 2019 8:17AM

    That's how I usually do, call an SP that sets the context after checking whatever needs to be checked (sometimes even time, some data should not be accessed outside working hours).

    Once the context is set, just run your query on VIEWs that filter data by reading the context...

    Then unset the context for the session.

  • brilliant
    brilliant Member Posts: 14
    edited May 9, 2019 8:22AM

    might you have an example of this implementation. This is new to me. Or pointer to resources on how to build?

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,789 Silver Crown
    edited May 9, 2019 8:44AM

    I do not have examples tight now, but I can explain the idea.

    Context are like "variables" that can be accessed by and Oracle session. Here is some info ion how to create and set a context:

    https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/CREATE-CONTEXT.html#GUID-FDF62812-A884-479C-9C1B-5BD…

    https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/SYS_CONTEXT.html#GUID-B9934A5D-D97B-4E51-B01B-80C76A…

    The approach I would use is something like this

    Scenario: You need to query table Invoices, showing all the information from user A. You are user X.

    1) Create a Context (let say its called "InvoiceQuery") and a procedure in a package (let's call it PKG_SET_CONTEXT.PR_SET_INVOICEUSER) to set values in it. The procedure should accept at least 2 values

         a) Desired user to be queried

         b) User running the query

    And it should have all the logic to confim that the user running the query can view invoices from the user to be queried... If that logic is fullfilled, then the parameter "UserID" of the context "InvoiceQuery" is set to 'A'

    2) Create a view like this:

    CREATE OR REPLACE VIEW vw_InvoicesByUser

    AS

    SELECT i.InvoiceDate, i.Amount, i.UserID , i. .... ... ... ..

    FROM Invoices i

    WHERE i.UserID = SYS_CONTEXT ('InvoiceQuery', 'UserID');

    Then allow the app user to query the view and also call the SP that sets the context:

    GRANT SELECT ON vw_InvoicesByUser TO AppUser;

    GRANT EXECUTE ON PKG_SET_CONTEXT TO AppUser;

    Then you need to do something like

    PKG_SET_CONTEXT.PR_SET_INVOICEUSER ('A'.'X');

    SELECT * FROM Finance.vw_InvoicesByUser;

    PKG_SET_CONTEXT.PR_SET_INVOICEUSER (NULL.'X'); -- To reset the context

  • brilliant
    brilliant Member Posts: 14
    edited May 9, 2019 9:07AM

    This is great reference thanks and looks pretty secure!

    Does this mean I still need VPD for defining what A can see because X does not have access to SELECT from the invoices table. I am guessing yes, since that's where I would define where A see what values in the invoices table based on say Region or Category or whatever else

    Thoughts?

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,789 Silver Crown
    edited May 9, 2019 11:01AM

    Actually, this is a "cheap" way to create something similar to VPD.

    The user that connects to the DB to query the invoice data (AppUser in my example) should have only access to the package that allows to set the context values, and to the "filtered" views.

    The tables required to manage your users and how they access data must be unreachable to him.

    Let's say you want to see invoices based on the regions assigned to a particular user, then you will need a table like this:

    TABLE UsersByRegions ( UserID, RegionID, ISEnabled)

    Then your view would be

    CREATE OR REPLACE VIEW vw_InvoicesByUserRegion

    AS

    SELECT i.InvoiceDate, i.Amount, RegionID, i. .... ... ... ..

    FROM Invoices i

    JOIN UsersByRegions ur ON ur.RegionID = i.RegionID

    WHERE ur.UserID = SYS_CONTEXT ('InvoiceQuery', 'UserID');

    But again, your "AppUser" must have no access to the users, invoices nor UsersByRegions tables, he should just see the appropiate views, and they would take care of filtering ...

  • brilliant
    brilliant Member Posts: 14
    edited May 9, 2019 12:57PM

    but sounds like the View needs to be in a procedure whereby the userID for switching context needs to be sent across so that the system user is able to switch contexts between users.

    Questions:

    1. What's the performance impact of doing the context switching on a tables with millions of records with concurrency of say just 10 users

    2. What's the performance impact of context switching and resetting since I believe it will need to be done

    Thanks,

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,789 Silver Crown
    edited May 9, 2019 1:51PM

    I really do not fully understand what you are saying/asking, I believe you are mixing two different things associated to the "context" word.

    1) Views are not in a procedure.

    2) You just SELECT from a VIEW that filters the information. The filter is not a hardcoded value, the value is obtained by calling the SYS_CONTEXT function.

    3) In order to set a value into a context, you need to execute a stored procedure that does that. When you create a context, you must tell which package contains (or will contain) the procedure(s) that can modify that particular context.

    4) You can control which Oracle user can execute that package.

    This CONTEXT feature has not to be confused with context switching, that usually means switching from SQL language/engine  to PL/SQL language/engine.

    When you query the view, it is a SQL sentence that will just execute the SYS_CONTEXT function only once and then use that value to execute the query, no matter how many rows exist on the table. The performance will be the same as usign a bind variable inside a PL/SQL block or using a hardcoded value.

    Imagine the call to SYS_CONTEXT() as a variable,a placeholder. The benefit is that in Views there are no variables, but you can use Contexts to mimic the behavior.