Post date: Mar 30, 2012 1:34:17 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:
The ExtrantDefault setting for the module must be changes, and if the behavior
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
)