RDS Administration & Security

1. RDS Administration & Security Session #396 Monday, 3/17/2003 11:45am HEUG 2003 Conference - Dallas

2. Carol Jordan Technical Lead, Application Support Stanford University Kevin Dale Functional Analyst, Application Support Stanford University Session #396 - RDS Security & Administration

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 Session #396 - RDS Security & Administration

4. Synopsis of PresentationDescription 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 Session #396 - RDS Security & Administration

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 Session #396 - RDS Security & Administration

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 Session #396 - RDS Security & Administration

7. Technical Infrastructure – Graphical overview Session #396 - RDS Security & Administration

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… Session #396 - RDS Security & Administration

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. Session #396 - RDS Security & Administration

10. Creating RPRD – Getting better • 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. Session #396 - RDS Security & Administration

11. Creating RPRD – Getting better • 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 Session #396 - RDS Security & Administration

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 Session #396 - RDS Security & Administration

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. Session #396 - RDS Security & Administration

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. Session #396 - RDS Security & Administration

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 JordanTechnical Lead, Application SupportStanford Universitycarol.jordan@stanford.eduHTTP://HEUG.ORG Session #396 - RDS Security & Administration