Transcript Mining FND data for access reporting

Mining FND data for Access
Reporting
Malcolm Speedie
Openwave Systems Inc.
Business Need
• Need to be able to tell auditors what each
employees can do in Oracle.
• Need to create a method to report user
related FND data for the business.
• Help staff ensure accounts are created
and removed as needed.
• Help with locked out accounts.
Constraints
• Target audience for reports are not
technical.
• DBA team did not want automated account
creation or end dating.
• We are using the Hard-To-Guess set up
with lockout activated.
Assumptions
• Employees use responsibilities to access
Oracle.
• Core users have access to Discoverer.
• Site uses custom responsibilities.
• If you can show what is in each
responsibility, you show what the
employee can do.
Solutions
• Since the data is relatively static (except user data) we
used materialized view to speed up queries.
• Design views to simplify folders in Discoverer by hiding
the complexity of the underlying schema.
• Create a batch program to keep the materialized views
up to date. Another to populate the menu tree.
• Create three alerts:
– DBA team of ex-employee accounts to end date.
– DBA team that new employees need accounts.
– Employees of locked out accounts.
• Locked out view
Primary Views
•
•
•
•
•
•
XX_FND_USER_RESP_LIST
XX_FND_EX_USER_RESP_LIST
XX_FND_APP_RESP_MENU_TREE
XX_FND_USER_LIST
XX_FND_FAILED_LOGON
XX_FND_ACTIVE_RESP
Table and programs
• XX_FND_MENU_TREES
• XX_FND_MVIEW_REFRESH
• XX_FND_POP_MENU_TREES
Other areas to explore
• Discoverer business area and folders tied
to users and responsibilities
• Oracle Access auditing
• Third Party tools
• http://www.mspeedie.com/mining_fnd_data.zip
for all source code and soft copy of presentation