Registration

Overview

The QBO Security module include a standard registration feature that can be easily extended to handle custom use cases.  The Person/Register method will:
  • create a new Person row, and
  • send a registration email
This does not actually grant the registered user access to the system.  To do that, either:
  1. an existing user (typically a member of a security administrator role) must activate their account and make them a member of one or more roles, or
  2. a custom registration statement can be executed, which may manipulate the database in whatever manner is required

REST API

// Create a user jdoe@acme.com
Security/Login.ashx/Register?Person=jdoe@acme.com

// Create a user with more info
Security/Login.ashx/Register?Person=jane.doe@acme.com&FirstName=Jane&LastName=Doe&Title=CEO

Use Case: Extending the Registration Feature for Service Attorney Tracking System

A client required that users specify a bank identifier upon registration, and if that bank identifier was valid, the user be automatically activated, become a member of a role, and be granted access to the Organization record associated with the bank identifier.  This requirement was handled by creating a custom statement: Person/RegisterServicer.  This statement used PersonID and Servicer parameters to handle the requirement.

// Create a user and execute custom registration logic. 
// Note that the custom statement is RegisterServicer, but the RegistrationType is merely 'Servicer'. QBO prepends with 'Register'.
Security/Login.ashx/Register?Person=jane.doe@acme.com&RegistrationType=Servicer

Use Case: Extending Registration for a Multi-Tenant Installation

Typical QBO multi-tenant installation base each tenant on an Organization. Several themes support registration using this construct, including QCM and QRM. A security administrator uses the User panel in Organization/Summary to register new users. The Person/RegisterProcessor statement handles this:

DECLARE @OrganizationID int = @GroupObjectID, @ContactID int   
IF @OrganizationID IS NULL BEGIN
SELECT TOP 1 @OrganizationID = GroupObjectID
FROM PersonAccess
WHERE GroupObject = 'Organization'
AND PersonID = @PersonID
END
IF @OrganizationID IS NULL BEGIN
  INSERT INTO Contact (Contact, CreatedDate, CreatedPersonID) VALUES (@Organization, GETDATE(), @PersonID)
  SELECT @ContactID = SCOPE_IDENTITY()
  INSERT INTO Organization (Organization, ContactID) VALUES (@Organization, @ContactID)
  SELECT @OrganizationID = SCOPE_IDENTITY()
END

-- Grant access to the Organization
INSERT INTO PersonAccess (PersonID, GroupObject, GroupObjectID, SourceObject, SourceObjectID)
SELECT @PersonID, 'Organization', @OrganizationID, 'Person', @PersonID WHERE NOT EXISTS (SELECT 1 FROM PersonAccess WHERE PersonID = @PersonID AND GroupObject = 'Organization' AND GroupObjectID = @OrganizationID)

IF @SystemRoleID = '0'
BEGIN
  -- Add to the Processor role
  INSERT INTO SystemMember (PersonID, SystemRoleID, IsDefault, CreatedDate, UpdatedDate) 
  SELECT @PersonID, SystemRoleID, 1, GETDATE(), GETDATE() FROM SystemRole WHERE SystemRole = 'Processor' AND NOT EXISTS (SELECT 1 FROM SystemMember WHERE SystemMember.PersonID = @PersonID AND SystemMember.SystemRoleID = SystemRole.SystemRoleID)

  -- Add to the Security Administrator role, if needed
  INSERT INTO SystemMember (PersonID, SystemRoleID, IsDefault, CreatedDate, UpdatedDate) 
  SELECT @PersonID, SystemRoleID, 0, GETDATE(), GETDATE()
  FROM SystemRole 
  WHERE SystemRole = 'Security Administrator'
 AND NOT EXISTS (
 SELECT 1 
 FROM PersonAccess
 INNER JOIN SystemRole
 ON SystemRole.SystemRole = 'Security Administrator'
 INNER JOIN SystemMember
 ON SystemMember.SystemRoleID = SystemRole.SystemRoleID
 AND SystemMember.PersonID = PersonAccess.PersonID
 WHERE
 PersonAccess.GroupObjectID = @OrganizationID
 AND PersonAccess.GroupObject = 'Organization'
 )

  -- Add to the Manager role, if needed
  INSERT INTO SystemMember (PersonID, SystemRoleID, IsDefault, CreatedDate, UpdatedDate) 
  SELECT @PersonID, SystemRoleID, 0, GETDATE(), GETDATE()
  FROM SystemRole 
  WHERE SystemRole = 'Manager'
 AND NOT EXISTS (
 SELECT 1 
 FROM PersonAccess
 INNER JOIN SystemRole
 ON SystemRole.SystemRole = 'Manager'
 INNER JOIN SystemMember
 ON SystemMember.SystemRoleID = SystemRole.SystemRoleID
 AND SystemMember.PersonID = PersonAccess.PersonID
 WHERE
 PersonAccess.GroupObjectID = @OrganizationID
 AND PersonAccess.GroupObject = 'Organization'
 )
END
ELSE
BEGIN
  -- Add to the specified role(s)
  INSERT INTO SystemMember (PersonID, SystemRoleID, IsDefault, CreatedDate, UpdatedDate) 
  SELECT @PersonID, SystemRoleID, 1, GETDATE(), GETDATE() FROM SystemRole WHERE SystemRoleID IN ({Param.SystemRoleID}) AND NOT EXISTS (SELECT 1 FROM SystemMember WHERE SystemMember.PersonID = @PersonID AND SystemMember.SystemRoleID = SystemRole.SystemRoleID)
END

Use Case: Extending the Registraton Feature for Vendor Registration

The Quandis Forms Hub allows companies to present any sort of form (task) to the outside world on a secure, neutral site (forms.quandis.net). In order for a user to submit a form, the user must register. Some banks wish to:
  • automate the approval process for users with an email address coming from a recognized "good" domain (white list)
  • automate the disapproval process for users with an email address coming from a recognized "bad" domain (black list)
  • leave the approval process as manual for anyone else
The following Person/RegisterDomain statement handles this, assuming a company has set up their Registration Whitelist and Blacklist as ContactMethod rows:

DECLARE @B int, @W int 
SELECT @B = ContactTemplateID FROM ContactTemplate WHERE ContactTemplate = 'Registration Blacklist' 
SELECT @W = ContactTemplateID FROM ContactTemplate WHERE ContactTemplate = 'Registration Whitelist' 
 
DECLARE @Domain nvarchar(50) SELECT @Domain = SUBSTRING(Person, CHARINDEX('@', Person) + 1, 255) FROM Person WHERE PersonID = @PersonID  
 
INSERT INTO SystemMember (PersonID, SystemRoleID, IsDefault, CreatedDate, UpdatedDate)   
SELECT @PersonID, SystemRoleID, 1, GETDATE(), GETDATE()  
FROM Organization 
 INNER JOIN SystemRole  
  ON SystemRole = Organization + ' Vendor' 
WHERE  
 EXISTS ( 
  -- No whitelist defined 
  SELECT 1 
  WHERE NOT EXISTS ( 
   SELECT 1 FROM Contact WHERE Contact.ObjectID = Organization.OrganizationID AND Contact.Object = 'Organization' AND Contact.ContactTemplateID = @W 
  ) 
  UNION ALL 
 
  -- Whitelist 
  SELECT 1  
  FROM Contact 
   INNER JOIN ContactMethod 
    ON ContactMethod.ContactID = Contact.ContactID 
     AND ContactMethod.ContactValue = @Domain 
  WHERE 
   Contact.ObjectID = Organization.OrganizationID 
   AND Contact.Object = 'Organization' 
   AND Contact.ContactTemplateID = @W 
 ) AND NOT EXISTS ( -- Blacklisted 
  SELECT 1  
  FROM Contact 
   INNER JOIN ContactMethod 
    ON ContactMethod.ContactID = Contact.ContactID 
     AND ContactMethod.ContactValue = @Domain 
  WHERE 
   Contact.ObjectID = Organization.OrganizationID 
   AND Contact.Object = 'Organization' 
   AND Contact.ContactTemplateID = @B 
 ) 
 AND NOT EXISTS ( 
 SELECT 1 
 FROM SystemMember  
 WHERE SystemMember.PersonID = @PersonID  
  AND SystemMember.SystemRoleID = SystemRole.SystemRoleID 
 )  
IF @@ROWCOUNT > 0 BEGIN 
 INSERT INTO SystemMember (PersonID, SystemRoleID, IsDefault, CreatedDate, UpdatedDate)   
 SELECT @PersonID, SystemRoleID, 1, GETDATE(), GETDATE()  
 FROM SystemRole 
 WHERE SystemRole.SystemRole IN ('Forms User', 'Documents User') 
END 

See Theme.forms > Config > Setup.HostedForms.xml for a full package to handle this use case.


Comments