Extranet Settings in DbStatements

posted Mar 30, 2012, 6:34 AM by Eric Patrick   [ updated Mar 13, 2015, 3:02 PM ]
The Extranet Security pattern enables control of row-level access by user.

The SqlPattern class supports the following Extranet clauses:
  • Basic: there must be an ObjectAccess row matching the object 
  • Generic: there must be an ObjectAccess row matching the object's Object/ID
  • Group: there must be a PersonAccess row matching the object
  • Template: reserved for future use
  • Custom: a filter named 'Extranet' is used in lieu of the statements above
  • Default: uses Configuration.ExtranetDefault if defined, which is Basic by default
Normally, Extranet.Default should be used by your DbStatements. Each object's configuration file can specify an ExtranetDefault attribute:

<!-- Loan does not specify ExtranetDefault, so Basic will be assumed -->
<Loan Type="qbo.Mortgage.LoanObject, qbo.Mortgage" BaseTransform="Templates/Mortgage/">

<!-- Contact will use Extranet.Generic for statements that use Extranet.Default -->
<Contact Table="Contact" Type="qbo.Contact.ContactObject, qbo.Contact" ExtranetDefault="Generic" BaseTransform="Templates/Contact/">

<!-- Organization will use Extranet.Group for statements that use Extranet.Default -->
<Organization Table="Organization" Type="qbo.Contact.OrganizationObject, qbo.Contact" ExtranetDefault="Group" ...>

How A Statement's Extranet Setting Is Applied

If a statement does not have an {Extranet.*} expression in it, there is no "extra" security applied to the statement.

If a statement has an {Extranet.*} expression in it, there might be "extra" security applied. Specifically:
  • If the user is not a member of a role that has Universal Access, "extra" security is applied
  • If the class has UniversalAccessEnabled=false, "extra" security is applied
  • Otherwise, no extra security is applied
"Extra" security applied means that the {Extranet.*} expression will be replaced by a sub-query. Which sub-query is used to replace the expression depends on the '*' portion of {Extranet.*}.  Examples are below.

Taking the Abstract.config Select statement as an example:

<Statement Name="SelectBasic" Query="SELECT {Select.Basic} FROM {From.Basic} WHERE {Table}.{Table}ID IN ({Where.IdList}) {Extranet.Default}"/>

For the Loan table, ExtranetDefault = Basic, producing:

AND EXISTS 
(
SELECT 1
FROM ObjectAccess WITH (NOLOCK)
INNER JOIN PersonAccess WITH (NOLOCK)
ON PersonAccess.GroupObjectID = ObjectAccess.GroupObjectID
AND PersonAccess.GroupObject = ObjectAccess.GroupObject
AND PersonAccess.PersonID = @UserID
WHERE ObjectAccess.ObjectID = [Loan].[LoanID]
AND ObjectAccess.Object = 'Loan'
AND ObjectAccess.HasSelect = 1
)

For the Contact table, ExtranetDefault = Generic, producing:

AND EXISTS 
(
SELECT 1
FROM ObjectAccess WITH (NOLOCK)
INNER JOIN PersonAccess WITH (NOLOCK)
ON PersonAccess.GroupObjectID = ObjectAccess.GroupObjectID
AND PersonAccess.GroupObject = ObjectAccess.GroupObject
AND PersonAccess.PersonID = @UserID
WHERE ObjectAccess.ObjectID = [Contact].ObjectID
AND ObjectAccess.Object = [Contact].Object
AND ObjectAccess.HasSelect = 1
)

For the Organization table, ExtranetDefault = Group, producing:

AND EXISTS 
(
SELECT 1
FROM PersonAccess WITH (NOLOCK)
WHERE PersonAccess.GroupObjectID = [Organization].[OrganizationID]
AND PersonAccess.GroupObject = 'Organization'
AND PersonAccess.PersonID = @UserID
)

Customizing Extranet Behavior

Two things must be accomplished to customize extranet behavior:
  1. The ExtrantDefault setting for the module must be changes, and if the behavior 
  2. An Extranet filter must be created (or modified) for the module
Use case: restrict Matrix access to most users, but not administrators.

Changing the ExtranetDefault setting can be accomplished by creating a ConfigurationEntry row along the following lines:

// Change the Matrix to use ExtranetDefault="Custom"
INSERT INTO ConfigurationEntry (
ConfigurationEntry, 
Catelog, 
Source, 
ConfigurationType, 
ConfigurationKey, 
ConfigurationXml, 
CreatedPersonID, 
CreatedDate, 
UpdatedPersonID, 
UpdatedDate
)
SELECT 
'Matrix',
'{myclient.quandis.net}',
'Matrix.config',
'qbo.Application.Configuration.ObjectConfiguration',
'Matrix',
'<Matrix ExtranetDefault="Custom" />',
1,
GETDATE(),
1,
GETDATE()

Modifying the Extranet filter from Design > Configuration > Modules > Matrix > Filters, along these lines:

AND EXISTS 
(
SELECT 1
FROM Matrix
WHERE Matrix.CreatedPersonID = @UserID

UNION

SELECT 1
FROM SystemRole
INNER JOIN SystemMember
ON SystemMember.SystemRoleID = SystemRole.SystemRoleID
AND SystemMember.PersonID = @UserID
WHERE SystemRole.SystemRole = 'Administrators'

UNION

SELECT 1
FROM ObjectAccess WITH (NOLOCK)
INNER JOIN PersonAccess WITH (NOLOCK)
ON PersonAccess.GroupObjectID = ObjectAccess.GroupObjectID
AND PersonAccess.GroupObject = ObjectAccess.GroupObject
AND PersonAccess.PersonID = @UserID
WHERE ObjectAccess.ObjectID = [Matrix].[MatrixID]
AND ObjectAccess.Object = 'Matrix'
AND ObjectAccess.HasSelect = 1
)


Comments