API tools faq
paste
DataCCIW

cust_CCIW_v_secu_permission

DataCCIW
Jul 8th, 2019 (edited)
528
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 8.80 KB | None | 0 0
raw download clone embed print report
  1. USE [ArenaDB]
  2. GO
  3.  
  4. /****** Object:  View [dbo].[cust_CCIW_v_secu_permission]    Script Date: 8/19/2020 12:12:36 PM ******/
  5. SET ANSI_NULLS ON
  6. GO
  7.  
  8. SET QUOTED_IDENTIFIER ON
  9. GO
  10. -- =============================================
  11. -- Source: http://community.shelbysystems.com/arena/m/reports/4001
  12. -- Author: David Ellis/Tony Visconti
  13.  
  14. --2/14/19 Added Module Parent Page Column, Breadcrumb for Page Objects / Tony Visconti
  15. --8/18/20 Added Page Path for Module Instances / TV
  16. -- TODO: Add html to all html paths
  17. -- TODO: Add Attibute Group Details in relation to Attribute
  18.  
  19. -- =============================================
  20.  
  21.  
  22. ALTER VIEW [dbo].[cust_CCIW_v_secu_permission]
  23. AS
  24. WITH permissions_base as
  25. (
  26. SELECT
  27. sp.subject_key,
  28. (CASE
  29. WHEN sp.subject_type=0 THEN 'Role'
  30. WHEN sp.subject_type=1 THEN 'Person'
  31. END ) AS subject_type_value
  32. ,(CASE
  33. WHEN sp.subject_type=0 THEN (SELECT TOP 1 role_name FROM secu_role WHERE role_id=sp.subject_key)
  34. WHEN sp.subject_type=1 THEN (SELECT first_name+' '+last_name FROM core_person WHERE person_id=sp.subject_key)
  35. END ) AS subject_key_value
  36. ,sp.object_type
  37. ,sp.object_key
  38. ,(CASE
  39. WHEN sp.object_type=0 THEN (SELECT TOP 1 portal_name FROM port_portal WHERE portal_id=sp.object_key)
  40. WHEN sp.object_type=1 THEN (SELECT TOP 1 page_name FROM port_portal_page WHERE page_id=sp.object_key)
  41. WHEN sp.object_type=2 THEN (SELECT TOP 1 module_title FROM port_module_instance WHERE module_instance_id=sp.object_key)
  42. WHEN sp.object_type=3 THEN (SELECT TOP 1 profile_name FROM core_profile WHERE profile_id=sp.object_key)
  43. WHEN sp.object_type=4 THEN (SELECT TOP 1 attribute_name FROM core_attribute WHERE attribute_id=sp.object_key)
  44. WHEN sp.object_type=5 THEN (SELECT TOP 1 group_name FROM core_attribute_group WHERE attribute_group_id=sp.object_key)
  45. WHEN sp.object_type=6 THEN (SELECT TOP 1 title FROM mtrc_metric WHERE metric_id=sp.object_key)
  46. WHEN sp.object_type=7 THEN (SELECT TOP 1 report_name FROM list_save_reports WHERE report_id=sp.object_key)
  47. WHEN sp.object_type=8 THEN (SELECT TOP 1 cluster_name FROM smgp_group_cluster WHERE group_cluster_id=sp.object_key)
  48. WHEN sp.object_type=9 THEN (CASE WHEN sp.object_key=1 THEN 'Family Registrations' WHEN  sp.object_key=2 THEN 'Contributions' WHEN sp.object_key=3 THEN 'Mailing' END)
  49. WHEN sp.object_type=10 THEN 'Field: '+(CASE
  50.     WHEN sp.object_key=10 THEN 'Person ID'
  51.     WHEN sp.object_key=15 THEN 'Member Status'
  52.     WHEN sp.object_key=20 THEN 'Record Status'
  53.     WHEN sp.object_key=25 THEN 'Name'
  54.     WHEN sp.object_key=30 THEN 'Birth Date'
  55.     WHEN sp.object_key=35 THEN 'Age'
  56.     WHEN sp.object_key=40 THEN 'Gender'
  57.     WHEN sp.object_key=45 THEN 'Marital Status'
  58.     WHEN sp.object_key=50 THEN 'Anniversary Date'
  59.     WHEN sp.object_key=52 THEN 'Grade'
  60.     WHEN sp.object_key=55 THEN 'Contribute Individually'
  61.     WHEN sp.object_key=57 THEN 'Print Statement'
  62.     WHEN sp.object_key=58 THEN 'Envelope Number'
  63.     WHEN sp.object_key=60 THEN 'Medical Information'
  64.     WHEN sp.object_key=65 THEN 'Date Added'
  65.     WHEN sp.object_key=70 THEN 'Date Modified'
  66.     WHEN sp.object_key=75 THEN 'Date Verified'
  67.     WHEN sp.object_key=80 THEN 'Family Information'
  68.     WHEN sp.object_key=83 THEN 'Peers'
  69.     WHEN sp.object_key=85 THEN 'Relationships'
  70.     WHEN sp.object_key=87 THEN 'Photo'
  71.     WHEN sp.object_key=90 THEN 'Phones'
  72.     WHEN sp.object_key=92 THEN 'Emails'
  73.     WHEN sp.object_key=93 THEN 'SSN'
  74.     WHEN sp.object_key=95 THEN 'Addresses'
  75.     WHEN sp.object_key=96 THEN 'Background Check Section'
  76.     WHEN sp.object_key=100 THEN 'Activity Section'
  77.     WHEN sp.object_key=110 THEN 'Activity Level'
  78.     WHEN sp.object_key=120 THEN 'Area'
  79.     WHEN sp.object_key=180 THEN 'Serving Tags'
  80.     WHEN sp.object_key=190 THEN 'Ministry Tags'
  81.     WHEN sp.object_key=200 THEN 'Attendance Section'
  82.     WHEN sp.object_key=210 THEN 'Recent Attendance'
  83.     WHEN sp.object_key=220 THEN 'Attendance Rate'
  84.     WHEN sp.object_key=300 THEN 'Notes Section'
  85.     WHEN sp.object_key=400 THEN 'Personality Section'
  86.     WHEN sp.object_key=410 THEN 'Spiritual Gifts'
  87.     WHEN sp.object_key=420 THEN 'DISC Profiles'
  88.     WHEN sp.object_key=500 THEN 'History Section'
  89.     END)
  90. WHEN sp.object_type=11 THEN (SELECT '"'+LEFT(CONVERT(VARCHAR,(SELECT TOP 1 history FROM core_person_history WHERE person_history_id=sp.object_key)),12)+'..."')
  91. WHEN sp.object_type=12 THEN (SELECT TOP 1 [type_name] FROM core_document_type WHERE document_type_id=sp.object_key)
  92. WHEN sp.object_type=13 THEN (SELECT TOP 1 [name] FROM asgn_assignment_type WHERE assignment_type_id=sp.object_key)
  93. WHEN sp.object_type=14 THEN (SELECT TOP 1 [name] FROM prot_background_check_type WHERE background_check_type_id=sp.object_key)
  94. END ) AS object_key_value
  95. ,sp.operation_type
  96. ,(CASE
  97. WHEN sp.operation_type=0 THEN 'View'
  98. WHEN sp.operation_type=1 THEN 'Edit'
  99. WHEN sp.operation_type=2 THEN 'Edit Security'
  100. WHEN sp.operation_type=4 THEN 'Edit People'
  101. WHEN sp.operation_type=5 THEN 'Edit Modules'
  102. WHEN sp.operation_type=6 THEN 'Edit Notes'
  103. WHEN sp.operation_type=7 THEN 'Approve'
  104. WHEN sp.operation_type=8 THEN 'Edit Registration'
  105. END ) AS operation_type_value,
  106. sp.template_id,
  107. sp.permission_id,
  108. CASE WHEN
  109. (sp.object_type in (0,1,2,3,4,5,6,7,8,11,12,13,14) AND NOT
  110. (
  111.     sp.object_type=0 AND sp.object_key IN (SELECT portal_id FROM port_portal)
  112.     OR (sp.object_type=1 AND sp.object_key IN (SELECT page_id FROM port_portal_page))
  113.     OR (sp.object_type=2 AND sp.object_key IN (SELECT module_instance_id FROM port_module_instance))
  114.     OR (sp.object_type=3 AND sp.object_key IN (SELECT profile_id FROM core_profile))
  115.     OR (sp.object_type=4 AND sp.object_key IN (SELECT attribute_id FROM core_attribute))
  116.     OR (sp.object_type=5 AND sp.object_key IN (SELECT attribute_group_id FROM core_attribute_group))
  117.     OR (sp.object_type=6 AND sp.object_key IN (SELECT metric_id FROM mtrc_metric))
  118.     OR (sp.object_type=7 AND sp.object_key IN (SELECT report_id FROM list_save_reports))
  119.     OR (sp.object_type=8 AND sp.object_key IN (SELECT group_cluster_id FROM smgp_group_cluster))
  120.     OR (sp.object_type=11 AND sp.object_key IN (SELECT person_history_id FROM core_person_history))
  121.     OR (sp.object_type=12 AND sp.object_key IN (SELECT document_type_id FROM core_document_type))
  122.     OR (sp.object_type=13 AND sp.object_key IN (SELECT assignment_type_id FROM asgn_assignment_type))
  123.     OR (sp.object_type=14 AND sp.object_key IN (SELECT background_check_type_id FROM prot_background_check_type))
  124. )
  125. )
  126. THEN 1 END AS orphaned_object_key
  127. FROM secu_permission AS sp
  128.  
  129. ) --END of CTE
  130.  
  131. select
  132.     b.object_type,
  133.     (CASE
  134.     WHEN b.object_type=0 THEN 'Portal'
  135.     WHEN b.object_type=1 THEN 'Page'
  136.     WHEN b.object_type=2 THEN 'Module Instance'
  137.     WHEN b.object_type=3 THEN
  138.         CASE
  139.             WHEN CP.profile_type = 0 THEN 'Personal Tag'
  140.             WHEN CP.profile_type = 1 THEN 'Ministry Tag'
  141.             WHEN CP.profile_type = 2 THEN 'Serving Tag'
  142.             WHEN CP.profile_type = 4 THEN 'Event Tag'
  143.         END
  144.     WHEN b.object_type=4 THEN 'Attribute'
  145.     WHEN b.object_type=5 THEN 'Attribute Group'
  146.     WHEN b.object_type=6 THEN 'Metric'
  147.     WHEN b.object_type=7 THEN 'Report'
  148.     WHEN b.object_type=8 THEN 'Group Cluster'
  149.     WHEN b.object_type=9 THEN 'Application'
  150.     WHEN b.object_type=10 THEN 'Person Field'
  151.     WHEN b.object_type=11 THEN 'Person Note'
  152.     WHEN b.object_type=12 THEN 'Document Type'
  153.     WHEN b.object_type=13 THEN 'Assignment Type'
  154.     WHEN b.object_type=14 THEN 'Background Check Type'
  155.     END ) AS object_type_value,
  156.     b.object_key,
  157.     b.object_key_value,
  158.     b.subject_key,
  159.     b.subject_type_value,
  160.     b.operation_type,
  161.     b.operation_type_value,
  162. (CASE
  163. WHEN object_type=2 -- Module Instance
  164.     THEN pp.page_name + ' - '+ Cast(pp.page_id as varchar(10))
  165. ELSE 'NA'
  166. END) as [Module Parent Page],
  167. (CASE
  168. WHEN b.object_type=1 -- Page
  169.     THEN (SELECT [dbo].[port_funct_get_page_path](b.object_key,1) + ' - ' +  Cast(b.object_key as varchar(10)))
  170. WHEN b.object_type=2 -- Module Instance
  171.     THEN (SELECT [dbo].[port_funct_get_page_path](pp.page_id,1) + ' - ' +  Cast(pp.page_id as varchar(10)))
  172. WHEN b.object_type=3 -- Profile/Tag
  173.     THEN (SELECT dbo.cust_CCIW_funct_profile_path(b.object_key)) --returns plain text
  174. ELSE 'NA'
  175. END) as [Path_Text],
  176. (CASE
  177. WHEN b.object_type=1 -- Page
  178.     THEN (SELECT [dbo].[port_funct_get_page_path](b.object_key,1) + ' - ' +  Cast(b.object_key as varchar(10)))
  179. WHEN b.object_type=2 -- Module Instance
  180.     THEN (SELECT [dbo].[port_funct_get_page_path](pp.page_id,1) + ' - ' +  Cast(pp.page_id as varchar(10)))
  181. WHEN b.object_type=3 -- Profile/Tag
  182.     THEN (SELECT dbo.core_funct_profile_path(b.object_key,376,'.\default.aspx')) --returns html
  183. ELSE 'NA'
  184. END) as [Path_html],
  185. b.template_id,
  186. permission_id,
  187. b.orphaned_object_key
  188. from permissions_base b
  189. LEFT JOIN port_module_instance m on m.module_instance_id = b.object_key and object_type = 2
  190. LEFT JOIN port_portal_page pp on pp.page_id = m.page_id
  191. LEFT JOIN core_profile cp on cp.profile_id = b.object_key and b.object_type = 3
  192.  
  193. GO
  194.  
  195.  
  196.  
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!