1 / 94

10 th Anniversary 1999 - 2009

10 th Anniversary 1999 - 2009. Many-to-One: Managing Multiple APEX Applications Scott Spendolini, Sumner Technologies. General Announcements. Please turn off all cell phones/pagers If you must leave the session early, please do so as discreetly as possible

brilliant
Télécharger la présentation

10 th Anniversary 1999 - 2009

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related