Sage Computing Services
Oracle Apex 4.1  Security
Scott Wesley
Systems Consultant & Trainer
authentication
encryption
denial of service
timeouts
plug-ins
database techniques
browser attacks
vpd
the 
truth
has
no agenda
that makes me a developer
I am not one of these
no security checklists
The
ubiquitis
security
symbol
of
Today's Architecture Diagram
Identity Management
Remote Queries
Data Encryption
VPD
(Application Data)
Application Express
Authentication
Session State Protection
SQL Injection
URL tampering
XSS
cross site scripting
INTERNAL
Session
Apex Application Files
LDAP
Custom Authentication
SSO
(Oracle) Application Server
Workstation
(yeah right)
see the plug?
Oracle OLTP Database
Remote Data Warehouse
Directory Service (AD/OID)
Layer 8
the real pebkac
Web Browser
Transparent Database Encryption
what flavour are you?
< Identities >
< Application Roles >
< Business Roles >
Medieval Castle Design
"Defence in Depth" philosophy
REMOTE_USER
SALES Schema
Limited access
Contains views to tables
DWS_VW
Views on remote views
Grants only to schemas that need it
Materialized Views
"stop the soldiers from reading data"
apex_dml_lock_wait_time
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
denial of service attack
"attack with numbers"
User Groups
Browser Security
"border patrol"
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
default settings
stop the average user from fiddling with the URL
... the users see something a little nicer
granular settings by page
application items
page items
honoured even if SSP is disabled
or if SSP applied just at page level
unprotected - you can do this
so read through the in-line documentation
talk directly with your LDAP server
or setup some PL/SQL to run queries against it
plenty of options
out of the box
is your master key
other people's definition
define your group
allocate to your Apex users
setup a scheme to allocate to components
interrogate your
own
tables
my favourite security setting
place your "schemes" in application items
what if I want two?
will their roles change once logged in?
 up the repository
you could load as per 3.x
or load direct to your table
kinda need to go out of your way
but allows mischief like this
and this!
so just
USE BIND VARIABLES
but if you must, use DBMS_ASSERT
and don't try this at home...
can start at item level definition
protects from internal viewing
also evident if viewing directly
c  a  n      b  e      s  l  o  w
/*+ DRIVING_SITE (alias_of_big_table) */
(apex_workspace_group_users)
Security Plug-ins
Authorisation Plug-in
Authentication Plug-in
better info for apex_workspace_access_log
bare bones example
Authentication scheme definition
Authorisation scheme definition
"your greatest threats are typically from within"
Virtual Private Database
Enterprise Edition only
Contexts OK
Simulate with views
AKA: Fine Grained Access Control (FGAC)
added to 
all
queries
the application
toad
sql developer
sql*plus
Enterprise Edition teaser
Poor man's VPD
Apex Application Security Attributes
fires for every page
-- no policy
Apex Application
Views Schema
Actual Data
protecting history
phishing protection
protect your hidden items
Encrypting data in the db
define encryption key
define a function to encypt
and the function to decrypt
data encrypted 
decrypt for display
Authorisation
xss
sql injection
SQL Injection
"beware the trojan horse"
still isn't really that cheap...
Schemes
encapsulate and share
audience question!!
WHERE user_name = :APP_USER
necessary with thanks to the session pool
within trigger
automagically
APIs exist to help
-- this is the documented example
because it solves a problem...
Timeout
just
clean
8 hours...
secure
razed
"Thank you"
How many
see?
you 
/*
Scott Wesley





*/
scott@sagecomputing.com.au
Systems Consultant & Trainer
sagecomputing.com.au

specially privileged, of course
Application
Workspace
did 
"loose lips sink ships"
entry level
"double think"
grassroots
-oracle.com
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
Topic List
-- 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');