Pipelined Stored Procedure optimization

[Deleted User]

brilliant wrote:This is great reference thanks and looks pretty secure!

Not as secure as VPD...

Reread Dom's reply #13...

Might you have a template of how this is accomplished using a procedure within a package using functions, table type and row type?

No. We used VPD, so we did not write any packages or functions, that wasn't necessary.

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.

Well, writing your own stuff won't be as fast, that's a given. Also be very careful with performance "goals per click", since that means you are involving a lot more than just the database. networks, clients, firewalls, routers etc all come in to play when you mention a "performance per click". It also really depends on what happens when the end user "clicks" (whatever that means): do you need 20 select statements to satisfy whatever the user wants, or does it take only 1? Is there some transport involved? Does it mean a download of data? Does it mean firing up extra .exe's, loading other dll's?

Like Andrew says (Reply #12): Take a step back. Figure out first where time is spent.

If this data is really that sensitive, then they can't complain spending money on protecting it, if money is an objection against VPD (otherwise they might as well just throw it on wikileaks right away....). So start reading this: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dbseg/index.html and use existing tools.

Dom Brooks

VPD is standard part of Enterprise license

brilliant

I think VPD is the route I will be taking but I have to call the query via a procedure that calls the view that has the VPD policy and context since the system user that is connecting from the the external system connects with Oracle using the system account. therefore calling the procedure gives me leverage to pass the logged on user's id to the procedure that then calls the view. I can't think of any other way to have ironclad security. The user experience depreciates if I use the userid of the logged on user to call the view directly as it prompts for password for every screen (annoying but true of the external system.)

L. Fernigrini

If you can afford (or already have) VPD that would be the first choice, no doubts about it.

I have used the context approach on some particular scenarios, dealing with specific places where filtering was required and not directly handled by the application. On those apps, all normal transactional activity was done through a PL/SQL API (calling procedures to do both DML operations and querying, thus the security validation was done directly on those SPs) but for some end-user reporting our customer used a third party reporting tool that required access to the data.

Obviously we did not grant direct access to the tables, we just produced a set of views that dynamically filtered sensitive data using context.

Hope this helps!!!