Question

Help with SQL Query

Laserfiche Version 11 Self-Hosted

Updated January 30, 2024

asked on December 28, 2023 • Show version history

I'm trying to develop a SQL Query to give me the templates and fields associated with them in the repository, along with the groups that have access to those templates and what the field rights are for each group per template. It is hard with the table names to figure out which tables I need to join, but I have managed so far to get the templates, and the fields associated with each template in the query. I am having trouble finding the table or tables to join to list the groups and rights for them. Here is what I have so far:

SELECT DISTINCT Templates.pset_id      AS TemplateID,
           Templates.pset_name    AS TemplateName,
           TemplateFields.prop_id AS FieldID,
           FieldDef.prop_name     AS FieldName
          
      FROM MSIGPROD_REP.dbo.propval    AS FieldValues
      JOIN MSIGPROD_REP.dbo.propdef    AS FieldDef
        ON FieldValues.prop_id = FieldDef.prop_id
      JOIN MSIGPROD_REP.dbo.pset_props AS TemplateFields
        ON FieldValues.prop_id = TemplateFields.prop_id
     
      JOIN MSIGPROD_REP.dbo.propset    AS Templates
        ON TemplateFields.pset_id = Templates.pset_id 

		ORDER BY TemplateName, FieldName

I did find a groupslist table but all it has is a group id and a member id. I don't expect anyone to write the query for me, but any help on which table or tables I am missing would be VERY appreciated, thanks!

0 0

Replies

replied on December 28, 2023

OK posting an update here, made some progress, I do have a report now listing access but wanted to make sure I have something right, it seemed to me that the mask field was giving read-create-write access as a '7' and read-only access as a '1'. Is that correct? This is from the propacl table. Here is what I have:

SELECT DISTINCT Templates.pset_id      AS TemplateID,
                Templates.pset_name    AS TemplateName,
                TemplateFields.prop_id AS FieldID,
                FieldDef.prop_name     AS FieldName,
		   CASE
             WHEN
               FieldAccess.mask = '7'
             THEN
              'READ - CREATE - EDIT'
			 WHEN
               FieldAccess.mask = '1'
             THEN
              'READ ONLY'
             ELSE
              'FULL ACCESS'
             END AS 'Rights',
	     	   FieldAccess.sid AS DevNumber,
		       trustee_id AS TrusteeNumber,
	    	   trustee_name AS TrustName
          
            FROM MSIGPROD_REP.dbo.propval    AS FieldValues
			  JOIN MSIGPROD_REP.dbo.propdef    AS FieldDef
			   ON FieldValues.prop_id = FieldDef.prop_id

			  JOIN MSIGPROD_REP.dbo.pset_props AS TemplateFields
			   ON FieldValues.prop_id = TemplateFields.prop_id
     
		      JOIN MSIGPROD_REP.dbo.propset AS Templates
               ON TemplateFields.pset_id = Templates.pset_id 

	       	  JOIN MSIGPROD_REP.dbo.propacl AS FieldAccess
		       ON TemplateFields.prop_id = FieldAccess.prop_id 

		      JOIN MSIGPROD_REP.dbo.trustee AS Trustees
		       ON FieldAccess.sid = Trustees.sid
			   		
		    WHERE Trustees.trustee_name IN ('Operations', 'Underwriting', 'Internal Audit', 'Marketing', 'Underwriting Manager', 'Underwriting Home Office', 'Underwriting Admin')
			---WHERE Templates.pset_name = 'Policy Document'

		    ORDER BY TrustName, TemplateName, FieldName

You would have to edit that WHERE clause toward the bottom with the IN clause to put the name of the group(s) you want to check, or you can comment it out and use uncomment the bottom WHERE clause to put in the name of a particular template to check the rights. Hope this can help someone else to do this!

1 0

replied on January 30, 2024

Correct, a 1 in the mask field is read-only access, and 7 is read+write+write-on-create.

0 0

You are not allowed to follow up in this post.

Question

Question

Help with SQL Query

Replies

Sign in to reply to this post.