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.

What about *last* Sunday?

An earlier (much earlier) post covered how to detect the next upcoming date that is a certain day of the week, and I just had a similar request for the last date it was a certain day of the week and cooked up this:

CREATE FUNCTION dbo.cust_funct_last_weekday
(@weekday INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @date DATETIME
SET @date=DATEADD(day,DATEDIFF(day,0,GETDATE()),0)

IF DATEPART(dw,@date)=@weekday
SET @date=DATEADD(day,-1,@date)
WHILE DATEPART(dw,@date)&lt;&gt;@weekday
SET @date=DATEADD(day,-1,@DATE)

RETURN @date
END

Note that this function accepts an integer and returns a date, so you specify the numeric day of the week (e.g. 1 = Sunday, 2 = Monday...7 = Saturday, although this is actually just the US default for a SQL setting called @@DATEFIRST (MSDN page)), and it returns the last Date that was that day of the week. If you specify the same day of the week as today, it returns the same day of the week from last week (i.e. it will not return today). Also, note that it returns the date with a midnight (00:00:00) time component, so you might need to massage your comparison to remove specific times, e.g. "WHERE core_occurrence.occurrence_start_time = dbo.cust_funct_last_weekday(1)" becomes "WHERE DATEADD(day,DATEDIFF(day,0,core_occurrence.occurrence_start_time),0) = dbo.cust_funct_last_weekday(1)".

This can be useful for some automated reporting, such as "I want a report in my inbox Monday morning showing our attendance numbers for Sunday's services" or "How many kids attended the Wednesday night service?", and a host of other "last N-day" questions (possibly even in lists...).

Security on a Portal

So the question came up for how to view the entire security model for the Pages and Module Instances of a Portal, and I realized that while we have a handful of security reports for other things, we don't have a generic query pulling security on those items. So I spent an hour and a half (probably shouldn't have taken me that long, but I'm a bit out of practice) and what I came up with shows the page structure (in order, just like in Pages) with the permissions for the page, and within each page it shows the modules on that page (in their template frames and in order within those frames) and the permissions on those module instances. It shows permissions as "Roles | Persons" in case you have person-level security on anything, and each of those is detailed as "Name (permissions)" where the permissions are outlined as “V”=View, “E”=Edit, ”S”=Edit Security, ”P”=Edit People, “M”=Edit Modules, “N”=Edit Notes, “A”=Approve, and “R”=Edit Registrations.

DECLARE @portalID INT,@a INT
SET @portalID=1

-- Pages
DECLARE @pagetable TABLE (page_id INT, page_name VARCHAR(500),permission_list VARCHAR(MAX),ordering INT)

;WITH pagetree (
Root
, Parent
, Name
, Level
, ID
, Branch
) AS (
SELECT
ppp.page_name
, CONVERT(VARCHAR(100),'')
, ppp.page_name
, 0
, ppp.page_id
, CONVERT(VARCHAR(MAX),REPLACE(STR(CONVERT(VARCHAR,ISNULL(NULLIF(ppp.page_order,2147483647),9999)), 5, 0), ' ', '0')+'.'+CONVERT(VARCHAR,ppp.page_id))
FROM
port_portal_page AS ppp
WHERE
ppp.page_id=(SELECT TOP 1 default_page_id FROM port_portal WHERE portal_id=@portalID)
UNION ALL
SELECT
tree.Root
, tree.Name
, CONVERT(VARCHAR(100),(replicate('-',tree.level+1))+ppp.page_name)
, tree.Level + 1
, ppp.page_id
, CONVERT(VARCHAR(MAX),tree.Branch + '.' + REPLACE(STR(CONVERT(VARCHAR,ISNULL(NULLIF(ppp.page_order,2147483647),9999)), 5, 0), ' ', '0')+'.'+CONVERT(VARCHAR,ppp.page_id))
FROM
port_portal_page AS ppp
JOIN pagetree AS tree ON ppp.parent_page_id = tree.ID
)

INSERT INTO @pagetable
SELECT
pt.ID AS page_id
,pt.Name
,ISNULL(SUBSTRING(
(SELECT DISTINCT
', '+sr.role_name+' ('
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=0) THEN 'V' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=1) THEN 'E' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=2) THEN 'S' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=5) THEN 'M' ELSE '' END)
+')'
FROM
secu_permission AS sp WITH(NOLOCK)
JOIN secu_role AS sr WITH(NOLOCK) ON sr.role_id=sp.subject_key AND sp.subject_type=0
WHERE
sp.object_type=1
AND sp.object_key = pt.ID
FOR XML PATH(''))
,3,5000),'')
+ISNULL(' | '+SUBSTRING(
(SELECT DISTINCT
', '+cp.first_name+' '+cp.last_name+' ('
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=0) THEN 'V' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=1) THEN 'E' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=2) THEN 'S' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=5) THEN 'M' ELSE '' END)
+')'
FROM
secu_permission AS sp WITH(NOLOCK)
JOIN core_person AS cp WITH(NOLOCK) ON cp.person_id=sp.subject_key AND sp.subject_type=1
WHERE
sp.object_type=1
AND sp.object_key = pt.ID
FOR XML PATH(''))
,3,5000),'')
,ROW_NUMBER() OVER (ORDER BY @a )
FROM
pagetree AS pt
ORDER BY
Root
, branch


-- Modules
DECLARE @moduletable TABLE (page_id INT, module_instance_id INT,module_title VARCHAR(500),module_type VARCHAR(500), module_url VARCHAR(500),template_frame_name VARCHAR(500),module_order INT, permission_list VARCHAR(MAX))

INSERT INTO @moduletable
SELECT
pmi.page_id
,pmi.module_instance_id
,pmi.module_title
,pm.module_name
,pm.module_url
,pmi.template_frame_name
,pmi.template_frame_order
,ISNULL(SUBSTRING(
(SELECT DISTINCT
', '+sr.role_name+' ('
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=0) THEN 'V' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=1) THEN 'E' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=2) THEN 'S' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=4) THEN 'P' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=6) THEN 'N' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=7) THEN 'A' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=8) THEN 'R' ELSE '' END)
+')'
FROM
secu_permission AS sp WITH(NOLOCK)
JOIN secu_role AS sr WITH(NOLOCK) ON sr.role_id=sp.subject_key AND sp.subject_type=0
WHERE
sp.object_type=2
AND sp.object_key = pmi.module_instance_id
FOR XML PATH(''))
,3,5000),'')
+ISNULL(' | '+SUBSTRING(
(SELECT DISTINCT
', '+cp.first_name+' '+cp.last_name+' ('
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=0) THEN 'V' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=1) THEN 'E' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=2) THEN 'S' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=4) THEN 'P' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=6) THEN 'N' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=7) THEN 'A' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=8) THEN 'R' ELSE '' END)
+')'
FROM
secu_permission AS sp WITH(NOLOCK)
JOIN core_person AS cp WITH(NOLOCK) ON cp.person_id=sp.subject_key AND sp.subject_type=1
WHERE
sp.object_type=2
AND sp.object_key = pmi.module_instance_id
FOR XML PATH(''))
,3,5000),'')
FROM port_module_instance AS pmi WITH(NOLOCK)
JOIN port_module AS pm WITH(NOLOCK) ON pm.module_id=pmi.module_id
WHERE page_id IN (SELECT page_id FROM @pagetable)

DECLARE @finaltable TABLE (ordering varchar(500), page_id INT,page_name VARCHAR(500),template_frame_name VARCHAR(500),module_instance_id INT, module_title VARCHAR(500),module_type VARCHAR(500), module_url VARCHAR(500),permission_list VARCHAR(MAX))
INSERT INTO @finaltable
SELECT REPLACE(STR(CONVERT(VARCHAR,pt.ordering), 9, 0), ' ', '0'),pt.page_id,pt.page_name,NULL AS template_frame_name,NULL AS module_instance_id, NULL AS module_title, NULL AS module_type, NULL AS module_url,pt.permission_list FROM @pagetable pt
UNION
SELECT (SELECT REPLACE(STR(CONVERT(VARCHAR,ordering), 9, 0), ' ', '0') FROM @pagetable WHERE page_id=mt.page_id)+CONVERT(VARCHAR,mt.module_order),mt.page_id,'zzzzzzzzzzzzz' AS page_name,mt.template_frame_name,mt.module_instance_id,mt.module_title,mt.module_type,mt.module_url,mt.permission_list FROM @moduletable mt

SELECT
page_id
,REPLACE(page_name,'zzzzzzzzzzzzz','') AS page_name
,template_frame_name
,module_instance_id
,module_title
,module_type
,module_url
,permission_list
FROM @finaltable
ORDER BY ordering

Event Discount Codes

On the same theme of rapidly reporting data from an Event, here's a sproc to report the Discount Codes that have been used in the current event. You can deploy it with a Report Grid From Query on the Event Details page (see Event Costs And Fees for how to set up a RGFQ on the Events page, just make sure you set Public Site = True since you dont need all the extras for this grid) with a single parameter of "profile=;".

CREATE PROCEDURE dbo.cust_sp_rgfq_evnt_applied_discount_codes
@profile VARCHAR(20)
AS
SELECT DISTINCT
cp.person_id
,cp.last_name+', '+cp.first_name AS Person_Name
,edc.code_value AS [Discount Code Applied]
,(CASE WHEN ereg.date_created<ereg.date_modified THEN ereg.date_modified ELSE ereg.date_created END) AS [Date Last Modified]
FROM
evnt_registrant AS er
JOIN evnt_discount_code AS edc ON edc.discount_code_id=er.discount_code_applied
JOIN core_person AS cp ON cp.person_id=er.person_id
JOIN evnt_registration AS ereg ON ereg.registration_id=er.registration_id
WHERE er.profile_id=@profile
ORDER BY [Discount Code Applied],Person_Name