10 th Anniversary 1999 - 2009

1. 10th Anniversary 1999 - 2009 Many-to-One: Managing Multiple APEX Applications Scott Spendolini, Sumner Technologies

2. General Announcements • Please turn off all cell phones/pagers • If you must leave the session early, please do so as discreetly as possible • Please avoid side conversations during the session Thank you for your cooperation!

3. About Me • Scott Spendolini • scott@sumnertechnologies.com • Ex-Oracle Employee of 10 years • Senior Product Manager for Oracle APEXfrom 2002 through 2005 • Founded Sumner Technologiesin October 2005 • Oracle Ace Director • Co-Author, Pro Oracle Application Express • “Scott” on OTN Forums

4. Agenda • Overview • APEX Components • Database Objects • The Framework • Demonstration • Summary

5. Overview

6. Has This Happened to You? • You’ve adopted APEX in your organization • Based on a pilot project of a application or two • It becomes successful. Wildly successful. • APEX applications are popping up all over the place, leaving useless spreadsheets and desktop databases in their wake • Others start to develop with APEX • And start to release their own applications • All of a sudden, your Access & Excel mess has simply moved from the client to the server

7. Common Early APEX Adoption Issues • Multiple user accounts for the same person • Some use APEX credentials, some use LDAP, others may use something else • No single point of account management • Because of the scattered nature of user accounts, it is difficult - if not impossible - to manage all accounts for a single user • No centralized role management • Impossible to tell which privileges a user has • Each application deals with role management in its own different way

8. The Solution • Develop and implement a centralized Framework which manages: • Application Definitions • Roles • Users • User to Role Mappings • Other Components • Themes/Templates • Common Regions • Navigation Bar Entries

9. Framework Components • The Framework should provide: • Single Sign On • Single Point of User & Role Management • Be extensible, yet simple • Take advantage of APEX components as much as possible • Easy to integrate • New Development • Existing Applications

10. Framework Components • The Framework can also incorporate a number of other components useful for building multiple APEX applications • Themes/Templates • Associated Images & Cascading Style Sheets • Navigation Bar Entries • Lists of Values • Shortcuts

11. Less is More • Most importantly, the framework should also be easy for developers to useand extend as well as transparent to your users

12. APEX Components

13. APEX Components • Most of what is required can be achieved with APEX components • Very little custom code • Which is almost 100% PL/SQL • Important to understand how the APEX components work before trying to grasp the solution as a whole

14. APEX Components • Shared Components • Authentication Schemes • Authorization Schemes • Navigation Bar Entries • Templates & Themes • Page Zero • APEX View • APEX_APPLICATIONS • Application Items & APEX_UTIL API • APEX_UTIL.FETCH_APP_ITEM

15. Shared Components

16. Shared Components • APEX components that can be shared: • Within a single application • In some cases, within multiple applications within a single workspace via Subscriptions • Little known, less publicized underrated feature of APEX • Subscriptions are the cornerstone of the Framework

17. Subscriptions • Feature of APEX that allow you to “link” shared components from one application to another within a workspace • When changes are made to the “parent” component, they can be pushed (published) or pulled (refreshed) to/by the “child” component • Allows changes of Shared Components to be centralized and easily synchronized amongst multiple applications

18. Subscriptions • Subscriptions work only within a single APEX Workspace • Application IDs must be preserved when moving the framework from one instance of APEX to another • Otherwise, all links will be broken • But the applications will still work

19. “Subscribe-able” Shared Components

20. Authentication Schemes • APEX mechanism used to authenticate a user • APEX contains a number of built-in schemes: • LDAP • Oracle Single Sign On • APEX Credentials • Database • Open Door • Custom • None

21. Authentication Schemes • The Framework uses a CustomAuthentication Scheme • Stores usernames and hashed passwords in an Oracle table • Easiest to demonstrate • Does not require an additional server • APEX Authentication is typically a one-time event • APEX doesn’t care HOW you authenticate, just that you DO authenticate • Thus, it would be trivial to change the Authentication Scheme to LDAP, for instance • More robust approach for enterprise user management

22. Authorization Schemes • What do you have access to? • Can be associated with almost every APEX Component • Application • Page • Region • Item • Report Column • When scheme evaluates to TRUE, item renders or process executes

23. Authorization Scheme Types • Several different types • Exists/Not Exists SQL Query • Item is NULL/NOT NULL • Item Comparison • PL/SQL Function • Evaluation Point • Per Page View vs. Per Session

24. Navigation Bar Entries • Links that appear on almost every page • Typically used for common navigation control • Home • My Account • Login/Logout • Can link to either: • Page • URL

25. Themes & Templates • Themes are collections of Templates • Templates make up the UI of an application • APEX ships with 20 pre-built Themes • You can use one of them or make your own • Less is More • Recommend deleting 2/3 of the provided templates from any theme • Will enforce consistency among your developers, causing your applications to look similar regardless of who developed them

26. Importance of Good Design • Good design helps to convey credibility • If you spend time on the design, then surely you also spent time on making the application work well • Poor design leaves users wondering what other corners were cut • If the design is bad, the application must be worse! • Perception is reality, more often than not • Phishing sites strive to look like those they are mimicing

27. Page Zero • Page Zero is a special page • Only contains Page Rendering UI components (Regions, Buttons & Items) • Does not include Computations or Processes • Items on Page Zero display on ALL pages in APEX unless conditionally restricted to do otherwise

28. Page Zero • Common Uses: • Breadcrumb Regions • Lists • Common Regions/Reports • JavaScriptLibraries

29. Page Zero

30. APEX Views • Set of pre-created views which provide access to the APEX metadata • Utilities > APEX Views • List of all views and descriptions of their columns • Can also be accessed via SQL Developer • Views can be incorporated into your own applications • Reuse APEX metadata to supplement your application's data • Use to render a list of Applications and their properties rather than maintaining your own parallel list

31. APEX_UTIL API • Application Items cannot technically be subscribed to from other applications • However, you can determine the value of any APEX Application Item in any application in the same workspace by using the API: APEX_UTIL.FETCH_APP_ITEM • Not well documented, but definitely supported APEX_UTIL.FETCH_APP_ITEM( p_item IN VARCHAR2, p_app IN NUMBER DEFAULT NULL, p_session IN NUMBER DEFAULT NULL) RETURN VARCHAR2;

32. Database Objects

33. Database Objects • Application Definitions, Users, Roles and Role Assignments are all managed in a set of tables • Could use LDAP to do the same and retrofit into the framework relatively easily • Schema Objects consist of: • 1 Context • 4 Tables • 8 Triggers • 2 Views • 1 Package • 4 Functions & 2 Procedures

34. ER Diagram ST_ROLES ST_APPLICATIONS ST_USERS ST_ROLE_USERS

35. ST_APPLICATIONS • Stores metadata about each application that is a part of the framework • Most data about an application will be derived from the APEX_APPLICATION view ST_APPLICATIONS ------------------------------------------------------ APPLICATION_ID NOT NULL NUMBER ACTIVE_FLAG NOT NULL VARCHAR2(1) DESCRIPTION VARCHAR2(4000) CREATED_BY NUMBER CREATED_ON DATE UPDATED_BY NUMBER UPDATED_ON DATE

36. ST_USERS • Stores user information, such as USER_ID, USER_NAME and hashed PASSWORD • Triggers will automatically hash the password and store the hash, not the actual password ST_USERS ----------------------------------------------------- USER_ID NOT NULL NUMBER USER_NAME NOT NULL VARCHAR2(255) PASSWORD NOT NULL VARCHAR2(255) EXPIRES_ON DATE CREATED_BY NUMBER CREATED_ON DATE UPDATED_BY NUMBER UPDATED_ON DATE

37. ST_ROLES • Stores the roles for a given application • Roles are related via a parent-child relationship • Not used in this demo, but could be activated ST_ROLES ------------------------------------------------------ ROLE_ID NOT NULL NUMBER PARENT_ROLE_ID NUMBER APPLICATION_ID NOT NULL NUMBER ROLE_NAME NOT NULL VARCHAR2(255) ROLE_KEY NOT NULL VARCHAR2(255) DESCRIPTION VARCHAR2(4000) CREATED_BY NUMBER CREATED_ON DATE UPDATED_BY NUMBER UPDATED_ON DATE

38. ST_ROLE_USERS • Intersect table that links Roles to Users ST_ROLE_USERS ---------------------------------------------- ROLE_USER_ID NOT NULL NUMBER ROLE_ID NOT NULL NUMBER USER_ID NOT NULL NUMBER CREATED_BY NUMBER CREATED_ON DATE UPDATED_BY NUMBER UPDATED_ON DATE

39. Packages • ST_FWK • PROCEDURE logout • PROCEDURE set_ctx • FUNCTION hash_pw • FUNCTION auth_user • FUNCTION app_gatekeeper • FUNCTION role_member

40. Views • Two views that assist in simplifying the interaction with the data model • ST_ROLE_USERS_V • Lists all active roles for a the currently signed on user • ST_USER_APPLICATIONS_V • Lists all active applications that any user has at leastone active role in

41. Context • st_fwk_ctx • Context created to store the G_USER_ID parameter

42. The Framework

43. Framework Applications • Four applications make up the core framework • Shared Components Master (999) • Will never be run, but its shared components are used by all other applications • Starter Application (998) • Will never be run, but used to clone all additional applications • Launchpad (1000) • Framework Access Control (1001) • Any number of “child” applications can be easily added to the Framework

44. Shared Components Master • Application 999

45. Shared Components Master - App 999 • Sole purpose is to store all Shared Components that will be subscribed to by all other applications • There are no pages in this application, since no end user should ever need to (or be able to) login to it • Any and all changes/additions to the subscribed shared components should be done here and published/subscribed to each subscriber • Most changes will be done to the templates

46. Shared Components Master Contents • Authentication Scheme • ST Child Authentication • Authorization Scheme • Application Gatekeeper • Navigation Bar Entries • Home • Logout • Themes/Templates • SumnerTheme

47. Authentication Scheme • ST Child Authentication • Acts as a pointer to the Launchpad application • All authentication occurs only at the Launchpad • Session Not Valid URL • f?p=LAUNCHPAD:101 • Cookie Name • ST • Logout URL • f?p=&G_LAUNCHPAD_APP_ID.:102:&SESSION.

48. Authorization Schemes • Application Gatekeeper • Checks to see if a specific user has at least one active role for a specific application • If so, then the user can access the application • PL/SQL Function Returning BOOLEAN • Evaluates for Every Page View RETURN st_fwk.app_gatekeeper( p_app_id => :APP_ID, p_app_user => :APP_USER);

49. ST_FWK.APP_GATEKEEPER FUNCTION app_gatekeeper (p_app_id IN NUMBER, p_app_user IN VARCHAR2)RETURN BOOLEANIS l_user_id st_users.user_id%TYPE; l_count NUMBER;BEGINSELECT count(*) INTO l_count FROM st_role_users_v WHERE application_id = p_app_id;IF l_count > 0 THEN RETURN TRUE;ELSE RETURN FALSE;END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN FALSE;END app_gatekeeper;

50. Navigation Bar Entries • Home • Redirects to the home page of the Launchpad Application • URL Target: • f?p=ST:1:&APP_SESSION. • Logout • Logs out of the suite of applications • URL Target: • &LOGOUT_URL. • Which will be replaced with the value of Logout URL from the current Authentication Scheme