Pennsylvania Banner Users Group 2008 Fall Conference

1. Pennsylvania Banner Users Group 2008 Fall Conference Application Express Security BOF

2. General Announcements: • Please turn off all cell phones/pagers • If you must leave the session early, please do so as discreetly as possible • Please avoid side conversations during the session • Questions will be answered ….. Thank you for your cooperation

3. Swarthmore College • Katie Bourne • Frank Milewski • Ed Siegle

4. APEX BOF • Self-Service APEX authentication • Securing APEX applications • Workspace Management • Open discussion and questions

5. Our Info • Currently have approx 40 APEX apps in production • @ 15 of these are Self-Service • Examples of what we are using APEX for • Account Maintenance • ITS Request Tracker • Help Desk Call Center • Faculty Services • Finance Budget Reporting (Replaces Finance Self-Service) • Freshman “Portal” • Online directory on our college website (wouldn’t even know it’s APEX) • Various upload processes • Surveys • What about you?

6. Self-Service APEX Authentication • Why use APEX to deploy Self-Service applications? • Much of the coding complexity is automatically taken care of • Able to provide functionality you currently are not using • You can easily make a web form as robust as an Oracle Form • You can use Banner Security – no need to reinvent the wheel • APEX eases/improves development, debugging ,maintenance & security • Oracle is committed to APEX – lots of good things coming down the pike • Your users will notice the difference vs. flat PL/SQL pages • Easy to learn – was built with PL/SQL and Form developers in mind • http://forums.oracle.com/forums/forum.jspa?forumID=137. You can find/get the answer to virtually any question

7. Self-Service APEX Authentication Banner Self-Service sets a session cookie named SESSID in the users browser. In order to validate our APEX apps we needed to capture this cookie in the database. ALTER TABLE TWGBWSES ADD (TWGBWSES_CUSTOM_SESSID VARCHAR2(50)) Modify package body TWBKWBIS custom_cookie varchar2(50) default 'no_cookie‘; Directly after this code: IF (NOT cp_integrated_mode) THENOWA_COOKIE.send ('SESSID', twbkbssf.f_encode (webid || pidm));ELSEOWA_COOKIE.send (      NAME   => cp_cookie_name,      VALUE  => twbkbssf.f_encode (twbkauth.f_reconstructcpcookie (webid,pidm)),                         domain      => cp_cookie_domain,                         PATH        => cp_cookie_path);END IF; custom_cookie :=twbkbssf.f_encode (webid || pidm);         UPDATE twgbwses SET twgbwses_swat_sessid = custom_cookie          WHERE twgbwses_pidm = pidm;         COMMIT; • Every subsequent update of twgbwses where twgbwses_sessionid is set to a value set twgbwses_custom_sessid =custom_cookie. Conversely, every instance where twgbwses_sessionid is set to null set twgbwses_custom_sessid to null.

8. Self-Service APEX Authentication PROCEDURE pabug_demo IS pidm number(8); swat_sessid varchar2(50); BEGIN IF NOT twbkwbis.f_validuser (pidm) THEN RETURN; END IF; SELECT twgbwses_swat_sessid INTO swat_sessid FROM twgbwses WHERE twgbwses_pidm = pidm; htp.p('<SCRIPT LANGUAGE="JavaScript">window.location= "https://your schools apex url/f?p=174:901:::NO::MYSWAT_SESSID:'||swat_sessid|| '"</script>'); END; Define in WebTailor

9. Self-Service APEX Authentication

10. Self-Service APEX Authentication Make DATABASE the current Authentication Scheme to bypass the login page

11. Self-Service APEX Authentication Two Authorization Schemes will be used MYSWAT ENTRY is set on the initial page entry (901). This will check the database to see if the cookie exists and verify that user has the applicable role associated with the application. If these conditions all return true the SESSID cookie passed from Self-Service is set in the APEX domain. The user is validated and allowed access into the application. MYSWAT COOKIE VERIFICATION is set on all subsequent application pages. This will check the cookie against twgbwses to validate that self-service web session has not exceeded your institutional time out value.

12. Self-Service APEX Authentication • On your entry page (in this case 901)create an item to hold the SESSID. This item must have the exact name (in this case MYSWAT_SESSID) as the URL variable containing the Self-Service SESSID

13. Self-Service APEX Authentication Set the Entry Authorization Scheme and set the page Access protection to Unrestricted

14. Self-Service APEX Authentication Set the cookie (Before Header Process) BEGIN owa_util.mime_header('text/html', FALSE); owa_cookie.send( name=>'MYSWAT_SESSID', value=>lower(:MYSWAT_SESSID)); exception when others then null; END; Initialize PIDM and redirect BEGIN SELECT twgbwses_pidm INTO :PIDM_BEING_PROCESSED from twgbwses where twgbwses_swat_sessid = :MYSWAT_SESSID; :ENTRY_PIDM := :PIDM_BEING_PROCESSED; EXCEPTION WHEN NO_DATA_FOUND THEN :ENTRY_PIDM := null; :PIDM_BEING_PROCESSED := NULL; END; OWA_UTIL.REDIRECT_URL('f?p=&APP_ID.:1:'||:APP_SESSION);

15. Questions?

16. Securing APEX Applications • Turn Debugging off When debugging is turned on, the web application becomes subject to a Cross-Site Scripting (XSS) flaw. Specifically, the type of XSS vulnerability created by enabling debug mode in Apex is "non-persistent". With this type of vulnerability in Apex, Javascript could be made to execute in the context of the user's browser (unbeknown to the user). This is because Apex would *reflect* (or echo) the inserted Javascript back to the client's browser where it would get *executed*. The bottom line is that if an attacker can get Javascript to run in a user's browser, then it's game over. For example, by using simple Javascript commands like "document.getElementByID" or "document.getElementsByTagName" the user's ID and password can be obtained. More complex Javascript could actually manipulate data, read other pages (in the same Apex app), etc. This was all possible because debug=Yes causes Apex to echo back the Javascript to the user where it is then executed. Nick Hannon Information Security Analyst

17. Securing APEX Applications • Use Session state protection to prevent URL tampering • Always use bind variables to prevent SQL injection • Control what the user enters • Security starts at the database • Use Validations for security and “prettier” error messages • Make objects conditional where applicable • To prevent Cross-Site Scripting Make Standard Report Columns Display as text (escape special characters, does not save state) (This will be the default in 3.1 versions) • Use Authorization Schemes

18. Securing APEX ApplicationsAuthorization Schemes • You don’t want just anyone with an account to access your app • Authorization Schemes can be applied at the Application, Page, Region, Item levels and can be applied to virtually anything. • Use an Authorization Scheme at the application level to restrict access to your app to a custom defined list of users select distinct 'x' from gwvsecr where form_process_name = 'FAAINVE' and user_id = :APP_USER • Use Authorization Schemes on Items, Buttons and Processesto control who can update data and who has read only access select 'x' from gwvsecr where form_process_name = 'FAAINVE' and user_id = :APP_USER and role_granted = 'BAN_DEFAULT_M'

19. Questions?

20. Workspace Management • Self-Service requires WWW_USER to have applicable DB grants • In APEX the workspace parsing schema is what needs to have the applicable DB grants • APEX_PUBLIC user does not need any DB grants to Banner objects • Developers do not need any DB grants to Banner objects • End users do not need any DB grants to Banner • This gives you a lot of flexibility in how you set up your development environment

21. Workspace ManagementOption #1 • Create a master workspace, (ex: GLOBAL_APEX) along with a corresponding schema with the same name. Assign all the developers to GLOBAL_APEX. • PROS All applications will be stored in one central repository and will be accessible to all developers. All database grants need only be given to the GLOBAL_APEX schema which will carry through to the workspace. Developers do not need any additional grants. • CONS • GLOBAL_APEX, like WWW_USER, will end up having the keys to the kingdom. • The workspace will become large and difficult to determine what app is for what module.

22. Workspace ManagementOption #2 (If we knew then what we knew now) • Create a workspace and corresponding schema for each module/area of responsibility (APEX_STUDENT, APEX_HR, APEX_FINANCE, etc). Assign developers only to the applicable workspaces matching their responsibilities. • PROS • Applications will be located in their specific areas of responsibilities. Only the specific DB grants need to be given to the parsing schema, thus avoiding the WWW_USER scenario. • Since all DB grants are granted to the schema/workspace and not to the developer, developers can be reassigned areas of responsibilities simply by being removed from one workspace and added to another. • The auditors may actually like this.

23. Workspace ManagementOption #3

24. Open to the Floor • Questions • Comments • Ed Siegle esiegle1@swarthmore.edu