The QBO Security Module comprises the following concepts:
- Person: a person is a user that accesses the system.
- System Function: a functional unit of the system
- e.g. Loan Insert is a system function that adds a loan to the database
- e.g. Message Update is a system function that changes the content of a message
- System Roles: roles are a group of users
- e.g. Administrators is a default role that has permission to all System Functions
- e.g. Power Users can be a role granted permission to modify various templates and rules
- e.g. Managers can be a role granted permission to update or approve things that regular users cannot
- System Member: maps System Roles to People
- System Permission: maps System Roles to System Functions
- e.g. Administrators is a default role that has permission to all System Functions
- e.g. Power Users can be a role granted permission to modify various templates and rules
- e.g. Managers can be a role granted permission to update messages, etc.
- System Address: maps the IP addresses or address ranges allow on a Person or System Role basis
- e.g. Administrators can access from any IP address
- e.g. No one may access from IP addresses registered outside the United States
- e.g. Managers may access from IP addresses registered in India
- e.g. someone@mycompany.com may access from IP address 193.169.169.1
- System Access: enable one-time login to non-users of the system
- e.g. Email a hyperlink to a document
- e.g. Email an investor requesting approval of a loan modification
- e.g. Provide ongoing public access to a subset of attachments
Adding a Person
To add a Person to a QBO system:
- Navigate to Design > Security > People
- Click on the New Person command
- Person: enter their username, typically their email address
- Active: click on the Active checkbox to enable this account
- Name: enter their first, middle and last names
- Email: click on the New Email icon, and enter their work email address
- Click Save
Note that users can only log in if the corresponding Person has the Active checkbox checked. This enables easy deactivition of user accounts without deleting references to a user's activity throughout the database.
Administrators may set other users passwords. To immediately set their password:
- Click the "Change Password" command
- Enter their new password
- Confirm their new password
- Click the "Reset Password" button
Adding a System Role
To add a System Role:
- Navigate to Design > Security > System Roles
- Click on the New Role command
- System Role: enter the name of the new System Role (e.g. "Managers", without the quotes)
- Click Save
Adding a System Member
One can associate People with System Roles two way: by adding a System Role to a Person, or by adding a Person to a System Role.
To map a System Role to a Person:
- Navigate to Design > Security > People
- Search for the username of the Person you wish to modify
- Click on the name of the Person from the search results
- Click on the Roles panel, and choose Options > Add Role
- Select the System Role from the dropdown list
- Click Save
To map a Person to a System Role
- Navigate to Design > Security > System Roles
- Search for the name of the System Role you wish to modify
- Click on the name of the System Role from the search results
- Click on the Add Person command
- Select the Person from the dropdown list
- Click Save
Granting System Permissions
To grant a System Role permissions to execute a System Function
- Navigate to Design > Security > System Roles
- Search for the name of the System Role you wish to modify
- Click on the name of the System Role from the search results
- Click on the Permissions panel > Options > Grant Permissions
- To Grant Permission to a single System Function:
- Click on the Single Function radio button
- Select the System Function from the dropdown list
- Click Save
- To Grant Permission to multiple System Functions at once:
- Click on the Multiple Functions radio button
- Enter the pattern of System Function names to Grant Permission to
- e.g. "Message%" grants permission to all functions related to the Message Module
- e.g. "%Insert" grants permission to all functions that involve inserting rows into the database
- Click Save
Adding System Address restrictions
Here is a few samples:
Use case: granting administrators access from any IP address
- Navigate to
Security/SystemAddress.ashx - Under Allowed IPs panel go to options -> Add IP Address
- for System Address put in % to allow all IPs
- for Applies to Role select Adminstrators from the drop down control
- Ensure that the Exclude checkbox is unchecked
Use case: Prohibit access from outside the United States
- Navigate to
Security/SystemAddress.ashx - Under Allowed IPs panel go to options -> Add IP Address
- for System Address put in 3% to allow all IPs in the 3.* address ranges
- for Applies to Role leave it as All Roles
- Ensure that the Exclude checkbox is unchecked
- After clicking save you would need to go back and add other IP blocks to allow access such as:
- 4% to allow 4.*.*.* address ranges and 5.1.94% to allow 5.1.94.* address ranges
Use case: managers may access from IP addresses registered in India
Similar caveat to the one above. It is best to find the exact IPs that your team is using and restrict access just to those.
Navigate to Security/SystemAddress.ashxUnder Allowed IPs panel go to options -> Add IP Address- for System Address put in 193.169.169.1
- for Person select the Person you want to provide access via the drop down
- Ensure that the Exclude checkbox is unchecked
Password Control
Password strength, reuse, and lockout after failed login attempts can be controlled via application and security settings. See Application Settings below.
Password Strength
Password strength is controlled via the "qbo.Security.PasswordRE" application setting. QBO enforces password strength via regular expressions so that each system can be configured independently. The default regular expression requires a minimum of 6 characters, without any required special characters or uppercase/lowercase mix.
Password Reuse Company password policies often dictate the frequency with which a user may reuse a password. For example, "the user may not reuse a given password within a 12 month timespan". Password resue is controlled via the "qbo.Security.PasswordReuseMonths". The default is NULL, which does not prevent password reuse.
Lockout after Failed Login Attempts If you wish to lock an account out after X number of failed login attempts, this can be configured from:
- Design > Configuration > Application Configuration
- Create a key called "qbo.Security.LockAttempts", and set the number of failed attempts that should cause a user to be locked out.
The default is 0, which never locks a user out for failed login attempts.
One-Time Access
There are situations where business requirements dictate granting access to a specific record to an entity that may not be a member of the Person table. Examples include:
- Emailing a document to a requesting party
- Enabling the public to view Foreclosure Notices of Default
- Requesting investor approval for a loan modification
In such cases, the SecurityAccess table can be configured to provide seamless but highly restricted access to record(s) without the need to set up user credentials.
Extranet Security
Granting access to specific rows of data is referred to as "Extranet Security". Extranet Security enabling clients, vendors, and other business partners to log into the same web site, but see different rows of data. For example, the organization assigned to manage a Process should see their processes only, and should not be able to see processes assigned to other organizations.
Note that to see any user-data record records, a user must either:
- Be granted explicit access to a group (e.g. Organization) associated with the record, or
- Be a member of a "Universal Access" System Role
This means that users that should be limited to seeing just their Organization's records must not be a member of a Universal Access System Role.
To set up Extranet Security for an organization from Address Book > Organizations:
- Set up user accounts as described above
- Navigate to Address Book > Organizations
- Search for the Organization that the user(s) should have access to
- Click on the name of the Organization from the search results
- Click on the Security tab / option for the Organization
- Click on the New Access command
- Select the user that should be granted access from the Person dropdown list
- Click Save
Alternatively, to set up Extranet Security for an organization from the Security > People
- Navigate to Security > People
- Search for the Person to be granted access to an Organization
- Click on the name of the Person from the search results
- Click on the Extranet tab / option for the Person
- Click on the New Access command
- Select "Organization" from the Group dropdown list
- Select the organization name from the Organization dropdown list
- Click Save
Extranet Security ImplementationQBO databases implement an "extranet security model" in the data tier. The extranet security model is intended to allow QBO systems to expose a single website to multiple organizations, and restrict those organizations to viewing only rows that they are associated with.
Each QBO table's foreign keys can contribute to the extranet security model. For example, a Loan table has keys including ServicerID, AgencyID, AssignedOrganizationID and AssignedPersonID. When a Loan row is added, all uses associated with the Servicer, Agency, AssignedOrganization and of course the AssignedPerson should have access to the Loan.
Things get trickier, however, when child table rows are added. For example, a Foreclosure table has a parent foreign key Foreclosure.LoanID. Anyone with access to the parent Loan should also be able to see the child Foreclosure. Likewise, anyone with access to the Foreclosure must be able to see it's parent Loan.
For performance reasons, QBO implements the bulk of the burden of maintaining this security model during inserts and updates, ensuring that selects have the fastest performance while still maintaining row-level security.
Data TierThe key data tier objects that implement this model are:
- ObjectAccess: table containing rows that grant row-level access to a group
- PersonAccess: table mapping users to groups
- {Object} table triggers: populate ObjectAccess rows automatically
- p{Object}ExtranetSync: modifies the ObjectAccess table to synchronize with all foreign keys
- p{Object}Search|List|Select: ensures the user has access to the {Object} rows returned
- dbo.UniversalAccess: UDF that returns system roles that should have access to everything
Web Tier:The web tier includes the following extranet security related components:
- /Security/PersonAccessList.aspx: displays PersonAccess rows for a group table (e.g. Organization)
- /Security/ObjectAccessList.aspx: displays ObjectAccess rows for a user data table (e.g. Loan)
- /Security/PersonAccessEdit.aspx: enables manual maintenance of the PersonAccess table
- /Security/ObjectAccessEdit.aspx: enables manual maintenance of the ObjectAccess table
Loan Example:When adding a row to the Loan table, the following steps occur:
- pLoanInsert stored procedure adds a row to Loan
- LoanEIServicerID trigger adds a row to ObjectAccess representing the Server's access to the Loan
- also grants Servicer access to the Loan's parent, Property
- LoanEIAgencyID trigger adds a row to ObjectAccess representing the Agency
- also grants Agency access to the Loan's parent, Property
- LoanEIAssignedOrganizationID trigger adds a row to ObjectAccess representing the assigned organization
- also grants AssignedOrganization access to the Loan's parent, Property
- LoanEIAssignedPersonID trigger adds a row to ObjectAccess representing the assigned person
- also grants AssignedPerson access to the Loan's parent, Property
- LoanEIParent: grants everyone with parental access to the Property access to the child Loan
- LoanEIUniversalAccess: grants all UniveralAccess system roles access to the Loan
At this stage, the ObjectAccess table looks something like the following:
Object | ObjectID | GroupObject | GroupObjectID | SourceTable | SourceColumn | SourceObjectID |
Loan | {LoanID} | Organization | {ServicerID} | Loan | ServicerID | {LoanID} |
Loan | {LoanID} | Organization | {AgencyID} | Loan | AgencyID | {LoanID} |
Loan | {LoanID} | Organization | {Assigned OrganizationID} | Loan | Assigned OrganizationID | {LoanID} |
Loan | {LoanID} | Person | {Assigned PersonID} | Loan | Assigned PersonID | {LoanID} |
Loan | {LoanID} | SystemRole | {SystemRoleID} | | UniversalAccess | |
Property | {PropertyID} | Organization | {ServicerID} | Loan | ServicerID | {LoanID} |
Property | {PropertyID} | Organization | {AgencyID} | Loan | AgencyID | {LoanID} |
Property | {PropertyID} | Organization | {Assigned OrganizationID} | Loan | Assigned OrganizationID | {LoanID} |
Property | {PropertyID} | Person | {Assigned PersonID} | Loan | Assigned PersonID | {LoanID} |
Loan | {LoanID} | {Some Table} | {Some key} | Property | {Some Column} | {PropertyID} |
... | ... | ... | ... | ... | ... | ... |
Note the color coding:
- Black: groups with a direct association with the Loan
- maintained by LoanEI{Foreign Key} triggers
- Green: groups with an indirect association Property (by way of the Loan)
- maintained by LoanEI{Foreign Key} triggers
- Purple: copies of the ObjectAccess rows for the Loan's Property
- maintained by the LoanEIParent trigger
- Red: universal access rows
- maintained by the LoanEIUniversalAccess trigger
So far this example has covered only inserts, not updates or deletes. Fortunately, updates and deletes are logically much easier. In short:
- LoanEUServicerID: modifies all ObjectAccess rows when changing the Loan.Servicer.
- affects rows have:
- SourceTable = 'Loan',
- SourceColumn = 'ServicerID',
- SourceObjectID={LoanID}
- LoanEUAgencyID:
- affects rows have:
- SourceTable = 'Loan',
- SourceColumn = 'AgencyID',
- SourceObjectID={LoanID}
- LoanEUAssignedOrganizationID:
- affects rows have:
- SourceTable = 'Loan',
- SourceColumn = 'AssignedOrganizationID',
- SourceObjectID={LoanID}
- LoanEUAssignedPersonID:
- affects rows have:
- SourceTable = 'Loan',
- SourceColumn = 'AssignedPersonID',
- SourceObjectID={LoanID}
Deletes of the Loan table follow the same pattern as updates; they delete ObjectAccess rows where the SourceTable, SourceColumn, and SourceObjectID match the deleted record(s).
ObjectAccess Data Definitions: - Object: table name of row being granted access to (e.g. Loan)
- ObjectID: primary key (identity) of row being granted access to (e.g. LoanID)
- GroupObject: table name of group being given access to Object (e.g. Organization)
- GroupObjectID: primary key (identity) of row being granted access (e.g. OrganizationID)
- SourceTable: table name containing a foreign key column that caused the row to be added
- SourceColumn: column name of foreign key that caused the row to be added
- IsParent: flag indicating if this row was granted by an ancestor of the record, or record itself
- HasSelect: flag indicating Select permissions
- HasInsert: flag indicating Insert permissions
- HasUpdate: flag indicating Update permissions
- HasDelete: flag indicating Delete permissions
Process-based TablesProcess-based tables are tables that essentially inherit Process properties. From a database perspective, this simply means the table has a ProcessID in it. From an application tier perspective, it means there is a 1:1 relationship between a process-based table and the Process table. The process table includes Sibling and SiblingID columns to "point back" to the derived table.
For example, the Foreclosure and Bankruptcy tables are Process-based. Looking in aggregate, the Foreclosure table has several foreign keys that need to affect the Foreclosure table:
- Foreclosure.ClientID: keyed to Organization
- Process.AssignedOrganizationID: keyed to Organization
- Process.AssignedPersonID: keyed to Person
This means that the Process table extranet triggers are "special"; they populate ObjectAccess rows for the Process table as described above. In addition, they populate ObjectAccess rows for the Sibling table. For example, a Foreclosure insert would result in:
Object | ObjectID | GroupObject | GroupObjectID | SourceTable | SourceColumn | SourceObjectID |
Foreclosure | {ForeclosureID} | Organization | {OrganizationID} | Foreclosure | ClientID | {ForeclosureID} |
Process | {ProcessID} | Organization | {Assigned OrganizationID} | Process | Assigned OrganzationID | {ProcessID} |
Process | {ProcessID} | Person | {Assigned PersonID} | Process | Assigned PersonID | {ProcessID} |
Foreclosure | {ForeclosureID} | Organization | {Assigned OrganizationID} | Process | Assigned OrganizationID | {ProcessID} |
Foreclosure | {ForeclosureID} | Person | {Assigned PersonID} | Process | Assigned PersonID | {ProcessID} |
... | ... | ... | ... | ... | ... | ... |
Note the color coding:
- Black: follows the standard pattern described above
- Red: "extra" rows added to ObjectAccess by the Process table triggers
- leverage Process.Sibling | SiblingID to figure out Object | ObjectID
Generic Tables:QBO contains several "generic" modules that are not foreign key constrained to parent tables. Examples include the Message, Milestone, Attachment, Decision and Score tables. Each of these generic tables include a Object and ObjectID column pointing to the parent record. For these tables, the extranet security pattern current will adopt a "if you have access to the parent, you have access to the generic object" philosophy.
Application Settings
The following application settings (Design > Configuration > Application Settings, or /Config/AppSettings.config) apply to the security module.
General settings:
- qbo.SecurityWeb.Services.Message.Email.EmailService: determines the web service that sends emails
- qbo.Security.SecurityAdminRole: defines the SystemRole membership required to perform advanced security functions (defaults to 'Administrators')
- qbo.Authentication: defines the authentication mechanism used; options are 'Application', 'Windows', or 'None'. Use 'Application'.
- qbo.Security.SupportEmail: defines the 'from' address in emails generated by the security module
- qbo.Security.PasswordReset.FromAddress: defines the 'from' address in password recovery emails
Login page details:
- qbo.LoginHeader: custom login message:
- qbo.BaseUrl: base url to include in emails:
- qbo.Security.LicenseXslt: determines whether a license must be accepted
- qbo.Security.ShowIPAddress: whether to display the user's IP address on the login screen
- qbo.Security.PINRequired: whether a PIN is required
Registration details:
- qbo.LoginPassword: whether to show the "Password Reset" hyperlink on the Login page
- qbo.LoginRegister: whether to show the "Register" hyperlink on the Login page
- qbo.Security.PersistPassword: setting whether a password is saved across sessions
- qbo.Security.LockAttempts: number of failed login attempts before a lockout
- qbo.Security.LockTime: how long to keep an account locked out
Registration details:
- qbo.RegistrationInstructionsXsltPath: registration instructions XSLT
- qbo.Security.Register.MakeActive: whether to make a new registrant active
- qbo.SystemRoleID: system role to automatically add a new person to
Password Changing details:
- qbo.Security.PasswordRE: regular expression controlling password strength
- qbo.Security.PasswordError: password strength error message
- qbo.Security.PasswordReservedWords: reserved (disallowed) words
- qbo.Security.PasswordReuseMonths: setting password reuse time frame
- qbo.Security.PasswordEmail: whether to email user when passwords are changed
- note: password reset email body is controlled by /Templates/Security/PasswordResetEmail.xslt