Transcript Session Title - Seattle University

Don’t Show Me the Money
Colleague GL User Security for
SQL data in the ODS
Presented by: Steve Mason & David Morelli
Pacific University Oregon
April 8, 2014
Session ID 1937
Session Rules of Etiquette
• Please turn off your cell phone/pager
• If you must leave the session early, please
do so as discreetly as possible
• Please avoid side conversation during the
session
Thank you for your cooperation!
2
About Pacific University
• Pacific University Oregon (Ory-gun)
– Founded in 1849 for Oregon Trail Orphans
– Small, private university with campuses in Forest
Grove, Hillsboro and Eugene, Oregon
– 5 Colleges; Arts & Sciences, Optometry, Education,
Health Professions, Business
– 3,600 FTE, Undergraduate & Professional, split 50/50
– Colleague since 1993, ODS 2008, Recruiter 2011.
– Mascot is Boxer, a Chinese qilin (dog-dragon)
3
Introduction
• Learn how to secure ODS views using schemas and GL account
access using GLROLES from Colleague. This click by click
presentation gives you all the techniques to use create the
necessary ODS source or target transforms, create SQL schemas
and populate them using Windows user groups, build the SQL
function to check account access using GLROLES and then use a
tool like MS Access to view the results.
• All levels of users can take the PPT to their IT shop and have
Colleague GL security in the ODS in a few hours.
• A Colleague SQL shop can use this technique with the ODS
because it uses native SQL tables, not ODS Target Transforms
4
Agenda
We will provide click by click instructions for the following topics:
• Add the GL Roles and GL Users tables to the ODS download
• Create the Security User Group that will use the schemas
• Inform the ODS Database Server that the User Group exists
• Create RSGL and Budget Schemas
• Create Views for the Schemas
• Connect Users to the ODBC
• Connect Access Database to ODS via the ODBC
5
Add the GL Roles and
GL Users tables to the
ODS download
COLLEAGUE
7
8
9
10
Create the Security User
Group that will use the
schemas
MS SQL ADMIN
12
13
14
15
16
17
18
Inform the ODS
Database Server that the
User Group exists
ODS DATABASE
20
21
22
23
24
25
Create RSGL and
Budget Schemas
ODS DATABASE
27
28
29
30
31
32
33
34
35
Create Views for the
Schemas
ODS DATABASE
37
38
39
40
41
Connect Users to the
ODBC
USERS
43
44
45
46
47
48
49
50
51
52
53
Connect Access
Database to ODS via the
ODBC
USERS
55
56
57
58
59
60
61
62
63
Summary
• This is a low cost solution for distributed
reporting
• It can be used for Colleague SQL shops
against their native tables or a mirror
• The Schema security is used for all our
views
64
Questions & Answers
65
Thank You!
Steve Mason & David Morelli
[email protected]
[email protected]
Please complete the online session evaluation form
Session ID 1937
.
© 2014 Ellucian. All rights reserved.
66