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:
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
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.