Integrate 'Oracle Forms', 'Oracle Reports', 'Oracle

Integrate 'Oracle For m s' , 'Oracl e Reports', 'Oracle Discoverer'

with ‘Oracle Single Sign On'

, 'Oracle

Internet Directory'

and '

V

irtual Private Database'

for

the Luxembourg com

m

unities.

How to make sure that a user can only use the produc

ts he is allowed to use and see only the data

he is allow

ed to see using 1 usernam

e/password

Integrating it all at sigi

Agenda

•I nt ro du ct io n – V DS Computing –S ig i • Project Constraints • S olut ion ORACLE-VDS • A pplicati on struct ure overview • Issues not covered w ith standard Oracle • E xam ple of a logon procedure • A dvantage s of this solut ion

VDS Computing: History

• Since 1982 • 7
55 em ployees • Turnover from 1.5
6.0 m illion Euro • International Focus – VD S-C om pu ting Luxem bourg sàrl – VD S-C om pu ting UK Ltd. • ISO 9001 certified

VDS Computing: Services

• C onsulti ng •A na ly si s • D evelopment • Project Managem ent • In stallation • T raining • S upport • O utsourci ng

VDS Computing: Products

•

Softwa

re

–

Financial and logist

ics S o ftware on Orac le : Pit on – Business Intelligen ce – Turn-key Developm ent on Oracle • Hardw are – Design – Implementation – Management

Presentation

of

S

Project Constraints

•

C

entr

alised

IT

I

nf

rastr

uct

ur

e

•

S

ecure

network between

C

ities

and

the

Datacent

er

•

S

ecure

Data(Base)

(account-and

budget

info)

•

Int

uiti

ve and

O

pen Applications

•

R

ic

h

U

ser In

terface

•

C

utting

E

dge

T

echnology

Solution ORACLE-VDS

• D atabase O RACLE 9i Enterprise Edition (EE) – V irtual Privat e D atabase – A dvanced Security Option (Enterprise U sers) • A pplicati on Server ORACLE 10g EE – S ingle Sign On – O racl e Intern et Directo ry (LDAP ) – T hin C lient: MS Internet Explorer – F or m s, Reports and D iscoverer S ervices – O racl e Portal – X ML-Interface • R AD Developm ent T ools – D esigner, Forms et Reports

Application Architecture

Databas

e meUpd. Sche ead me: R Sche

A p plic at ion S er ver I A S LDA P Directory Data Filtersb y VPD Forms _Reports Data Re ad Re ad GESCO M GESCO M PORTA L Othe r A p pl ic at ions Busin ess Logi c Au thentification Acces s A SO Disco ve re r

Application Architecture

•

O

racl

e P

ortal

– Intra-or Internet websi te bui lder/publisher. – N o programm

ing skills neede

d.

•

O

racl

e F

or

m

s

– B

uild and run OLAP-applicat

ions

–

M

oved from character based via

client/ server – N ow 3-tier architecture

Application Architecture

•

O

racle Reports _–Reporting tool. _–Run using a report server (w

ith poss ibility to run in batch) – P ossibility to e-m ail th e result o r re tr iev e it f rom a

repository via the web.

•

O

racle Discoverer _–Business Intelligence tool _–Design without DB-knowledge _–Design without extra progra

m

Application Architecture

•

SSO : Single Sign On –

A

uthenticate once, then au

th enti cat e aut omati call y for different products. • O ID

: Oracle Internet Directory

–

A

standard (LDAP) way

to hold security data.

•

V

irtual Private Database _–Lim

it us er acces on reco rd level. – A

llow a user only to see wh

at he is allowed to see

Security

issues NOT s

olved

wit

h

standar

d Oracl

e

•

F

orms

/ reports menu

– D efinition w ho can use which form s is m aintained inside the application – D efinition w ho can run w hich report is m aintained inside the application

Example

of

a Logon Procedure

•U

se

r :

– M arcel DUP O NT o f the city SEPTFONTAINES

•

L

og

in

to

po

rta

l

– L ogin : mdupont.septfonta ines – P assword SSO : abcdefgh1

•R

es

ul

t:

– U ser gets the portal pages he is allowed to see

Example

of

a Logon Procedure

•

U

ser cl

icks on the

U

RL t

o start

the

fo

rm

s

applicati

on.

– Based on the

SSO info, form

s checks if the user is allowed to start the application – T he ‘resource information’ is

retrieved from the

OI D – ‘Resource inform ation’ : Database logon inform

Example

of

a Logon Procedure

•R

es

ou

rc

e

:

– L ogin : mdupont.septfonta ines – P assword : ##### (se cret) – D atabase : REC1

•

F

or

ms applicati

on starts and logs ont

o t

he DB

•

In the dat

abase mdupont.septf

ontai

nes

is

unkown

as a schema user :

ASO is

activated.

Example

of

a Logon Procedure

•

A

dvanced

S

ecurity

O

ption :

– T he database ‘asks’

the OID if the user

m dupont.septfontaines is allow ed to log i nto t he

DB with the password #####

–

T

he OID replies ‘ok’

and

‘map this user to the

schema recdev’

–

T

Example

of

a Logon Procedure

•

T

he logon in the DB fires a logon trigger

_–Using the ASO info (m

dupont) the OID is

interroga

te

d to find his comm

unit y (septfontai nes). – T he context is se t to activate the VPD.

•

V

irt

ual P

rivat

e Database

– F or Each select/i nsert /upda te/de lete statem ent an addit iona l ‘where comm unity=‘septfontai nes’ is added

Example

of

a Logon Procedure

•

T

he forms application is started

_–User sees only info he is allow

ed to see – U ser can s tart reports (also usi ng ASO and VPD

because the same logon-info is used)

–

U

ser can s

ee the reports he

has run with the results

–

T

he user can only se

e his

reports

(Reports server is also SSO enabled.) –T

Example

of

a Logon Procedure

•

D

iscoverer AS10G Rel2. Is started

_–User logs i

nto t

he dat

abase as the discoverer user

–

SSO inform

ation is available during t

he DB logon

–

U

sing the SSO info (m

dupont) the OID is

interroga

te

d to find his comm

unit y (septfontai nes). – T he context is se t to activate the VPD. – T

he user can only i

nt

errogate info from his

comm

Advantages

of

thi

s

soluti

on

•

C

entr

al s

tandar

d s

ecurity maintenance.

•

S

ecurity is independent

f

rom devel

opment

(developer doesn’t have

to think about security)

–

A

pplication security is ma

intained in the OID, not

in Form s/reports. – D ata security is m aintain ed in the DB using VPD – D ata security se tup i s done autom atically

Advantages

of

thi

s

soluti

on

•

Integration with othe

r fron

t-e

nd

s with

guaranteed securi

ty.

– M S office integra tion pos sible : ODBC co nnection

to get info from

the DB ( w ith VP D active) –

.NET integration possi

ble

–

M

S Active Directory integra

Le progiciel de GEStion

C

OMmunale

du L

uxembour

g

Historique du projet

• S ituat ion de départ : – 100 sites délocalisés – D éveloppem ent de GESCOM au tour de la plate-form e propriétaire HP3000 • N ovem bre 2001 : –

annonce par HP de l’arrêt

du HP3000 programm é au 31 décem bre 2 006 • N

ouveau projet GESCOM : _–2002 : pilotes architectu

re et développem ent – 2003 et 2004 : réécriture – 2005 : finalisation et dé but de la m igration

Organisation de GESCOM

•

U

ne ar

chitecture appli

cati

ve sécuris

ée

– G

estion des utilisateurs au travers un LDAP

–

S

écurité

d’accès aux données c

onfiée à la DB

•

A

pplicati

ons

avec un poi

nt d’ent

rée uni

que

– P

ortail applicatif ave

c SSO

•

D

es

so

lu

tio

ns

d

e Re

po

rtin

g

pe

rfo

rm

an

te

s

•

D

éploiement facilité

(B

rowser, Acrobat

R

eader)

Bilans

•

B

ilan technique _–Dém

arches im

posées par les SSO et VPD

–

B

énéfices imm

édiats grâce aux SSO et VPD

• B ilan ut il is ateurs – S

SO avec Modules intégrés

– S écurité rassuran te et transparen te • B ilan fina ncier – C harge im portante pour la m ise au point de l’architecture – L argem ent récupéré lors du développem ent et de la m ainten anc e

Questions

-R