Oracle Database 12c: Real Application Security for Oracle Application Express

1. Oracle Database 12c: Real Application Security for Oracle Application Express Joel R. Kallman Director, Oracle Application Express Tanvir AhmedPrincipal Technical Staff, Database Security

2. The following is intended to outline Oracle’s general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

3. Brief Introduction Joel R. Kallman Director of Software Development, Database Server Technologies Division Responsible for all product management and software development of Oracle Application Express 17 year Oracle employee Actively developing Oracle Application Express since its creation in 1999

4. Brief Introduction Tanvir Ahmed, Ph.D. Principle Technical Staff, Database Security, Server Technologies Primary developer for Oracle Real Application Security 8 years at Oracle Ph.D. in CS from Univ. of Minnesota, Twin Cities Participant at IEEE/ACM as Track Chair, PC member, and reviewer 20 papers in areas related to system security

5. Program Agenda • Oracle Application Express (APEX) Security • Oracle Real Application Security (RAS) • RAS Integration with APEX • Demo: Human Resource (HR) Application

6. Oracle Application Express (APEX) Overview and Security

7. Oracle Application Express (Oracle APEX) • Architectural Overview • Simple 2-Tier Architecture • Pages dynamically rendered using database metadata • No code generation or file based compilation • Runs everywhere Oracle Database runs

8. Oracle Application Express (Oracle APEX) Rapid Browser Based Development Leverage SQL Skills Point your browser and start developing Build reports, forms and charts using SQL Self Service Provisioning Runs within Database Out-of-the-box elastic private cloud service Simple to manage, highly scalable

9. Oracle Application Express Security Users, Authorization, Data Security • Authentication schemes • LDAP, SSO, Database Credentials… • Authorization schemes • Page item, page, or application • User Groups • Data security using database features • Views or Virtual Private Databases(VPD) • Uses application context in filtering predicate

10. Oracle Application Express Security Access Control • Easy wizard to setup Access Control in an APEX application • Targeted for UI, not data

11. Oracle APEX Security Challenges on Data Access Control HR Application APEX Applications Custom Authorization • Code executes under privileged user, database unaware of end users • No native support for application privileges and application roles • Data access policy is hard coded and fragmented in views • VPD provides hooks for custom logic, adds ‘WHERE’ clause to SQL Executes as Privileged User

12. Oracle Real Application Security (RAS) End-to-end application security

13. Oracle Database Real Application Security A database authorization solution for end-to-end application security • Define, provision, and enforce application-level security policies at the database layer • Simplify administration using declarative security policies • Build upon experiences from authorization policies used in Oracle Applications

14. Typical Application Security with APEX Oracle Real Application Security CRM Application HR Application Business Logic Business Logic & Security & Security Identity & Policy Store Users and Roles Users and Roles Shared, Schema User DB Sessions Light-weight Sessions Security enforced on direct connections Direct, Uncontrolled Access Access Control Policy Users and Roles

15. Oracle Real Application Security Key Features • Support Application Users and Sessions • Schema-less user, Security and application context in DB • Support Application Privileges and Roles • E.g., ViewSalary, SubmitPO, ApprovePOprivileges • E.g., Manager, HR_Rep, Approver roles • Efficient database-enforced data access control • Support various application-centric data access patterns • Enforce security close to data

16. Application Security Requirements: HR - Employees can view public information - An employee can view own record, update contact - Manager can view salary of his reports

17. Employee My own My report Real Application Security Concepts Data Realms • A group of rows representing a business object • Employees in North America • My own employee record • All employees under my report • Assign privileges to columns • viewSSN for SSN column • viewSalaryfor Salary column All records viewSalary viewSSN

18. Real Application Security • Data Security Policy Components Application Role R E.g.,Manager Data Realm D E.g., Employees under my report Access Control List (ACL) Grant privilege P to role R Application Privilege P E.g.,ViewSalary • Each Data Realm has an associated ACL with grants • Data Security policy is a collection of Data Realms and ACLs

19. Demo: Oracle Real Application Security Human Resource (HR) Application

20. Oracle Real Application Security Uniform Authorization on All Access Paths Direct SQLPLUS connect as Nancy

21. Data Security Use Cases

22. Real Application Security • Required Features for Application Security

23. Integration with Oracle APEX Runtime Integration Page Request • Application users continue to be provisioned in the database or identity stores • User authentication remains in APEX • RAS session contains application user, its roles, and session context • Based on APEX user’s security context • Application code executes within RAS session • Attached and detached to a db session, being multiplexed APEX Session Attach RAS Session Application code Detach RAS Session Page Display

24. Integration with Oracle APEX Data Security and Authorization • Data Security policy • Application privileges, roles, and grants in ACL • Instead of custom authorization using VPD (Virtual Private Database) • For authorization schemes, use RAS ACL check operators

25. Oracle Real Application Security Benefits for APEX Developers • Stronger security • Enforced regardless of entry points: direct, APEX, or middleware • Audit end-user activity in database audit trail • Simpler development • Declarative policy, relieves writing authorization code • Native support for application roles, application privileges, application users • Optimized access control for typical data access patterns • Simpler administration • End-to-end uniform security across mid-tier and database • Centralized management of security policies

26. Oracle APEX & Oracle RAS • Fully supported no-cost feature of Oracle Database • APEX • Oracle Database EE, Oracle Database SE, Oracle Database SE One, Oracle Database XE • 10gR2 (10.2.0.4 +), 11gR1, 11gR2,12c • Exadata, ODA, RAC • RAS • Oracle Database EE 12c • RAS Integration with APEX • Oracle Database EE 12c PS1

27. Resources • Oracle Application Express and Oracle RAS • Oracle Application Express – http://apex.oracle.com • APEX at OOW – http://apex.oracle.com/oow • Demogrounds - MosconeSouth, Left SL-036 • Oracle RAS Developer Guide – http://www.oracle.com/pls/db121 • Demogrounds - Moscone South, # SL-051

28. Oracle Database Security Sessions

29. Oracle Database Security Sessions

30. Complimentary eBook Register Now www.mhprofessional.com/dbsecUse Code: db12c