Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.2K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 27 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 390 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1K Español
- 1.9K Japanese
- 230 Portuguese
Pipelined Stored Procedure optimization
Answers
-
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.
-
VPD is standard part of Enterprise license
-
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.)
-
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!!!