Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Oracle Apex 4.1 Security

Offering Obfuscation Options - InSync 2012
by

Scott Wesley

on 4 November 2012

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Oracle Apex 4.1 Security

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"
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');
The road ahead
4.2 is coming
Full transcript