A report to know all the documents which are visible to a particular security role.

I'm trying to write an eql to filter out all the documents which have a particular role in them thus making it visible to all the users who are included in that role but there are no such fields for 'security' tab in the designer. Is this query possible?

Parents
  • Yes, you can query the Roles to accomplish this:

    START WITH Role 
    SELECT 
       Id,
       Name,
       SecurityRefs.Object.Id,
       SecurityRefs.Object.Label
    WHERE 
       SecurityRefs.Object.Type = 3
       AND Name = '[Name of Role]'
     ORDER BY
    SecurityRefs.Object.Label

    * * * * * 

    Unfortunately, all that you can return  from a Role query is the document id and the object label (title). However, you can use the id to generate a hyperlink to the document.

    If you want to see all the permissions you can output those, too. You can also add a parameter to select the role you want to view at runtime;

    START WITH Role 
    SELECT 
       Id,
       Name,
       SecurityRefs.Object.Id,
       SecurityRefs.Object.Label,
       CASE BITAND( SecurityRefs.Permission, 1 ) WHEN 1 THEN 'Yes' ELSE 'No' END pView,
       CASE BITAND( SecurityRefs.Permission, 2 ) WHEN 2 THEN 'Yes' ELSE 'No' END pModify,
       CASE BITAND( SecurityRefs.Permission, 4 ) WHEN 4 THEN 'Yes' ELSE 'No' END pApprove,
       CASE BITAND( SecurityRefs.Permission, 16 ) WHEN 16 THEN 'Yes' ELSE 'No' END pDelete 
    WHERE 
       SecurityRefs.Object.Type = 3
       AND Id = @Id 
    ORDER BY
    SecurityRefs.Object.Label 

    * * * * * 

    Use this search to populate the @Id parameter:

    START WITH Role 
    SELECT 
       Id col1,
       Name col2 
    WHERE 
       Name LIKE @StringValue 
    ORDER BY 
       Name

    * * * * * 

    Note that returning permission "1" (view) is not really necessary, because if the role doesn't have that, the document wont appear in the report anyway. It will never be "No". Also, the designer will want you to alias the names of the CASE statement columns, but if you use the word "View" as an alias, that will cause an error, so I prefixed it with "p". You can change it back on the format layout tab.

    Eric Rajala | Consultant

    Cohesive

    Email: eric.rajala@cohesivegroup.com

    www.bentley.com

       
    This is a test

    Answer Verified By: Devang Singh 

Reply
  • Yes, you can query the Roles to accomplish this:

    START WITH Role 
    SELECT 
       Id,
       Name,
       SecurityRefs.Object.Id,
       SecurityRefs.Object.Label
    WHERE 
       SecurityRefs.Object.Type = 3
       AND Name = '[Name of Role]'
     ORDER BY
    SecurityRefs.Object.Label

    * * * * * 

    Unfortunately, all that you can return  from a Role query is the document id and the object label (title). However, you can use the id to generate a hyperlink to the document.

    If you want to see all the permissions you can output those, too. You can also add a parameter to select the role you want to view at runtime;

    START WITH Role 
    SELECT 
       Id,
       Name,
       SecurityRefs.Object.Id,
       SecurityRefs.Object.Label,
       CASE BITAND( SecurityRefs.Permission, 1 ) WHEN 1 THEN 'Yes' ELSE 'No' END pView,
       CASE BITAND( SecurityRefs.Permission, 2 ) WHEN 2 THEN 'Yes' ELSE 'No' END pModify,
       CASE BITAND( SecurityRefs.Permission, 4 ) WHEN 4 THEN 'Yes' ELSE 'No' END pApprove,
       CASE BITAND( SecurityRefs.Permission, 16 ) WHEN 16 THEN 'Yes' ELSE 'No' END pDelete 
    WHERE 
       SecurityRefs.Object.Type = 3
       AND Id = @Id 
    ORDER BY
    SecurityRefs.Object.Label 

    * * * * * 

    Use this search to populate the @Id parameter:

    START WITH Role 
    SELECT 
       Id col1,
       Name col2 
    WHERE 
       Name LIKE @StringValue 
    ORDER BY 
       Name

    * * * * * 

    Note that returning permission "1" (view) is not really necessary, because if the role doesn't have that, the document wont appear in the report anyway. It will never be "No". Also, the designer will want you to alias the names of the CASE statement columns, but if you use the word "View" as an alias, that will cause an error, so I prefixed it with "p". You can change it back on the format layout tab.

    Eric Rajala | Consultant

    Cohesive

    Email: eric.rajala@cohesivegroup.com

    www.bentley.com

       
    This is a test

    Answer Verified By: Devang Singh 

Children
  • You are welcome. Two caveats to finish this topic. If you have any documents with no security applied, they are completely open (all permissions) to every user, but they will not appear on this report.

    If a specific view permission is not applied, but others are, you WILL see 'No' in the view column. In this case, it usually means that all users can view the document. It could also mean that the permission settings are inconsistent with each other, in which case you will see a warning about security in Director.

    Also, the report doesn't include document templates, if you want to look at those, templates are object type 188, not 3.

    Eric Rajala | Consultant

    Cohesive

    Email: eric.rajala@cohesivegroup.com

    www.bentley.com

       
    This is a test

  • Thanks Eric, I also have a doubt regarding search. Is it possible to search in different elements then merge the columns in result in the same eql query? eg here Im trying to search under 'Roles' and another one in 'Documents', can I merge different select columns (exclusive) in a single eql say in 'Roles' and get a result with columns from 'Documents' as well?

  • No. You'd have to export the reports to an Excel workbook and merge them there. What "merging" is possible you can see with the intellisense.in the report designer. For example, if a report begins START WITH Document you can, of course, return the document Id, as with every object. But you can also get the Ids for related objects such as Tags, Physical items, Change Requests and etc.:  PhysicalItems.PhysicalItem.Id, Tags.Tag.Id, Projects.Project.Id, . . . Similarly, you can retrieve related document Ids from a report that START[s] WITH one of these objects - Documents.Document.Id.. Then you can merge on the Ids in Excel. You could also merge on other values that are available in both reports, but Ids are always present at a minimum. So on a START WITH Role report, "SecurityRefs.Object.Id" is the document id WHERE  SecurityRefs.Object.Type = 3. On a Role object report, all you can see from the related objects are the Ids and the Labels (aka the title in the case of a document),

    If you want to do it all on the server side, then you will need SSRS (Microsoft SQL Server Reporting Services).

    Eric Rajala | Consultant

    Cohesive

    Email: eric.rajala@cohesivegroup.com

    www.bentley.com

       
    This is a test