Leveraging Oracle Portal for Enterprise Identity Management

1
Leveraging Oracle Portal for Enterprise Identity
Management

• Greg Pike

2
Greg Pike (Associate Partner)

• History 15-year Oracle solution provider
• Home Chicagoland
• Focus Business Intelligence, Data Warehousing,
  Portal, web applications
• Author and moderator www.SingleQuery.com

3
Overview

• Alternative provisioning solutions using Oracle
  Portal constructs (10.1.2/10.1.4).
• Manage data security and the graphical user
  experience for external applications.
• Manage user security with out-of-the-box portal
  Administration screens.
• Create an Enterprise Provisioning API.

4
User Provisioning and Oracle Portal

• Employing scattered islands of user information
  is inefficient.
• Many products fill the Provisioning void (i.e.
  Oracle Identity Management).
• Oracle Portal can also fill many Provisioning
  roles without the costs associated with more
  robust solutions.

5
Understanding Groups and Items

• Groups
• Empower administrators to craft comprehensive and
  efficient security models for accessing Portal
  content.
• Relatively static objects, but include an
  inheritance feature.
• Items
• Designed to represent any type of content found
  on a web page.
• May include custom metadata content and can be
  exposed to Groups for security.

6
Creating a Group
Builder ? Administer Tab ? Create Group
7
Case Study 1 Limiting Access in Oracle Reports

• Restrict the viewable information in Oracle
  Reports based upon a location (facility).
• Traditional Approach User/Privilege schema,
  alter queries and add administration system.
• Portal Approach Virtual Private Database (VPD)
  and provisioning through Portal.

8
Corporate Location Hierarchy

• Model the location hierarchy as a set of Portal
  Groups.

• Parent Groups are added as members to the
  immediate child Groups.
• Create a unique naming convention to facilitate
  queries (LOC-NN).

9
LOC-NN Naming Convention
Direct Group Membership
Inherited Group Membership
10
Create a Simple Portal API

• FUNCTION get_user_locations(f_in_username IN
  VARCHAR2) RETURN VARCHAR2 IS
• csv_locations VARCHAR2(32767)
• CURSOR LOC_cur (c_in_username IN VARCHAR2) IS
• BEGIN
• FOR loc_rec IN loc_cur(f_in_usernane) LOOP
• IF length(csv_locations) gt 1 THEN --
  append a comma to the list
• csv_locations csv_locations ','
• END IF
• csv_locations csv_locations
  loc_rec.location_id
• END LOOP

SELECT to_number(regexp_replace(wug.group_name,'L
OC-','')) location_id FROM
wwv_user_groups WHERE user_name
c_in_username AND group_name like 'LOC-'
11
Create a VPD Function

• VPD Functions Allow dynamic WHERE clauses to be
  added to queries at the table level.
• FUNCTION request_user_locations RETURN VARCHAR2
  IS
• allowed_location_ids VARCHAR2(32767)
• BEGIN
• END -- function request_user_locations
• Attach VPD function to the table
• DBMS_RLS.add_policy (object_schema   gt
  'XYZ_DW',          object_name      gt
  'LOCATIONS_D',          policy_name      
  gt 'LOC_SECURITY',         
  policy_function  gt 'REQUEST_USER_LOCATIONS)

allowed_location_ids get_user_locations_at_ASDB(US
ER) RETURN location_id IN (allowed_location_i
ds)
Request allowed locations from Portal.
12
System Architecture
13
Case Study 2 Dynamic Web Menus

• Web-based reporting interface includes
  hierarchically-structured menu items.
• Menus are constructed dynamically based upon
  privileges from the users Portal group
  membership.
• All menu metadata is contained in Portal Items.

14
Web Menu Structure (Bottom-Up)

• Users gain access to the individual lowest-level
  menu items.
• Child Groups are added as members to the parent
  Groups.
• Upward inheritance preserves the menu visual
  structure.

15
Combining Groups and Items

• Portal Groups provide a powerful framework for
  developing hierarchical access.
• Items serve as a repository for dynamic web menu
  metadata.
• The combined Group-Item object provides a
  powerful tool for applying a highly-customizable
  user security model to menus.

16
Group-Item Constructs
Item-level Security provides the glue
Portal Groups provide hierarchical access.
Portal Items describe individual menu items.
17
Add Custom Attributes
Navigator ? Page Groups ? Shared Objects ?
Attributes ? Create New Attribute
18
Create a Menu Item Type
Navigator ? Page Groups ? Shared Objects ? Item
Types ?Create New Item Type
19
Add Attributes to the Item Type
Edit Item Type ? Attributes
20
Create the Menu Items
Navigator ? Custom Menu Page
21
Enable Item Level Security
Navigator ? Edit Page ? Access
22
Apply Item Level Security
Edit Menu Item ? Access
23
Consolidation Groups

• Granting access to individual menu items may lead
  to a maintenance nightmare.
• For menus with dozens or hundreds of menu items,
  individual access is not feasible.
• Consolidation groups simplify menu access
  administration.

24
Querying the menu items
Rows returned from the dynamic menu view for a
user with full access.
25
Menu Item API Dynamic Menu Query

• WITH temp AS ( -- This query gets 1 row for
  each item/attribute combination
• SELECT t.masterthingid id,
• t.title,
• t.parentid,
• wa.name item_name,
• DECODE(ta.valuetype,'text',ta.value,ta.
  numbervalue) item_value,
• pg1.name parent_page
• FROM portal.wwv_things t,
• portal.wwpob_page pg1,
• portal.wwsec_group gp,
• portal.wwsec_sys_priv p,
• portal.wwv_user_groups wg,
• portal.wwsbr_item_types wit,
• portal.wwsbr_attributes wa,
• portal.wwsbr_item_type_attributes wita,
• portal.wwv_thingattributes ta
• WHERE t.siteid
  SUBSTR(p.name,1,INSTR(p.name,'/')-1)
• AND t.masterthingid
  substr(p.name,instr(p.name,'/')1,8)
• AND p.object_type_name 'ITEM' -- ITEM /
  GROUP

SELECT id,title, parentid, menu_item_type,
report_id, url, sort_order - Order the
result FROM ( -- A classic pivoting mechanism
to place all attributes in a single row
SELECT temp1.id, -- Needed
for the CONNECT BY temp1.parentid,
-- Needed for the CONNECT BY
temp1.title, -- The
menu item text temp1.item_value
menu_item_type, -- Is this a node or a leaf
temp2.item_value report_id, -- What
report to run temp3.item_value url,
-- What portal page to show
temp4.item_value sort_order -- Order of
the item in the menu FROM temp temp1,
temp temp2, temp temp3, temp temp4 WHERE
temp1.id temp2.id AND temp2.id
temp3.id AND temp3.id
temp4.id AND temp1.item_name
'MenuItemType AND temp2.item_name
'MenuItemReportID' AND temp3.item_name
'MenuItemURL' AND
temp4.item_name 'MenuItemSortOrder'
) CONNECT BY parentid PRIOR id START WITH
parentid 0 ORDER SIBLINGS BY sort_order
--Orders the menu items in the order displayed
Portal tables and views.
Custom item attributes to return.
User parameter
26
Portal Objects Used in the Query
Table descriptions for a dynamic menu item query.
27
System Architecture
28
Case Study 3 The need for an Enterprise
Provisioning API (EP-API)

• Using the default Portal Administration screens
  may hinder full integration with external
  systems.
• Included Portal APIs are complex and require a
  deep understanding of Portal.
• Exposing Portal APIs directly to external systems
  is unwise from a security standpoint.
• An EP-API eliminates the risk to external systems
  when Oracle Portal is upgraded.

29
Required Elements of the EP-API

• Create and delete/inactivate Portal users.
• Grant or revoke user access to Portal Groups.
• Inquire on the allowed Groups for a user.
• Create new Groups and modify their hierarchical
  structures.
• Create new Items and associate these to Groups.
• Modify the custom attributes for Portal Items.
• Inquire on existing Portal hierarchies.

30
Selected Oracle Portal APIs
31
Wrap-Up Advantages

• Portals out of the box functionality includes a
  complete suite of support structures to start
  provisioning immediately.
• Centralizing user metadata with Portal enables a
  help desk model for application support.
• For current owners of Oracle Portal, in-house
  skills and existing software is leveraged.
• Compatible with BPEL when appropriate APIs are
  constructed.

32
Wrap-Up Disadvantages

• The effort of creating duplicate users in Oracle
  Portal may be greater than the perceived time
  savings.
• Default portal administration pages are not
  efficient.
• Exposing Portal privileges to external
  applications requires careful security scrutiny.
• Centralizing user privileges upgrades the Portal
  to a mission-critical system.

33
Summary

• Oracle Portal can fill enterprise user
  provisioning roles.
• Combined, Portal Groups and Items become a very
  powerful provisioning tool.
• These case studies were derived from existing,
  real-world implementations.

34
(No Transcript)
35
Thank You for attending!

• Contact Greg Pike at
• gpike_at_piocon.com
• [phone]
• Thanks to contributors
• John Weicher, Jeremy Simmons and Chip Dawes