Total views : 220
Performance Optimization of Row-Level-Data-Security for Transactional Business Intelligence Queries in Cloud and SaaS Business Applications
Objectives: To optimize row-level data security for Transactional Business Intelligence (TBI) SQL queries to reduce complexity and enable the back-end database to create better optimized execution plans that perform and scale well. Methods/Statistical Analysis: Benchmark experiments were conducted using Oracle RDBMS 11gR2 using representative SQL queries from Oracle’s Fusion CRM TBI Applications for five different users with multiple, varying roles and data access permissions. All four measures of SQL performance viz. SQL response time (RT), Input-Output (IO) Buffer Gets, Hard-Parse-Time and Shared Memory utilization were recorded with and without our proposed optimizations. The four performance measures were then compared to record actual improvements. Findings: The benchmark experiments established very promising results. We recorded repeatable, significant gains in not only the four measures of individual SQL performance but also at the database resources level. The proposed architecture enables the creation of a hand-shake mechanism between the application and security frameworks to optimize query and application performance. This is done by creating name-value pairs of roles and filter criteria and passing these from the middleware/application to the security framework at run time. The result is a pruned version of the final physical SQL, retaining only relevant RDSPs while removing ones not logically needed. Such optimized row-level data security makes TBI SQL queries less complex and the back-end database is able to create better optimized execution plans that perform and scale well. Query Response Time (RT) improvements ranging from 5% to 745 times, Hard-Parse Time improvements from 1% to 208 times, Logical I/O or Buffer Gets’ improvement ranging from 43% to 454 times and SQL-Shared-Memory reduction by up to 52%. Application/Improvements: Our proposed architecture is directly applicable to improve performance of all TBI applications that use row-level data security, especially in the Software-as-a-Service (SaaS) and Cloud Models.
Access Control and Database Security, Cloud and SaaS Applications, Row-Level Data Security, RBAC, SQL Query Performance, Transactional Business Intelligence.
- Ferraiolo DF, Kuhn DR. Role based Access Control' 15th National Computer Security Conf, USA. 1992. p. 554–63.
- Kuhn DR. Mutual Exclusion of roles as a means of implementing separation of duty in role-based access control systems'. Second ACM Workshop on Role-Based Access Control, Maryland. 1997; 1–8.
- Sandhu R, Ferraiolo D, Kuhn R. American National Standard for Information Technology – Role based Access Control. ANSI INCITS. 2004; 359:1–49.
- American National Standards Institute Standards for RBAC. Available from: http://www.incits.org/INCITS_Published_Standards.pdf, Date accessed: 4/03/ 2015.
- Chandramouli R, Sandhu R. Role based access control features in commercial database management systems'. 21st National Information Systems Security Conference, Crystal City, Virginia. 1998. p. 1–9.
- Chandramouli R. Business Process Driven Framework for defining an Access Control Service based on Roles and Rules'. 23rd National Information Systems Security Conference, Gaithersburg, 2000. p. 1–16.
- Ferraiolo DF, Kuhn DR, Chandramouli R. Role Based Access Control (book), Artech House, USA, 2007.
- Ferraiolo DF, Kuhn DR, Chandramouli R. Role Based Access Control (book), Artech House, 2nd edition, 2007.
- XACML OASIS Standard, XACML-V3.0, 2013. Available from: http://docs.oasis-open.org/xacml/3.0/xacml-3.0-core-spec-os-en.pdf, Date accessed: 24/ 03/ 2015.
- Sun Y, Wang Q, Li N, Bertino E, Atallah MJ. On the Complexity of Authorization in RBAC under Qualification and Security Constraints. IEEE Transactions on Dependable and Secure Computing. 2011; 8(6):883–97.
- Resource document, 'Oracle® Fusion Applications Security Guide 11g Release 1 (11.1.4)'. Available from: http://docs.oracle.com/cd/E28271_01/fusionapps.1111/e16689/F323388AN16D1F.htm Date accessed: 10/ 07/ 2015.
- Kohler M, Schaad A. ProActive Access Control for Business Process-Driven Environments. Computer Security Applications Conference, Annual, 2008. p. 153–62.
- Coyne E, Weil TR. ABAC and RBAC: Scalable, Flexible, and Auditable Access Management, IT Professional. 2013; 15(3):14–6.
- Li N, Byun J, Bertino E. A Critique of the ANSI Standard on Role-Based Access Control. IEEE Security and Privacy. 2007; 5(6):41–9.
- Chaudhary N, He L. Analyzing the performance impact of authorization constraints and optimizing the authorization methods for workflows, HIPC. 20th Annual International Conference on High Performance Computing, U K. 2013. p. 1–9.
- Resource document, 'Oracle® Fusion Applications Security Guide 11g Release 1 (11.1.4). Available from: http://docs.oracle.com/cd/E28271_01/fusionapps.1111/e16689/F323388AN16D1F.htm, Date accessed: 10 / 07/2015.
- Kohler M, Fies R. ProActive Caching - A Framework for Performance Optimized Access Control Evaluations', IEEE International Workshop on Policies for Distributed Systems and Networks, IEEE International Symposium on Policies for Distributed Systems and Networks, Germany. 2009. p. 92–4.
- Resource document, Oracle Business Intelligence OTBI Architecture. Available from: http://docs.oracle.com/cd/E51367_01/fa_lcm_gs/OASAD/otbi_trouble.htm#OASAD6512 Date accessed: 12/06/2015.
- Yaish H, Goyal M. Multi-tenant Database Access Control. IEEE 16th International Conference on Computational Science and Engineering. 2013. p. 870–7.
- Takabi H, Joshi JBD, Ahn G. Secure Cloud: Towards a Comprehensive Security Framework for Cloud Computing Environments. IEEE 38th International Computer Software and Applications Conference Workshops, USA. 2010. p. 393–8.
- Msahli M, Chen X, Serhrouchni A. Towards a Fine-Grained Access Control for Cloud, ICEBE. 2014, IEEE 11th International Conference on e-Business Engineering (ICEBE). 2014. p. 286–91.
- Resource document, Oracle® Fusion Transactional Business Intelligence. Available from: https://docs.oracle.com/cloud/farel8/common/OATBI.pdf, Date accessed: 4/ 06/ 2015.
- Resource document, Oracle Application Development Framework. Available from: http://www.oracle.com/technetwork/developer-tools/adf/overview/index.html, Date accessed:12/06/2015.
- Resource document, Oracle Business Intelligence Enterprise Edition product details. Available from: http://www.oracle.com/us/solutions/ent-performance-bi/enterprise-edition-066546.html
- Gandhi A. Literature Review on Impact of CRM, SRM, Information Sharing and Goal Congruence on Retail-SCM. Indian Journal of Science and Technology. 2016 Jun; 9(22):1–9.
- Leena NF, Jaykumar V, Issac SS. Assessing CRM Practices in Hotel Industry: A Look at the Progress and Prospects. Indian Journal of Science and Technology. 2015 Mar; 8(S6):1–9.
This work is licensed under a Creative Commons Attribution 3.0 License.