PENNERA_SECURITY Table - Data Element Index   Tables and Data Elements   PennERA Proposals Home   Data Warehouse Home

PENNERA_SECURITY Table

DWSP Schema

Explanation
Stores information about PennERA users' security profiles for the Proposal modules. This includes their roles and the orgs. whose records they are authorized to access.

There is one PENNERA_SECURITY record per person, role, and org. code.

Common Uses

• Checking a PennERA user's security profile for the Proposal modules. ("What proposal data is Lee Smith authorized to access in PennERA?")
• Listing the PennERA users that have access to specified data in the Proposal modules. ("Who are the Module Administrators? Who has TOPU access (can see records for all orgs., including external orgs.)?")
  

The table does not have 
a physical PK.  The 
logical PK is the 
combination of the 
following columns: 
ERA_IDENTIFIER
ROLE_ID
ORGANIZATION_CODE

ORGANIZATION_CODE
PENN_ID
ROLE_NAME

PENNERA_FUND_ADJ
PENNERA_INCREMENT
PENNERA_INCR_STAT_HIST
PENNERA_PEOPLE
PENNERA_PERIOD
PENNERA_PROPOSAL
PENNERA_PROPOSAL_ACTIVITY
PENNERA_PROPOSAL_INVESTIGATOR
PENNERA_PROPOSAL_SUMMARY
PENNERA_PROP_STAT_HIST
PENNERA_REQUEST
PENNERA_REQ_STAT_HIST

Cautions

• PENNERA_SECURITY stores information on the secured access roles that have been assigned to specific users of the Proposal modules in PennERA.  It does not store information on the users’ default access privileges for the Proposal modules.  (By default, if the user is the proposal’s PI, they can access all of that proposal’s data in PennERA.  All PennERA users—faculty, staff, and students—have this default access.)
• PENNERA_SECURITY was developed to enable users of the Warehouse's PennERA Proposals data collection to get information on the security profiles for users of PennERA's Proposal modules. Although the list of related tables shown above includes many that may be joined to PENNERA_SECURITY, the table is intended to be used only with PENNERA_PEOPLE and some General Ledger reference tables (ORG_CODES and PARENT_ORG_CODES).
  • Business Objects users that wish to use the PENNERA_SECURITY data will find it (in the PennERA Security folder) in the PennERA Proposals and PennERA Proposals - DWHE Universes.
  • A BusinessObjects query that uses any data elements in the PennERA Security folder and/or its subfolder (PennERA Security - child orgs. (SEC+)) should not use any data elements from any other folder or sub-folders.
• Those who have University-wide access to the PennERA Proposals data collection may see all of the records in PENNERA_SECURITY. Those with Organization-based access may see a PENNERA_SECURITY record only if they are authorized to access data for the ORGANIZATION_CODE.
• A person may have any number of PENNERA_SECURITY records, depending on whether they are a PennERA user, how many secured access roles they have for the Proposal modules, and how many ORGANIZATION_CODEs are associated with their role(s).
• PENNERA_SECURITY has two different unique identifiers for a person: PENN_ID (the 8-digit identification number assigned to the person by the Penn Community system) and ERA_IDENTIFIER (the person's unique identification code assigned by, and used internally by, the PennERA system). In a given record, PENN_ID will not have the same value as ERA_IDENTIFIER. PENN_ID is the person identifier that is recommended for use by those querying the PENNERA_SECURITY table; ERA_IDENTIFIER is not recommended.
• The ORGANIZATION_CODE specifies the org. whose records the person can access in PennERA's Proposal modules according to the privileges associated with the role.
  • If the ORGANIZATION_CODE's value consists of 4 numerals, the code refers to a child org.--a single org. with no descendants in the org. hierarchy. In PennERA's Proposal modules, the values stored in org.-related data elements (such as the proposal responsible org.) are child orgs.
  • If the ORGANIZATION_CODE's value includes any letters, the code refers to an org. parent or rollup group, which includes one or more other orgs. The person can access the records for all of the ORGANIZATION_CODE's descendants. For example, if the ORGANIZATION_CODE is IDOM (SL-Center for Sleep Parent), the person can access records for orgs. 4638 (ID-Institute for Diabetes, Obesity and Metabolism), 4642 (ID-Translational Center of Excellence in Type 1 Diabetes), 4797 (ID-Stable Isotope TracerKinetic), and 4805 (ID-Islet Cell Biology Core).
    • The code for the org. parent that corresponds to a school/center is ##XX, where ## is the school/center code. For example, if the ORGANIZATION_CODE is 06XX, the person has access to all of the records for the School of Nursing (school/center code 06).
  • To see whether a person has access to data for a given org.:
    • If you are editing a query in BusinessObjects, open the PennERA Security folder, let your cursor hover over Organization Code (SEC), and follow the directions in the pop-up window.
  • If you are not using BusinessObjects, join DWSP.PENNERA_SECURITY s to DWADMIN.PARENT_ORG_CODES p as follows:
    s.ORGANIZATION_CODE=p.PARENT_ORG(+)
    This is an outer join because some of the ORGANIZATION_CODE values in PENNERA_SECURITY are not PARENT_ORG values.
• To see whether the PennERA user has access to data for a child org. (5802 in this example), use a WHERE statement like this:
  s.ORGANIZATION_CODE= '5802' OR p.CHILD_ORG= '5802'
• To see whether the PennERA user has access to the data for all of the descendants of an org. parent or rollup group (CANC--CC-Cancer Center--in this example), use a WHERE statement like this:
  s.ORGANIZATION_CODE= 'CANC' OR p.CHILD_ORG IN (SELECT CHILD_ORG FROM DWADMIN.PARENT_ORG_CODES WHERE PARENT_ORG = 'CANC'
• Beware using WHERE statements that refer to the school/center code or name associated with the ORGANIZATION_CODE. For example, if you retrieve PENNERA_SECURITY records for school/center code 58, you will get records for some of the people who have access to any (not all) of the data for the School of Veterinary Medicine. Your results will include people who have access to the data for all of the school's orgs. (ORGANIZATION_CODE 58XX). However, your results will also include people who have access to the data for just one of the school's orgs. (such as ORGANIZATION_CODE 5802), and your results will omit people who have access to all of the data regardless of org. (ORGANIZATION_CODE TOPU). To get a list of people who have access to the data for all of a school's orgs., follow the CANC example above, using the org. parent that corresponds to a school/center (such as 58XX) instead of CANC.
  • In BusinessObjects, Organization Code (SEC) and Organization Desc (SEC) are the school/center code and name associated with Organization Code (SEC).
  • In SQL, if you join DWSP.PENNERA_SECURITY s to DWADMIN.ORG_CODES o (WHERE s.ORGANIZATION_CODE=o.ORGANIZATION_CODE), o.CENTER_CODE and o.CENTER_NAME are the school/center code and name associated with s.ORGANIZATION_CODE.
• PENNERA_SECURITY has two different unique identifiers for a role: ROLE_ID (the alphanumeric code used internally by the PennERA system) and ROLE_NAME (the Office of Research Services name for the role). Because its values are relatively short, it is usually easier to use ROLE_ID in WHERE statements (filters); because its values are easier to understand, it is usually better to use ROLE_NAME when displaying role information in a report.

Source
The Proposal modules of PennERA, used at Penn since October 14, 2003 to track information on proposed and awarded grants and contracts.

PennERA is the University's electronic research administration system, providing support to faculty and research administrators for all sponsored project activity at the University of Pennsylvania.

PENNERA_SECURITY Table - Data Element Index   Tables and Data Elements   PennERA Proposals Home   Data Warehouse Home