Tuesday, March 18, 2008

BSAR Specifications V.3

BSAR: Banner Security Access Request


The process starts with an authorized user logging into the site. The user may have one or more roles. The possible roles are User, Requestor, Coordinator, Functional Lead, and Banner Id Management (BIM).


These roles are defined as follows:

  1. User: Employee seeking access to Banner.

  2. Requestor: Employee’s boss.

  3. Coordinator: The contact person(s) authorized to make Banner access requests for a particular campus.

  4. Functional Lead (AKA Approvers): Individual(s) designated to review and authorize access to specific functions (i.e. Accounts Receivables, Fin.Aid, Student).

  5. Banner Id Management (BIM): Individual(s) that provide the technical support of establishing Banner access.


When an individual logs in, they may be directed to one of five locations depending on their role, or lack thereof. If the user has no role, they get an error message, otherwise they go to the page appropriate for their role. If a user has multiple roles, they will be directed to a page with tabs, where each tab handles a different role.


Here’s a high-level, typical-case, overview of the process as far as the site is concerned, details will be discussed in the appropriate section.

  1. A Coordinator receives a request (via email, etc) for a new account from a Requestor.

  2. The Coordinator logs in and fills out a form with the Requestor and User’s information, and submits the request.

  3. Approvers are notified, as appropriate, that they have pending requests.

  4. The Approver(s) log in and approve/deny the request.

  5. Once all approvers have responded as needed, the User receives an email asking them to log in and approve a FERPA notice.

  6. If the FERPA notice is approved, the BIM staff receive an email telling them an account request is pending.

  7. When the BIM staff mark the request as completed, a notification email is sent to the User, Requestor, and the Coordinator.


For now, that is the extent of the system, although hopefully we can add in the ability to create/edit the requested account from inside the site as well.


The following sections will go through the actions of each role in depth.






Coordinator:


After logging in, the Coordinator comes to a page with several options: create/modify account, view/edit a pending request, view all past requests, undo a past action, or view all past actions.


ToDo: add “show all past actions” form.


All pending requests (those which have not yet been marked as completed by BIM staff), are in the pending requests table. While all requests with completed status are found by clicking on the “View past requests” link.


The pending requests form shows the date the request was originally made, the full name of the user along with the campus association, the last action taken on the request and the date of that action, and a summary of the permissions requested. Note that names in the pending request table are links to detail pages about the user and their request. This is the same page the user is taken to should they click the Edit button in that row.


This page also contains a list of the most recent past actions taken by the current Coordinator. Each action has an undo button, and even undo’s can be undone. The Coordinator can also click the “View all past actions” link to get a comprehensive list.


Coordinator, Request New Account (c2):


To request a new account, the Coordinator enters the UH username of the User and clicks the “Create/Modify Account” button. The system checks to make sure this is a valid UH username and if the user has an existing account or not.


If the username is not found in either the BSAR database, or through an ldap lookup, the Coordinator will see an error message stating that there is no such UH username.


If such a username is found via ldap, but not in the BSAR database, the system knows this is a new account. The Coordinator will see the User’s information (username, first name, and last name), fields for the Requestor’s username (if different from User), classes/forms the User wants to access, and the campuses for those classes/forms, a means to mark the request as being urgent, and a field for entering comments. There is also a sub-form where the Coordinator can enter the username of another User and opt to copy that individual’s permissions for this account.

ToDo: Add form to show what happens when an account is copied.


If on reviewing the User’s information, the Coordinator discovers they have entered the wrong username, they click the “back” button to correct the mistake, otherwise, the user enters the Requestor username, selects the appropriate classes/forms and clicks “Submit”. If no classes or forms are provided, the default set will be assumed by the BIM staff.


Upon submission, the class/form fields are checked to ensure they are proper class/form names. If errors are found, the form is repopulated and displayed, along with an error message next to the offending entry. If all values are correct, the Coordinator goes to a confirmation page which displays a standard “Your request has been submitted” message, a summary of the request, and a button to edit the request should there have been any mistakes. At the same time the Coordinator is seeing the confirmation page, any necessary Approvers are being notified they have an action item.


Coordinator, Modify existing account (c2):


Changing a previously existing account starts off just like creating a new account: the Coordinator enters the UH username of the User and clicks the “Create/Modify Account” button. The username is verified and their account information is retrieved.


The Coordinator is directed to the same page as described in the “Create New Account” section above, but there are two differences: Along with the information and forms described for a new account, there are additional forms to show and modify existing and pending permissions.


Existing permissions consists of all classes and forms the User already has access to, the form level access (query or modify), and means to request removal of any, or all, of the permissions. This form is used only to modify or remove existing permissions, while new ones are entered below. Note: the existence of an existing permission form assumes that we can import existing account information into the new system’s database.


Pending permissions are requests for the current user that are not yet marked as completed by the BIM staff. If the request has already been forwarded to the BIM staff, no edits are allowed and changes must be done via a new request. This is because the BIM staff may have already executed the request, but not yet marked it as completed. If instead, the request has not yet been sent to the BIM staff, it is editable and the Coordinator can use this form to cancel pending requests or to go to a details page for editing. If the Coordinator made changes to existing permissions while setting new permissions, both parts are bundled into one request. Changes to pending permissions are handled as part of the pending request, and are not bundled with the others.

With the rest of this page, the Coordinator can create a new request. Upon submission, the request is checked to see if any approvals are required, and if so, the Approvers are notified. Since this is an existing account, the FERPA notice is already approved, so if no approvals are required, an email is sent directly to BIM staff.


ToDo: Should there be a button to “Cancel Account,” or is removing all permissions sufficient?


Coordinator, Change pending request (c2b):


If from the create/modify account page, the user selects the button to edit a pending request, they are taken to this detail page of the selected request. This page is only reachable when the request has not yet been forwarded to BIM staff.


From here, the Coordinator can cancel all or part of the request, or modify the requested permissions or urgency status. Note that no additions can be made to a pending request. Additions are considered a new request rather than a modification of an existing request. If the change switches a form from query to modify, and requires an approval, the appropriate approver is notified.


Approver, View Request (a1):


An Approver is notified of a pending request via an email containing a link to the site. When they log in, they see a table of all

pending requests, a table of the most recent past requests, and a link to all past requests.


In the pending request table, the columns are as follows: date request was made, date Approvers were notified (this is the column the table is sorted by), User’s information (username, first name, last name, and campus), a summary of the requested classes/forms that are under this Approver’s authority, two columns of option boxes (approve and deny) which the Approver can use to handle the pending requests immediately, and an Edit button.


Note that in selecting either the Approve or Deny option boxes and clicking save, the Approver will be marking all pertinent classes/forms in that request as approved or denied. If they want to approve part of the request, and deny part, they click the Edit button next to the request which takes them to the details page (a3, below).


The table of recent past requests contains those which the current Approver has approved or denied, but which have not yet been sent to the BIM Staff. As long as the request is in this state, it is editable, but once it is sent to BIM, the record is moved to the past entries table and is no longer modifiable.


The link to all past entries takes the user to a table of all requests which have been sent to BIM, marked as completed, or cancelled. This table looks exactly like the pending request table except it includes the date the request was marked as complete.


The View and Edit buttons above take the Approver to the request details page for that request. The details page shows basic information for the User, the Requestor, and Coordinator, comments, FERPA status, all existing permissions (previously approved requests), links to details of past requests, and the status of approvals for all classes/forms for this request, even those outside of the Approver’s authority.


While the details page shows all parts of the request, including those not relevant to this user, they are listed showing which Approver is responsible for each part along with the current status of that part: Pending, Approved, Denied, Cancelled.


For each form/class the Approver has authority for, they can do the following: approve/deny each individual part of the request, change the nature of a form (modify or query), and change the name of a class they will approve for the request. There is also a field into which the Approver can enter comments, such as explaining a change or denial. If the Approver makes changes to the request (either the class name or the form nature), then upon saving, an email is sent to the Requestor so they can examine the change and refute as necessary.


This page can also be used to change previously set permissions. For example, if an Approver has been convinced to change a denied request to approved, and the request had been marked as complete, the request status would be changed to reopened and an email would be sent to the BIM staff asking them to add the newly approved permission.


When the Approver either approves or denies a request, and clicks the Save button, the request is removed from the list of pending requests and added to the list of recent past requests.


Once all Approvers have responded, providing at least part of the request was approved (and if/when the FERPA is approved for a new account), an email is sent to the BIM staff notifying them of a pending request. If any Approvers denied the request, an email is also sent to the Requestor and Coordinator so they can look into it.


User, FERPA notice (r1):


When a request is submitted and approved (as necessary), an email is generated and sent to the User asking them to click on a link to login and approve the FERPA notice.

If there is no response, the email is sent two more times, at intervals of one week each. If there is no response one week after the last notice is sent, an email is sent to the Coordinator for them to decide on the next action.


When the user logs in, they are shown the FERPA notice, along with three buttons: Approve, Decline, and Cancel Request. If the User declines the FERPA notice, they are prompted with a warning telling them their Request will be cancelled if they do not accept. Should they continue, the request is marked as cancelled, and the Coordinator is informed. This is the same scenario for Cancel Request. Finally, if and when the user accepts the FERPA notice, the system checks to see if any approvals are required, and if so, notifies the appropriate Approvers. If no approvals are required, the request is sent directly to the BIM staff.


BIM (Banner ID Management):


When all necessary approvals have been obtained, the BIM staff receive an email providing a link to the site. When the BIM staff log in, they see a table with all pending requests. The table contains the following columns: date request made, date of BIM notification (sort column), type of request (new, modify, remove, etc), User’s info (username, first name, last name, and campus), Coordinator’s information (name and campus), and the classes/forms requested. Should the BIM staff require more information about the request, each username in the table is a link that will take the staff member to a details page containing all the same information seen on the Approver’s user details page.


Each request in the table also has two option buttons: one to deny the request (only exists when the forms/classes require approval from the BIM staff), and another to mark the request as completed. If everything is approved, the BIM staff give the user access to the desired forms/classes and marks the request as finished by selecting the “Completed” option and clicking the “Save” button. If instead, the denial option is selected, the Coordinator is notified.


The past requests table shows all requests that BIM staff have marked as completed or denied. It shows the date the request was made, the date it was completed/denied, the User’s username, firstname, lastname, and campus, the forms and classes they requested, and the final status (completed/denied). Clicking on the view button will take the BIM staff member to a details page showing all information for the request.


The BIM staff receive a daily “termination report” of people who no longer work for U.H.. In response to getting this email, they check to see if those people have Banner accounts, and if so, they send an email to the Coordinator to determine is the account should be terminated. If the account needs to be closed, the Coordinator make a new request to remove all permissions. Since no approvals are needed, the BIM staff receive the request directly and cancel the account.


ToDo: If an error is found in a completed request. Who should edit it? And how?


Feature Set:


Bold Items: These are features required for version one, these are the minimum working set.

Underlined items: Version two features.

All others are for future versions and are in order of priority.


All users:

  1. Login/Logout


Coordinators:

  1. Submit new account request

  2. Submit change account request

  3. Cancel existing request

  4. Get notification of completion

  5. Get notification of denial

  6. Get notification of user or requestor cancellation

  7. Mark request as urgent

  8. Submit change to existing request

  9. Copy another person’s permissions to save data entry


Users:

  1. Send email for FERPA

  2. Accept/Decline FERPA

  3. Cancel request


Approvers:

  1. Get notification of pending request

  2. Approve/Deny request

  3. Change approval/denial of pending/completed requests

  4. Edit pending requests


BIM:

  1. Get notification of pending request

  2. See status (approvals, etc)

  3. Mark request as completed or denied


Future feature: Search. Will need to be able to page through the results as well. The search feature should be searchable by user name and by the involved class/form (eg: who requested access to form ‘x’).


Changes:

V.2 -> V.3

1.) Changed process from Coordinator->FERPA->Approvers->BIM to Coordinator->Approvers->FERPA->BIM.

2.) Added last paragraph to BIM section describing the process for removing an account.

3.) Added ability for Approver to edit the request (change class name or access type for forms). Includes email notification to the Requestor.

4.) Added first/last name and campus to all forms.

5.) Added means of undoing past actions for a Coordinator.

6.) Replaced Edit button with View in BIM staff past requests table.

7.) Added a new role “User” which is what Requestor used to be. Requestor has been redefined as the boss of User and the person who likely asked the Coordinator to make the new account.

8.) Added means for a Coordinator to request a copy of another person’s account.


V.1-> V.2

1.) Changed the coordinator form to get rid of the checkboxes for selecting classes and replace them with fields. It’s more efficient for the users given their subject matter familiarity.

2.) Merged c2 (add to existing user) and c2a (edit existing permissions) because it seems odd to have the user add in new permissions while not being able to see what the existing ones are without going to another page. This has the side affect of allowing multiple changes (new permissions, and changes to old ones) to be submitted in one shot, which is more efficient for all involved.


3.) Deleted question regarding if one user can hold multiple roles, as the answer is "Yes."


4.) Changed handling of a Coordinator submission where no classes/forms are entered. Instead of triggering an error, a default set of permissions is assumed.

Thursday, January 17, 2008

Questions

After the meeting on Monday, I've been re-writing the specs, and I have a few questions I need answered. Anyone who has an answer to any of these is requested to leave their answers in the comments section of this post. Be sure to include the number of the question to which you are responding. The questions are broken up as they pertain to user roles.

Note, to see an enlarged version of any image, right-click on it and select the option to open it in a new tab or window.

Approvers/Functional Leads

1.) What circumstances would cause you, acting as an Approver/Functional Lead, to edit a request?

2.) Given question 1, what sort of edits do you make?

3.) Given question 1, what is the current process for making such edits? For example, do you email the Coordinator to tell them you made the change? Do you just forward it on to the Banner Id Management people?



4.) What information do you need to see in the summary table of requests pending your approval?

5.) What information do you need to see in the summary table of past handled requests?



6.) What information do you need to see in a detailed listing of a request? Currently, I'm planning on showing the User's information, the Coordinator's information, a listing of all past requests, a listing of all current permissions, the status of other Functional Leads' actions on this request, a history of all actions of this request, and the form through which you Approve/Deny the individual parts of this request. Too much information? Not enough? what else?

Coordinators

7.) A request was made for giving the ability to copy another user's permissions. Does this ever happen in the current process? For example, do you ever dig up an old email to copy the permission requested? Or do you ever tell the BIM staff "Make the permissions like so-and-so?" How often would this feature likely be used?



8.) What would be the most useful information to include in a summary table of pending requests?

9.) What information do you need to see regarding the details of a request? Right now I have: basic user information (name, contact info), date the request was made, approval status, ferpa status, comments, and the list of forms/classes requested. Anything else?

Banner Id Management (BIM)

10.) BIM staff get termination reports which cause them to immediately cancel an account. When this occurs, are there any other actions taken? Such as notifying a Coordinator, or sending email to the affected User?

11.) Given the previous question, are there any other "back-door" actions that the BIM staff make?



12.) What information do you want to see in the summary table of pending requests?

13.) What information do you want to see in the summary table of past requests?

14.) Would you ever need to edit a completed request? I don't mean editing an account, I mean a specific request. Under what circumstances would you edit it?

Monday, January 14, 2008

Meeting notes

Met with the entire Banner team today to give an overview of the system. Many changes need to be made.

1.) Add a means to copy another person's access permissions to a new user. Will save entering all the classes/forms when you know of a user who you can just copy.
2.) Add a means to set the campus for the request.
3.) What happens when there are multiple campuses, each requiring different access? Separate requests?
4.) Need a means to consolidate all approver/bacu reminders into a single daily email. Must provide means to indicate a request is urgent and should be handled individually rather than batched.
5.) Add means for BACU staff to enter back door modifications. They get termination reports and immediately go in and close off those person's access. Need means for them to indicate that in the system.
6.) Approvers need a mechanism to modify a request. Apparently they do this regularly.
7.) Need to re-think how the process works regarding an Approver changing their approval/denial.

Thursday, January 10, 2008

Presentation

Here are the slides for the presentation to the Banner staff on Monday, 1/14/08 at 2 pm.

Wednesday, January 2, 2008

BSAR Database Tables and Fields V.2

USER:

General user information, name, uh_username, etc.

Initial size: TODO

Anticipated growth: TODO

References: N/A

Referenced by: bsar_user_role, approver_domain, request, request_comments, requestor, request_object_action, banner_permission

Fields:

id: PK auto-generated

first_name: varchar(50), not null

last_name: varchar(50), not null

uh_username: varchar(8), not null


BSAR_USER_TYPE:

Tells what type of users exist in the bsar system, excluding requestors. Is essentially a three record table with the values of the description fields being Coordinator, Approver, and BACU.

Initial size: 3

Anticipated growth: none

References: N/A

Referenced by: bsar_user_role, request_comments

Fields:

id: PK auto-generated

description: varchar(11), not-null


BSAR_USER_ROLE:

Associates users with roles in the BSAR system, this tells if the user is a Coordinator, Approver, and/or BACU. Handles the case where a user has multiple roles. Requestors are not in this table.

Initial size: TODO

Anticipated growth: TODO

References: user, bsar_user_type

Referenced by: N/A

Fields:

user_id FK user.id, not null

bsar_user_type_id FK bsar_user_type.id, not null


CAMPUS:

Place to store campus short and long names so they aren’t replicated all over the db.

Initial size: 10

Anticipated growth: none

References: N/A

Referenced by: request_campus

Fields:

id: PK auto-generated

code: char(3), not null, the three letter code for a campus, like WCC, MAN, etc

description: char(20), not null, the full name of the campus


REQUEST:

The general information of a request: who wants access, who filed the request, if the request is marked completed, etc.

Initial size: TODO

Anticipated growth: TODO

References: user

Referenced by: request_object, request_comments, request_campus

Fields:

id: PK auto-generated

coordinator_id: FK user.id, not null

requestor_id FK user.id, not null

completed: boolean, not null

Index on coordinator_id

Index on requestor_id


REQUEST_CAMPUS:

Groups together a request with all campuses to which it applies.

Initial size: 0

Anticipated growth: TODO

References: request, campus

Referenced by: N/A

Fields:

request_id: FK request.id, not null

campus_id: FK campus.id, not null

constraint PK on request_id, campus_id

index on request_id


REQUEST_COMMENT:

May be overkill, but this allows multiple user types to make comments. Since approvers may want to make a comment about the entire part of the request they have control over, the comment section in the request_object_action table isn’t enough.

Initial size: 0

Anticipated growth: TODO

References: request, user, bsar_user_type

Referenced by: N/A

Fields:

id: PK auto-generated

request_id: FK request.id, not null

date: date, not null

comment: varchar(2000), not null

commenter_id: FK user.id, not null

commenter_role: FK bsar_user_type.id, not null, needed because if a user has 2+ roles, which role did the make the comment as, it makes a difference in terms of how it’s displayed.

index on request_id.


REQUESTOR:

Tells if a user has filled out a ferpa notice or not.

Initial size: TODO

Anticipated growth: TODO

References: user

Referenced by: N/A

Fields:

user_id: PK FK user.id, not null

ferpa_sent_date: date, holds last sent date if repeated, null if not sent

ferpa_action_date: date, null if user hasn’t responded

ferpa_approved: boolean, null until user responds

ferpa_resend_count: int, null until sent. number of resends, one week apart


EMAIL:

Holds the different emails that are sent out (ferpa, approver notice, bacu notice, etc) along with resend info (like resend ferpa once a week, for three weeks if not answered). This table will likely disappear in favor of files for the emails, and a properties file for the limit and delay settings, still, I figured I’d put it here so it doesn’t get forgotten.

Initial size: 1

Anticipated growth: none

References: N/A

Referenced by: N/A

Fields:

ferpa_resend_limit: int, not null, number of times to re-nag user

ferpa_resend_delay: int, not null, number of days between resend attempts

ferpa_notice: varchar(?), not null, Text of the first and interim emails to send

ferpa_notice_final: varchar(?), not null, Text of the final email to send

coordinator_ferpa_notice: varchar(?), not null, Email text sent to coordinator when no ferpa obtained.

approver_notice: varchar(?), not null, text of email sent to approvers when action is required.

bacu_notice: varchar(?), not null, Text of email sent to BACU staff when action is required.


BANNER_FORM_ACCESS_TYPE:

Tells the possible access types for a banner form, basically a two-entry table: query and modify.

Initial size: 2

Anticipated growth: 0

References: N/A

Referenced by: request_object

Fields:

id PK auto-generated

code: char(1), not null, shorthand for description, values will be m or q.

description: varchar(6), not null, values will be query or modify.


BANNER_OBJECT_TYPE:

The types of banner_objects, basically a two-entry table: classes and forms

Initial size: 2

Anticipated growth: 0

References: N/A

Referenced by: banner_object

Fields:

id: PK auto-generated

description: varchar(5), not null (values will be class or form)


BANNER_OBJECT:

A listing of all banner classes and forms.

Initial size: TODO

Anticipated growth: TODO

References: banner_object_type

Referenced by: banner_permission, request_object

Fields:

id: PK auto-generated

description: varchar(20?), not null (name of class or form)

type_id FK banner_object_type.id, not null


REQUEST_OBJECT:

Shows what objects were asked for w/ a given request. Groups together all the classes/forms that were asked for in a given request.

Initial size: 0

Anticipated growth: TODO

References: request, banner_object

Referenced by: request_object_action

Fields:

id: PK auto-generated

request_id FK request.id, not null

banner_object_id: FK banner_object.id, not null

access_type: FK banner_form_access_type, only filled in if banner_object is type form, null if is type class

index on request_id

index on banner_object_id


ACTION_TYPE:

The different actions that can happen to a request, or objects in a request, like cancel, approve, deny, create, delete, change to modify/query status, etc.

Initial size: < 10

Anticipated growth: 0

References: N/A

Referenced by: request_object_action

Fields:

id: PK auto-generated

description:varchar(10?), not null


REQUEST_OBJECT_ACTION:

A history of events for each object in a request. Like if permission for an object was initially denied, then approved, then modified, and finally revoked. It’s all here along with who and when for accountability purposes.

Initial size: 0

Anticipated growth: TODO

References: request_object, action_type, user, bsar_user_type

Referenced by: N/A

Fields:

id: PK auto-generated

request_object_id: FK request_object.id, not null

action_type_id: FK action_type.id, not null

action_user_id: FK user.id, not null

action_user_type: FK bsar_user_type.id, not null

action_date: date, not null

action_comment: varchar(2000)

index on request_object_id



BANNER_PERMISSION:

Tells which banner objects to which a user already has access.

Initial size: TODO

Anticipated growth: TODO

References: user, banner_object

Referenced By: N/A

Fields:

user_id FK user.id, not null

object_id FK banner_object.id, not null

form_access_type_id FK banner_form_access_type.id, can be null if object is type class, gets set if object is type form.

constraint PK: user_id, object_id

index on user_id



APPROVER_DOMAIN:

For users with approver roles only, tells which classes and forms they have approval power over.

Initial size: TODO

Anticipated growth: TODO

References: user, banner_object

Referenced by: N/A

Fields:

user_id FK user.id, not null

banner_object_id FK banner_object.id, not null

constraint PK user_id, banner_object_id

index on user_id



BANNER_FORM_ACCESS_TYPE:

Tells the possible access types for a banner form, basically a two-entry table: query and modify.

Initial size: 2

Anticipated growth: 0

References: N/A

Referenced by: request_object

Fields:

id PK auto-generated

code: char(1), not null, shorthand for description, values will be m or q.

description: varchar(6), not null, values will be query or modify.

BSAR Database Diagram V.2

To see the diagram in full-size, right-click on the image and select the option to open in a new window.

The change from V.1 is a fix because a request can apply to multiple campuses.  To fix this, I added the request_campus table which aggregates the request with all pertinent campuses, and removed the campus field from the request table.


Monday, December 31, 2007

BSAR Database Diagram V.1

To see the diagram in full-size, right-click on the image and select the option to open in a new window.