Dynamic Custom Fields and the RGFQ

Part of the problem with Custom Fields is that they're so custom! So they change. A lot. Meaning reporting on them isn't exactly easy without a lot of work. So here's a little snippet I cooked up with Alex's help to query the members of a tag with their values for any custom fields that happen to be on the Tag:

DECLARE @profile_id INT
SET @profile_id = 350


DECLARE @fields VARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX), @parameters NVARCHAR(MAX)


SELECT @fields=COALESCE(@FIELDS+',','')+'['+x.title+']'
FROM
(SELECT DISTINCT ccf.title
FROM
core_profile_member_field AS cpmf
JOIN core_profile AS cpro ON cpro.profile_id=cpMf.profile_id
JOIN core_profile_member AS cpm ON cpm.profile_id=cpro.profile_id
LEFT OUTER JOIN core_profile_member_field_value AS cpmfv ON cpmfv.custom_field_id=cpmf.custom_field_id AND cpmfv.profile_id=cpmf.profile_id AND cpmfv.person_id=cpm.person_id
JOIN core_person AS cp ON cp.person_id=cpm.person_id
JOIN core_custom_field AS ccf ON ccf.custom_field_id=cpmf.custom_field_id
WHERE cpro.profile_id=@profile_id
) AS x

SET @sql = 'SELECT *
FROM
(
SELECT cp.person_id, cp.last_name+'', ''+cp.first_name AS [person_name], ccf.title, REPLACE(ISNULL(cpmfv.selected_value,''''),''^'','' '') AS selected_value
FROM core_profile_member_field AS cpmf
JOIN core_profile AS cpro ON cpro.profile_id=cpMf.profile_id
JOIN core_profile_member AS cpm ON cpm.profile_id=cpro.profile_id
LEFT OUTER JOIN core_profile_member_field_value AS cpmfv ON cpmfv.custom_field_id=cpmf.custom_field_id AND cpmfv.profile_id=cpmf.profile_id AND cpmfv.person_id=cpm.person_id
JOIN core_person AS cp ON cp.person_id=cpm.person_id
JOIN core_custom_field AS ccf ON ccf.custom_field_id=cpmf.custom_field_id
WHERE cpro.profile_id = @profileID
) AS s
PIVOT
(
MAX(selected_value)
FOR [title] IN ('+@fields+')
) AS p'

SELECT @sql

SET @parameters = '@profileID INT'

EXEC sp_executesql @sql=@sql, @params=@parameters, @profileID=@profile_id

There's actually a lot of room for expansion from this point, including adding custom field groups, an active-only option, or adapting it to work for Groups/Missions/Appointments/etc. Maybe I'll come back and post some of those later.

Who is "AS-IP" and why is he in my DB?

Random tidbit for the day: if you're looking around in secu_permission and you see the username "AS-IP", that's the user for the Cascade Security buttons throughout Arena. Not sure why that username was chosen, there's probably something back in dark antiquity that makes sense, but heck this is just an information post, not an explanation.

Security Laws

I just ran across the Ten Immutable Laws Of Security from TechNet (Link):

1. If a bad guy can persuade you to run his program on your computer, it's not solely your computer anymore.
2. If a bad guy can alter the operating system on your computer, it's not your computer anymore.
3. If a bad guy has unrestricted physical access to your computer, it's not your computer anymore.
4. If you allow a bad guy to run active content in your website, it's not your website any more.
5. Weak passwords trump strong security.
6. A computer is only as secure as the administrator is trustworthy.
7. Encrypted data is only as secure as its decryption key.
8. An out-of-date antimalware scanner is only marginally better than no scanner at all.
9. Absolute anonymity isn't practically achievable, online or offline.
10. Technology is not a panacea.

Several of these pop out in the context of Arena.

#4 is the reason I hate iframes for anything that doesn't come from your servers. It's also why Arena has built in approval systems for pretty much everything that gets posted as content and I very much frown upon any attempts to ignore/bypass that approval process.

#5 is the reason I encourage everyone to increase the minimum requirements on the password complexity RegEx. 8+ minimum characters is still fairly reasonable for a password, and if you're doing online giving or anything involving money my password in your system is going to run the full gamut of upper & lower case, alphanumeric, special characters, and 16+ character length options, but a combination of #5 and #7 means my password security doesn't really matter if an administrator with  "pass1" as their password gets compromised. While you can't view their actual passwords, you can view the salted hash result in SQL, and I highly encourage you to go create a login on a security-less record and then rotate through a couple of common passwords comparing the hash of that user's results to other passwords in your database (basically a smaller version of a rainbow table attack, but the goal is to force people to have good security). If you can guess their password in the first 20 or so that you come up with (suggestion: "password" + month numbers), they need to change that.

#6 is one that I think applies in two ways. First: it is categorically impossible to completely prevent your Arena Administrator (read: person in charge of administrating Arena, not just the security role) from being able to view something in the database (such as SSNs, Counseling records, or even secured notes) as long as they have access to the Security Roles area. But you can't lock them out of Security Roles because then they'd be unable to do their job. So if you don't trust someone 100% (I'd say 110% but then my OCD kicks in), don't make them your database administrator. Second: There is such a thing as a system that is too secure. You should have a record in your system first name "HitBy", last name "ABus", with an inactive record status. This record should have an active login with a long and complex username and password, should be a Global Admin, and that login's information should be stored away in a firesafe somewhere off-site. If you only have one administrator who can access your SQL database to get at that information, you really need a backup access mechanism in case that person leaves suddenly (for any reason, voluntary or not) and you really need access to your data immediately.

Cascade Pages... And Modules!

So Arena's been able to Cascade Permissions on a bunch of things for a while, but what's been missing the entire time has been Module permissions when cascading Page permissions. Well, this sproc update will change that:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[secu_sp_cascadePagePermissions]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[secu_sp_cascadePagePermissions]
GO

CREATE PROC [dbo].[secu_sp_cascadePagePermissions]
@parent INT,
@OrganizationID int

AS

DECLARE @page INT

DELETE FROM dbo.secu_permission
WHERE object_type=2 AND object_key in (SELECT module_instance_id FROM port_module_instance WHERE page_id=@parent AND organization_id=@OrganizationID) and organization_id = @OrganizationID

INSERT INTO dbo.secu_permission (created_by, modified_by, object_type, object_key, operation_type, subject_type, subject_key, organization_id)
SELECT DISTINCT 'AS-IP', 'AS-IP',2, module_instance_id, operation_type, subject_type, subject_key, @OrganizationID
FROM dbo.secu_permission AS sp
JOIN dbo.port_module_instance AS pmi ON pmi.page_id=@parent AND sp.object_type=1 AND pmi.organization_id=sp.organization_id
WHERE sp.object_key=@parent
AND sp.organization_id = @OrganizationID

DELETE FROM dbo.secu_permission
WHERE object_type = 2 AND object_key IN (SELECT child.module_instance_id FROM port_module_instance AS child JOIN port_module_instance AS parent ON parent.module_instance_id=child.parent_module_instance_id AND parent.organization_id = child.organization_id WHERE parent.page_id = @parent AND parent.organization_id =@OrganizationID) AND organization_id = @OrganizationID

INSERT INTO dbo.secu_permission (created_by, modified_by, object_type, object_key, operation_type, subject_type, subject_key, organization_id)
SELECT DISTINCT 'AS-IP', 'AS-IP',2, child.module_instance_id, operation_type, subject_type, subject_key, @OrganizationID
FROM dbo.secu_permission AS sp
JOIN dbo.port_module_instance AS pmi ON pmi.page_id=@parent AND sp.object_type=1 AND pmi.organization_id=sp.organization_id
JOIN dbo.port_module_instance AS child ON child.parent_module_instance_id=pmi.module_instance_id AND child.organization_id=pmi.organization_id
WHERE sp.object_key=@parent
AND sp.organization_id = @OrganizationID

DECLARE PageCursor CURSOR READ_ONLY FOR
WITH pageTree (page_id) AS (
SELECT page_id
FROM port_portal_page
WHERE organization_id=@OrganizationID
AND parent_page_id=@parent
UNION ALL
SELECT p.page_id
FROM port_portal_page AS p
JOIN pageTree AS t ON t.page_id=p.parent_page_id
WHERE p.organization_id=@OrganizationID)
SELECT page_id FROM pageTree;

OPEN PageCursor

FETCH NEXT FROM PageCursor
INTO @page

WHILE (@@FETCH_STATUS <> -1)
BEGIN

 IF (@@FETCH_STATUS = 0)
 BEGIN

  DELETE FROM dbo.secu_permission
  WHERE object_type=1 AND object_key=@page and organization_id = @OrganizationID

  INSERT INTO dbo.secu_permission (created_by, modified_by, object_type, object_key, operation_type, subject_type, subject_key, organization_id)
  SELECT 'AS-IP', 'AS-IP',object_type, @page, operation_type, subject_type, subject_key, @OrganizationID
  FROM dbo.secu_permission
  WHERE object_type=1
  AND object_key=@parent
  AND organization_id = @OrganizationID

  DELETE FROM dbo.secu_permission
  WHERE object_type=2 AND object_key in (SELECT module_instance_id FROM port_module_instance WHERE page_id=@page AND organization_id=@OrganizationID) and organization_id = @OrganizationID

  INSERT INTO dbo.secu_permission (created_by, modified_by, object_type, object_key, operation_type, subject_type, subject_key, organization_id)
  SELECT DISTINCT 'AS-IP', 'AS-IP',2, module_instance_id, operation_type, subject_type, subject_key, @OrganizationID
  FROM dbo.secu_permission AS sp
  JOIN dbo.port_module_instance AS pmi ON pmi.page_id=@page AND sp.object_type=1 AND pmi.organization_id=sp.organization_id
  WHERE sp.object_key=@parent
  AND sp.organization_id = @OrganizationID

  DELETE FROM dbo.secu_permission
  WHERE object_type = 2 AND object_key IN (SELECT child.module_instance_id FROM port_module_instance AS child JOIN port_module_instance AS parent ON parent.module_instance_id=child.parent_module_instance_id AND parent.organization_id=child.organization_id WHERE parent.page_id=@page AND parent.organization_id = @OrganizationID) AND organization_id = @OrganizationID

  INSERT INTO dbo.secu_permission (created_by, modified_by, object_type, object_key, operation_type, subject_type, subject_key, organization_id)
  SELECT DISTINCT 'AS-IP', 'AS-IP',2, child.module_instance_id, operation_type, subject_type, subject_key, @OrganizationID
  FROM dbo.secu_permission AS sp
  JOIN dbo.port_module_instance AS pmi ON pmi.page_id=@page AND sp.object_type=1 AND pmi.organization_id=sp.organization_id
  JOIN dbo.port_module_instance AS child ON child.parent_module_instance_id=pmi.module_instance_id AND child.organization_id=pmi.organization_id
  WHERE sp.object_key=@parent
  AND sp.organization_id = @OrganizationID

 END

 FETCH NEXT FROM PageCursor
 INTO @page

END

CLOSE PageCursor
DEALLOCATE PageCursor

Don't go hunting with Reflection

So, apparently .NET's reflection doesn't like Properties that are marked Private. Oops. That's three hours of my life and a few forehead-shaped indentations in my desk I'd like back. Just thought I'd share. Carry on.