vendor_many to one managing multiple apex applications

8/12/2019 Vendor_Many to One Managing Multiple APEX Applications

1/94

10thAnniversary 1999 - 2009

Many-to -One: Managing

Mult ip le APEX Appl icationsScott Spendolini, Sumner Technologies


2/94

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 thesession

Thank you for your cooperat ion !


3/94

3

About Me

Scott Spendolini

[email protected]

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
mailto:[email protected]:[email protected]


4/94

4

Agenda

Overview APEX Components

Database Objects

The Framework

Demonstration

Summary


5/94

5

Overview


6/94


7/947

Common Early APEX Adoption Issues

Multiple user accounts for the same person

Some use APEX credentials, some use LDAP, othersmay use something else

No single point of account management

Because of the scattered nature of user accounts, it isdifficult - 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/948

The Solution

Develop and implement a centralizedFramework which manages:

Application Definitions

Roles Users

User to Role Mappings

Other Components Themes/Templates

Common Regions

Navigation Bar Entries


9/949

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 aspossible

Easy to integrate

New Development

Existing Applications


10/9410

Framework Components

The Framework can also incorporate a numberof other components useful for building multipleAPEX applications

Themes/Templates

Associated Images & Cascading Style Sheets


Lists of Values Shortcuts


11/9411

Less is More

Most importantly, the framework should also be

easy for developers to useand extendas

well as transparent to your users


12/9412

APEX Components


13/9413

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/9414

APEX Components

Shared Components

Authentication Schemes

Authorization Schemes


Templates & Themes

Page Zero

APEX View

APEX_APPLICATIONS

Application Items & APEX_UTIL API

APEX_UTIL.FETCH_APP_ITEM


15/9415

Shared Components


16/9416

Shared Components

APEX components that can be shared:

Within a single application

In some cases, within multiple applicationswithin asingle workspace via Subscriptions

Little known, less publicized underrated feature ofAPEX

Subscriptionsare the cornerstone of theFramework


17/9417

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

centralizedand easily synchronizedamongstmultiple applications


18/94

18

Subscriptions

Subscriptions work only withina single

APEX Workspace

Application IDsmust be preserved when moving theframework from one instance of APEX to another

Otherwise, all links will be broken

But the applications will still work


19/94

19

Subscribe-able Shared Components


20/94

20


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/94

21


The Framework uses a CustomAuthenticationScheme

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 doesnt care HOWyou 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/94

22


What do you have access to?

Can be associated with almost every APEXComponent

Application

Page

Region

Item Report Column

When scheme evaluates to TRUE, item renders or

process executes


23/94

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/94

24


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/94

25

Themes & Templates

Themesare collections of Templates

Templatesmake 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 consistencyamong your developers,

causing your applications to look similar regardless of

who developed them


26/94

26

Importance of Good Design

Good design helps to convey credibility

If you spend time on the design, then surely you alsospent time on making the application work well

Poor design leaves users wondering what othercorners 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/94

27

Page Zero

Page Zerois a special page

Only contains Page Rendering UI components(Regions, Buttons & Items)

Does not include Computations or Processes

Items on Page Zerodisplay on ALLpages inAPEX unless conditionally restricted to do

otherwise


28/94

28

Page Zero

Common Uses:

Breadcrumb Regions

Lists

Common Regions/Reports JavaScriptLibraries


29/94

29

Page Zero


30/94

30

APEX Views

Set of pre-created views which provide access tothe 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 ownapplications

Reuse APEX metadata to supplement your application'sdata

Use to render a list of Applications and their properties

rather than maintaining your own parallel list


31/94

31

APEX_UTIL API

Application Itemscannot technically besubscribed to from other applications

However, you can determine the value of anyAPEX 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/94

32

Database Objects


33/94

33

Database Objects

Application Definitions, Users, Rolesand RoleAssignmentsare all managed in a set of tables

Could use LDAP to do the same and retrofit into the frameworkrelatively easily

Schema Objects consist of:

1 Context

4 Tables

8 Triggers

2 Views

1 Package

4 Functions & 2 Procedures


34/94

34

ER Diagram

ST_ROLE_USERS

ST_ROLES

ST_USERS

ST_APPLICATIONS


35/94

35

ST_APPLICATIONS

Stores metadata about each application that is apart of the framework

Most data about an application will be derived fromthe APEX_APPLICATIONview

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/94

36

ST_USERS

Stores user information, such as USER_ID,USER_NAMEand hashed PASSWORD

Triggers will automatically hash the password andstore 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/94

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 activatedST_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/94

38

ST_ROLE_USERS

Intersect table that links Roles to UsersST_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/94

39

Packages

ST_FWK

PROCEDURE logout

PROCEDURE set_ctx

FUNCTION hash_pw FUNCTION auth_user

FUNCTION app_gatekeeper

FUNCTION role_member


40/94

40

Views

Two views that assist in simplifying theinteraction 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 atleast

one active role in


41/94

41

Context

st_fwk_ctx

Context created to store the G_USER_IDparameter


42/94

42

The Framework


43/94

43

Framework Applications

Four applications make up the core framework

Shared Components Master (999)

Will never be run, but its shared components areused by all other applications

Starter Application (998)

Will never be run, but used to clone all additionalapplications

Launchpad (1000)

Framework Access Control (1001)

Any number of child applications can be easily added

to the Framework


44/94


45/94

45

Shared Components Master - App 999

Sole purpose is to store all SharedComponentsthat will be subscribed to by all

other applications

There are no pagesin this application, since no end

user should ever need to (or be able to) login to it

Any and all changes/additionsto thesubscribed shared components should be

done hereand published/subscribedto eachsubscriber

Most changes will be done to the templates


46/94

46

Shared Components Master Contents

Authentication Scheme

ST Child Authentication

Authorization Scheme

Application Gatekeeper Navigation Bar Entries

Home

Logout

Themes/Templates

SumnerTheme


47/94

47


ST Child Authentication

Acts as a pointer to the Launchpadapplication

Allauthentication occurs onlyat the Launchpad

Session Not Valid URL f?p=LAUNCHPAD:101

Cookie Name

ST Logout URL

f?p=&G_LAUNCHPAD_APP_ID.:102:&SESSION.


48/94

48


Application Gatekeeper

Checks to see if a specific user has at least one activerole 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,


49/94

49

ST_FWK.APP_GATEKEEPER

FUNCTION app_gatekeeper (p_app_id IN

NUMBER, p_app_user IN VARCHAR2)RETURNBOOLEANIS l_user_id

st_users.user_id%TYPE; l_countNUMBER;BEGINSELECT count(*) INTO l_count FROM

st_role_users_v WHERE application_id =

p_app_id;IF l_count > 0 THEN RETURN TRUE;ELSERETURN FALSE;END IF;

EXCEPTION WHEN NO_DATA_FOUND THEN RETURN

FALSE;END app_gatekeeper;


50/94

50


Home

Redirects to the home page of the Launchpad Application

URL Target:

n 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 URLfrom the current Authentication Scheme


51/94

51

Themes/Templates

SumnerTheme

Set of pre-built custom templates

Could be a built-in APEX theme/templates as well

Only a total of 26 templates are included inSumnerTheme as compared to about 70-80 for theAPEX built-in themes

Most templates in the built-in themes are not needed andcan be safely and easily removed

Additional templates can be addedto thisapplication and published/subscribed as needed


52/94

52

Starter ApplicationApplication 998


53/94

53

Starter Application - App 998

The Starter Applicationwill have all of theShared Componentsubscriptions established

Thus, they are linked back to the SharedComponents Masterapplication

This application will be the starting point for allNEWapplications that will be a part of your suite

No longer need to use Create Application

Instead, start by Copyingthis application


54/94

54

Shared Component Subscriptions

Subscribe to and Associate the AuthorizationScheme Application Gatekeeperat the

Application Level

Subscribe to and make the AuthenticationScheme ST Child Authenticationcurrent anddelete all others

Subscribe to the Navigation Bar Entries Homeand Logoutand delete all others


55/94

55

Shared Component Subscriptions

Subscribe to each of the Templatesin the themeSumnerTheme

There is no easy way to do this

You must do each one individually

Best approach:

Get a nice cup of coffee/tea

Export the theme from the Subscription Master

Import it into the Starter Application

Edit each template in the Starter Applicationand subscribeit back to the corresponding one in the Subscription Master


56/94

56

Components

Page Zero

Pre-created Page Zero for items residing on multiple pages

My ApplicationsReport

Lists all applications a given user has access to

Pre-created Breadcrumbfor site navigation and placed it onPage Zero

Page One also has an entry pre-created in the

breadcrumb

No Login Page

Since all authentication will be done at the Launchpad, there

is no need to preserve the login page in the Starter


57/94

57

Components

Call to Set Security Context

Used to set both G_USER_ID&G_LAUNCHPAD_APP_ID

Called from Security Attributes of ApplicationProperties


58/94

58

Additional Components

Any additional non-subscribe-able sharedcomponent or Page Zero items that you want all

of your applications to have should be set up

here

Take the time to think this through, as its a lot easierto do it now verses when you have 20 applications up

and running


59/94

59

LaunchpadApplication 1000


60/94

60

Launchpad Application - App 1000

The Launchpadwill:

Provide centralized authentication servicesfor thesuite of applications

Any unauthenticated session will end up here

All loginswill occur on Page 101of this application

All logoutswill occur on Page 102of this application

Provide a home page that users will see should theyhave access to more than one application

Or automatically redirectthe user to a singleapplication, if that is all they have access to


61/94

61

Deep Linking

The Launchpad Application supports deeplinking

Linking to a specific APEX application & page,typically from a bookmark

Done in the Loginprocess on Page 101

Uses the APEX item FSP_AFTER_LOGIN_PAGE

Which is set automatically by the APEX engine


62/94

62

Login Process on Page 101

DECLARE l_count NUMBER; l_flow_page VARCHAR2(4000);BEGINIF

:FSP_AFTER_LOGIN_PAGE IS NULL THEN l_flow_page := :APP_ID ||

':1';ELSE -- Count the number of |s in the FSP_AFTER_LOGIN_URL item

SELECT INSTR(:FSP_AFTER_LOGIN_URL, '|', 1) INTO l_count FROM dual; IFl_count = 1 THEN -- Session ID is NOT included l_flow_page :=

REPLACE(SUBSTR(:FSP_AFTER_LOGIN_URL, 5),'|',':'); ELSE -- Session

ID is included l_flow_page :=

REPLACE(SUBSTR(SUBSTR(:FSP_AFTER_LOGIN_URL, 1,

INSTR(:FSP_AFTER_LOGIN_URL, '|',1,2)-1),5), '|', ':'); END IF;END

IF;-- Perform the loginwwv_flow_custom_auth_std.login( P_UNAME

=> :P101_USERNAME, P_PASSWORD => :P101_PASSWORD, P_SESSION_ID =>

v('APP_SESSION'), p_flow_page => l_flow_page);END;


63/94

63

Creating the Launchpad

The Launchpad will be unique in that it will bethe only application in the Framework that has a

login page

It will also have a different authentication scheme than

all other application in the framework

Additional changes can be made to page 1, as this isthe landing page for users who have access to more

than 1 application


64/94

64

Application Alias

Add the Application Alias LAUNCHPAD toApplication 1000

This way, we can refer to the LAUNCHPAD and notrely on the Application ID always being 1000


65/94

65

What is G_USER_ID?

Surrogate Key for the USERStable

Also an Application Itemin the Launchpad

Could have opted to use APP_USER, as that istypically a unique key

However, as people change their names, there wouldbe more maintenance involved in preserving auditing

records or role reports

Thus, the surrogate key will never change

Allowing for variance in APP_USER, should it bedesired


66/94

66

Setting G_USER_ID

Set via the Application Attribute VPD PL/SQLCall to Set Security Context

Not actually using VPD, but any code there isexecuted at the proper place to set the context for any

purpose

st_fwk.set_ctx (p_user_name => :AP


67/94

67

DBMS_SESSION.SET_CONTEXT

dbms_session.set_context( namespace

=> 'ST_FWK_CTX', attribute =>'G_USER_ID', value => l_user_id,

username => p_user_name, client_id

=> p_app_session);


68/94

68

G_USER_ID as a Context

More efficientto use a Context in WHEREclauses

Will only be evaluated oncefor X number of rows

v('G_USER_ID')will be evaluated once per row forX number of rows

Usage:WHERE user_id = SYS_CONTEXT('ST_FWK_CTX',

'G_USER_ID')


69/94

69

G_LAUNCHPAD_APP_ID

Also set with st_fwk.set_ctx

Refers to the Launchpad Application ID

Set as a variable to allow for a different ID to be usedif 1000 is not available


70/94

70


The Launchpad will have its own Authentication Scheme

ST Parent Authentication

Session Not Valid Page

101

Authentication Function

RETURN st_fwk.auth_user

Cookie Name

ST

Logout URL

wwv_flow_custom_auth_std.logout?p_this_flow=&APP_ID.&p_next_flow_page_sess=&G_LAUNCHPAD_APP_ID

.:1


71/94

71

Custom Authentication Function

Must have the following signature:

p_username VARCHAR2

p_password VARCHAR2

And it must return a BOOLEAN Can be used for more than just a custom table

that stores usernames & password

Multiple LDAP servers

Multiple authentication mechanisms based onusername

f


72/94

72

st_fwk.auth_user

FUNCTION auth_user (p_username

IN VARCHAR2, p_password IN

VARCHAR2)RETURN BOOLEAN AS l_password_hash

VARCHAR2(4000); l_stored_password_hash

VARCHAR2(4000); l_expires_on

DATE; l_countNUMBER;BEGINSELECT count(*) INTO l_count

FROM st_users

WHERE UPPER(user_name) =

UPPER(p_username);if l_count > 0 then SELECT

password, expires_on INTO

l_stored_password_hash, l_expires_on FROM

st_users WHERE user_name = p_username;

f k h


73/94

73

st_fwk.auth_user

IF l_expires_on > SYSDATE OR l_expires_on IS NULL THEN

l_password_hash := hash_pw(p_password); IF

l_password_hash = l_stored_password_hash THEN RETURN

TRUE; ELSE RETURN FALSE; END IF; ELSERETURN FALSE; END IF;ELSE RETURN FALSE;END IF;END

auth_user;

A th i ti S h


74/94

74

Authorization Scheme

The Launchpad application has no authorizationscheme associated with it

Users with no roles will simply get a message statingsuch and will not be able to login to any other

application

B h


75/94

75

Branches

Create a Before Header Branchon Page 1

Checks to see how many applications a user has accessto

Will branch directly to that applicationif the user only

has access to a single application

Otherwise, it will stay on Page 1and display theWelcome page, allowing the user to choose which

application to run

Possible Enhancements:

Remove this; always end up on Page 1

Allow the user to choose and save a Default Applicationto branch to and o there

L t P


76/94

76

Logout Page

Page 102 is the Framework Logout Page

Clears the context

Logs the users out of the Framework

More actions can occur here, is desired

-- Unset the contextdbms_session.clear_context( namespace

=> 'ST_FWK_CTX', client_id => v('APP_SESSION')); --

Process the logoutwwv_flow_custom_auth_std.logout(p_this_flow => v('G_LAUNCHPAD_APP_ID'),

p_next_flow_page_sess => v('G_LAUNCHPAD_APP_ID') || ':1');

F k Fl


77/94

77

Framework Flow

App 1000

Page

101Page 1

App 1002

11Pages

App 1003

11PagesAuthenticate

F k Fl


78/94

78

Framework Flow

App 1000

Page

101Page 1

App 1002

11Pages

App 1003

11PagesAuthenticate

F k Fl


79/94

79

Framework Flow

App 1000

Page

101Page 1

Authenticate

App 1002

11Pages

App 1003

11Pages


80/94

80

Framework

Access ControlApplication 1001

A C t l A li ti A 1001


81/94

81

Access Control Application - App 1001

Access to Framework Application is managed byan APEX Application

Mostly made up of out-of-the-box APEX components

Born from cloning the Starter Application

Subscriptions and Authentication/Authorization schemesare still in tact

Access to the Access Controlapplication ismanaged via the Access Control application

Thus, you will need to seed the first application, user, role& role mapping with SQL*Plus

Access Control O er ie


82/94

82

Access Control - Overview

8 Pages

One of which is Page Zero

4 Reports

Applications, Roles, Users, User Roles 4 Forms

Applications, Roles, Users, User Roles


83/94

83

Additional Applications



84/94

84


As new applications are needed, the StarterApplicationis cloned and used as a startingpoint

All subscriptions to the Shared Components Master

are preserved this way

Development can then begin on the clonedapplication as normal

Caution:

If a developer removes or alters the FrameworkAuthenticationor AuthorizationSchemes, things

will likely stop working

Retro fitting an Existing Application


85/94

85

Retro-fitting an Existing Application

Retro-fitting existing applications isjust as simple

Subscribe to the ST Child AuthenticationScheme

Make Current

Subscribe to the App GatekeeperAuthorization Scheme

Associate it at the application level

Subscribe to Home& LogoutNavigation Bar Entrires

Configure application via the FrameworkAccess Controlapplication

Add Application & Roles

Assign Users to Roles

Mapping Existing Authorization Schemes


86/94

86

Mapping Existing Authorization Schemes

Existing Authorization Schemes can be mappedto Roles in the Framework

Use the Member of Role: DemoexampleAuthorization Scheme as a model

PL/SQL Function Returning Boolean

Passing in a Role Keywill return TRUEif thecurrently signed on user is a member of the

associated role defined in the Framework Otherwise, it will return FALSE

RETURN st_fwk.role_member(

p_role_key => 'DEMO')

ST FWK ROLE MEMBER


87/94

87

ST_FWK.ROLE_MEMBER

FUNCTION role_member (p_role_key IN VARCHAR2,

p_app_id IN NUMBER DEFAULT

nv('APP_ID'))RETURN BOOLEANIS l_countNUMBER;BEGINSELECT count(*) INTO l_count FROM

st_role_users_v WHERE role_key = p_role_key AND

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 role_member;


88/94

88

Demonstration

Demonstration


89/94

89

Demonstration

Overview of the Access Control application

Creating a New Application

Integrating it Into the Framework

Changing a Template Pushing Changes to All Applications

Integrating the APEX Sample Application



Navigation Bar Entry

Practical Framework Applications


90/94

90

Practical Framework Applications

Manage Multiple Applications

Module-based application

Release and manage (and charge for) componentsindividually

Multiple code lines for multiple developers

Easier to release a subset of functionality

APEX does this

White-listed Subset of Functionality

Easier to secure a small application entirely than a smallportion of a large application


91/94

91

Summary

Summary


92/94

92

Summary

Consider implementing some sort of centralizedframework in your APEX environment

Sooner than Later

It will pay for itself by means of:

Centralized User & Role Management

Better auditing capabilities

Flexibility to adapt to both new and existing APEXinvestments

Download Files


93/94

93

Download Files

http://sumnertechnologies.com/framework
http://sumnertechnologies.com/extreme_makeover.htmlhttp://sumnertechnologies.com/extreme_makeover.html


94/94

http://sumnertechnologies.com
http://sumnertechnologies.com/http://sumnertechnologies.com/

vendor_many to one managing multiple apex applications

Documents