2007 Pre-Summit Workshop & User Cooperative Admin and Security Track Bruce Knox University of Arkansas Division o

1. 2007 Pre-Summit Workshop & User Cooperative Admin and Security Track Bruce Knox University of Arkansas Division of Agriculture Cooperative Extension Service

2. Pre-Summit Admin and Security Track Copyright 2002 Cision Studios Mad Tea Party

3. Argos Pre-Summit Admin and Security Track How are you supporting your users? What tools have you developed? What works for you? Got some code to share?

4. Topics and Questions? Argos and Banner Security Banner Value Based Security Create a CSV Import File for creating MAPS Users Reports that help our security administrators.    I need to know when a user is set up in Argos, but is no longer an employee? Using Argos with Operational Data Store (ODS)? How to handle continuous updates from Evisions?  How to handle problems with using LDAP server on Argos? Implementation of any of the various Banner Security models in Argos?  How to approach end-user account creation and security?

5. Argos and Banner Security

6. Argos and Banner Security Banner Reporting ultimately requires an Oracle grant for the Tables being used.

7. Argos and Banner Security Within Banner: Grants are provided for the Forms or other Objects via a complex password authentication and obscuration scheme. This Banner Security relies upon changing the user’s default role to the role set up in GSASECR, User Maintenance, for specific Banner Objects.

8. Argos and Banner Security So, unless you are set up in a Payroll Role, you cannot get into the Payroll Objects. This works fine for Banner provided Objects.

9. Argos and Banner Security This same scheme is how the “Argos and Banner Security” Script works. An USR_ARGOS User Class Object is created and it is assigned to specific users. When the user logs in to Argos, the user is authenticated using this User Class. This depends upon the Argos and Banner User ID being the same.

10. SQL*Plus Scripts This same concept is used by many Banner sites for their SQL*Plus scripts. In this use, each SQL*Plus script is paired to an Object name which is passed to a “Banner Security Script” similar to the “Argos and Banner Security” Script. This allows some improvement in security by allowing only specific Tables to be used in a SQL*Plus script. The End-Users are limited to seeing only certain objects. The exposure of these scripts is limited by the Operating System’s security and to a degree by obscuration.

11. Argos and Banner Security The “Argos and Banner Security” Script is exposed only to the Argos Administrator.

12. Argos and Banner Security The “Argos and Banner Security” Script allows for the Banner User ID to be used to authenticate Argos Users. Once past this point, the question becomes: How to limit the users to seeing only information they should see.

13. Argos and Banner Security One can use the Argos Security to allow only specific users into Argos Folders set up by Functional Areas. This works and it keeps the user from even seeing the contents of other Folders, if desired. Alone, this does not limit the access to the Tables for the Designers.

14. End-User Ad Hoc reporting Since the Banner Security concept relies upon the Form or Procedure source code for part of the security, it is not suitable for End-User Ad Hoc reporting.

15. Ad Hoc Reporting Security Solutions vary, but often fall into two categories: GRANT specific “SELECT ON Table” to a User or GRANT “SELECT ANY TABLE” to a User

16. Ad Hoc Reporting Security Solutions vary, but often fall into two categories: GRANT specific “SELECT ON Table” to a User (Very DBA Intensive) or GRANT “SELECT ANY TABLE” to a User (Very Insecure).

17. Argos and Banner Security plus Oracle Role Another way to handle this is to use an Oracle Role directly instead of the Banner Classes. (Note that I am still using the “Argos and Banner Security” Script to limit which Users can actually Logon to Argos.) The Role further limits access after this Connection Login.

18. Oracle Roles A Role is a collection of grants. The Role can be assigned to specific users. While this is semi-DBA Intensive, it is normal stuff for them. They are in Control.

19. Argos and Banner Security using Oracle Roles We would create a new BAN_DEFAULT_BANNERARGOS role for Argos table access, then: GRANT SELECT ON owner.table_name TO BAN_DEFAULT_BANNERARGOS; GRANT SELECT ON owner.view_name TO BAN_DEFAULT_BANNERARGOS;

20. Argos and Banner Security using Oracle Roles Then, you revoke "SELECT ANY TABLE" privileges from those users and assign the BAN_DEFAULT_BANNERARGOS role to them instead. Since this would be done through roles instead of classes, the “Banner Security Script” wouldn't be used (since it only deals with Classes).

21. Argos and Banner Security using Oracle Roles This would allow you to reduce the security effort for the Argos Administrator since exposing an Argos Folder or DataBlock would still not give the User access to the underlying Banner Tables. I prefer to view Argos Security as a further limit upon the Banner and Oracle Security rather than the mainstay of my security for Argos users.

22. Security for Any Ad Hoc Reporting Tool The BANNERARGOS role could be used for any Ad Hoc Reporting Tool. This idea is far simpler than any other scheme I have seen for providing security for Ad Hoc Banner Reporting.

23. Banner Value Based Security

24. Banner Value Based Security This is the “Fund/Orgn” security that allows one to see just their Funds and Orgns when running a report.

25. Banner Value Based Security There are basically two ways to handle this. One is to put the restricting logic into each Argos DataBlock. The other, more secure way, is to put the logic into Oracle Views.

26. Banner Value Based Security Once you are into Oracle Views of the Banner Tables, you are confronted with the Banner naming conventions and their exceptions. While PEVEMPL is a view for PEBEMPL, FTVORGN is a Table.

27. Banner Value Based Security This security is based upon the User ID and the values in the FOBUSFN and FOBUSOR Tables. (Forms used to update Fund/Orgn security are FOMUSFN and FOMUSOR.)

28. Create a CSV Import File for creating MAPS Users

29. SQL*Plus script to create a CSV Import File for creating MAPS Users:maps_users.sql To use this, one must have the Users already assigned to the Argos Security Class Object.  Which is a reasonable expectation, I think.Description is always Finance, that is easily changed.  (Determining Department assignments can be a difficult Banner issue.) The password for the Users' first logon to MAPS will be their Banner User ID + the last four digits of their SSN Be sure to check mark the "User must password change at next login" when Importing the file so that the User can sync their Banner and MAPS password.  (Relates to using "Argos and Banner Security" option.)

30. SQL*Plus script to create a CSV Import File for creating MAPS Users: SELECT gurucls_userid Sort_Name, '"MAPS","'||LOWER(gurucls_userid)||'","'||LOWER(gurucls_userid)||SUBSTR(spbpers_ssn,6,9)||'","Finance","'||TRIM(goremal_email_address)||'"' FROM goremal, gurucls, spbpers, gobeacc WHERE gurucls_class_code = 'ARGOS' -- Your Argos Security Class Object AND goremal_pidm = spbpers_pidm AND goremal_status_ind = 'A' AND goremal_preferred_ind = 'Y' AND gobeacc_pidm = goremal_pidm AND gurucls_userid = gobeacc_username ORDER BY Sort_Name; http://www.uaex.edu/bknox/maps_users.sql

31. Topics and Questions? Slide 5. Argos and Banner Security Slide 23. Banner Value Based Security Slide 28. Create a CSV Import File for creating MAPS Users

32. 2007 Pre-Summit Workshop & User Cooperative Admin and Security Track Bruce Knox bknox @t uaex.edu http://www.uaex.edu/bknox/BannerArgos.htm University of Arkansas Division of Agriculture Cooperative Extension Service Book Dragon copyright 2006 J. Wilson Spence,Cision Studios Images are used with the permission of J. Wilson Spence.