5 Security & 5 Performance techniques for Oracle APEX projects

#APEX5

5 Security & 5 Performance

Techniques for APEX projects

Scott Wesley

@swesley_perth

www.packtpub.com/content/oracle-apex-techniques/video

9,192, 631,770 times

http://hyperphysics.phy-astr.gsu.edu/hbase/acloc.html

1 metre

Security has layers

Conditions: Declarative vs Dynamic

Named Column (Row Template)

Any condition, anywhere

Timing is everything

Process point

https://apex.oracle.com/pls/apex/f?p=73000

Sum (1%ers)

t

* 50 = 1.5s

Alternative?

Dynamic PL/SQL Region

htp.tableopen()

...

if r_rec.some_value < 0.2 then

htp.tabledata()

else

...

Performance

Security

http://www.grassroots-oracle.com/2013/05/performance-of-apex-conditions.html

Process point

#APEX5

'After Authentication'

"The first time your authenticated session is used for an application"

Low Hanging Fruit

PL/SQL

Applicable to any form of PL/SQL

Plugins out-of-box

Modularise your applications - but log in once

Plug-ins tweaked

Menu App

Conditions

Post Authentication

App Alias : LOGIN

User interface -> Login URL

f?p=LOGIN

http://www.grassroots-oracle.com/2014/04/shared-authentication-across-multiple-apex-apps.html

select round(avg(execution_time),5) avg_time

,message

from apex_debug_messages

where message like '%sparkline%'

group by message;

CRM

Bookings

on new instance

imitates post-authentication

(<= 4.2)

PL/SQL Packages

Application Sharing

#APEX5

Declaratively link between apps

http://roelhartman.blogspot.nl/2014/08/apex-5-new-column-link-features.html

#APEX5

Workspace Authentication Scheme

details to come with release of documentation?

Multiple Menu Methods

Every application has one

Feature Management

upgrade from 3.2

Menus

Features by Role

so many options

encapsulate pl/sql

Authorisation Schemes

declarative conditions

+ links

+ buttons

+ items

+ regions

+ columns

+ rows

+ pages

+ processes

+ dynamic actions

Conditions

consider materialised view

especially if using apex_ views

and is rendered on every page

consider security clause

Plug-in Patterns

URL Tampering

exists (app/page/role)

http://www.grassroots-oracle.com/2013/05/css-pull-down-menu-using-apex-list.html

Conditions

Extremely flexible

Re-use your own wheel

component re-use patterns

For every Dynamic Action

CSS

XSS

that has a notification event

means rendering the same thing many times

Event: Change

Item: P0_SIGNAL

Users are factories for bad data

One render of plug-in, used many times

Not all performance gains come from the database

JAMES&lt;!--5

JAMES<!--5

p_code => 'ONE'' OR ''1''=''1'

Even users figure this out

Selectors

SQL Injection

doesn't need to be malicious to break your application

XSS

Access pages not allowed

garbage

writing dynamic sql

Access data not allowed

#id vs .class -> comparable to indexes

sql using 'Standard Report Column'

http://www.artzstudio.com/2009/04/jquery-performance-rules/

l_sql := 'SELECT description FROM open_tab WHERE code = ' ||

SYS.DBMS_ASSERT.ENQUOTE_LITERAL(p_code);

apex_item.text(2,ename)

https://apex.oracle.com/pls/apex/f?p=73000:6:25081683920960::NO::P6_EMPNO:3

or HTML Expression

DBMS_ASSERT

Images

<span title="#TOOLTIP#">#ITEM#</span>

Don't just secure menu

APEX_ESCAPE.HTML(column)

Binary vs Sprite vs Data URI

literals in JavaScript

jQuery

jsPerf.com

http://www.dotnetcurry.com/showarticle.aspx?ID=986

alert("You entered &P1_TEXT!JS");

http://stackoverflow.com/questions/648004/what-is-fastest-children-or-find-in-jquery

#APEX5

http://roelhartman.blogspot.nl/2014/09/apex-5-new-substitution-syntax-features.html

data-attribute

SQL Reports - build more into HTML to save AJAX

Application Process - all pages

Redirect if not allowed

That's better