Background
The QBO Security Module comprises the following concepts:
Person: a person is a user that accesses the system.
Email addresses are typically used as the Person's username
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
e.g. someone@mycompany.com is a member of the Managers role
People can be a member of multiple roles
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
This might get out of hand since there are several hundred possible class C IP address ranges in the US. But the sample below can get you started.
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.ashx
Under Allowed IPs panel go to options -> Add IP Address
for System Address put in 1.6.% to allow all IPs in the 1.6.* address ranges
for Applies to Role select the Manager Role you want to grant access
Ensure that the Exclude checkbox is unchecked
After clicking save you would need to go back and add other IP ranges to allow access such as:
1.7.% to allow 1.7.*.* ranges and 1.22.% to allow 1.22.*.* ranges
Use case: someone@mycompany.com may access from IP address 193.169.169.1
Navigate to Security/SystemAddress.ashx
Under 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 Implementation
QBO 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 Tier
The 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:
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 Tables
Process-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:
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