Oracle Apex 4.1 Security

Topic List

truth

the

Sage Computing Services

• browser cache
• url tampering - entry level
• session state protection
• hidden items
• xss - little cleverer
• sql injection - takes skill
• apex_dml_lock_wait_time
• transparent database encryption

• database xxim - starts at design
• remote queries
• vpd
• encryption

• authentication schemes
• ldap
• SSO
• user groups
• security plugins
• custom auth
• authorization schemes

• apex_application_files
• session timeout
• internal settings
• authorized urls

• summary

has

Oracle Apex 4.1 Security

no agenda

Scott Wesley

Systems Consultant & Trainer

-- encryption

CREATE OR REPLACE FUNCTION sw_code(p_this IN VARCHAR2) RETURN RAW IS

v_length NUMBER;

v_sum VARCHAR2(32); -- hashed to 32

v_value VARCHAR2(100);

as0893j0f VARCHAR2(1) := '~';

f2no3ifn0a VARCHAR2(1) := 'o';

fn2oi3fm VARCHAR2(1) := '4';

s093mafm VARCHAR2(1) := 'A';

p23o02dm2 VARCHAR2(1) := '&';

n2oi3f8nc VARCHAR2(1) := 'g';

a01l2nlfi VARCHAR2(1) := 'w';

i92hf0xzz VARCHAR2(1) := '|';

q02n208n3 VARCHAR2(1) := '0';

-- This is not the actual key!

v_key_string CONSTANT VARCHAR2(8) := 'mR3s5fHq';

BEGIN

-- Get checksum of keyed value.

v_sum := dbms_obfuscation_toolkit.md5(input=>utl_raw.cast_to_raw(p_this));

v_value := -- Vary the substring length to get different amount of salt.

SUBSTR(v_sum,1,32)||

-- Second character of extra

SUBSTR(p_this, 2, 1)||

-- Last character of extra

SUBSTR(p_this, LENGTH(p_this), 1)||

-- More characters of key. Nbr used varies depending on amount of checksum used.

as0893j0f||fn2oi3fm||i92hf0xzz||n2oi3f8nc||as0893j0f||q02n208n3;

IF v_value IS NOT NULL THEN

RETURN utl_raw.cast_to_raw(v_value);

ELSE

RETURN NULL;

END IF;

END sw_code;

/

CREATE OR REPLACE FUNCTION sw_write(p_that VARCHAR2, p_this VARCHAR2) RETURN VARCHAR2 IS

v_string VARCHAR2(1024);

v_encrypted_string RAW(1024);

v_padding CONSTANT VARCHAR2(1) := CHR(0);

BEGIN

IF p_that IS NULL THEN

RETURN NULL;

ELSE

-- Pad it with up to 8 characters to ensure string to be encrypted is a multiple of 8 bytes long.

v_string := RPAD( p_that, CEIL(LENGTH(p_that)/8) *8, v_padding );

-- Encrypt and return the value.

dbms_obfuscation_toolkit.des3encrypt(input => utl_raw.cast_to_raw(v_string)

,key => sw_code(p_this)

,encrypted_data => v_encrypted_string

,which => dbms_obfuscation_toolkit.threekeymode

);

RETURN utl_raw.cast_to_varchar2(v_encrypted_string);

END IF;

END sw_write;

/

CREATE OR REPLACE FUNCTION sw_read(p_that VARCHAR2, p_this VARCHAR2) RETURN VARCHAR2 IS

v_string VARCHAR2(1024);

v_padding CONSTANT VARCHAR2(1) := CHR(0);

BEGIN

IF p_that IS NULL THEN

RETURN NULL;

ELSE

-- Ensure string to be read is multiple of 8 bytes long.

-- Determine value of string.

v_string := utl_raw.cast_to_varchar2(dbms_obfuscation_toolkit.des3decrypt

(input => utl_raw.cast_to_raw(p_that)

,key => sw_code(p_this)

,which => dbms_obfuscation_toolkit.threekeymode

));

-- Return decrypted value minus the padded characters.

RETURN RTRIM(v_string, v_padding);

END IF;

END;

/

ALTER TABLE demo_customers

DROP COLUMN credit_limit_enc;

--ALTER TABLE demo_customers

--ADD credit_limit_enc RAW(128);

ALTER TABLE demo_customers

ADD credit_limit_enc VARCHAR2(128);

CREATE OR REPLACE TRIGGER cust_enc_biur

BEFORE INSERT OR UPDATE ON demo_customers

FOR EACH ROW

WHEN (NEW.credit_limit IS NOT NULL)

BEGIN

:NEW.credit_limit_enc := sw_write(:NEW.credit_limit, :NEW.customer_id);

:NEW.credit_limit := NULL;

END;

/

UPDATE demo_customers

SET credit_limit = credit_limit;

col credit_limit format a20

col enc_limit format a20

SELECT customer_id

,credit_limit

,sw_read(credit_limit_enc, customer_id) enc_limit

,credit_limit_enc

FROM demo_customers;

-- vpd / context

http://www.talkapex.com/2010/07/poor-mans-vpd-in-apex-and-oracle-xe.html

http://www.google.com.au/url?sa=t&rct=j&q=vpd%20oracle%20apex&source=web&cd=9&ved=0CHYQFjAI&url=http%3A%2F%2Fd2850a.aderas.net%2Fpls%2Fapex%2FADERASWWW.download_my_file%3Fp_file%3D80513909248389119&ei=47irToS8AsqtiQfbz7zgDw&usg=AFQjCNHTd95ZXv5A_URxPwYwkfeVo5K6rg&sig2=rKjLciv8-zaG4QwLzmRjiQ

update demo_orders set user_name = 'SWESLEY' where rownum< 3;

select *

from demo_orders

where user_name = 'DEMO';

select count(*), user_name from demo_orders group by user_name;

-- enterprise edition

CREATE OR REPLACE PACKAGE ee_rls AS

PROCEDURE p_set_context;

FUNCTION f_ord_rls_policy

(object_schema IN VARCHAR2

,object_name IN VARCHAR2

) RETURN VARCHAR2;

END ee_rls;

/

CREATE CONTEXT ee_ctx USING ee_rls;

CREATE OR REPLACE PACKAGE BODY ee_rls AS

PROCEDURE p_set_context IS

BEGIN

dbms_session.set_context

(namespace => 'EE_CTX'

,attribute => 'USER_NAME'

,value => COALESCE(v('APP_USER')

,SYS_CONTEXT('USERENV'

,'SESSION_USER'))

);

END p_set_context;

FUNCTION f_ord_rls_policy

(object_schema IN VARCHAR2

,object_name IN VARCHAR2

) RETURN VARCHAR2 IS

BEGIN

RETURN 'user_name = '||q'[sys_context('EE_CTX','USER_NAME')]';

END f_ord_rls_policy;

END ee_rls;

/

begin

dbms_rls.drop_policy('AUSOUG','DEMO_ORDERS','ORD_RLS_POLICY');

end;

/

BEGIN

dbms_rls.add_policy

(object_schema => 'AUSOUG'

,object_name => 'DEMO_ORDERS'

,policy_name => 'ORD_RLS_POLICY'

,function_schema => 'AUSOUG'

,policy_function => 'ee_rls.f_ord_rls_policy'

,statement_types => 'SELECT');

END;

/

-- fetched from app_user

execute ee_rls.p_set_context;

select sys_context('EE_CTX','USER_NAME') from dual;

select count(*), user_name from demo_orders group by user_name;

-- cheapo

CREATE CONTEXT xe_ctx USING xe_rls;

CREATE OR REPLACE PACKAGE xe_rls AS

PROCEDURE p_set_context

(p_user_name IN VARCHAR2);

PROCEDURE p_clear_context;

END xe_rls;

/

CREATE OR REPLACE PACKAGE BODY xe_rls AS

PROCEDURE p_set_context IS

BEGIN

dbms_session.set_context

(namespace => 'XE_CTX'

,attribute => 'USER_NAME'

,value => v('APP_USER'));

END p_set_context;

PROCEDURE p_clear_context IS

BEGIN

dbms_session.clear_context('XE_CTX');

END p_clear_context;

END xe_rls;

/

exec xe_rls.p_set_context('ADMIN');

select sys_context('XE_CTX','USER_NAME') from dual;

CREATE OR REPLACE VIEW demo_orders_vw AS

SELECT *

FROM demo_orders

WHERE user_name = SYS_CONTEXT('XE_CTX', 'USER_NAME')

WITH CHECK OPTION

/

select count(*), user_name from demo_orders_vw group by user_name;

SQL> select count(*), user_name from demo_orders group by user_name;

COUNT(*) USER_NAME

---------- ------------------------------

2 ADMIN

8 DEMO

2 rows selected.

SQL> select count(*), user_name from demo_orders_vw group by user_name;

COUNT(*) USER_NAME

---------- ------------------------------

2 ADMIN

1 row selected.

-- apex_dml_lock_wait_time

BEGIN

FOR r_rec IN

(SELECT *

FROM demo_customers

WHERE customer_id = :p_id

FOR UPDATE OF credit_limit WAIT 0

) LOOP

-- do update...

null;

END LOOP;

END;

/

DECLARE

CURSOR c_sample IS

SELECT *

FROM demo_customers

WHERE customer_id = :p_id

FOR UPDATE OF credit_limit WAIT 0;

lr_sample c_sample%ROWTYPE;

BEGIN

OPEN c_sample;

FETCH c_sample

INTO lr_sample;

CLOSE c_sample;

END;

/

-- XSS

UPDATE demo_customers

SET cust_street_address1 = '<script>alert("danger");</script>'

WHERE customer_id = 1;

-- groups

DECLARE

VAL VARCHAR2(32765);

BEGIN

VAL := APEX_UTIL.GET_GROUPS_USER_BELONGS_TO(p_username => 'FRANK');

END;

DECLARE

VAL BOOLEAN;

BEGIN

VAL := APEX_UTIL.CURRENT_USER_IN_GROUP(p_group_name=>'Managers');

END;

-- custom

PROCEDURE post_authentication IS

lc_exists VARCHAR2(1);

BEGIN

SELECT 'Y'

INTO lc_exists

FROM dual

WHERE EXISTS

(SELECT null

FROM application_roles

WHERE username = :APP_USER);

apex_util.set_session_state('F_USR_TEAMLEADER', lc_exists);

END post_authentication;

-- files

INSERT INTO my_files (my_id, filename, blob_col, mime_type)

SELECT :P1_ID, filename, mime_type

FROM apex_application_files

WHERE name = :P1_FILENAME;

DELETE FROM apex_application_files

WHERE name = :P1_FILENAME;

-- sql injection

create table secrets (id number, secret varchar2(100));

insert into secrets values (1, 'Who shot JFK');

insert into secrets values (2, 'Coke''s secret ingredient');

insert into secrets values (3, 'Origin of the universe');

insert into secrets values (4, 'Which way to plug in a USB cable');

-- plugins

CREATE table app_roles (username VARCHAR2(200), role_name VARCHAR2(200));

insert into app_roles values ('SCOTT','OFFICER');

insert into app_roles values ('PENNY','MANAGER');

insert into app_roles values ('BRANKA','TEAM_LEADER');

http://chrisonoracle.wordpress.com/2011/05/09/how-to-create-an-apex-4-1-authorization-plugin/

FUNCTION is_authorised (

p_authorization IN apex_plugin.t_authorization,

p_plugin IN apex_plugin.t_plugin )

RETURN apex_plugin.t_authorization_exec_result

IS

lc_group VARCHAR2(4000) := p_authorization.attribute_01;

ln_count PLS_INTEGER;

lr_result apex_plugin.t_authorization_exec_result;

BEGIN

SELECT 1

INTO ln_count

FROM dual

WHERE EXISTS

(SELECT NULL

FROM apex_workspace_group_users

WHERE user_name = p_authorization.username

AND group_name = lc_group);

lr_result.is_authorized := ln_count > 0;

RETURN lr_result;

END is_authorised;

FUNCTION custom_auth (

p_authentication IN apex_plugin.t_authentication,

p_plugin IN apex_plugin.t_plugin,

p_password IN VARCHAR2)

RETURN apex_plugin.t_authentication_auth_result

IS

l_user demo_users.user_name%TYPE := UPPER(p_authentication.username);

l_entered_pwd demo_users.password%TYPE := p_authentication.attribute_02;

l_pwd demo_users.password%TYPE;

l_id demo_users.user_id%TYPE;

lr_result apex_plugin.t_authentication_auth_result;

BEGIN

pr_sw_dummy('l_user:'||l_user);

pr_sw_dummy('l_entered_pwd:'||l_entered_pwd);

SELECT user_id, password

INTO l_id, l_pwd

FROM demo_users

WHERE user_name = l_user;

lr_result.is_authenticated := l_pwd = custom_hash(l_user, l_entered_pwd);

IF NOT lr_result.is_authenticated THEN

lr_result.log_code := 4;

lr_result.log_text := 'Incorrect password';

END IF;

lr_result.log_code := 0;

RETURN lr_result;

EXCEPTION

WHEN NO_DATA_FOUND THEN

lr_result.log_code := 1;

lr_result.log_text := 'Unknown user';

lr_result.is_authenticated := FALSE;

RETURN lr_result;

END;

update demo_users set password = custom_hash(user_name, 'ausoug');

"Thank you"

The road ahead

How many

4.2 is coming

you

did

see?

Systems Consultant & Trainer

sagecomputing.com.au

/*

Scott Wesley

*/

scott@sagecomputing.com.au

secure

entry level

"double think"

razed

that makes me a developer

Today's Architecture Diagram

The

ubiquitis

symbol

security

of

I am not one of these

Workstation

Medieval Castle Design

(yeah right)

denial of service attack

"attack with numbers"

apex_dml_lock_wait_time

encryption

vpd

browser attacks

authentication

timeouts

database techniques

sql injection

denial of service

xss

plug-ins

no security checklists

Browser Security

Layer 8

f?p=147:8:3816416929962008::::P8_ID:24

user just modifies ID they're editing

f?p=147:8:3816416929962008::::P6_ROWID:AAAXMbAAGAAAD2lAAv

Apex now offers better security through obscurity

f?p=147:8:3816416929962008::::P8_ID:23&cs=3D4E91375C318A9AD184555771D961CB1

Checksums are easiest out of box solution to guard the gate

the real pebkac

(Oracle) Application Server

see the plug?

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

"border patrol"

URL tampering

"Defence in Depth" philosophy

cross site scripting

SQL Injection

protecting history

phishing protection

"loose lips sink ships"

Transparent Database Encryption

"stop the soldiers from reading data"

Web Browser

what flavour are you?

audience question!!

Authentication

Security Plug-ins

Identity Management

encapsulate and share

Custom Authentication

Schemes

Authorisation Plug-in

Authentication Plug-in

< Identities >

SSO

< Business Roles >

bare bones example

< Application Roles >

• Views on remote views
• Grants only to schemas that need it

plenty of options

is your master key

out of the box

-- this is the documented example

better info for apex_workspace_access_log

other people's definition

interrogate your

Authorisation scheme definition

own

LDAP

Remote Queries

tables

SALES Schema

(apex_workspace_group_users)

User Groups

DWS_VW

Authentication scheme definition

setup a scheme to allocate to components

talk directly with your LDAP server

c a n b e s l o w

define your group

or setup some PL/SQL to run queries against it

my favourite security setting

place your "schemes" in application items

allocate to your Apex users

because it solves a problem...

APIs exist to help

Authorisation

Session State Protection

Data Encryption

SQL Injection

Encrypting data in the db

/*+ DRIVING_SITE (alias_of_big_table) */

define a function to encypt

but allows mischief like this

can start at item level definition

what if I want two?

define encryption key

will their roles change once logged in?

and the function to decrypt

REMOTE_USER

default settings

specially privileged, of course

stop the average user from fiddling with the URL

kinda need to go out of your way

so read through the in-line documentation

automagically

data encrypted

within trigger

INTERNAL

protects from internal viewing

• Limited access
• Contains views to tables

also evident if viewing directly

decrypt for display

protect your hidden items

and this!

... the users see something a little nicer

unprotected - you can do this

Application

USE BIND VARIABLES

VPD

so just

Enterprise Edition teaser

Poor man's VPD

Session

still isn't really that cheap...

Timeout

Virtual Private Database

or if SSP applied just at page level

AKA: Fine Grained Access Control (FGAC)

Enterprise Edition only

Contexts OK

-- no policy

granular settings by page

Workspace

Simulate with views

application items

Apex Application

page items

8 hours...

honoured even if SSP is disabled

but if you must, use DBMS_ASSERT

Views Schema

Apex Application Security Attributes

Materialized Views

"your greatest threats are typically from within"

Apex Application Files

fires for every page

Actual Data

XSS

queries

added to

all

WHERE user_name = :APP_USER

"beware the trojan horse"

the application

Directory Service (AD/OID)

sql*plus

toad

you could load as per 3.x

necessary with thanks to the session pool

sql developer

clean

just

up the repository

or load direct to your table

(Application Data)

and don't try this at home...

Remote Data Warehouse

Application Express

Oracle OLTP Database