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

2007 Pre-Summit Workshop
&
User Cooperative
Admin and Security Track
Bruce Knox
University of Arkansas
Division of Agriculture
Cooperative Extension Service
Admin and Security Track
Pre-Summit Admin and Security Track
Copyright 2002 Cision Studios Mad Tea Party
Admin and Security Track
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?
Admin and Security Track
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?
Admin and Security Track
Argos and Banner Security
Admin and Security Track
Argos and Banner Security
Banner Reporting ultimately requires an Oracle grant for the Tables being used.
Admin and Security Track
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.
Admin and Security Track
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.
Admin and Security Track
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.
Admin and Security Track
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.
Admin and Security Track
Argos and Banner Security
The “Argos and Banner Security” Script is exposed only to the Argos Administrator.
Admin and Security Track
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.
Admin and Security Track
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.
Admin and Security Track
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.
Admin and Security Track
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
Admin and Security Track
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
Admin and Security Track
(Very Insecure).
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.
Admin and Security Track
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.
Admin and Security Track
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;
Admin and Security Track
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).
Admin and Security Track
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.
Admin and Security Track
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.
Admin and Security Track
Banner Value Based Security
Admin and Security Track
Banner Value Based Security
This is the “Fund/Orgn” security that allows one to see just their
Funds and Orgns when running a report.
Admin and Security Track
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.
Admin and Security Track
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.
Admin and Security Track
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.)
Admin and Security Track
Create a CSV Import File for creating MAPS Users
Admin and Security Track
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.)
Admin and Security Track
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
Admin and Security Track
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
Admin and Security Track
2007 Pre-Summit Workshop
&
User Cooperative
Admin and Security Track
Book Dragon copyright 2006 J. Wilson Spence, Cision Studios Images are used with the permission of J. Wilson Spence.
Bruce Knox bknox @t uaex.edu http://www.uaex.edu/bknox/BannerArgos.htm
University of Arkansas
Division of Agriculture
Cooperative Extension Service
Admin and Security Track