rds administration & security

RDS Administration & Security

Session #396

Monday, 3/17/2003 11:45am

HEUG 2003 Conference - Dallas

Session #396 - RDS Security & Administration

2

Carol Jordan

Technical Lead, Application Support

Stanford University

Kevin Dale

Functional Analyst, Application Support

Stanford University


3

Presenter Background

-- My first implementation was commercial HRMS in 1997.

-- I joined Stanford in 2001, during the Student Administration implementation project. As part of that implementation project, Stanford installed the RDS in early 2001.

-- Stanford’s PeopleSoft implementation:

PeopleSoft 7.6 modules:Human Resources, Payroll, Benefits

Admissions, Student Records, Student Financials,Financial Aid, HTML Access

PeopleTools 7.62


4

Synopsis of Presentation

Description of the presentation: To share the techniques we’ve used in the security and administration of the RDS to make it a strong and useful tool for enterprise-wide reporting.

In this presentation, I’ll talk about the original vision for enterprise-wide reporting, our hardware set-up, the highlights of our nightly batch process, and some of our automation tricks for end-user security.

What to take away: setting up a reporting environment for PeopleSoft users takes a committed cross-functional effort from DBAs, Storage, and UNIX/Windows system administrators


5

Stanford’s vision for enterprise-wide reporting:

To provide a single, web-based interface for all reporting needs, across all applications and all back-end platforms using Brio Portal


6

Technical Infrastructure – Overview

Two components to our reporting infrastructure: PeopleSoft & ReportMart

ReportMart:• Database/application/web servers: Oracle 8i, UNIX Solaris 7

PeopleSoft:• Database servers: Oracle 8i, UNIX Solaris 7• Separate database servers for transactional and reporting databases• Storage: EMC Symmetrix frame, BCVs• RDS server: Windows 2000

Features of our set-up:• Separate servers for transactional and reporting databases• EMC TimeFinder to re-create the reporting DB… in just a few minutes…with no downtime!• DB links to tie together operator security between the RM and PS applications• AutoSys scheduler spans UNIX & Windows jobs


7

Technical Infrastructure – Graphical overview

PSRPRD

ODS

PS-Reporting server

PSPRD

PS server

RMPRD

ReportMart server

RDS server

EMC TimeFinderDB links tieoperatorsecuritybetween PSand RM

RM serverruns reportson behalf ofend usersand enforcesPS security

Reporting Users

otherapps


8

Creating RPRD – Original

• Export ODS schema

• Create a RPRD as an exact image of PRD (using TimeFinder)

• Import ODS schema

• Run RDS (and other) builds

This worked okay, but we learned as we went along…


9

Creating RPRD – Getting better

Problem: RPRD isn’t ready due to problems with overnight processing…but users are running reports on incomplete or incorrect data.

Solution: We implemented Oracle “restricted session” to lock out reporting users while the builds are in progress. Once the builds are complete, we let the reporting users access the reporting DB.


10


Problem: Performance of reporting builds, performance of reports and ad hoc queries

Solutions:

• We placed an Oracle “resource limit” on reporting users – this kills queries after a pre-defined time period.

• We scheduled a cron job that runs a UNIX “top” command every 10 minutes, and if CPU is over 75% send an e-mail – we can then take a look at problems before we get the “reporting is too slow” trouble ticket.

• We scheduled reports of the highest-cost SQL for reporters’ daytime activity and overnight batch-builds activity. This report shows us the SQL that needs to be tuned.


11


Problem: Needed to extend the operator security defined within PeopleSoft to the reporting system

Solutions:

• We used DBA policies to limit ad hoc reporters’ access to secured data (such as salary).

• The ReportMart team used PS operator classes to grant the privilege to see various report categories. A cron job runs several times daily to update RM security from PeopleSoft.

• We implemented Fast Security, but only for reporting – we re-wrote the AppEngine process into a custom SQR that runs in about an hour:-- drop all indexes, then rebuild only the ones we need-- use “truncate table reuse storage”-- load fewer columns in the Fast Security table


12

Creating RPRD – Revised

• Run daytime SQL Cost Report (for reporting activity)

• Export ODS schema

• Create a RPRD as an exact image of PRD (using TimeFinder)

• Place DB in “restricted session” – lock out reporting users

• Import ODS schema

• Run RDS (and other) builds, run Fast Security

• Run overnight SQL Cost Report (for batch processing)

• Remove “restricted session” – make system available to reporters


13

Creating RPRD – Next steps

• We need a way to block users from “testing” in RPRD. Our next improvement will be to block users from getting into the reporting DB through the PeopleSoft and trying out fixes in a fresh copy of production.

• We think we’d get a performance gain from running RDS builds on UNIX.We haven’t had time to put this together…yet.

• We’d like to move the reporting database to a larger DB server.…particularly in anticipation of V8 and more RDS processing and more reporting users.


14

Creating RPRD On Time

…requires constant vigilance!

• Our application administrators are paged when the reporting builds do not complete on time.

• We monitor application availability and must provide an Incident Report when we do not achieve our application up-times. The goal is to acknowledge problems, discuss them, and to put mechanisms in place to avoid repeating them.

• We monitor CPU utilization on the reporting DB server, and anticipate upgrading in the near future – we need more CPUs to multi-thread our reporting builds.

• Our SQL Cost Reports show what SQL needs to be tuned;we try to tune proactively make the builds run as fastas possible.


15

Conclusion

-- Our implementation, and our ongoing administration, span several technical teams: DBAs, Storage, and UNIX/Windows System

Administrators, as well as the application team responsible for our reporting portal. The variety of expertise on the virtual team is essential.

-- Our reporting and transactional systems are available M-F 6am-12M, and Sat-Sun 8am-12M. To achieve this goal requires constant vigilance –

continual, proactive tuning.

Contact information:Carol Jordan

Technical Lead, Application SupportStanford University

[email protected]

HTTP://HEUG.ORG

rds administration & security

Documents